Záludné SQL

Honza

Záludné SQL
« kdy: 28. 07. 2011, 14:47:19 »
Ahoj,
  potřeboval bych vyřešit záludný SQL dotaz.

Mám MySQL tabulku se sloupci:

dt - datetime
sn - varchar
value - varchar

do které se ukládají měřená data. Do sloupečku dt čas měření, do sn sériové číslo zařízení a do value naměřená hodnota. A nyní k dotazu. Potřebuji zjistit JEDNÍM SQL dotazem unikátní seznam všech seriových čísel, a k nim pouze nejvyšší hodnotu měření (tj. poslední měření) a naposledy známou naměřenou hodnotu. Ne vždy se podaří všechna zařízení změřit, tedy časy mohou být odlišné, ale v jeden čas neexistují dvě stejná sn.

Výsledek by měl vypadat asi takto:

sn, dt, value
00001, 10:30:01, 50
00008, 08:30:01, 30
00020, 10:30:01, 50
...
tj. nesmí být ve výsledku např. řádka:
00001, 09:30:01, 48

Jediné co mě napadá, je SELECT * from table GROUP BY sn,dt ORDER BY sn,dt DESC a pak
vyfiltrovat všechny položky pro které bude dt nižší než maximum...

SQL dotaz bude v MySQL.

Víte někdo jak toto vyřešit? Zkoušel jsem joiny, group_concat, ale nic nevedlo k tíženému výsledku.

Díky,
Honza



Sipi

Re: Záludné SQL
« Odpověď #1 kdy: 28. 07. 2011, 14:56:54 »
mysql neovladam ale v ms sql by to slo takto

Kód: [Vybrat]
Select a.sn ,a.dt,b.value
From (
 Select sn,max(dt) as dt
 From table
 Group by sn ) a
Join table b On a.sn=b.sn and a.dt=b.dt

Honza

Re: Záludné SQL
« Odpověď #2 kdy: 28. 07. 2011, 15:04:16 »
Tak to vypadá, že to fakt funguje. Boží, a co navíc je to i rychlý. Projíždím tabulku s 5M řádků, a dotaz cca 5 sek. Samozřejmě mám index (sn,dt)  :)

Moc děkuji.

Honza

BoneFlute

  • *****
  • 1 981
    • Zobrazit profil
Re: Záludné SQL
« Odpověď #3 kdy: 28. 07. 2011, 22:25:09 »
Otázkou je, zda se ti ten index vůbec použije. Protože MySQL trpí chybou, že je neumí použít v poddotazech.
A 5s u 5M řádcích, je opravdu hodně.

Honza

Re: Záludné SQL
« Odpověď #4 kdy: 28. 07. 2011, 22:37:42 »
Ahoj,
  tak ne 5 sek, ale 170 ms, omlouvám se, koukal jsem na špatný čas. Mě by stačilo i těch 5 sek. Jinak indexy to používá... Ve výpise níže mám ještě jeden join, ale to je jedno.

Kód: [Vybrat]
+----+-------------+------------+--------+-----------------------------+---------+---------+-----------------+------+--------------------------+
| id | select_type | table      | type   | possible_keys               | key     | key_len | ref             | rows | Extra                    |
+----+-------------+------------+--------+-----------------------------+---------+---------+-----------------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                        | NULL    | NULL    | NULL            | 2455 |                          |
|  1 | PRIMARY     | b          | ref    | i_time_device,i_serial,i_st | i_st    | 138     | a.serial,a.time |    1 |                          |
|  1 | PRIMARY     | c          | eq_ref | PRIMARY                     | PRIMARY | 4       | inv.b.device    |    1 |                          |
|  2 | DERIVED     | probe      | range  | NULL                        | i_st    | 130     | NULL            | 2456 | Using index for group-by |
+----+-------------+------------+--------+-----------------------------+---------+---------+-----------------+------+--------------------------+
4 rows in set (0.09 sec)


Honza


kuka

Re: Záludné SQL
« Odpověď #5 kdy: 28. 07. 2011, 23:01:07 »
Otázkou je, zda se ti ten index vůbec použije. Protože MySQL trpí chybou, že je neumí použít v poddotazech.
A 5s u 5M řádcích, je opravdu hodně.

Pouzije se urcite, jinak by full scan tabulky, nasledny sort (bez ktereho group by de facto nejde udelat) a pak jeste join tech maxim opet bez indexu byl pro 5M radek za 5s dost nerealny.

Kit

Re: Záludné SQL
« Odpověď #6 kdy: 29. 07. 2011, 11:45:28 »
Otázkou je, zda se ti ten index vůbec použije. Protože MySQL trpí chybou, že je neumí použít v poddotazech.
A 5s u 5M řádcích, je opravdu hodně.

Neumí na výsledku poddotazu vytvořit index. To je rozdíl.

V tomto případě udělá nejprve výsledek poddotazu (index použije, ale nový nevytvoří). K výsledku sice přistupuje sekvenčně, ale to nevadí, protože je krátký. Spojí ho s indexovanou tabulkou, index se tedy použije podruhé.

Jen mi nebylo jasné, proč to trvalo celých 5 s. Nový výsledek 170 ms je odpovídající.

kuka

Re: Záludné SQL
« Odpověď #7 kdy: 29. 07. 2011, 15:26:19 »
A bereš v úvahu diskovou/databázovou cache?

Kit

Re: Záludné SQL
« Odpověď #8 kdy: 29. 07. 2011, 17:35:52 »
A bereš v úvahu diskovou/databázovou cache?
Je známo, že MySQL u poddotazů databázovou cache nepoužije, ale disková by měla stačit.

Už jsem slyšel názor, že za účelem maximálního využívání databázové cache je lepší provést sadu primitivních (cachovaných) dotazů místo jednoho komplexního. To je však IMHO zcestná myšlenka, která narušuje filosofii SQL.

ls

Re: Záludné SQL
« Odpověď #9 kdy: 11. 09. 2011, 18:52:08 »
jestli bych se mohl zeptat k dotazu. šlo by, nějakým způsobem, který funguje rychle, vybrat z té databáze jen zařízení, která má k dispozici např. jeden uživatel, příčemž by existovala tabulka, kde by byly jednotliná sn zařízení k tomu užovateli připojena. ptám se proto, že řeším podobný problém, ale nenapadá mě, jak to napsal SQL dotazem tak, aby se to provedlo v rozumném čase.

díky