k47.cz  — každý den dokud se vám to nezačne líbit
výběr foto Praha povídky kultura
TECH ▞▞ kolo | twitter RSS

MySQL group by trik

18. 6. 2012 — k47 (CC by) (♪)

Před­stavte si, že máme apli­kaci jako #sto­kliku, kde si uži­va­tel může za­zna­me­ná­vat, kolik udělal kliků.

Data jsou ulo­žena ve dvou jed­no­du­chých ta­bul­kách:

user:     id, name
exercise: userId, date, count

Někde budeme chtít vypsat seznam všech zú­čast­ně­ných spo­lečně s cel­ko­vým počtem a ma­xi­mem kliků. To není žádný pro­blém, bude nám stačit jed­no­du­chý dotaz:

select
  u.name,
  sum(e.count),
  max(e.count)
from user u
join exercise e on e.userId = u.id
group by u.id

Ale teď někdo do­stane nápad, že by bylo skvělé, kdyby se uka­zo­valo i ma­xi­mum v po­sled­ních čtyřech týd­nech.

A tady na­stává ma­ličký pro­blém.

Pokud dotaz ome­zíme where datediff(now(), e.date) < 28, pak vypíše jenom data v po­sled­ních 4 týd­nech. Ale my bychom po­tře­bo­vali, aby vypsal souhrn všech cvi­čení a zá­ro­veň souhrn za po­slední 4 týdny.

Po­tře­bo­vali bychom něco jako:

select
  u.name,
  sum(e.count),
  max(e.count)
  max(e.count where datediff(now(), e.date) < 28) -- tohle by se nám hodilo
from user u
join exercise e on e.userId = u.id
group by u.id

Ale to v MySQL nejde. Skoro to vypadá, jako bychom se museli uchý­lit ke dvěma do­ta­zům.

Právě teď je čas vy­táh­nout z rukávu jeden trik:

select
  u.name,
  sum(e.count),
  max(e.count)
  max((datediff(now(), e.date) < 28) * e.count) -- magic!
from user u
join exercise e on e.userId = u.id
group by u.id

Kouzlo spo­čívá v tom, že SQL je věrné tra­dici céčka a bo­o­lov­ské hod­noty jsou špatně mas­ko­vané in­te­gery. True je ve sku­teč­nosti 1 a false 0.

A to je celé.

Pokud se ne­cí­títe dost od­vážně, je možné použít o něco pří­čet­nější va­ri­antu:

max(if(datediff(now(), e.date) < 28, e.count, 0))

Takhle je možné v jednom dotazu pro­vá­dět agre­gace na celých datech a zá­ro­veň na jejich li­bo­vol­ných čás­tech.

píše k47 & hosté, ascii@k47.cz