Postgresqlでカレンダーを使わずに日付を列挙する方法(generate_series)

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

今回、カレンダーテーブルを使わずに日付を列挙する方法を書きます。

■ 運用中のシステムでカレンダーと結合したい要求が発生！

カレンダーテーブルを作りたいが、データベースにテーブル追加ができない場合、どうしよう？

■ generate_series

Postgresql8.xから、generate_series という集合関数が出来た。

version 8と9で拡張されているので注意

■ 9.xの場合

関数	引数型	戻り型	説明
generate_series(start, stop)	intまたはbigint	setof intまたはsetof bigint（引数の型と同一）	startからstopまで、刻み１で連続する値を生成します。
generate_series(start, stop, step)	intまたはbigint	setof intまたはsetof bigint（引数の型と同一）	startからstopまで、刻みstepで連続する値を生成します。
generate_series(start, stop, step interval)	timestamp または timestamp with time zone	setof timestamp または setof timestamp with time zone（引数型と同じ）	startからstopまで、刻みstepで連続する値を生成します。

SELECT * FROM generate_series(‘2008-03-01 00:00’::timestamp,

‘2008-03-04 12:00′, ’10 hours’);

generate_series

———————

2008-03-01 00:00:00

2008-03-01 10:00:00

2008-03-01 20:00:00

2008-03-02 06:00:00

2008-03-02 16:00:00

2008-03-03 02:00:00

2008-03-03 12:00:00

2008-03-03 22:00:00

2008-03-04 08:00:00

(9 rows)http://www.postgresql.jp/document/9.1/html/functions-srf.html

これを使って日付の列挙をしてみる。

6月一か月分の日付を以下のSQLで取得してみる。

SELECT

*

FROM

generate_series( cast(‘2012/06/01’ as timestamp) ,

DATE_TRUNC(‘month’, cast(‘2012/06/01’ as timestamp) + ‘1 months’) + ‘-1 days’,

‘1 days’)

以下のように出力出来ました。

generate_series

———————

2012-06-01 00:00:00

2012-06-02 00:00:00

2012-06-03 00:00:00

2012-06-04 00:00:00

2012-06-05 00:00:00

2012-06-06 00:00:00

2012-06-07 00:00:00

2012-06-08 00:00:00

2012-06-09 00:00:00

2012-06-10 00:00:00

2012-06-11 00:00:00

2012-06-12 00:00:00

2012-06-13 00:00:00

2012-06-14 00:00:00

2012-06-15 00:00:00

2012-06-16 00:00:00

2012-06-17 00:00:00

2012-06-18 00:00:00

2012-06-19 00:00:00

2012-06-20 00:00:00

2012-06-21 00:00:00

2012-06-22 00:00:00

2012-06-23 00:00:00

2012-06-24 00:00:00

2012-06-25 00:00:00

2012-06-26 00:00:00

2012-06-27 00:00:00

2012-06-28 00:00:00

2012-06-29 00:00:00

2012-06-30 00:00:00

(30 rows)

■ 8.xの場合

関数	引数型	戻り値の型	説明
generate_series(start, stop)	int または bigint	setof int またはsetof bigint (引数の型と同一)	startからstopまでの刻み1で連続する値を生成します。
generate_series(start, stop, step)	int または bigint	setof int または setof bigint (引数の型と同一)	startからstopまでの刻みstepで連続する値を生成します。

select current_date + s.a as dates from generate_series(0,14,7) as s(a);

dates

————

2004-02-05

2004-02-12

2004-02-19

(3 rows)http://www.postgresql.jp/document/pg800doc/html/functions-srf.html

以下のSQLで先ほどと同様に6月の一か月分の日付を出力してみます。

SELECT

to_char( ‘2012-06-01’::Date + arr.i, ‘YYYY/MM/DD’ ) as date1

FROM

generate_series( 0,

(select EXTRACT(DAY FROM DATE_TRUNC(‘month’, cast(‘2012/06/01’ as timestamp) + ‘1 months’) + ‘-1 days’) – 1)::int

) as arr(i)

date1

————

2012/06/01

2012/06/02

2012/06/03

2012/06/04

2012/06/05

2012/06/06

2012/06/07

2012/06/08

2012/06/09

2012/06/10

2012/06/11

2012/06/12

2012/06/13

2012/06/14

2012/06/15

2012/06/16

2012/06/17

2012/06/18

2012/06/19

2012/06/20

2012/06/21

2012/06/22

2012/06/23

2012/06/24

2012/06/25

2012/06/26

2012/06/27

2012/06/28

2012/06/29

2012/06/30

(30 rows)

■ 応用

この日付一覧をテーブルと結合してみます。

以下のデータを例とします。

テーブル名:test1

id	field1	create_date
1	test	2012-09-01
2	test2	2012-09-01
3	test3	2012-09-09

9.xの場合

SELECT

cal.date1,

COALESCE( test1_date.cnt, 0 ) as cnt

FROM

• 日付一覧

(SELECT

to_char(arr,’YYYY/MM/DD’) as date1

FROM

generate_series( cast(‘2012/09/01’ as timestamp) ,

DATE_TRUNC(‘month’, cast(‘2012/09/01’ as timestamp) + ‘1 months’) + ‘-1 days’,

‘1 days’) as arr

) as cal

• 結合テーブル

LEFT JOIN

(

SELECT

to_char(create_date, ‘YYYY/MM/DD’) as date2,

count(*) as cnt

FROM

test1

GROUP BY

date2

) as test1_date

ON cal.date1 = test1_date.date2

ORDER BY

cal.date1

以下のように集計結果が出ました。

date1 | cnt

————+—–

2012/09/01 | 2

2012/09/02 | 0

2012/09/03 | 0

2012/09/04 | 0

2012/09/05 | 0

2012/09/06 | 0

2012/09/07 | 0

2012/09/08 | 0

2012/09/09 | 1

2012/09/10 | 0

2012/09/11 | 0

2012/09/12 | 0

2012/09/13 | 0

2012/09/14 | 0

2012/09/15 | 0

2012/09/16 | 0

2012/09/17 | 0

2012/09/18 | 0

2012/09/19 | 0

2012/09/20 | 0

2012/09/21 | 0

2012/09/22 | 0

2012/09/23 | 0

2012/09/24 | 0

2012/09/25 | 0

2012/09/26 | 0

2012/09/27 | 0

2012/09/28 | 0

2012/09/29 | 0

2012/09/30 | 0

8.xの場合

SELECT

cal.date1,

COALESCE( test1_date.cnt, 0 ) as cnt

FROM

• 日付一覧

(

SELECT

to_char( ‘2012-09-01’::Date + arr.i, ‘YYYY/MM/DD’ ) as date1

FROM

generate_series( 0,

(select EXTRACT(DAY FROM DATE_TRUNC(‘month’, cast(‘2012/09/01’ as timestamp) + ‘1 months’) + ‘-1 days’) – 1)::int

) as arr(i)

) as cal

• 結合テーブル

LEFT JOIN

(

SELECT

to_char(create_date, ‘YYYY/MM/DD’) as date2,

count(*) as cnt

FROM

test1

GROUP BY

date2

) as test1_date

ON cal.date1 = test1_date.date2

ORDER BY

cal.date1

以下のように集計結果が出ました。

date1 | cnt

————+—–

2012/09/01 | 2

2012/09/02 | 0

2012/09/03 | 0

2012/09/04 | 0

2012/09/05 | 0

2012/09/06 | 0

2012/09/07 | 0

2012/09/08 | 0

2012/09/09 | 1

2012/09/10 | 0

2012/09/11 | 0

2012/09/12 | 0

2012/09/13 | 0

2012/09/14 | 0

2012/09/15 | 0

2012/09/16 | 0

2012/09/17 | 0

2012/09/18 | 0

2012/09/19 | 0

2012/09/20 | 0

2012/09/21 | 0

2012/09/22 | 0

2012/09/23 | 0

2012/09/24 | 0

2012/09/25 | 0

2012/09/26 | 0

2012/09/27 | 0

2012/09/28 | 0

2012/09/29 | 0

2012/09/30 | 0

このように、generate_series関数を駆使すれば、カレンダーテーブルが無くとも

データの集計処理に使えます。

generate_series関数は他にも使い方があるので、詳しくは、PostgreSQLのドキュメントを参照ください。