BigQueryで仮想テーブルを作成・活用 #SQL

はじめに

今回の記事は、かゆいところに手が届く仮想テーブルの作成方法を紹介します。

直近1年分のテーブル（月ごとに抜け漏れがない状態）が欲しいときなどに重宝します。

仮想テーブル作成

sql1

SELECT
    DATE_SUB( CURRENT_DATE(), INTERVAL n month), MONTH) AS month
FROM
    UNNEST(GENERATE_ARRAY(0, 11)) AS n
ORDER BY
    n
;

作成されるテーブル

month
2021-04-01
2021-03-01
2021-02-01
2021-01-01
2020-12-01
2020-11-01
2020-10-01
2020-09-01
2020-08-01
2020-07-01
2020-06-01
2020-05-01

DATE_TRUNC()は、日付、時刻を指定された時間単位に切り捨てる（今回は、MONTHですので、月の最初の日となります。）
DATE_SUB( CURRENT_DATE(), INTERVAL n month)で現在からnヶ月分、減算する（current_dateが2021-04-01であれば、2021-03-01となります。）
UNNESTは、配列を取得し、配列に含まれる各要素を 1行にしたテーブルを返します

UNNEST補足説明

先程、実行したsql1ですと、様々な関数が混ざっていて、少しイメージがしにくかったかもしれません。
UNNESTを使用している部分を取り出して、実行してみましょう！

sql2

SELECT
    n
FROM
    UNNEST(GENERATE_ARRAY(0, 11)) AS n
ORDER BY n
;

作成されるテーブル

このようなテーブルが作成されます。sql1は、DATE_SUB( CURRENT_DATE(), INTERVAL n month), MONTH) AS monthのn部分に値を代入することによって、今月、前月、前々月と値を取得することが出来ます。

ちょっとしたカスタマイズ

ちょっとしたカスタマイズを共有します。その他、良いカスタマイズがありましたら、共有していただけると幸いです。

DATE_ADD()は、特定の日付のn日後・n日前などの日付を取得できます。

DATE_ADD(, ~省略~ INTERVAL 14 day)を使ってみましょう。

sql3

SELECT
    DATE_ADD(DATE_TRUNC(DATE_SUB( CURRENT_DATE(), INTERVAL n month), MONTH), INTERVAL 14 day) AS month
FROM
    UNNEST(GENERATE_ARRAY(0, 11)) AS n
ORDER BY n
;

毎月15日のテーブルが出来ました！

month
2021-04-15
2021-03-15
2021-02-15
2021-01-15
2020-12-15
2020-11-15
2020-10-15
2020-09-15
2020-08-15
2020-07-15
2020-06-15
2020-05-15

JOINして活用しよう！

これは注意点ですが、BigQueryには、JOINするときに、イコールで条件を結ぶ決まりがあります。

ですので、関連性がないテーブルには、1 as dummy,とダミーテーブルを追記して、JOINして値を抽出しています。

sql4

SELECT
    month,
    element
FROM
    (
        SELECT
            1 AS dummy,
            DATE_ADD(DATE_TRUNC(DATE_SUB( CURRENT_DATE(), INTERVAL n month), MONTH), INTERVAL 14 day) AS month
        FROM
            UNNEST(GENERATE_ARRAY(0, 4)) AS n
        ORDER BY n
    )   AS t1
    LEFT JOIN
    (
        SELECT
            1 AS dummy,
            element
        FROM UNNEST(['foo', 'bar', 'baz']) AS element
    )   AS t2
    ON t1.dummy = t2.dummy
;

以下のテーブルになります。


month   　　element
2021-04-15  foo
2021-04-15  bar
2021-04-15  baz
2021-03-15  foo
2021-03-15  bar
2021-03-15  baz
2021-02-15  foo
2021-02-15  bar
2021-02-15  baz
2021-01-15  foo
2021-01-15  bar
2021-01-15  baz
2020-12-15  foo
2020-12-15  bar
2020-12-15  baz

今回はelementの中にてきとうな値を入れていますが、日付など様々なカラムを入れて、mouth値の条件に該当するものをカウントするみたいな使い方も出来ます。

以上です。最後まで読んでくださって、ありがとうございます。

参考リンク

とても分かりやすい記事をありがとうございます！