Pravidelná dvojrozměrná data v MySQL

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):

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.