[SQL] MySQL 5.5 - GROUP BY

[Aigor]

Ahoj, mořím se druhý den s jedním dotazem a už mi z toho začíná hrabat...

Jednoduchá databáze cen, mějme třeba vše v jedné tabulce (view) jako
- zboží
- cena
- datum
- obchod

Jde mi o select, který vypíše zboží spolu s poslední (nejnovější datum) cenou od každého obchodu.
Pokud použiju group by obchod, tak neumím ovlivnit které hodnoty vyhodí, takže pak mám jednu cenu, ale za libovolné datum.

Asi si už sedím na vedení... vyspání taky nepomohlo, tak budu vděčný za nakopnutí správným směrem.

[D_a_v_i_d]

Tak v rychlosti mě napadlo následující:

SELECT [xx].[zbozi], [xx].[obchod], [yy].cena
from
(SELECT [zbozi]
      ,[obchod]
      ,max([datum]) as max_datum
  FROM [tabulka]  GROUP BY ZBOZI, obchod
) [xx]

left outer join [tabulka] [yy] on

[xx].[zbozi] = [yy].[zbozi]
and
[xx].[obchod] = [yy].[obchod]
and
[xx].[max_datum] = [yy].[datum]

První částí si vytahám max. datumy pro kombinace zboží - obchod, druhou částí tu tabulku najoinuju na sebe samou a dotáhnu si k tomu ty ceny... Asi by to šlo napsat elegantněji, ale já jsem to takhle zprovoznil... Nemám k dispozici mySQL, zkoušel jsem to na MS SQL serveru, ale snad jsem nepoužíval žádné "specialitky"

[Aigor]

Jo takhle jsem to zkoušel taky, problém je v tom že už ten vnitřní subselect vrací nesmysly. Vybere sice poslední datumy, ale ceny k nim vyhazuje z úplně jiných datumů. Nerozumím tomu.

[D_a_v_i_d]

Tak to nechápu, mně to na zkušebních datech fungovalo v pohodě, ale je fakt že jsem si tam naflákal tabulku jen asi o pěti řádcích. Jestli mi můžeš někam poslat třeba jen nějaký vzorek dat, klidně si s tím pohraju ještě zítra, pokud to nevyřešíš...

[frelichl]

Tak sem pastni svuj dotaz, daviduv subselect nesmysly urcite nevraci.

Problem Davidova dotazu je akorat v tom, ze muze vratit vic nez jeden radek ke kombinaci zbozi/obchod (pokud bylo muze byt za jeden den vic cen). Tohle se vubec dela v SQL strasne blbe, v Oracle se to da elegantne resit analytickymi funkcemi, ale ty v MySQL podle vseho nejsou.

[D_a_v_i_d]

Jj máš recht. Já jsem předpokládal (ano, předpoklad je v IT obvykle matkou průseru ), že by se nic takového stát nemělo. I kdyby, tak snad při použití formátu s uvedením datumu I ČASU by už to byla zatracená náhoda... Ale nevíme přesně, v jakém formátu lze ten časový údaj získávat...

Chtělo by to vážně nějaký vzorek "živých" dat, pokud to není něco supertajného, do té doby tu můžeme dost tápat

[Aigor]

Zkusím hodit testovací data..

mysql> select id_disku, model, dtime, cena, id_obchodu, obchod from v_ceny_disku;
+----------+-------------+---------------------+---------+------------+----------------+
| id_disku | model       | dtime               | cena    | id_obchodu | obchod         |
+----------+-------------+---------------------+---------+------------+----------------+
|        1 | ST2000DL003 | 2012-05-14 08:24:28 | 2734.00 |          1 | AlfaComp       |
|        1 | ST2000DL003 | 2012-04-13 09:55:55 | 2799.00 |          1 | AlfaComp       |
|        1 | ST2000DL003 | 2012-03-15 09:56:44 | 2820.00 |          1 | AlfaComp       |
|        1 | ST2000DL003 | 2012-03-01 09:57:54 | 2797.00 |          1 | AlfaComp       |
|        1 | ST2000DL003 | 2012-01-30 09:58:15 | 3341.00 |          1 | AlfaComp       |
|        2 | WD20EARX    | 2012-01-30 10:05:55 | 3100.00 |          1 | AlfaComp       |
|        2 | WD20EARX    | 2012-03-01 10:06:12 | 3060.00 |          1 | AlfaComp       |
|        2 | WD20EARX    | 2012-03-15 10:06:37 | 2840.00 |          1 | AlfaComp       |
|        2 | WD20EARX    | 2012-04-13 10:06:49 | 2720.00 |          1 | AlfaComp       |
|        2 | WD20EARX    | 2012-05-14 10:07:43 | 2700.00 |          1 | AlfaComp       |
|        3 | WD30EZRX    | 2012-01-30 10:09:53 | 4277.00 |          1 | AlfaComp       |
|        3 | WD30EZRX    | 2012-03-01 10:10:11 | 4011.00 |          1 | AlfaComp       |
|        3 | WD30EZRX    | 2012-03-15 10:10:39 | 4059.00 |          1 | AlfaComp       |
|        3 | WD30EZRX    | 2012-04-13 10:10:49 | 3820.00 |          1 | AlfaComp       |
|        3 | WD30EZRX    | 2012-05-14 10:11:08 | 3813.00 |          1 | AlfaComp       |
|        3 | WD30EZRX    | 2012-05-14 12:23:26 | 3895.00 |          2 | Czech Computer |
|        3 | WD30EZRX    | 2012-05-14 12:24:10 | 4055.00 |          3 | Alza           |
|        3 | WD30EZRX    | 2012-05-14 12:24:10 | 3907.00 |          4 | Softcom        |
+----------+-------------+---------------------+---------+------------+----------------+

A výsledek dotazu

mysql> SELECT serie, model, MAX(dtime) as max_datum, cena, obchod FROM v_ceny_disku GROUP BY model,obchod;
+-------------------------+-------------+---------------------+---------+----------------+
| serie                   | model       | max_datum           | cena    | obchod         |
+-------------------------+-------------+---------------------+---------+----------------+
| Seagate Barracuda Green | ST2000DL003 | 2012-05-14 08:24:28 | 2734.00 | AlfaComp       |
| WD Caviar Green         | WD20EARX    | 2012-05-14 10:07:43 | 3100.00 | AlfaComp       |
| WD Caviar Green         | WD30EZRX    | 2012-05-14 10:11:08 | 4277.00 | AlfaComp       |
| WD Caviar Green         | WD30EZRX    | 2012-05-14 12:24:10 | 4055.00 | Alza           |
| WD Caviar Green         | WD30EZRX    | 2012-05-14 12:23:26 | 3895.00 | Czech Computer |
| WD Caviar Green         | WD30EZRX    | 2012-05-14 12:24:10 | 3907.00 | Softcom        |
+-------------------------+-------------+---------------------+---------+----------------+
6 rows in set (0.00 sec)

Viditelně např. cena WD20EARX je špatně, datum sice mělo by to vracet 2700,-
Stejně tak u WD30EZRX je chybně cena 4277,-

Vážně nechápu...

[frelichl]

To ja nechapu, ze ti takovej dotaz vubec projde, v SELECT klauzuli bys mel mit jenom sloupce z GROUP BY a agregacni funkce. Kazdopadne tvuj dotaz nema s Davidovym vubec nic spolecneho.

[D_a_v_i_d]

Jj... za tohle bys měl dostat vylískáno, v případě mrkvošrotího SQL serveru třeba takto:

Column '[tabulka].serie' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[Aigor]

No jen do mě... Uznávám, že MySQL si moc hlavu neláme s ANSI a z toho vznikají blbý návyky
Nicméně pokud do group by hodím všechny sloupce co potřebuju v selectu, pak to zase nebude fungovat tak jak potřebuju...

Asi sem fakt přetaženej..

[frelichl]

Ses, protoze takhle se proste GROUP BY pouzivat neda. Podivej se na Daviduv dotaz jeste jednou, uvidis, ze v poddotazu ma opravdu jen GROUP BY sloupce + jednu agregacni funkci a teprve ve vnejsim dotazu si k tomu pomoci JOIN doda zbyle sloupce.

[D_a_v_i_d]

Spíš už na to koukáš moc dlouho a motáš se v kruhu, to znám moc dobře Pokud to napíšeš tak jak jsem měl já, respektive jak ti radí frelichl, že VŠE co bude v SELECTu, bude buď "obaleno" agregační funkcí - v tomto případě max() - nebo bude v GROUP BY, mělo by to fungovat. A vzhledem k tomu, že máš datum ve formátu včetně času na sekundy, tak by se dalo předpokládat, že i duplicita záznamů v tom poddotazu bude přinejhorším velmi, velmi výjimečná.. (museli by měnit cenu 2x za sekundu)...

EDIT: Čili poddotaz ve tvaru

SELECT serie, model, MAX(dtime) as max_datum, obchod FROM v_ceny_disku GROUP BY serie, model,obchod;

K tomu pak joinem přes model, obchod, max_datum dotáhnout sloupec cena... A mělo by to jet...

[Aigor]

Díky za nakopnutí!, čumím do toho tak dlouho že píšu kraviny (teď sem přemejšlel proč řve chybu a na začátku vynechávám SELECT...)

Dotaz sem se zbytkem kafe slepil do tvaru

SELECT
	b.id_disku,
	b.model,
	a.id_obchodu,
	b.obchod,
	b.cena,
	b.dtime
FROM
	(
	SELECT
		id_disku,
		id_obchodu,
		MAX(dtime) AS last_datum
	FROM
		v_ceny_disku
	GROUP BY
		id_disku,
		id_obchodu
	) AS a
	LEFT OUTER JOIN v_ceny_disku AS b ON a.id_disku = b.id_disku AND a.id_obchodu = b.id_obchodu AND a.last_datum = b.dtime;

a zdá se, že na testovacích datech už vrací to co má. Jdu si dát panáka a vyspat se