Fusic Developers Weblog

皆様こんにちは、
新婚初日のウィーク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分の分岐が多い、またネストが多くなる場面で使用すると
驚くほどの速度向上が見込めることがありますので、おためしあれ。