皆様こんにちは、
新婚初日のウィークdayから午前帰宅で新妻にメールで泣かれた安元です。
結婚式の前後でAdvent Calendarに当たるなんて、
きっと日頃の行いがいいからだと思います！
早くも２回目になっているのは気のせいです。
閑話休題
本日のお題はクロス集計です。
SQLを実行した結果は、基本的に下に々伸びるものです。
あなたも思ったことがあるのでは無いでしょうか？
「SQLの結果が横に伸びれば。。。。」
そうなんです、SQLの結果を横に伸ばすことが出来れば、クロス集計が行えるんです。
今日はそんなお話です。
まずはサクッとyumでpostgresql-contribをインストールしましょう
このpostgresql-contribには、postgreSQLをいろいろと便利にしてくれる関数などのSQLが格納されています
yum install postgresql-contrib
インストールが完了したら、以下のコマンドでDBに関数を生成します
postgrsql-contribの設置場所は環境に合わせて変更してください。
psql -U postgres -f /usr/share/doc/postgresql-contrib-8.4.1/tablefunc.sql dbname
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
さて準備がととのいました。
pgAdminなどでDBを確認してみてください。関数などが増えているはずです。
今回使用するのは以下のようなデータです。
ID｜氏名｜性別｜登録日
12/1～12/5までに登録があったユーザーと性別が登録されています。
このデータから、日付と性別でクロス集計して見ましょう。
id | name | sex | created
----+---------+-----+---------------------
1 | USER001 | f | 2010-12-01 00:00:00
2 | USER002 | t | 2010-12-05 00:00:00
3 | USER003 | t | 2010-12-01 00:00:00
4 | USER004 | t | 2010-12-02 00:00:00
5 | USER005 | f | 2010-12-02 00:00:00
6 | USER006 | t | 2010-12-04 00:00:00
7 | USER007 | t | 2010-12-05 00:00:00
8 | USER008 | f | 2010-12-04 00:00:00
9 | USER009 | f | 2010-12-04 00:00:00
10 | USER010 | t | 2010-12-01 00:00:00
11 | USER011 | f | 2010-12-04 00:00:00
12 | USER012 | t | 2010-12-04 00:00:00
13 | USER013 | f | 2010-12-04 00:00:00
14 | USER014 | t | 2010-12-05 00:00:00
15 | USER015 | t | 2010-12-02 00:00:00
16 | USER016 | t | 2010-12-05 00:00:00
17 | USER017 | f | 2010-12-02 00:00:00
18 | USER018 | t | 2010-12-01 00:00:00
19 | USER019 | t | 2010-12-02 00:00:00
20 | USER020 | t | 2010-12-05 00:00:00
(20 rows)
①GROUP BYによる日付ごと、性別ごとの人数を集計
集計といえば、GROUP BYとCOUNT()ですね。
SELECT
created,
sex,
count(id)
FROM
customer
GROUP BY
created,
sex
ORDER BY
created,
sex ;
created | sex | count
---------------------+-----+-------
2010-12-01 00:00:00 | f | 1
2010-12-01 00:00:00 | t | 3
2010-12-02 00:00:00 | f | 2
2010-12-02 00:00:00 | t | 3
2010-12-04 00:00:00 | f | 4
2010-12-04 00:00:00 | t | 2
2010-12-05 00:00:00 | t | 5
(7 rows)
②crosstab関数の利用
①で生成したSQLをcrosstab関数に投げ込みます
SELECT
*
FROM
crosstab(
-- ここから①のSQL
'SELECT
created,
sex,
count(id)
FROM
customer
GROUP BY
created,
sex
ORDER BY
created,
sex'
-- ここまで
) AS customer(
-- 最終的に出力するデータの型を指定します
days timestamp,
male bigint,
female bigint
);
days | male | female
---------------------+------+--------
2010-12-01 00:00:00 | 1 | 3
2010-12-02 00:00:00 | 2 | 3
2010-12-04 00:00:00 | 4 | 2
2010-12-05 00:00:00 | 5 |
(4 rows)
さあ、これだけで、縦に日付、横に性別をもったクロス集計が出来上がっています！
SELECT * FROM crosstab('GROUP化SQL') AS (結果表のデータ型指定)
たったこれだけで、あとはPHPなりRubyなりでぐるっとforeachでも回せば、
集計表の出力が行えます。
ただのグループ化よりも、はるかに簡潔なプログラムになることうけあいです！
余談ですが、このままでは12/3日の集計が出ていませんね。。。
12/3はだれも入会しなかったのでしょう。データないものはGROUP BYでも集計できません。
先日ご紹介したgenerate_seriesも合わせて利用して見ましょう
SELECT
days,
coalesce(F,0) AS male,
coalesce(T,0) AS female
FROM
crosstab(
'SELECT
days,
sex,
count(id)
FROM
(
SELECT
date(''2010-12-1'') + a::int as days
FROM
generate_series(0,4) as a
) as generate
LEFT OUTER JOIN
customer
ON
(generate.days = customer.created)
GROUP BY
days,
sex
ORDER BY
days,
sex
'
) AS customer(
days date,
F bigint,
T bigint
);
days | male | female
------------+------+--------
2010-12-01 | 1 | 3
2010-12-02 | 2 | 3
2010-12-03 | 0 | 0
2010-12-04 | 4 | 2
2010-12-05 | 5 | 0
(5 rows)
これで3日と5日の「０」も集計できました。
FOR文やIF分の分岐が多い、またネストが多くなる場面で使用すると
驚くほどの速度向上が見込めることがありますので、おためしあれ。
