エクセルおなじみの機能のピボットテーブル。クロス集計と呼んだりもする。このピボットテーブルは、SQLでやろうとすると実は難しい。
そもそもRDBの表構造というものが、「列は固定で行は可変」というのが鉄則。ピボットテーブルは集計が終わるまで列の数が定まらない。列が可変の表構造はそもそも認められない。
列方向を集計を予めルール化する方法
一本のSQLで最初から表構造にしちゃう。
列の集計軸が固定なら、SQLでは以下のように実装できる。
SELECT DATE_FORMAT(updated_at, '%Y-%m-%d') as tm,
sum(status=0) '未処理' ,
sum(status=1) '処理済',
max(updated_at)
FROM `jobs`
WHERE updated_at > (CURDATE() - INTERVAL 7 DAY )
order by tm desc
実はこれでこれでメリットがあって、存在しないレコードの集計を0件とカウントすることができる。
ただしこの方法では、予めデータの範囲を知っておく必要がある。上の例ではstatusが0と1しか存在しないことを知ってることが前提。そういうのが面倒だからピボットテーブルを使いたいのだ。
一旦は二つのカラムでのグループ化をする
group by カラム1, カラム2
でグループ化する。そのままだとピボットテーブルにはならないけど、情報量としてはピボットテーブルと同じ。
その結果をうけて、表示のタイミングでピボットテーブルにする。
metabaseのピボットテーブルはこの方式。
SQL一発ではできないので必ずプラスアルファのプログラムが必要。
そもそものグループ化は存在しない行はグループ化できないのがちょっとつらい。