BigQueryで月初・月末を計算する、またその関数を自作する(UDF)

なにこれ

2020-01-15 というtimestampなりdateが渡された時に、
月初: 2020-01-01
月末: 2020-01-31
を出す方法です。

月末を出す書き方が少し長いので、1つのクエリに複数書く場合は冗長になります。そこで、dateが渡されたら月末を出すという関数を自作します。

月初・月末

SELECT
  -- 月初
  DATE_TRUNC('2020-01-15', MONTH),
  -- 月末
  DATE_SUB(DATE_ADD(DATE_TRUNC('2020-01-15', MONTH), INTERVAL 1 MONTH), INTERVAL 1 DAY)

• 月初はDATE_TRUNC関数を使って、day以下を切り捨てることで実現できます
• 月末は月初を出した後に、①1ヶ月足す②1日引くことで実現できます

月末を出す関数を自作する

標準 SQL ユーザー定義関数  |  BigQuery  |  Google Cloud

CREATE TEMP FUNCTION end_month_date(d DATE)
  AS(
    DATE_SUB(DATE_ADD(DATE_TRUNC(d, MONTH), INTERVAL 1 MONTH), INTERVAL 1 DAY)
  );

SELECT
  -- 月初
  DATE_TRUNC('2020-01-15', MONTH),
  -- 月末
  end_month_date('2020-01-15')

行頭に関数を宣言しておけば、クエリ内ではすっきり書くことができ、見た目がきれいになりました！