まさか３度目は無いだろうと完全に油断していた。。。。
Advent Calendar３度目の登場の安元です。
年末ジャンボを買うなら今だ！と無責任にけしかける社員がいっぱいる楽しい会社です。
さて、前回、前々回とSQLについて書いております。今回も趣向も変えずにSQLです。
PHPでもRubyでもフレームワークを利用されることがほとんどで
「SQLなんて書かねーよ！！」というかたも多いのではないでしょうか？
弊社でもフレームワーク導入の前後ではSQLに携わる機会は激減しているのが現状です。
しかし業務系のシステムでは、集計や統計を必要とすることがよくありますので、
「SQLでデータまとめる」という選択肢を持っておくと、プログラムがぐっとシンプルになることもあります。
というわけで、今回はGroup ByとCase式を絡めて利用してみましょう。
年齢ごとの集計
今回利用するのは以下のようなテーブルです
性別と誕生日が格納されているシンプルなテーブル構成です。
実務ではユーザーテーブルなんかでよくある構成ですね。
レコード数は全部で3525件準備しました。
id | sex | birthday
----+-----+------------
1 | f | 1920-08-08
2 | f | 1927-07-28
3 | f | 1920-03-28
4 | f | 1921-03-21
5 | f | 1913-12-23
(10 rows)
まずはシンプルに年齢ごとの集計をとってみましょう
（12月1日時点での年齢をもとに集計します）
これで「年齢」ごとの集計はできました。
しかし実務では、「年齢」ごとの集計では情報の粒度が細かすぎることが多いです。
年代ごとの集計
では、10代・20代のように年代ごとの集計をしてみましょう。
使うのは、Case式です。
まずはSQLを見てみましょう
特徴的なのは、
SELECT句の最初のCase式で条件分けをしていることと
COUNT関数の中でのCase式の利用いる部分ですね
SELECT句のCase式はGROUP BYにも書くことができますが、
SELECT句に先に記述し「AS」で別名を付けることで、
GROUP BY、ORDERともに別名を利用でき、SQLを短くすることができます。
Case式の中でやっていることは年齢を年代に置き換える作業です
date_part('year', age('2010-12-1',birthday)) between 1 and 19 then 10
分解してみましょう
１：age関数を利用して、誕生日からの経過年・月・日が取得できます
age('2010-12-1',birthday)
２：取得した年月日から、年だけを取得します。これが年齢になりますね
date_part('year', age('2010-12-1',birthday))
３：最後にbetweenを利用して、「年代」に置き換えています
case
when date_part('year', age('2010-12-1',birthday)) between 1 and 19 then 10
end
このようにCase式を使うと、「条件に一致する複数のデータを一つのデータにまとめ直す」ことができるのが大きな利点です。
性別・年代ごとの集計
それでは最後に、先ほどの年代別の集計を性別ごとに分けてみましょう。
問い合わせ結果が横に伸びるということは、SELECT句の項目が増えることになります。
今回はシンプルにCase式の各条件に性別条件を付加しました
結果はこのようになります
age_group | men | women
-----------+-----+-------
20 | 0 | 1
30 | 14 | 192
40 | 45 | 663
50 | 56 | 905
60 | 47 | 743
70 | 9 | 367
80 | 21 | 278
90 | 4 | 141
100 | 1 | 36
(9 rows)
とてもシンプルな結果が返ってきました。
Case式は応用の幅が広いです。
思いもつかなかった活用法がWEB紹介されていることもありますから、
集計に行き詰ったら、Case式の活用を検討してみるといいことがあるかもしれません。
