2014年10月29日水曜日

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)

0 件のコメント:

コメントを投稿

statistics

Arsip