今となっては横持ちのデータを縦持ちに展開する場合、もしくは縦持ちのデータを横持ちに変換する場合、
Airflow なりDataflow なりデータパイプラインで実現すると思います。
今回は検証をしたいためだけのため、横持ちのデータを縦持ちに展開することをSQLだけで頑張らないといけない機会があったので、
記録として残しておきます。
いつもどおり、誰かの参考、助けになれば嬉しいです。
以前にもHive でデータを縦持ちに展開するとかの記事を書いたのが懐かしい…
qiita.com
対象のデータとどう展開したいか
さて、本題に入っていきます。
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_serise( start, end, interval )
で指定できて、start からend までinterval の間隔で閉区間の数列を生成してくれます。
CROSS JOIN
cross join は簡単に言えば2つのテーブルの行を全通り組み合わせるものです。
cross join で調べると資料がたくさん出てくるが、以下のQiita の記事がわかりやすいと思う。
qiita.com
dateadd
dateadd は複数のSQL で追加されている関数です。
Redshift の関数の仕様は以下のドキュメントを一読してください。
docs.aws.amazon.com
大まかに関数を説明すると、
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だけで目的のテーブルを作成することができました。
序文に書いた通り、本来はデータパイプラインなどで前処理を実装するほうが確実に実装できると思います。
ですが、このようなやり方もあるということを発見できただけでも良かったかなと思います。