Redshiftのクエリで日毎のリストを生成する方法

generate_series

日毎の数値をグラフ化しようとすると日によっては数値がないケースもあり、きれいなグラフを作れないことがあります。

PostgreSQLではそんな時に generate_series 関数を利用して簡単に日毎のデータを生成することができます。

日毎

with day_list AS (
  SELECT '2019-01-01'::date + (generate_series(0,30) * interval '1 day') as day 
)
select * from day_list

月毎

with month_list AS (
	SELECT '2019-01-01'::date + (generate_series(0,7) * interval '1 month') as month 
)
select * from month_list

Redshiftでgenerate_seriesを使うと

しかし、PostgreSQLが基になっているAmazon Redshiftでこの関数がうまく使えませんでした。

上記のクエリ単独なら正常に結果も返ってくるのですが、RedshiftのテーブルとJOINすると

Function "generate_series(integer,integer)" not supported

このようなエラーとなってしまいます。

row_number()でgenerate_seriesの代用

いろいろと調べた結果、代わりにWindows関数のrow_number()で代用できるとわかりました。

Windows関数については以下のサイトがわかりやすく参考になりました。

Window関数

クエリを row_number() を使う形に変更し、
RedshiftのテーブルとJOINしてもエラーにならずに正常に結果が返るようになりました。

日毎

with day_list AS (
  SELECT
    dateadd(day, (row_number() over ()) - 1, '2019-01-01'::Date) AS day
  FROM test.renban LIMIT 31
)
select * from day_list
day
2019-01-01 00:00:00
2019-01-02 00:00:00
2019-01-03 00:00:00
2019-01-04 00:00:00
2019-01-05 00:00:00
2019-01-06 00:00:00
2019-01-07 00:00:00
2019-01-08 00:00:00
2019-01-09 00:00:00
2019-01-10 00:00:00
2019-01-11 00:00:00
2019-01-12 00:00:00
2019-01-13 00:00:00
2019-01-14 00:00:00
2019-01-15 00:00:00
2019-01-16 00:00:00
2019-01-17 00:00:00
2019-01-18 00:00:00
2019-01-19 00:00:00
2019-01-20 00:00:00
2019-01-21 00:00:00
2019-01-22 00:00:00
2019-01-23 00:00:00
2019-01-24 00:00:00
2019-01-25 00:00:00
2019-01-26 00:00:00
2019-01-27 00:00:00
2019-01-28 00:00:00
2019-01-29 00:00:00
2019-01-30 00:00:00
2019-01-31 00:00:00

月毎

with month_list AS (
	SELECT
	  add_months('2019-01-01'::Date, (row_number() over ()) - 1) as month
	FROM test.renban LIMIT 5
)
select * from month_list
month
2019-01-01 00:00:00
2019-02-01 00:00:00
2019-03-01 00:00:00
2019-04-01 00:00:00
2019-05-01 00:00:00

ここではtest.renbanというテーブルを仮に用意していますが、
レコードが入っていればどんなテーブルでも構わないので
レコード件数がちょうどいいくらいの既存テーブルを代用しても良いかと思います。