【SQL】日付だけの列を作成して、歯抜けの日付を埋めよう！

日付が歯抜けだとミスリードに繋がる！？

ＳＱＬでデータ分析をしていると、次のように特定の日付がないデータが抽出されることがあります。

これだと、パッと見で0の値の日がないかのように見えるので、スプレッドシートなどで集計するときにミスリードが起こりやすくなります。

今日は、このようなデータの日付を埋めて、以下のようなデータとして抽出する方法をご紹介します！

日付の欠損を埋めよう！

先ほど登場した、以下の日付が欠損しているusersテーブルを用いて、日付を埋めたデータを抽出したいと思います。

抽出の流れは次の通りです。

• 日付だけのテーブルを作る
• usersテーブルと結合して抽出

以下がそのクエリです。

WITH
calendar AS (
SELECT
    DATE_ADD("2022-1-1", INTERVAL n DAY) AS d
FROM
    UNNEST(GENERATE_ARRAY(0, DATE_DIFF("2022-1-7", "2022-1-1", DAY))) AS n
)

SELECT
    calendar.d,
    users.n_of_users
FROM
    calendar
    LEFT OUTER JOIN users
        ON calendar.d = users.created_date
ORDER BY
    d ASC
    
    

それぞれ、順に解説していきます！

Step1. 日付だけのテーブルを作る

以下のクエリで、日付だけのテーブルを作成しています。

WITH
calendar AS (
SELECT
    DATE_ADD("2022-1-1", INTERVAL n DAY) AS d
FROM
    UNNEST(GENERATE_ARRAY(0, DATE_DIFF("2022-1-7", "2022-1-1", DAY))) AS n
)

ここではまず、GENERATE_ARRAY関数によって、数字の配列を作っています。

/*関数の説明*/
GENERATE_ARRAY(最初の数字, 最後の数字)

今回は、GENARATE_ARRAY関数で、

最初の数字：0
最後の数字：6（2022-1-1から2022-1-7までの日数差）

と指定しているため、以下のように0から6の配列が作成されます。

[0, 1, 2, 3, 4, 5, 6]

次に、UNNESTという演算子で配列を展開します。

/*関数の説明*/
UNNEST(展開したい配列)

展開後の行に対して、nというカラム名で命名を行っているので、以下のような列を作成しています。

最後に、DATE_ADD関数を用いて、開始日である2022-1-1に、上記の数字を次々と足していき、以下のテーブルを作成します。

Step2. LEFT OUTER JOINで結合する

以下のクエリのFROM句にて、calendarテーブルとusersテーブルをLEFT OUTER JOINで結合しています。

SELECT
    calendar.d,
    users.n_of_users
FROM
    calendar
    LEFT OUTER JOIN users
        ON calendar.d = users.created_date
ORDER BY
    d ASC

結合したテーブルを元に、SELECT句で、

• calendarテーブルのdカラム
• usersテーブルのn_of_usersカラム

それぞれ指定することで、以下のような日付が埋まったデータを抽出できます！

ちなみに、n_of_usersカラムの値をNULLから0に変えて抽出したい場合は、SELECT句を以下のように記載することで実現できます。

...

SELECT
    calendar.d,
    CASE WHEN users.n_of_users IS NOT NULL THEN users.n_of_users ELSE 0 END as n_of_users

いかがだったでしょうか？
配列の作成や展開など、難しい話が出てきましたね(;^_^A
こういうのは何回も使わないと覚えづらいので、今は難しく考えずに、「そういうもんなんだ〜」くらいの気持ちで捉えていただけると幸いです！

ここまで読んでいただいて、ありがとうございました！
また次の記事でお会いしましょう (^^)/