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`),


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ázisok

csirip

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 :( )

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Szerintem alaptalanul vannak

Hidvégi Gábor · 2011. Aug. 22. (H), 09.29

Szerintem alaptalanul vannak önértékelési gondjaid.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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 )

A hozzászóláshoz regisztráció és belépés szükséges
új téma

É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


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

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.)

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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

 
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

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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


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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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. ;))

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

(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

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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));


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));

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.)

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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 ) )


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 ) )

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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?

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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... :-) )

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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!

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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!

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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;


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ó.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Weblabor

Keresés