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