BigQuery SQLサンプル － ユーザ定義関数で月初と月末を取得する関数を作成する

2020年2月18日2020年8月21日BigQueryBigQuery SQLサンプル

BigQueryのユーザ定義関数とは

SQLに直接記載すると複雑になったり、何度も繰り返し処理するような場合にSQLを関数化することができます。

BigQueryではユーザ定義関数といいます。

ユーザが作成する関数だからそのままの名称ですね。

構文としては、以下の内容ですが実際にユーザ定義関数を作ってみましょう。

一時的に利用するユーザ定義関数

CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS]
    function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

　

永続的に利用するユーザ定義関数

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS]
    [`project_name`.]dataset_name.function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

月初を取得するユーザ定義関数

CREATE TEMP FUNCTION GET_START_OF_MONTH(d DATE) AS 
(
  DATE_TRUNC(d, MONTH)
)
;

関数について説明します。

1行目：一時的なユーザ定義関数である TEMP を指定します。

また、引数として DATE型 を受け取るようにします。

3行目：関数の本体ですが、単純に DATE_TRUNC関数で月以下を切り捨てていますので、指定した日付の1日を取得できます。

例えば、’2020-02-14′ なら '2020-02-01’ が取得できます。

これで月初を取得する一時的なユーザ定義関数が作成できました。

　

作成したユーザ定義関数を利用する

作成した月初を取得するユーザ定義関数 GET_START_OF_MONTH を呼び出して見ましょう。

一時的なユーザ関数として作成したので、まとめて実行しましょう。

　

パラメータとして'2020-01-15’ を指定すると、月初の '2020-01-01’ が取得されました。

CREATE TEMP FUNCTION GET_START_OF_MONTH(d DATE) AS 
(
  DATE_TRUNC(d, MONTH)
)
;

SELECT GET_START_OF_MONTH(DATE("2020-01-15"));

行	f0_
1	2020-01-01

　

　

パラメータとして本日の日付（2020-02-18） を指定すると、月初の '2020-02-01’ が取得されました。

CREATE TEMP FUNCTION GET_START_OF_MONTH(d DATE) AS 
(
  DATE_TRUNC(d, MONTH)
)
;

SELECT GET_START_OF_MONTH(CURRENT_DATE());

行	f0_
1	2020-02-01

月末を取得するユーザ定義関数

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

関数について説明します。

1行目：一時的なユーザ定義関数である TEMP を指定します。

また、引数として DATE型 を受け取るようにします。

3行目：関数の本体ですが、多少複雑になっていますのが分解してみると次の通りです。

1. DATE_TRUNC(d, MONTH)で月初（その月の1日）を取得します
2. DATE_ADD関数でINTERVAL 1 MONTHとすることで翌月の1日を取得します
3. 最後にDATE_ADD関数でINTERVAL -1 DAYすることで翌月の1日の前日。つまり月末を取得します。

例えば、’2020-02-14′ なら '2020-02-29’ が取得できます。

これで月末を取得する一時的なユーザ定義関数が作成できました。

　

作成したユーザ定義関数を利用する

作成した月末を取得するユーザ定義関数 GET_END_OF_MONTH を呼び出して見ましょう。

一時的なユーザ関数として作成したので、まとめて実行しましょう。

パラメータとして'2020-01-15’ を指定すると、月初の '2020-01-31’ が取得されました。

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

SELECT GET_END_OF_MONTH(DATE("2020-01-15"));

行	f0_
1	2020-01-31

　

パラメータとして本日の日付（2020-02-18） を指定すると、月末の '2020-02-29’ が取得されました。

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

SELECT GET_END_OF_MONTH(CURRENT_DATE());

行	f0_
1	2020-01-31