PostgreSQLでクロス集計

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

こんにちは、sasimiです。
ユーザの属性情報を分析したい、という場合がよくあります。
例えば、キャリア毎に男性と女性の割合を表示したい、など。
そんな場合には、クロス集計が便利です。

 キャリア | 男性 | 女性 
----------+------+------
 au 　　 |　     |    
 softbank |      |    
 docomo   |     |    

こんな風に集計した結果を表示することを目指します。

まずはデータを用意しましょう

test=# create table users ( carrier varchar(10), sex varchar(1), created_at timestamp default now());  
test=# insert into users (carrier, sex) values ('docomo', 'm');
test=# insert into users (carrier, sex) values ('docomo', 'f');
test=# insert into users (carrier, sex) values ('docomo', 'f');
test=# insert into users (carrier, sex) values ('au', 'm');
test=# insert into users (carrier, sex) values ('softbank', 'm');

では、早速、クロス集計をしてみたいと思います。

SELECT
  carrier as キャリア
, sum(case when sex='m' then 1 else 0 end) as 男性
, sum(case when sex='f' then 1 else 0 end) as 女性
FROM
  users
GROUP BY
  carrier;

実行結果は

 キャリア | 男性 | 女性 
------------+------+------
 au            |    1   |    0
 softbank |    1  |    0
 docomo  |    1  |    2

これで目的どおりのものを表示できました。
さて、Excelなどでピボットテーブルなんて便利な機能がありますよね。
項目を入れ替えたりすると集計しなおす便利な機能です。
では、性別とキャリアを入れ替えてみましょう。

SELECT
  case when sex='m' then '男性'
       when sex='f' then '女性'
       end as 性別
, sum(case when carrier='docomo' then 1 else 0 end) as ドコモ
, sum(case when carrier='au' then 1 else 0 end) as au
, sum(case when carrier='softbank' then 1 else 0 end) as ソフトバンク
FROM
  users
GROUP BY
  sex;

ちょっとだけ、表示をわかりやすくするために、性別カラムで男性、女性と表示するようにしました。

 性別 | ドコモ | au | ソフトバンク 
------+--------+----+--------------
 男性 |      1   |  1   |            1
 女性 |      2   |  0   |            0

いかがでしょうか？
クロス集計は集計するときに便利な機能です。 是非、利用してみてください。