SELECT query sa GROUP BY maksimumom

[ Predrag Supurovic @ 27.09.2012. 15:08 ] @

Nikako da napravim upit koji radi sledeće:

Imam tabelu sa tri polja id_dokumenta (char), id_autora(char), vrsta_dokumenta (char), datum_dokumenta (timestamp)

Treba mi upit koji iz ove tabele izdvaja po jedan slog za svakog autora i to slog poslednjeg dokumenta po datumu.

[ igor.vitorac @ 27.09.2012. 17:07 ] @

Probaj ovako nesto:

select distinct id_dokumenta, id_autora, vrsta_dokumenta, datum_dokumenta
from table1
where table1.datum_dokumenta = (select MAX(alias_table1.datum_dokumenta) from table1 alias_table1)

[ Predrag Supurovic @ 27.09.2012. 19:36 ] @

Ne bih rekao da će to da da tačan rezultat. Datum dokumenta nje jedinstven, može biti više dokumenata jednog ili više autora, jedne ili vise vrsta dokumenata sa istim datumom tako da samo datum kao kriterijum ne vredi a i distinct je tu bespomocan.

[ bogdan.kecman @ 27.09.2012. 19:38 ] @

@igor, nece to bas da radi

@predrag, generalno los dizajn tabele ako ti treba takav rezultat ... idealno bi bilo da

select * from t1 group by id_autora order by datum_dokument desc;

radi posao, ali NE RADI! zato sto bez obzira na ovaj order by po sql standardu ti dobijas "bilo koji slog" iz grupe... tako da moras da radis neki odvratni self join

select * from t1, (select max(datum_dokument) as mdat, id_autora from t1 group by id_autora) t2 where t1.datum_dokument = t2.mdat and t1.id_autora=t2.id_autora;

e sad, ni ovo nece da radi ako imas za istog autora dve knjige sa istim datumom koji je "max" pa u tom slucaju mora dodas distincti pre *

Code:

select distinct * from
  t1,
  (select
           max(datum_dokument) as mdat,
           id_autora
   from t1
   group by id_autora
  ) t2
where
  t1.datum_dokument = t2.mdat and
  t1.id_autora=t2.id_autora;

[ igor.vitorac @ 27.09.2012. 21:40 ] @

@bogdan.kecman, u pravu si. Brzi prsti nego pamet. :-)

Kad sam pazljivije pogledao vidim da nije bas jednostavno u cistom SQL-u to uraditi. Ono sto ja radim za ovake situacije je pomoc reporting engine-a, npr iReport/JasperReport:

1. Zadam mu query tipa:

Code:
select id_dokumenta, id_autora, vrsta_dokumenta, datum_dokumenta
from table1
order by id_autora, datum_dokumenta

2. Napravim reporting group-u id_autora i stampam samo footer od id_autora grupe. Na taj nacin stampam samo poslednji slog u id_autora grupi tj. u ovom slucaju record koji ima max(datum_dokumenta) od svakog autora.

Mana ovakvog resenja je da svi record-i "dolaze" do report engine-a, ali obzirom da se report engine nalazi na istom DB server-u nije problem za manje i osrednje tabele.

[ bogdan.kecman @ 27.09.2012. 21:48 ] @

generalno je to lose normalizovan sistem tako da bi valjalo opraviti db model .. ali kad mora onaj upit sto sam ja stavio ce raditi

[ Predrag Supurovic @ 28.09.2012. 08:00 ] @

A kako bi trebalo da izgleda model da bude dobar?

[ bogdan.kecman @ 28.09.2012. 15:35 ] @

zavisi od ostatka baze i od toga koliko cesto ti ovakav upit treba ..
prvo poboljsanje je key u toj tabeli .. sta je tu PK? Ako je PK (id_autora, datum_dokumenta) onda ti ne treba distinct, ali taj kljuc znaci da ne mozes da imas od istog autora dva dokumenta isti dan, ako je PK (id_dokumenta) (sto bi po meni bilo logicno npr) a imas dodatne kljuceve za autora i datum onda upit moze da bude vrlo spor ... zavisno od ostatka baze mozes da redizajniras drugacije a opet mozes da radis kesiranje "najsvezijeg" nekim trigerom (tako sto ces da imas npr fleg "svez" koji na svaki update/insert/delete remarkira najsveziji dokument pa onda imas ultra brz upit, jos ako toga ima mnogo uzmes i particionises po toj koloni pa kada vuces sveze vuces iz mnogo manjeg seta sto jos ubrza stvari ... naravno ako ti ovakav spisak treba cesto, ako ti treba jednom nedeljno bilo kakav skarabudz od upita (kao npr to sto sam napisao pre par poruka) radi posao :)

[ Predrag Supurovic @ 28.09.2012. 16:46 ] @

Kada malo bolje analiziram stvar, nece se desavati slucajevi da ce biti vise dokumenata istog autora sa istim datumom.

U prinipcu mi je treba da dobijem id_dokumenta jer preko njega naknadno mogu da izvucem sve ostale podatke o tom dokumentu koji mi trebaju.

[ bogdan.kecman @ 28.09.2012. 20:40 ] @

onda ti ne treba distinct i mozes da stavis da ti pk bude user_datum ...

elem, mozes da ukrades malo .. zavisno od strukture samih podataka ovo moze da bude super brzo, samo onda kod tebe u aplikaciji iz group_concat izvadis samo prvi id posto te samo on zanima

Code:

select
  *,
  group_concat(dokument_id order by datum_dokumenta desc)
from
  t1
group by
  user_id;

primer:

Code:

mysql> select * from t1;
+-------------+-----------------+---------+
| dokument_id | datum_dokumenta | user_id |
+-------------+-----------------+---------+
|           1 |               1 |       1 |
|           2 |               2 |       1 |
|           3 |               3 |       1 |
|          10 |              10 |       1 |
|           4 |               4 |       1 |
|           2 |               2 |       2 |
|           3 |               3 |       2 |
|          10 |               4 |       2 |
|          20 |               6 |       2 |
|          50 |               5 |       2 |
+-------------+-----------------+---------+
10 rows in set (0.00 sec)

mysql> select *,group_concat(dokument_id order by datum_dokumenta desc) from t1 group by user_id;
+-------------+-----------------+---------+---------------------------------------------------------+
| dokument_id | datum_dokumenta | user_id | group_concat(dokument_id order by datum_dokumenta desc) |
+-------------+-----------------+---------+---------------------------------------------------------+
|           1 |               1 |       1 | 10,4,3,2,1                                              |
|          20 |               6 |       2 | 20,50,10,3,2                                            |
+-------------+-----------------+---------+---------------------------------------------------------+
2 rows in set (0.00 sec)

(obrati paznju da ti je u group concat id dokumenta sortiran po datumu i da ti polja dokument_id i datum_dokumenta ne vrede nicemu!!)

[ bogdan.kecman @ 28.09.2012. 20:43 ] @

inace i dalje stoji to koliko ti cesto tako nesto treba, obicno ti treba "poslednji PERIN dokument" a onda imas where user_id = perinid i mozes da uradis obicno

select * from t1 where user_id=perinid order by dokument_date desc limit 1;

i dobijes to sto ti treba

[ Predrag Supurovic @ 29.09.2012. 10:00 ] @

Da mi teba sama za jednog lako bi, nego mi bas treba da izdvojim po jedan slog za svakog autora...

Hvala na trudu i sugestijama, isprobacu sve ovo.

[ bogdan.kecman @ 29.09.2012. 10:07 ] @

pa cela fora u tom modelu je sto je pravljen za "jednog" (i u tom slucaju je to dobar model) .. e sad kada ti treba za "sve" pitanje je koliko cesto, ako treba jendom nedeljno za neki report onda nije problem, bilo koji od ovih nacina koje sam ti napisao su dovoljno dobri ... ako treba non stop onda moras da pribegnes trigerima

[ Predrag Supurovic @ 29.09.2012. 11:25 ] @

Mene si zaintrigirao mogucim prilagodjavanjem modela da se ovaj podatak lako dobije ali ja nikako da smislim kako bi taj model podataka izgledao.

[ bogdan.kecman @ 29.09.2012. 11:52 ] @

Citat:

Predrag Supurovic: Mene si zaintrigirao mogucim prilagodjavanjem modela da se ovaj podatak lako dobije ali ja nikako da smislim kako bi taj model podataka izgledao.

nema tu neke prevelike filozofije, ne mozes da izmodelujes tako da se taj podatak vuce lako sa jednim upitom, ili je self join ili moras da ga negde kesiras. ako ti taj podatak non stop treba onda kesiranje najbolje radi posao, mozes da dodas npr jos jednu tabelu koja je user_id, latest_document_id i stavis da ti je pk (user_id) i tu tabelu odrzavas trigerima, dakle na primer za insert u ovu tabelu koju vec imas proveris dal je datum koji upisujes noviji od datuma koje taj user vec ima i ako nije ne radis nista, ako jeste onda u toj novoj tabeli za tog usera zamenis latest_document_id sa ovim koji insertujes... slicno za update/delete .. znaci za update samo proveris da li updateujes datum ili nesto drugo, ako je nesto drugi ne radis nista, ako je datum proveris posle promene koji je poslednji dokument i updateujes ga u novoj tabeli, na delete samo proveris dal je taj dokument koji brises tamo u novoj tabeli, ako nije nista, ako jeste onda nadjes koji je novi najnoviji i updateujes tamo tabelu ..

[ Predrag Supurovic @ 29.09.2012. 12:13 ] @

To je ok, i ja sam dosao do zakljucka da normalno ne mogu doci do tog podatak nego da mi trebaju trigeri i/ili redudantni podaci (tako sma i resio problem, samo sam mislio d ato moze bolje, pa sam pitao), a to ipak nije bolje normalaizovano.

Razumeo sam da si imao ideju da se podaci kao takvi mogu normalizovati bolje da se dobije ovaj izvestaj.

[ bogdan.kecman @ 29.09.2012. 12:40 ] @

verovatno bi moglo ali bi morao kompletno da se razbuca sistem ... a i tada pitanje dal bi bilo mnogo jednostavnije / brze doci do podataka, fora je da tu mysql nema neke agregatne "fore i fazone" koje imaju neki drugi igraci .. realno bi mogao da se napravi agregatni udf koji bi vracao tako nesto ali to je ludnica posle za odrzavanje :( ...

btw jos bolje od onog prvog upita koji sam ti napisao bi bilo

select id_autora, max(datum_dokumenta), (select id_dokumenta from t1 as t_p where t_p.id_autora = t1.id_autora order by datum_dokumenta desc limit 1) from t1 group by id_autora;

[ bogdan.kecman @ 29.09.2012. 12:49 ] @

btw, sad sam se setio, pogledaj obavezno: http://jan.kneschke.de/projects/mysql/groupwise-max/

to je kolegin blog i obradjuje taj problem sa vise strana :D ima dobrih ideja :)

[ michaelk @ 29.09.2012. 16:29 ] @

Bogdane, MySQL ne podrzava window funkcije? Mislim da se ovo vrlo lako resava sa tim.

[ bogdan.kecman @ 29.09.2012. 16:39 ] @

ne, nista fancy aggregation (keep/first/last) nista windowing (over) ... na zalost .. sad ovi moji kazu - to nije u SQL standardu (first i last znam da nisu usli u SQL 2003, ne znam za over) i mi necemo to da implementiramo .. ono sto je dobro i lose istovremeno je sto je ceo taj tim koji je to radio otisao u mysql ab kod montija i menjaju ih polako novi ljudi koji nemaju tako rigidne stavove .. no koliko ja znam, a ne znam previse posto se nisam udubljivao u to sta radi server tim jer imam previse briga i ucestvujem oko toga sta planiramo sa ndbcluster-om, oni imaju plan za naredne dve godine sta sve treba da urade i tu te funkcije nisu ni spomenute ..

znate ono kada zli jezici kazu "je* taj mysql, to je igracka za decu" .. ne misle oni tu na brzinu (posto je ultra brz), stabilnost, sigurnost... misle bas na te stvari .. na primer nista od te fenserije nije implementirano, nema auditing, nema ...

[ michaelk @ 29.09.2012. 16:48 ] @

Cekaj, Oracle ima kompletno sve window funkcije (ukljucujuci i first_value i last_value) kao i MSSQL i PostgreSQL. Ovo zadnje dvoje nisu toliko bitni za ovu pricu koliko je Oracle jer isti je i vlasnik MySQL; sem ako nije poenta da imaju u prodaji pricu ako hocete "analytics" mogucnosti onda uzmite Oracle......

[ bogdan.kecman @ 29.09.2012. 17:20 ] @

imaju oni sve to ali svuda je razlicito (doduse pgsql je kopirao oracle sintaksu) .. no nema to nikakve veze. oracle se uopste ne mesa u to sta i kako mysql radi, mi smo prilicno "firma u firmi" ... a to sto oni imaju ceo analytics paket u oraklu, nije to tako jednostavno uzmes i prebacis u mysql, ne zaboravi da je mysql open source