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

Pravidelná dvojrozměrná data v MySQL

— k47 (CC by-nc) (♪)

SQL da­ta­báze jsou silné v mnoha ob­las­tech, ale v jiných ka­ta­stro­ficky se­lhá­vají. Jednou z ob­lastí, kde jsou už podle de­fi­nice špatné, je práce s dvoj­roz­měr­nými daty. Co tím myslím? Před­stavte si, že po­tře­buji vynést do grafu nějaké hod­noty roz­dě­lené do ka­te­go­rií a jed­not­li­vých dnů. Po­tře­bo­val bych tedy ob­dél­ník, jehož jeden rozměr by před­sta­vo­val ka­te­go­rie a druhý rozměr dny.


Tady na­stá­vají pro­blémy: ma­po­vat jeden rozměr na řádky jde snadno, ale ma­po­vat druhý rozměr na sloupce je velice kr­ko­lomné – počet sloupců je fixní a všechny musím v dotazu vy­jme­no­vat. Také musím po­čí­tat s tím, že mi v ně­ja­kých kom­bi­na­cích ka­te­go­rie a dnu budou chybět data.

Jeden způsob jak z da­ta­báze dostat ob­dél­ník dat je hack kom­bi­nu­jící group_concat a před-při­pra­vený ob­dél­ník.

mém pří­padě musím mít dvě ta­bulky _dates_boards – jedna ob­sa­huje ne­pře­ru­šo­va­nou sek­venci všech dnů, které mě za­jí­mají a druhá všechny ka­te­go­rie. Kar­téz­ským sou­či­nem (joinem bez pod­mí­nek) vznikne po­ža­do­vaný ob­dél­ník do kte­rého data dodám left joinem. group_concat pak data spojí do for­mátu, který je oče­ká­ván ná­stro­jem na tvorbu grafů.

set @@group_concat_max_len = 100000
;

-- záhlaví
select concat('* ', group_concat(date order by date separator ' '))
from _dates
;

-- data
select _boards.board, group_concat(data_table.ratio order by _dates.date separator ' ') grouped_data
from _dates, _boards
left join data_table on data_table.date = _dates.date and data_table.board = _boards.board
group by _boards.board
;

Vý­sle­dek může vy­pa­dat nějak takhle (místo chy­bě­jí­cích dat jsou nuly):

boardgrou­ped_data
v0.7934 0.8178 0.7831 0.8279 0.7692 0.8176 0.8155 0.7957 0.8288 0.8099
b0.8601 0.8149 0.8462 0.8576 0.8653 0.8431 0.8475 0.8500 0.8288 0.8381
sp0.0000 0.0000 0.0000 0.0000 0.0000 0.8492 0.8878 0.7296 0.8545 0.8583
vg0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
mu0.8108 0.8498 0.8263 0.8186 0.7727 0.8305 0.8287 0.8133 0.8367 0.8627
a0.0000 0.0000 0.0000 0.0000 0.0000 0.8702 0.8868 0.8407 0.8606 0.7739
fit0.7657 0.7578 0.6800 0.7477 0.7350 0.7043 0.6653 0.8011 0.7451 0.7379

Ale jak ge­ne­ro­vat data v po­moc­ných ta­bul­kách _boards_dates?

Rozměr, který roz­dě­luje naše data do ka­te­go­rií se dá ob­vykle vy­táh­nout z dat sa­mot­ných, ale jak na rozměr jed­not­li­vých datumů? Musím nějak vy­ge­ne­ro­vat spo­ji­tou se­řa­ze­nou sek­venci – a to v SQL da­ta­bá­zích nejde vůbec snadno. Na­štěstí si na to můžu napsat ulo­že­nou pro­ceduru:

drop procedure if exists genseq;

delimiter |
create procedure genseq(count int)
begin
  declare i int default 0;

  drop temporary table if exists _seq;
  create temporary table _seq (i int);

  start transaction;
  while i < count do
    insert into _seq (i) values (i);
    set  i = i + 1;
  end while;
  commit;
end
|

Když tuhle zrůd­nost za­vo­lám, vznikne mi do­časná ta­bulka _seq, která ob­sa­huje řádky s hod­no­tami od nuly do count. Pak už jenom stačí tuhle sek­venci nějak upra­vit a ukot­vit v do­časné ta­bulce _dates.

call genseq(366);

create temporary table _dates
select date_add('2012-01-01', interval _seq.i day) date from _seq;

A vý­sled­kem tohoto hac­ko­vání pak může být tahle krásná bestie.

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