【BigQuerySQL演習】世界ビールの日なのでブルームーンが次に訪れる日について求めてみよう【特別編】

こんにちは、とりゅふです。今日のBigQuerySQL演習ですがその前に…
本日8月6日は、世界ビールの日、だそうです！
成人済みエンジニアの皆さんはビールお好きですか？
私もビールは大好きで、クラフトビールも嗜む程度には飲みます。嗜む程度に飲むっていうと、そうとう飲む人だと誤解されがちではあります。
クラフトビールの中でも一番好きなのがこのブルームーン、全米No1クラフトビールと謳われるこの商品、柑橘系の甘い香りが特徴の、とっても飲みやすいビールです。
日本のスーパーにも置いてあるところはありますし、Amazonでも購入できます！

全米№1クラフトビール BLUE MOON(ブルームーン)330ml瓶(チェコ)×1ケース(24本)

閑話休題、ブルームーンといえば、ひと月に2回満月が訪れたときの2回目の満月のことを指す言葉でもあったりします（2回目に限定しない場合もあるそうです）。月の満ち欠けの周期が平均約29.5日なので、なかなか珍しい現象です。ということで、本日のお題はこちら！

BigQueryのSQLで、ブルームーンが次に訪れる日について求めてみよう

なんでBigQueryで求めるかって？ bigquery-public-dataに、moon_phasesというデータセットがあったからです！

ルール

• BigQueryのSQL1回の実行で算出すること
• ブルームーンの定義は、"ひと月に2回満月が訪れたときの2回目の満月"とする
• 満月のデータは、bigquery-public-data:moon_phasesから、以下のSQLのように取得する
• moon_phasesのpeak_datetimeは、datetimeは日本時間に変換する。
• peak_datetimeが日本時間2020年1月1日以降のデータのみ出力する

# 2020年以降の満月の日時を求めるSQL
SELECT
    DATETIME_ADD(peak_datetime, INTERVAL 9 HOUR) AS datetime,
FROM
    `bigquery-public-data.moon_phases.moon_phases` 
WHERE
    phase = 'Full Moon'
    AND DATE(DATETIME_ADD(peak_datetime, INTERVAL 9 HOUR)) > DATE('2020-01-01')

ページスクロールすると答えのSQLと解説が見れます。

2018年1月31日に筆者が撮影したスーパーブルーブラッドムーン（色彩変更済)

BigQueryのSQLを学ぶにはこちらの書籍がおすすめ！分析に特化しているので取り組みやすいです！

そもそもSQL初心者の方はこちらもおすすめ！とにかく書いて結果を見るのをおすすめします！

答え

WITH full_moon AS (
    SELECT
        DATETIME_ADD(peak_datetime, INTERVAL 9 HOUR) AS datetime,
        DATE_TRUNC(DATETIME_ADD(peak_datetime, INTERVAL 9 HOUR), MONTH) AS month,
    FROM
        `bigquery-public-data.moon_phases.moon_phases` 
    WHERE
        phase = 'Full Moon'
        AND DATE(DATETIME_ADD(peak_datetime, INTERVAL 9 HOUR)) >= DATE('2020-01-01')
),
blue_moon AS (
SELECT
    month,
    COUNT(*) AS cnt
FROM
    full_moon
GROUP BY
    1
HAVING
    cnt = 2
)
SELECT
    full_moon.datetime
FROM
    full_moon
    INNER JOIN blue_moon
        ON full_moon.month = blue_moon.month
WHERE
    TRUE
QUALIFY
    RANK() OVER(PARTITION BY blue_moon.month ORDER BY full_moon.datetime DESC) = 1
ORDER BY
    1

解説

今回はWITH句を用いたサブクエリを2つ使ってみました。
full_moonサブクエリは、単にデータソースから、2020年以降の満月の日付、何月かを算出したものになります。
blue_moonサブクエリは、full_moonを元に、1ヶ月に2回満月がある月を計算しています。この段階ではまだ何月にブルームーンがあるのかしかわからず、何日かはわかりません。
最後にサブクエリ同士を内部結合し、ブルームーンがある月の日付を取得します。最後にQUALIFY句内で、２回目の日付のみを出力するようにすれば完成です！
次回のブルームーンは2023年8月31日、それまでにビールのブルームーンをたくさん飲んで備えましょう！

全米№1クラフトビール BLUE MOON(ブルームーン)330ml瓶(チェコ)×1ケース(24本)