人工知能してみる

人工知能の中の人が機械学習とか統計とかAI的なことを書き連ねます

Redshiftで条件ごとに集計する

こんにちは。Grahamianです

↓みたいなデータのときに

category | number
1 | 3
1 | 4
1 | 10
2 | 5
2 | 6
2 | 1
3 | 2
3 | 11

categoryごとにnumberを集計したいときがあると思います。あるはずです。私はありました。

普通にやるとcategoryをgroup byしてsumするので↓みたいな結果になります

category | sum(number)
1 | 17
2 | 21
3 | 14

普段はこれで良いのですが、たまに条件ごとに横持ちにしたいときがあります。

cate1 | cate2 | cate3
17 | 21 | 14

こんなときにどうしたら良いのかいろいろと探し回ってしまったのでメモ。

具体的にはsumとcaseメソッドを使います。caseはif文みたいなやつです。こんな風に書きます。

sum(case when category = 1 then number else 0 end) as cate1
sum(case when category = 2 then number else 0 end) as cate2
sum(case when category = 3 then number else 0 end) as cate3

これは何をしているかというと when category = 1 then number else 0 のように指定してやることで category = 1 のときだけ number の値を得ることができます。他のときは else 0 とすることで返り値を0にしています。この結果をsumするので最終的には category = 1 のときのsumが得られるというわけです。
ただし、categoryそれぞれについて横持ちに計算したい場合はそれぞれのcategoryの数だけカラムを作ってやる必要があるのでselect文が長くなってしまう点は注意かもしれません。最も、そのようになってしまう場合はそもそも抽出元や作りたいデータの構造に問題がある可能性もありそうですが...

SQLは簡単だと思ったことが意外と難しかったりしてツライときもありますが、やはり大規模データを高速に処理するためには必須の技術なので頑張って身につけていきたいですね。