Pravidelná dvojrozměrná data v MySQL

— k47 (CC by-nc)

SQL databáze jsou silné v mnoha oblastech, ale v jiných katastroficky selhávají. Jednou z oblastí, kde jsou už podle definice špatné, je práce s dvojrozměrnými daty. Co tím myslím? Představte si, že potřebuji vynést do grafu nějaké hodnoty rozdělené do kategorií a jednotlivých dnů. Potřeboval bych tedy obdélník, jehož jeden rozměr by představoval kategorie a druhý rozměr dny.

Tady nastávají problémy: mapovat jeden rozměr na řádky jde snadno, ale mapovat druhý rozměr na sloupce je velice krkolomné – počet sloupců je fixní a všechny musím v dotazu vyjmenovat. Také musím počítat s tím, že mi v nějakých kombinacích kategorie a dnu budou chybět data.

Jeden způsob jak z databáze dostat obdélník dat je hack kombinující group_concat a před-připravený obdélník.

V mém případě musím mít dvě tabulky _dates a _boards – jedna obsahuje nepřerušovanou sekvenci všech dnů, které mě zajímají a druhá všechny kategorie. Kartézským součinem (joinem bez podmínek) vznikne požadovaný obdélník do kterého data dodám left joinem. group_concat pak data spojí do formátu, který je očekáván nástrojem 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ýsledek může vypadat nějak takhle (místo chybějících dat jsou nuly):

board	grouped_data
v	0.7934 0.8178 0.7831 0.8279 0.7692 0.8176 0.8155 0.7957 0.8288 0.8099
b	0.8601 0.8149 0.8462 0.8576 0.8653 0.8431 0.8475 0.8500 0.8288 0.8381
sp	0.0000 0.0000 0.0000 0.0000 0.0000 0.8492 0.8878 0.7296 0.8545 0.8583
vg	0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
mu	0.8108 0.8498 0.8263 0.8186 0.7727 0.8305 0.8287 0.8133 0.8367 0.8627
a	0.0000 0.0000 0.0000 0.0000 0.0000 0.8702 0.8868 0.8407 0.8606 0.7739
fit	0.7657 0.7578 0.6800 0.7477 0.7350 0.7043 0.6653 0.8011 0.7451 0.7379

Ale jak generovat data v pomocných tabulkách _boards a _dates?

Rozměr, který rozděluje naše data do kategorií se dá obvykle vytáhnout z dat samotných, ale jak na rozměr jednotlivých datumů? Musím nějak vygenerovat spojitou seřazenou sekvenci – a to v SQL databázích nejde vůbec snadno. Naštěstí si na to můžu napsat uloženou proceduru:

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ůdnost zavolám, vznikne mi dočasná tabulka _seq, která obsahuje řádky s hodnotami od nuly do count. Pak už jenom stačí tuhle sekvenci nějak upravit a ukotvit v dočasné tabulce _dates.

call genseq(366);

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

A výsledkem tohoto hackování pak může být tahle krásná bestie.