Postgresで年代毎の人数を集計する方法

Postgresで今日現在の年齢で年代別に集計する方法です。
Userテーブルにbirthdayというtimestamp型のカラムがあると想定します。

SELECT
    CASE
        when date_part('year', age(birthday)) between 1 and 9  then 0
        when date_part('year', age(birthday)) between 10 and 19 then 10
        when date_part('year', age(birthday)) between 20 and 29 then 20
        when date_part('year', age(birthday)) between 30 and 39 then 30
        when date_part('year', age(birthday)) between 40 and 49 then 40
        when date_part('year', age(birthday)) between 50 and 59 then 50
        when date_part('year', age(birthday)) between 60 and 69 then 60
        when date_part('year', age(birthday)) between 70 and 79 then 70
        when date_part('year', age(birthday)) between 80 and 89 then 80
        when date_part('year', age(birthday)) between 90 and 99 then 90
        when date_part('year', age(birthday)) >= 100 then 100
    end AS age_group,

    count(
        case 
        when date_part('year', age(birthday)) between 1 and 9  then 0
        when date_part('year', age(birthday)) between 10 and 19 then 10
        when date_part('year', age(birthday)) between 20 and 29 then 20
        when date_part('year', age(birthday)) between 30 and 39 then 30
        when date_part('year', age(birthday)) between 40 and 49 then 40
        when date_part('year', age(birthday)) between 50 and 59 then 50
        when date_part('year', age(birthday)) between 60 and 69 then 60
        when date_part('year', age(birthday)) between 70 and 79 then 70
        when date_part('year', age(birthday)) between 80 and 89 then 80
        when date_part('year', age(birthday)) between 90 and 99 then 90
        when date_part('year', age(birthday)) >= 100 then 100
        end
    ) AS age_count
FROM user   
GROUP BY age_group
ORDER BY age_group

結果は、以下のようになります。値は適当です。

age_group	age_count
0	335
10	515
20	27632
30	65276
40	12572
50	819
60	161
70	31
80	3
90	2
100	1

年代の算出に以下を使っています。

age(timestamp)

これは、現在から引数のタイムスタンプを減算します。
それを年に変換してグループ化しています。
例えば、2014-01-01時点の場合は、age(timestamp, timestamp)を使えば、算出可能です。

age('2014-01-01', birthday)