ugrás a tartalomhoz

SQL Join-ban szűrés

shark300 · 2011. Aug. 21. (V), 21.47
Sziasztok!

Egyszerű adatbázis utasítással lehetséges lenne-e az összes termosztátot kilistázni, és a hozzá tartozó utolsó hőmérséklet-et? Mert eddig csak arra találtam módot, hogy listázom a termosztátokat, és utána a maximális hőmérsékletet a termosztátokhoz.
DDL:

CREATE TABLE `temperature` (
  `temp_id` int(11) NOT NULL AUTO_INCREMENT,
  `therm_id` int(11) NOT NULL,
  `temp_current` DOUBLE NOT NULL,
  `date` timestamp NOT NULL,
  PRIMARY KEY (`temp_id`),
  KEY `term_id` (`therm_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16607333 ;

CREATE TABLE `thermostat` (
  `therm_id` int(11) NOT NULL AUTO_INCREMENT,
  `therm_name` varchar(50) NOT NULL,
  PRIMARY KEY (`therm_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;

ALTER TABLE `temperature`
  ADD CONSTRAINT `temperature_ibfk_3` FOREIGN KEY (`therm_id`) REFERENCES `thermostat` (`therm_id`),
Segítségeteket köszönöm!
‹ Adatbázison belüli tábla tükrözés, azonos tábla használata (prestashop) fizetési egyenleg lekérdezése ›
 ■
1

Félek, hogy rosszul értem,

H.Z. v2 · 2011. Aug. 22. (H), 07.40
Félek, hogy rosszul értem, mit is szeretnél, de

SELECT a.therm_id,a.therm_name,MAX(b.temp_current) FROM thermostat a,temperature b WHERE a.therm_id=b.therm_id GROUP BY a.therm_id,a.therm_name;

Nem ezt akarod? (100%-ig nem vagyok biztos benne, hogy szintaktikailag OK, rég játszottam SQL-lel :( )
2

Válaszod köszönöm! Lehet

shark300 · 2011. Aug. 22. (H), 08.18
Válaszod köszönöm!

Lehet elég kevés infót adtam meg.
Szóval:
Vannak a termosztátok (thermostat táblában felsorolva), amelyek 5 percenként mintát vesznek, és beszúrnak egy új rekordot a temperature táblába.
Azt kéne megoldanom, hogy az összes termosztátot kilistázni, és mellé az utoljára mért hőmérsékletet is, és nem akarom feltétlenül a termosztátokat kilistázni, és utólag, új selecttel hozzá venni a legutolsó hőmérsékletet.
Arra gondoltam, hogy inner join-nal összekötöm a termosztátonként grouppolt hőmérsékleteket, csak ugye a sellistában nem állhat nem aggregált attribútum a group attribútuma mellett (pl. MAX(date), temp_id).
Vagy rendezett, limit 1-gyel ellátott selecttel kapcsolom össze.

Sajnos egyre inkább úgy érzem, hogy amit szeretnék, egyszerű sql-ben nem lehet megcsinálni, lehet, mivel postgres a szerver, írok rá egy függvényt.
3

Bocs, szokás szerint csak

H.Z. v2 · 2011. Aug. 22. (H), 08.54
Bocs, szokás szerint csak félig olvastam el amit írtál. :(
(most az átlagosnál is hülyébb vagyok, mert dokihoz készülök és ahogy a múltkor elnéztem, a doki sem áll jobban agyilag, mint én :( )

Esetleg a HAVING nem segít? (tartok tőle, hogy nem)

Ehhh... azt hiszem, most jobb lesz, ha eldugulok.
4

Szerintem alaptalanul vannak

Hidvégi Gábor · 2011. Aug. 22. (H), 09.29
Szerintem alaptalanul vannak önértékelési gondjaid.
22

Francokat... amit itt

H.Z. v2 · 2011. Aug. 22. (H), 16.24
Francokat... amit itt hajnalban elkövettem... Részeg még életemben nem voltam, de így utólag kb. ilyesmire saccolom azt a "spicces"-nek nevezett állapotot... :DDD
(hűűű... ezt a mai dokit szívesen elmesélném valakinek... azt hittem, egészségügyben engem már nem érhet meglepetés. Tévedtem. Szóval nem volt alaptalan a reggeli agybajom :( :D )
5

Én valami ilyesmit csinálnék.

bb0072 · 2011. Aug. 22. (H), 11.20
Én valami ilyesmit csinálnék. MySQL-ben ez vagy valami hasonló biztosan működne, postgre-ben nem vagyok biztos.

SELECT therm_id, therm_name, temp_current
FROM (
      SELECT therm.therm_id, therm_name, temp_current
      FROM thermostat therm
          LEFT JOIN temperature temp ON (therm.therm_id = temp.therm_id)
      ORDER BY date DESC
     )
GROUP BY therm_id
6

Pont úgy egyikben sem fog

kuka · 2011. Aug. 22. (H), 12.06
Pont úgy egyikben sem fog működni. MySQL és PostgreSQL is megköveteli, hogy az al-lekérdezéseknek legyen aliasa.

Ami az al-lekérdezés order by-ját illeti, tök fölösleges, mert MySQL és PostgreSQL is külső group by hatására átrendezi az adatokat a feldolgozás során.

(Az on feltétel körüli kerek zárójelek szerintem csak az olvashatóságot rontják.)
7

Pont úgy egyikben sem fog

bb0072 · 2011. Aug. 22. (H), 12.14
Pont úgy egyikben sem fog működni. MySQL és PostgreSQL is megköveteli, hogy az al-lekérdezéseknek legyen aliasa.


Ebben lehet, hogy igazad van, nem néztem utána, de ez csak kis módosítás.

Ami az al-lekérdezés order by-ját illeti, tök fölösleges, mert MySQL és PostgreSQL is külső group by hatására átrendezi az adatokat a feldolgozás során.


Nem fölösleges. A külső group valóban átrendezi az adatokat, de hogy jó legyen az eredmény, a belső querybe kell az order. MySQL-nél legalábbis az történik ilyenkor, hogy mivel van group, de nincs aggregátum, a sorok közül a group by-nak megfelelő, fizikailag első találat (sor) kerül az eredménybe. Ez a fizikailag első találat pedig a belső order by miatt a legutóbb felvitt érték lesz az adott termosztáthoz. Mondom, postgre-ben nem tudom ez így van-e.
9

Utánanéztem, igazad van. Kell

bb0072 · 2011. Aug. 22. (H), 12.20
Utánanéztem, igazad van. Kell az alias :
 
SELECT therm_id, therm_name, temp_current  
    FROM (  
          SELECT therm.therm_id, therm_name, temp_current  
          FROM thermostat therm  
              LEFT JOIN temperature temp ON (therm.therm_id = temp.therm_id)  
          ORDER BY date DESC  
         ) AS tmp_view
    GROUP BY therm_id
13

Ah, szóval arra pályáztál.

kuka · 2011. Aug. 22. (H), 12.41
Ah, szóval arra pályáztál. Most már értem, úgy tényleg nem fölösleges. PostgreSQLt illetően
ERROR: Attribute tmp_view.therm_name must be GROUPed or used in an aggregate function
Én ehhez vagyok szokva, ezért nem ugrott be a trükköd lényege.
14

Igen, én is úgy vettem észre,

bb0072 · 2011. Aug. 22. (H), 13.00
Igen, én is úgy vettem észre, hogy a postgre szigorúbb a group by tekintetében. Ami nem aggregátum, azt mind fel kell sorolni a group by részben. Így pedig a megoldásom valóban nem fog működni postgre-ben. Marad a ronda, egymásba egyázott subquery-k tömkelege, vagy a termosztátonkénti lekérdezés.
15

Ami nem aggregátum, azt mind

kuka · 2011. Aug. 22. (H), 13.22
Ami nem aggregátum, azt mind fel kell sorolni a group by részben.
Vagy úgy, vagy nem kell őket felsorolni, azaz group by helyett distinct ont használunk:

SELECT    DISTINCT
       ON (therm_id)
          therm_id, therm_name, temp_current    
FROM      (    
          SELECT    therm.therm_id, therm_name, temp_current    
          FROM      thermostat therm
          LEFT JOIN temperature temp
                 ON therm.therm_id = temp.therm_id
          ORDER BY  date
               DESC    
          ) AS tmp_view
Elméletileg működik, de csak régi teszt adatokkal próbáltam.
16

Ezt érdemes lenne kipróbálni!

bb0072 · 2011. Aug. 22. (H), 13.45
Ezt érdemes lenne kipróbálni! Érdekel, hogy működik-e.
18

Megnéztem, jól működik,

shark300 · 2011. Aug. 22. (H), 15.43
Köszönöm válaszod!
Megnéztem, jól működik, azonban az "mindent-joinolok-mindennel" miatt 43 mp a futási ideje lokális gépen, szerveren pedig a jóval több adat miatt 2 perc után állítottam le.
20

Még azt próbáld meg, hogy a

bb0072 · 2011. Aug. 22. (H), 16.00
Még azt próbáld meg, hogy a belső selectet egy view táblaként definiálod, majd abból kérdezel le. Meg ne felejtsd el a megfelelő indexeket a on-ban, a where-ben és az order by-ban lévő mezőkre. Ha még így is használhatatlanul lassú lesz, akkor elmegyek Tibetbe szerzetesnek. ;))
27

30 mp és 45 mp. Ne menj el

shark300 · 2011. Aug. 23. (K), 10.52
30 mp és 45 mp. Ne menj el ilyesmi miatt, nagyon sok az adat, egy picit hiányos adatstruktúrával.
8

(Az on feltétel körüli kerek

bb0072 · 2011. Aug. 22. (H), 12.16
(Az on feltétel körüli kerek zárójelek szerintem csak az olvashatóságot rontják.)


Kivéve akkor, ha több feltétel is van az on-ban
10

Próbáld ezt!

gabesz666 · 2011. Aug. 22. (H), 12.31
Ha nem lesz törlés a temperature táblából, akkor a következő megoldás működhet:

SELECT thermostat.*, temperature.temp_current FROM thermostat LEFT JOIN temperature ON (thermostat.therm_id = temperature.therm_id AND temperature.temp_id = MAX(temperature.temp_id));
11

Szerintem ez nem lesz jó. A

bb0072 · 2011. Aug. 22. (H), 12.36
Szerintem ez nem lesz jó. A MAX(temperature.temp_id) az egész temperature tábla legfrissebb rekordjának id-je lesz, ez egyetlen termosztátot fog listázni, amelyik a legutoljára küldött adatokat a db-be. (Azt az egyet viszont helyes temp_current értékkel fogja.)
12

Pontosítok

gabesz666 · 2011. Aug. 22. (H), 12.39
Nem teszteltem, szar is lett :) A helyes megoldás teszt után:

SELECT thermostat . * , temperature.temp_current
FROM thermostat
LEFT JOIN temperature ON ( thermostat.therm_id = temperature.therm_id
AND temperature.temp_id = ( 
SELECT MAX( temp_id ) 
FROM temperature
WHERE therm_id = thermostat.therm_id ) )
17

Köszönöm válaszod! 14 mp

shark300 · 2011. Aug. 22. (H), 15.42
Köszönöm válaszod!
14 mp lokális gépen, 36 mp szerveren, LIMIT 50-nel a WHERE feltételben 25 mp. 22 millió rekord van a temperature táblában, ezzel kéne valami csinálni.
19

22 millió rekord van a

bb0072 · 2011. Aug. 22. (H), 15.55
22 millió rekord van a temperature táblában, ezzel kéne valami csinálni


Pl. archiválni az 1 napnál régebbieket?
21

Sajnos máshol van rá

shark300 · 2011. Aug. 22. (H), 16.12
Sajnos máshol van a régi adatokra is szükség... Már több helyre ki lett helyezve a kód, nem lehet a táblákon változtatni. De próbálok definiálni egy view-t.
23

1000bocs, ha a szokásos

H.Z. v2 · 2011. Aug. 22. (H), 16.44
1000bocs, ha a szokásos figyelmetlenségem, de index van legalább a where-ben szereplő mezőkre? Ha van, explain plan (pgsql-nek is van ilyen?) szerint használja őket?

Asszem, Oracle forrásból származó tipp: particionált index? Létezik ilyen postgresql-ben?

(remélem, most nem írok nagy marhaságot... :-) )
24

Legutolsó id

gabesz666 · 2011. Aug. 22. (H), 23.55
Érdemes volna akkor a termosztátokhoz tartozó legutolsó temp_id-ket tárolni egy külön táblában. Ez lényegesen lecsökkentené a subquery idejét!
26

Még konzultálok az többi

shark300 · 2011. Aug. 23. (K), 10.50
Még konzultálok az többi fejlesztővel, de szerintem ez lesz. Köszönöm!
25

Mindenhol index, és nézet

shark300 · 2011. Aug. 23. (K), 10.49
Mindenhol index, és nézet tábla:

CREATE OR REPLACE VIEW "last_temp" AS 
 SELECT max(temp_id) AS max
   FROM temperature
  GROUP BY therm.id;
Így ezt az temperature-rel inner és a thermostattal left outer joinolva lokális gépen 5 mp, szerveren 7 mp.
Ez már elég elfogadható.