WINDOWフレーム句のROWSとRANGEの違い

WINDOW関数において、 WINDOWフレーム句の指定方法は2種類ある。

ROWS

• OVER句のORDER BYで指定されたフレーム内の「行」を基準に動作
• 1 PRECENDING AND CURRENT ROW → 1行前と自分の行がフレームになる

RANGE

• OVER句のORDER BYで与えられたフレーム内の「値」を基準に動作
• 1 PRECENDING AND CURRENT ROW → 1つ前の値と自分の値がフレームになる
• OVER句のORDER BYは数値でなくてはならない

サンプルクエリ

移動平均を求める

with tbl as (
select
  date "2022-04-01" as date, 1 as num
union all
select
  date "2022-05-02", 2
union all
select
  date "2022-05-03", 3
)  

select
  date,
  unix_date(date) as unix_date,
  num,
  --
  avg(num) over (
    order by unix_date(date)
    rows between 1 preceding and current row
    ) as window_by_row,
  avg(num) over (
    order by unix_date(date)
    range between 1 preceding and current row
    ) as window_by_range,
from tbl

結果

説明

• window_by_rowは1行前と自分の行の移動平均
• window_by_rangeは1日前と自分のレコードの日の移動平均
  • 5/2は1日前のレコードがないので、自分のレコードのみで平均を算出している
  • 5/2のレコードを1行増やした場合、5/2のwindow_by_rangeは2レコードの平均、5/3のwindow_by_rangeは3レコードの平均となる

参考

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析