BigQueryでプレビューになったRange functionsを触ってみる

#Google Cloud (GCP)

#Google BigQuery

hanzawa.yuya

2024.03.23

Google Cloudデータエンジニアのはんざわです。
2024年1月27日にtime series functionsとrange functionsの2つが新たにプレビューとして追加されました。

February 27, 2024

以前紹介した記事ではtime series functionsを触ってみましたが、今回はRange functionsを触ってみたいと思います。

新たに追加された関数

新たに追加された関数は下記の8つです。

名前	詳細
RANGE	DATE、DATETIME、TIMESTAMPいずれかの区間を作成する
GENERATE_RANGE_ARRAY	区間を小さい区間に分割した配列を作成する
RANGE_CONTAINS	何らかの範囲か値が特定の範囲に含まれているか確認する
RANGE_START	区間の下限を取得する
RANGE_END	区間の上限を取得する
RANGE_INTERSECT	2つの交わる区間の上限と下限を取得する
RANGE_OVERLAPS	2つの区間が重なっているか確認する
RANGE_SESSIONIZE	Produces a table of sessionized ranges.

実際に触ってみる

数は多いですが、1個ずつ簡単に触ってみたいと思います。
また、Range functionsでもtime series functionsの記事で紹介した区間の概念が登場します。
区間を知らない方や忘れてしまった方は先に以前の記事を確認してみてください。

RANGE

RANGEは、DATE、DATETIME、TIMESTAMPいずれかの区間を作成することができます。
また、下限は区間に含まれますが、上限は区間に含まれません。

SELECT RANGE(DATE '2024-02-01', DATE '2024-03-01')

> [2024-02-01, 2024-03-01)

GENERATE_RANGE_ARRAY

GENERATE_RANGE_ARRAYは、区間を小さい区間に分割した配列を作成することができます。
分割するときの幅は、INTERVALで指定することが可能です。

SELECT GENERATE_RANGE_ARRAY(RANGE(DATE '2024-03-01', DATE '2024-03-08'), INTERVAL 1 DAY)

> [
[2024-03-01, 2024-03-02),
[2024-03-02, 2024-03-03),
[2024-03-03, 2024-03-04),
[2024-03-04, 2024-03-05),
[2024-03-05, 2024-03-06),
[2024-03-06, 2024-03-07),
[2024-03-07, 2024-03-08),
]

RANGE_CONTAINS

RANGE_CONTAINSは、何らかの値か区間が特定の区間に含まれているか確認することができます。

SELECT RANGE_CONTAINS(RANGE(DATE '2024-03-01', DATE '2024-03-08'), RANGE(DATE '2024-03-03', DATE '2024-03-04'))

> true

SELECT RANGE_CONTAINS(RANGE(DATE '2024-03-01', DATE '2024-03-08'), DATE '2024-01-01')

> false

RANGE_START

RANGE_STARTは、区間の下限を取得することができます。

SELECT RANGE_START(RANGE(DATE '2024-03-01', DATE '2024-03-15'))

> 2024-03-01

RANGE_END

RANGE_ENDは、区間の上限を取得することができます。

SELECT RANGE_END(RANGE(DATE '2024-03-01', DATE '2024-03-15'))

> 2024-03-15

RANGE_INTERSECT

RANGE_INTERSECTは、2つの交わる区間の下限と上限を取得することができます。
2つの区間が交わらない場合はエラーを返すようです。

SELECT RANGE_INTERSECT(RANGE(DATE '2024-02-01', DATE '2024-02-15'), RANGE(DATE '2024-02-10', DATE '2024-03-15'))

> [2024-02-10, 2024-02-15)

RANGE_OVERLAPS

RANGE_OVERLAPSは、2つの区間が重なっているか確認することができます。

SELECT RANGE_OVERLAPS(RANGE(DATE '2024-02-01', DATE '2024-02-15'), RANGE(DATE '2024-02-10', DATE '2024-03-15'))

> true

RANGE_SESSIONIZE

RANGE_SESSIONIZEはテーブルに対し、グルーピングしたいキー毎に区間をセッション化します。
以下は公式ドキュメントのサンプルクエリです。

サンプルテーブル

/*--------+---------+--------------------------+
 | emp_id | dept_id | duration                 |
 +--------+---------+--------------------------+
 | 10     | 1000    | [2010-01-10, 2010-03-10) |
 | 10     | 2000    | [2010-03-10, 2010-07-15) |
 | 10     | 2000    | [2010-06-15, 2010-08-18) |
 | 20     | 2000    | [2010-03-10, 2010-07-20) |
 | 20     | 1000    | [2020-05-10, 2020-09-20) |
 +--------+---------+--------------------------*/

以下の例では、emp_idでグルーピングし、一致している区間における上限と下限を取得することができます。

SELECT
  emp_id, duration, session_range
FROM
  RANGE_SESSIONIZE(
    TABLE mydataset.my_sessionized_range_table,
    'duration',
    ['emp_id'])
ORDER BY emp_id;

/*--------+--------------------------+--------------------------+
 | emp_id | duration                 | session_range            |
 +--------+--------------------------+--------------------------+
 | 10     | [2010-01-10, 2010-03-10) | [2010-01-10, 2010-08-18) |
 | 10     | [2010-03-10, 2010-07-15) | [2010-01-10, 2010-08-18) |
 | 10     | [2010-06-15, 2010-08-18) | [2010-01-10, 2010-08-18) |
 | 20     | [2010-03-10, 2010-07-20) | [2010-03-10, 2010-07-20) |
 | 20     | [2020-05-10, 2020-09-20) | [2020-05-10, 2020-09-20) |
 +--------+-----------------------------------------------------*/

また、グルーピングの条件を一致ではなく、重複に変えることもできます。

SELECT
  emp_id, duration, session_range
FROM
  RANGE_SESSIONIZE(
    TABLE mydataset.my_sessionized_range_table,
    'duration',
    ['emp_id'],
    'OVERLAPS')
ORDER BY emp_id;

/*--------+--------------------------+--------------------------+
 | emp_id | duration                 | session_range            |
 +--------+--------------------------+--------------------------+
 | 10     | [2010-03-10, 2010-07-15) | [2010-03-10, 2010-08-18) |
 | 10     | [2010-06-15, 2010-08-18) | [2010-03-10, 2010-08-18) |
 | 10     | [2010-01-10, 2010-03-10) | [2010-01-10, 2010-03-10) |
 | 20     | [2020-05-10, 2020-09-20) | [2020-05-10, 2020-09-20) |
 | 20     | [2010-03-10, 2010-07-20) | [2010-03-10, 2010-07-20) |
 +--------+-----------------------------------------------------*/

詳細はドキュメントを確認してみてください。