Amazon RedshiftのSQLで日付情報を生成する方法

背景
対応内容

背景

BIツール（Tableau）を利用して、予算と実績のテーブルを結合させようとした際に、予算はあるが実績がないということがあり、結合させると予算が少なく見えるということがありました。

年間では実績はあるの場合が多いのですが、月単位で見るとどうしても売上の発生していない月があり、そこを補うためのデータが必要な状況でした。

そこで、SQLで実績に売上0円のダミーデータを作成して、予算テーブルと結合させることにしました。

Amazon Redshiftでは、GENERATE_SERIES()関数が利用できなかったので、別のやり方を探したのですが、やり方を見つけるのにそこそこ苦労しました。

SQLでの結合を利用しなかった理由は、データの粒度が異なっていたためで、実績は同じ日に複数の売上がたつ場合がありますが、予算は月ごとに作成されていたため、BIツールの機能を利用した結合をしたためです。

対応内容

基本的には、DATEADD()関数を利用して、月単位で数値を足していく処理です。ROW_NUMBER() OVER()-1を利用して、0から順番に数を足していっています。

--昨年1月1日から当年12月まで、1か月単位で日付データを生成
SELECT
DATE(
  DATEADD(
    'month',
    --0から1ずつ足していく数を生成
    ROW_NUMBER() OVER() -1,
    --昨年1月1日
    DATEADD('year',-1,DATE_TRUNC('year',CURRENT_DATE))
    )
  ) AS "generated_date"
FROM "sample_data_dev"."tpcds"."customer"
--24か月に制限
LIMIT 24;

大抵の場合は、店舗、商品、顧客等毎にデータを生成しないと数値が合わないので、下記のようにCross Joinを利用して、年月×〇〇のデータを生成します。

下記の例の場合は、昨年から今年度末までの年月×”c_customer_id”のデータを生成し、”sales”に0円を入れています。

with "generated_date_table" AS
(
SELECT
DATE(
  DATEADD(
    'month',
    --0から1ずつ足していく数を生成
    ROW_NUMBER() OVER() -1,
    --昨年1月1日
    DATEADD('year',-1,DATE_TRUNC('year',CURRENT_DATE))
    )
  ) AS "generated_date"
FROM "sample_data_dev"."tpcds"."customer"
LIMIT 24
)
SELECT
	"generated_date" AS "generated_date",
	--売上は0円とする
	0 AS "sales",
	"sample_data_dev"."tpcds"."customer"."c_customer_id" AS "c_customer_id"
FROM generated_date_table
--"customer"テーブルとCross Join
CROSS JOIN "sample_data_dev"."tpcds"."customer"

もっといい方法があるのかもしれませんが、ご参考まで。