Redshift で日付区間を縦持ちにする
今となっては横持ちのデータを縦持ちに展開する場合、もしくは縦持ちのデータを横持ちに変換する場合、
Airflow なりDataflow なりデータパイプラインで実現すると思います。
今回は検証をしたいためだけのため、横持ちのデータを縦持ちに展開することをSQLだけで頑張らないといけない機会があったので、
記録として残しておきます。
いつもどおり、誰かの参考、助けになれば嬉しいです。
以前にもHive でデータを縦持ちに展開するとかの記事を書いたのが懐かしい…
対象のデータとどう展開したいか
さて、本題に入っていきます。
RDBのデータとして以下のような期間を表現しているデータがあったとします。
SELECT 'hoge' AS name, '2021-02-01'::DATE AS start_date, '2021-02-15'::DATE AS end_date
テーブルで表現するとこんな感じです。
| name | start_date | end_date |
|---|---|---|
| hoge | 2021-02-01 | 2021-02-15 |
start_date が開始の日時、 end_date が終了の日時を表現してます。
以下のように name カラムを start_date から end_date まで日を更新しながら一行ずつに展開していくことがゴールです。
| name | expand_date |
|---|---|
| hoge | 2021-02-01 00:00:00 |
| hoge | 2021-02-02 00:00:00 |
| hoge | 2021-02-03 00:00:00 |
| ... | ... |
| hoge | 2021-02-13 00:00:00 |
| hoge | 2021-02-14 00:00:00 |
| hoge | 2021-02-15 00:00:00 |
どうやったか
SQL の cross join と Redshift の関数の generate_series , dateadd 関数を組み合わせて実現しました。
generate_series
generate_series 指定した区間とインターバルで連続的に数字を生成する関数です。
Redshift ではgenerate_series は使えないという記事をよくでてきますが、from 句で生成するケースでは使うことができます。
SELECT * FROM generate_series(1, 4, 1);
このようなSQL を実行すると
| generate_series |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
という結果が返ってきます。 関数としては、
generate_serise( start, end, interval )
で指定できて、start からend までinterval の間隔で閉区間の数列を生成してくれます。
CROSS JOIN
cross join は簡単に言えば2つのテーブルの行を全通り組み合わせるものです。
cross join で調べると資料がたくさん出てくるが、以下のQiita の記事がわかりやすいと思う。
dateadd
dateadd は複数のSQL で追加されている関数です。
Redshift の関数の仕様は以下のドキュメントを一読してください。
大まかに関数を説明すると、
DATEADD( datepart, interval, {date|time|timetz|timestamp} )
という関数で各引数は
- datepart:
year,month,dayなど加算したい部分を指定 - interval: 加算数
- {date|time|timetz|timestamp}: date や timestamp データ型のカラム
を表しています。
組み合わせていく
ではまず最初に最初の1行しかないテーブルとgenerate_serise 関数で生成したテーブルをcross join で組み合わせていきます。
WITH tmp_table_1 AS (
SELECT
'hoge' AS name,
'2021-02-01'::DATE AS start_date,
'2021-02-15'::DATE AS end_date
)
SELECT
*
FROM
tmp_table_1
CROSS JOIN (
SELECT
*
FROM
generate_series(1, 30, 1)
) tmp_table_2;
これにより
| name | start_date | end_date |
|---|---|---|
| hoge | 2021-02-01 | 2021-02-15 |
を30 行生成します。 生成したテーブルの部分抜粋は以下の通りです。
| name | start_date | end_date | generate_series |
|---|---|---|---|
| hoge | 2021-02-01 | 2021-02-15 | 1 |
| hoge | 2021-02-01 | 2021-02-15 | 2 |
| hoge | 2021-02-01 | 2021-02-15 | 3 |
| ... | ... | ... | ... |
| hoge | 2021-02-01 | 2021-02-15 | 28 |
| hoge | 2021-02-01 | 2021-02-15 | 29 |
| hoge | 2021-02-01 | 2021-02-15 | 30 |
次にこの生成した generate_series の数字を使い、 dateadd していきます。
SQL としては以下の通りです。
WITH tmp_table_1 AS (
SELECT
'hoge' AS name,
'2021-02-01'::DATE AS start_date,
'2021-02-15'::DATE AS end_date
)
SELECT
tmp_table_1.name,
tmp_table_1.start_date,
tmp_table_1.end_date,
tmp_table_2.generate_series,
dateadd(day,tmp_table_2.generate_series,tmp_table_1.start_date-1) AS expand_date
FROM
tmp_table_1
CROSS JOIN (
SELECT
*
FROM
generate_series(1, 30, 1)
) tmp_table_2;
生成されたテーブルは
| name | start_date | end_date | generate_series | expand_date |
|---|---|---|---|---|
| hoge | 2021-02-01 | 2021-02-15 | 1 | 2021-02-01 00:00:00 |
| hoge | 2021-02-01 | 2021-02-15 | 2 | 2021-02-02 00:00:00 |
| hoge | 2021-02-01 | 2021-02-15 | 3 | 2021-02-03 00:00:00 |
| ... | ... | ... | ... | |
| hoge | 2021-02-01 | 2021-02-15 | 28 | 2021-02-28 00:00:00 |
| hoge | 2021-02-01 | 2021-02-15 | 29 | 2021-03-01 00:00:00 |
| hoge | 2021-02-01 | 2021-02-15 | 30 | 2021-03-02 00:00:00 |
のようになり、うまく日付が生成されていることがわかります。
しかし end_date 期間以上の日付が生成されています。
なので、最後に end_date で期間を絞りつつ、カラムも絞って目的のテーブルに整形していきます。
今回は name と expand_date に制限します。
SQLとしては以下の通りです。
WITH tmp_table_1 AS (
SELECT
'hoge' AS name,
'2021-02-01'::DATE AS start_date,
'2021-02-15'::DATE AS end_date
)
SELECT
tmp_table_1.name,
dateadd(day,tmp_table_2.generate_series,tmp_table_1.start_date-1) AS expand_date
FROM
tmp_table_1
CROSS JOIN (
SELECT
*
FROM
generate_series(1, 30, 1)
) tmp_table_2
WHERE expand_date <= end_date;
そして生成されたテーブルは以下の通りです。
| name | expand_date |
|---|---|
| hoge | 2021-02-01 00:00:00 |
| hoge | 2021-02-02 00:00:00 |
| hoge | 2021-02-03 00:00:00 |
| ... | ... |
| hoge | 2021-02-13 00:00:00 |
| hoge | 2021-02-14 00:00:00 |
| hoge | 2021-02-15 00:00:00 |
目的の縦持ちに展開したテーブルを生成することができました。
expand_date のフォーマットを変えたい場合は、 to_char 関数とかを使えばフォーマットできます。
ネックになっているところ
イケてないところとしては、生成する行数が start_date と end_date とで一致していないところだと思っています。
必要以上のレコードを生成してフィルターしているのをもう少しスマートにできればなという感触です。
もっとスマートなやり方をご存知の方がいればコメントをお願いします…！
所感
一応SQLだけで目的のテーブルを作成することができました。
序文に書いた通り、本来はデータパイプラインなどで前処理を実装するほうが確実に実装できると思います。
ですが、このようなやり方もあるということを発見できただけでも良かったかなと思います。
