👏

BigQuery で月末日を簡単に取得できる LAST_DAY 関数の紹介

2022/11/25に公開約1,500字

クラウドエースの小坂です。
データウェアハウス構築、最近は特に Looker を活用した分析環境構築プロジェクトのプロジェクトマネージャーをよくやっています。

今回はとても短い記事ですが、BigQuery で日付操作を行うにあたってとても便利な関数を教えてもらったので紹介させていただきます。

BigQuery に限らず日付の操作をするにあたって、月末日を取得する際に以下のようなクエリを書いたことがある方は多いと思います。

-- 今日の日付(CURRENT_DATE())に DATE_TRUNC() を実行して月初日を取得し、それに DATE_ADD() で1ヶ月後の日付を取得し、更に DATE_SUB() で1日遡ることで月末日を取得する
SELECT DATE_SUB(DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH), INTERVAL 1 DAY) AS last_day
last_day
2022-11-30

毎回これを書くたびに月末日を取得する関数があればいいのに…と思っていたのですが、いつのまにか LAST_DAY 関数 として実装されていました。
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#last_day

SELECT
  LAST_DAY(CURRENT_DATE()) AS last_day --第一引数に最終日に変更する日付を入力する
last_day
2022-11-30

先程のクエリと同じ結果が取得できました。

基本的には月末日を返すものですが、第二引数に 何の最終日 を結果として返すか指定することができ、年末日を取得することなども可能です。

SELECT
  LAST_DAY("2023-01-01", YEAR) AS year_end,
  LAST_DAY("2023-01-01", QUARTER) AS quarter_end,
  LAST_DAY("2023-01-01", MONTH) AS month_end,
  LAST_DAY("2023-01-01", WEEK) AS week_end
year_end quarter_end month_end week_end
2023-12-31 2023-03-31 2023-01-31 2023-01-07

まとめ

非常に簡単に月末日等を取得できる LAST_DAY 関数 の紹介でした。

改めて調べてみると 2020年10月14日 にリリースされており、2年近く知らずに無駄に長いクエリを書いてしまっていました。
https://cloud.google.com/bigquery/docs/release-notes#October_14_2020

BigQuery 以外の DWH・DB でも使用できるものもあるようです。(AWS Redshift など)

また便利な関数を見つけたら共有しようと思います。

Discussion

ログインするとコメントできます