この記事はアピリッツの技術ブログ「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のドキュメントを参照ください。
