- Part 2

Author Archive

{ 2010.12.8 }

PostgreSQLでクロス集計！　tablefunc　crosstab

category：PostgreSQL｜author：yasumoto｜tag：fac2010, PostgreSQL｜comment：1

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

{ Page Top }

{ 2010.12.3 }

Postgres generate_series関数によるデータの生成

category：PostgreSQL, SQL｜author：yasumoto｜tag：fac2010, PostgreSQL｜comment：non

はじめましてこんにちは、明日（１２／４）結婚式の安元です。

弊社期待の新人の活躍によりクジを引き当て、

結婚式の前日にAdvent Calendar2010に寄稿することになりました。

明日の結婚式でいきなりスピーチさせてやろうかと思います。。。。

というわけで、本日はPostgres　generate_series関数のご紹介です。

集合を返す関数　generate_seriesについて

さて、Postgres8系からgenerate_seriesという関数が使えるようになっております。
何をする関数か。以下をごらんいただければ一目瞭然


SELECT * FROM generate_series(1,5);

 generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

このように連番を生成してくれます。

第3引数を渡すことで、刻み（増分値）の変更も出来ます


 SELECT * FROM generate_series(1,10,2);
 generate_series
-----------------
               1
               3
               5
               7
               9

日付計算を行うことで、カレンダーも生成出来ます


SELECT
    date('2010-12-1') + a::int as day
FROM
    generate_series(0,5) as a
    day
------------
 2010-12-01
 2010-12-02
 2010-12-03
 2010-12-04
 2010-12-05
 2010-12-06
(6 rows)

generate_seriesを利用した集計

さて、「連番が作れるのはわかった。それの何がうれしいの？」　という方もいらっしゃるかと思います。
DBは、データの集計は得意ですが、そこに存在しないデータは集計することは出来ません。
しかし、generate_seriesを利用すし、集計のキーとなる値を生成することで、「0」という集計を行えるのです。

以下のようなデータを日付ごとに合計します


 id |    days    | customer
----+------------+----------
  1 | 2010-12-01 |       10
  2 | 2010-12-01 |       15
  3 | 2010-12-03 |       14
  4 | 2010-12-03 |       13
  5 | 2010-12-03 |        8
  6 | 2010-12-04 |       12

(5 rows)

SELECT days , sum(customer) FROM customer GROUP BY days ORDER BY days;
    days    | sum
------------+-----
 2010-12-01 |  25
 2010-12-03 |  35
 2010-12-04 |  12
(3 rows)

GROUP BYを使うとそこに存在する日付だけが集計情報として表示されます。

generate_seriesを使ってみましょう。


SELECT

	generate.days,
	sum(customer) 
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.days)
GROUP BY generate.days 
ORDER BY generate.days;


    days    | sum
------------+-----
 2010-12-01 |  25
 2010-12-02 |
 2010-12-03 |  35
 2010-12-04 |  12
 2010-12-05 |
(5 rows)

このままでは、「0」がとれませんのでcoalesce関数でもう一工夫


SELECT

	generate.days,
	coalesce(sum(customer) ,0) --←変更してのはココ
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.days)
GROUP BY generate.days 
ORDER BY generate.days;


    days    | sum
------------+-----
 2010-12-01 |  25
 2010-12-02 |  0
 2010-12-03 |  35
 2010-12-04 |  12
 2010-12-05 |  0
(5 rows)

これで日付ごとの集計がそろいました。

プログラムで、IF分岐でも出来る処理ですが、ビジネスロジックが複雑な場合など
集計処理の一環として「存在しないデータを作る」とコードがすっきりすることもありますよ。

{ Page Top }

Fusic Developers Weblog

Author Archive

PostgreSQLでクロス集計！　tablefunc　crosstab

①GROUP BYによる日付ごと、性別ごとの人数を集計

②crosstab関数の利用

Postgres generate_series関数によるデータの生成

集合を返す関数　generate_seriesについて

generate_seriesを利用した集計

Official

tag

Fusic Developers Weblog

Author Archive

PostgreSQLでクロス集計！ tablefunc crosstab

①GROUP BYによる日付ごと、性別ごとの人数を集計

②crosstab関数の利用

Postgres generate_series関数によるデータの生成

集合を返す関数 generate_seriesについて

generate_seriesを利用した集計

Official

tag

PostgreSQLでクロス集計！　tablefunc　crosstab

集合を返す関数　generate_seriesについて