Zenn
👌

BigQuery の RANGE 型の大小比較

2024/11/28に公開
BigQuery
tech

ググったけどパッと出てこず、手元で検証したのでメモしておきます。

概要

  • RANGE 型の大小比較は開始時刻での判定が優先される
    • 開始時刻が同じ場合は終了時刻で判定される
  • 終了時刻で最大値・最小値を取得したければ MAX_BY, MIN_BY を使用する

BigQuery の RANGE 型について

調べれば色々出てきますが、ざっとまとめると

  • 日付や時刻といった期間のデータを半開区間として扱えるデータ型
  • range function を使用して期間の結合や重複判定が可能

というような感じで、以下の資料が詳しいです。

RANGE 型の大小比較

基本的な挙動

RANGE<DATE> 型について以下のクエリを作成して検証しました。

WITH range_table AS(
  SELECT
    *
  FROM
    UNNEST( ARRAY<STRUCT<id INT64, duration RANGE<DATE>>>
      [
      STRUCT(1, RANGE(DATE(2000, 1, 1), DATE(2000, 2, 1))),
      STRUCT(1, RANGE(DATE(2000, 1, 1), DATE(2000, 3, 1))),
      STRUCT(1, RANGE(DATE(2000, 2, 1), DATE(2000, 3, 1))),
      STRUCT(1, RANGE(DATE(2000, 2, 1), DATE(2000, 4, 1))),
      STRUCT(2, RANGE(DATE(2000, 1, 1), DATE(2000, 4, 1))),
      STRUCT(2, RANGE(DATE(2000, 2, 1), DATE(2000, 3, 1)))
      ]
    )
)

SELECT *
FROM range_table
ORDER BY id, duration

結果は下図の通りです。

  • 開始時刻が異なる場合、開始時刻が小さいレコードが小さいと判定される
  • 開始時刻が同じ場合、終了時刻で大小が判定される

RANGE 型の終端での最大・最小値の取得

上記の id が 2 の場合のように 、2 つの期間が内包されている際に終了時刻で大小関係を比較したい場合は RANGE_END(RANGE<T>) で終了時刻を取得できます。
最大値、最小値を取得したい場合は以下のように、MIN_BY, MAX_BY を使用して取得できます。

WITH range_table AS(
  SELECT
    *
  FROM
    UNNEST( ARRAY<STRUCT<id INT64, duration RANGE<DATE>>>
      [
      STRUCT(2, RANGE(DATE(2000, 1, 1), DATE(2000, 4, 1))),
      STRUCT(2, RANGE(DATE(2000, 2, 1), DATE(2000, 3, 1)))
      ]
    )
)

SELECT
  MIN(duration) AS minimum,
  MIN_BY(duration, RANGE_END(duration)) AS minimum_end,
  MAX(duration) AS maximum,
  MAX_BY(duration, RANGE_END(duration)) AS maximum_end
FROM range_table

Discussion