Fusic Developers Weblog

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

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

結婚式の前日に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分岐でも出来る処理ですが、ビジネスロジックが複雑な場合など
集計処理の一環として「存在しないデータを作る」とコードがすっきりすることもありますよ。