この記事が解決できること
- 歯抜けになった日付を補完したテーブルが作成できる
はじめに
今回は、歯抜けになってしまっている日付を、SQLを使用して補完する方法を紹介します。トランザクションデータを扱っていると、日付が飛び飛びなデータに遭遇することがあります。
そのままBIツールで可視化をした場合、ぽっかりと空いてしまっている日付が発生します。
このような可視化の場合はもちろん、データ整形の際に日付が存在しないことで結合が上手くできなかったりもするので、できれば処理をしておきたいところです。
BigQueryを使った実践
今回使用するデータは、TableauというBIツールに付属しているサンプルデータです。こちらのデータをBigQueryに保存して使用します。赤枠で囲んだ部分が今回の解説で登場するカラムです。
order_date：注文日
sales：売上
ロジックの概要
ロジックはとてもシンプルで、既存の日付カラムの最小日と最大日を使って、新しい日付カラムを生成します。
この生成した日付カラムに対して、歯抜けが起きているテーブルをLEFT JOINさせます。当然としてNULLが発生するので、IFNULL関数を使用して0で埋めます。
クエリ
今回使用したクエリは以下の通りです。
SQLクエリ
WITH _generate_date_table AS
(
SELECT
gen_order_date
FROM
(
SELECT
--日付最小日と最大日を使って、generate関数で日付カラムを生成
GENERATE_DATE_ARRAY(MIN(order_date),MAX(order_date)) AS gen_order_date
FROM `your_pj.your_dataset.sample_data_for_looker_studio`
)
--generate関数で作成したカラムは配列になっているので、unnestで分解
,UNNEST(gen_order_date) AS gen_order_date
)
SELECT
gen_order_date
,IFNULL(sales,0) AS sales
FROM _generate_date_table AS a
--生成した日付カラムに対して元のセールスデータを結合させる
LEFT JOIN `your_pj.your_dataset.sample_data_for_looker_studio` AS b
ON a.gen_order_date = b.order_date
ロジックで組んだ通りの結果を出すことができました。
まとめ
歯抜けになった日付を補完する方法をご紹介しました。この工程をすっ飛ばしてデータ処理をしたりBIで可視化をすることも可能ですが、手段として知っておくことで便利なタイミングがあると思います。是非ご活用ください。
ここまでお読みいただきありがとうございました！
この記事をシェア
クリップボードにコピーしました
