連番テーブル、カレンダーテーブルを作成する

連番テーブルやカレンダーテーブルを作成する意義

SQLを使い、連番が入ったテーブルや、カレンダーとなるテーブルを使用したいという機会が往々にしてあります。
例えば、アクセスログに対して日付毎のPV数を求める場合、アクセスログ内である日付のアクセスがない場合、その日付が欠損されて出力されます。
（※以下、Redshift準拠で説明をしています）

access_log

time	date	user_id
2018-07-01 00:00:02	2018-07-01	0001
2018-07-01 20:10:10	2018-07-01	0002
2018-07-02 08:00:49	2018-07-02	0001
2018-07-03 09:24:02	2018-07-03	0003
2018-07-05 11:01:02	2018-07-05	0001
2018-07-06 04:10:02	2018-07-06	0002

SELECT
    date
    ,COUNT(user_id) AS pv
FROM
    access_log
GROUP BY
    date
ORDER BY
    date

access_logの集計結果(2018-07-04が欠損)

date	pv
2018-07-01	2
2018-07-02	1
2018-07-03	1
2018-07-05	1
2018-07-06	1

以下のようなカレンダーテーブルを用いると欠損を防ぎます。

calender

date
2018-07-01
2018-07-02
2018-07-03
2018-07-04
2018-07-05
2018-07-06

SELECT
    t1.date
    ,NVL(COUNT(t2.user_id),0) AS pv
FROM
    calender t1
LEFT OUTER JOIN
    access_log t2
ON
    t1.date = t2.date
GROUP BY
    t1.date
ORDER BY
    t1.date

calenderを使ったaccess_logの集計結果(欠損なし)

date	pv
2018-07-01	2
2018-07-02	1
2018-07-03	1
2018-07-04	0
2018-07-05	1
2018-07-06	1

このようなカレンダーテーブルや連番テーブルは実テーブルとして事前に作成していても良いですが、その場で作成することも可能です。

連番テーブル

連番テーブルは、ある十分大きなレコード数を持つ実テーブル(big_tbl)に対してROW_NUMBER関数を用いることで作成できます。
これは、big_tblから10行抽出し、行番号を1から振ることで作成しています。

1~10の連番テーブル

SELECT
    ROW_NUMBER() OVER () as n
FROM
    big_tbl
LIMIT
    10

結果

n
1
2
3
4
(以下略)

連番テーブルの作成方法を応用して、カレンダーテーブルも作成できます。
big_tblから365行抽出し、2018-01-01に行番号(1~356) - 1を足していくことで2018-01-01から365-1日分の日付を作成しています。

カレンダーテーブル(2018年)

SELECT
    '2018-01-01'::DATE + (ROW_NUMBER() OVER ()) - 1 as date
FROM
    big_tbl
LIMIT
    365

結果(一部)

date
2018-01-01
2018-01-02
2018-01-03
2018-01-04
2018-01-05
2018-01-06
(以下略)

余談

GENERATE_SERIES関数を用いると、実テーブルを経由しないでも作成可能です。
RedshiftはpostgreSQL8.0.2準拠ですが、GENERATE_SERIES関数はサポート対象外なので使用することができないようです。
※正確には、Redshiftのデータをスキャンしない場合のみ使用できますが、CREATE TABLEやWITH句、JOIN先などに使用することはできない。

docs.aws.amazon.com

連番テーブル

SELECT
    GENERATE_SERIES(1,10,1) AS n

カレンダーテーブル(2018年)

SELECT
    '2018-07-01'::DATE + GENERATE_SERIES(0,10,1) AS d