BQ の Window Frame に関する注意

2024-07-07

• #bigquery
• #sql

先日、購入しようとした本を自宅の本棚で発見しました。

購入した記憶はあったのですが、記録がなかったため購入しようとしていたところでした。

これまで蔵書管理は honto アプリにより行なっていたのですが、アプリの LINE への移行により購入履歴に基づく蔵書管理ができなくなってしまいました。

購入の度に自分で記録をするほどまめではありません。例えレシートの画像 1 枚を撮影するだけだとしてもです。

同じ書籍を重複して購入するリスクが増加してしまいました。。一大事です！

閑話休題、少し前に話題にもなりましたが、皆さんは SQL にどのような印象を抱いているでしょうか？ 私は初めて SQL を知ったとき、なんて直感的に書けるのだろうと感動したことを覚えています。

ところが、最近 BQ を書いていた際に直感に反する挙動を知りました。

下記のクエリをご覧ください。 それぞれどのような結果が得られると思いますか？

1. with
   
   dates as (
     select jan_date
     from unnest(generate_date_array('2024-01-01', '2024-01-05')) as jan_date
   )
   
   select * from dates
   qualify jan_date = max(jan_date) over()
   

2. with
   
   dates as (
     select jan_date
     from unnest(generate_date_array('2024-01-01', '2024-01-05')) as jan_date
   )
   
   select * from dates
   qualify jan_date = max(jan_date) over(order by jan_date)
   

3. with
   
   dates as (
     select jan_date
     from unnest(generate_date_array('2024-01-01', '2024-01-05')) as jan_date
   )
   
   select * from dates
   qualify jan_date = max(jan_date) over(order by jan_date desc)
   

クエリ上の違いは qualify で使用している over 句内の order by の指定です。 qualify は window 関数を使ったフィルタリングを行うことができる BQ の構文です。 今回は window frame を使用していないため、jan_date が最大のレコードを抽出しようとしています。

max() の定義を考えると、order by の指定によらず DATE 型の jan_date の最大値は、定義域が決まれば一意に決まるように思えます。 しかし、実際には下記のような結果が得られます。

1. max(jan_date) over()

   jan_date
   2024-01-05

2. max(jan_date) over(order by jan_date)

   jan_date
   2024-01-01
   2024-01-02
   2024-01-03
   2024-01-04
   2024-01-05

3. max(jan_date) over(order by jan_date desc)

   jan_date
   2024-01-05

order by jan_date により昇順でソートした場合のみ異なる結果が得られます。

この挙動はやや私の直感に反するため、少々困惑を覚えます。

Window Frame の仕様

上記の挙動は、下記の通り window frame の document に仕様として記載があり、order by の指定の有無によりデフォルトの window frame が異なることが明記されています。

If neither the ORDER BY clause nor window frame clause are present, the window frame includes all rows in that partition. For aggregate analytic functions, if the ORDER BY clause is present but the window frame clause is not, the following window frame clause is used by default:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

order by を指定しない場合、window frame はpartition 内の全行を含み、指定した場合には current row までが window frame の範囲となります。

つまり、それぞれ下記の window frame を指定した場合と等価な結果が得られることになります。

case	query	window frame
1	max(jan_date) over()	rows between unbounded preceding and unbounded following (※)
2	max(jan_date) over(order by jan_date)	order by jan_date asc range between unbounded preceding and current row
3	max(jan_date) over(order by jan_date dsc)	order by jan_date desc range between unbounded preceding and current row

※ case 1 については結果は等価になりますが、クエリ自体が等価になるわけではありません。

array_agg() を使うと、挙動の違いがわかりやすいと思います。

with

dates as (
  select jan_date
  from unnest(generate_date_array('2024-01-01', '2024-01-05')) as jan_date
)

select
  jan_date,
  array_agg(jan_date) over(order by jan_date)      as max_asc,
  array_agg(jan_date) over(order by jan_date desc) as max_desc,
  array_agg(jan_date) over()                       as max_no_sort
from dates order by jan_date

jan_date	asc_sort	desc_sort	no_sort
2024-01-01	2024-01-01	2024-01-05 2024-01-04 2024-01-03 2024-01-02 2024-01-01	2024-01-05 2024-01-04 2024-01-03 2024-01-02 2024-01-01
2024-01-02	2024-01-01 2024-01-02	2024-01-05 2024-01-04 2024-01-03 2024-01-02	2024-01-05 2024-01-04 2024-01-03 2024-01-02 2024-01-01
2024-01-03	2024-01-01 2024-01-02 2024-01-03	2024-01-05 2024-01-04 2024-01-03	2024-01-05 2024-01-04 2024-01-03 2024-01-02 2024-01-01
2024-01-04	2024-01-01 2024-01-02 2024-01-03 2024-01-04	2024-01-05 2024-01-04	2024-01-05 2024-01-04 2024-01-03 2024-01-02 2024-01-01
2024-01-05	2024-01-01 2024-01-02 2024-01-03 2024-01-04 2024-01-05	2024-01-05	2024-01-05 2024-01-04 2024-01-03 2024-01-02 2024-01-01

昇順にソートした場合は最小値のレコードから昇順に自身のレコードまでを、 降順にソートした場合は最大値のレコードから降順に自身のレコードまでを、 ソートしない場合は全行を aggregate しています。

なぜこの仕様なのか

ではなぜこのような仕様になっているのでしょうか？

今回は window 関数を適用するカラムと window frame の指定に使用するカラムが同一だったため、この挙動の違いの意味を理解しづらくしていたかもしれません。

下記のクエリを比べてみましょう。

1. max(price) over(partition by year order by sales_date)
2. max(price) over(partition by year)

1 は order by を指定しているため、年初から販売日当日までの間での最大価格を意味します。 2 は order by の指定がないため、年内の販売に関する最大価格を意味します。

window 関数が適用されるカラムと window frame の指定に仕様するカラムが異なる場合、この仕様は比較的直感的なのではないかと思います。 (個人的には window frame を明示的に指定したくなりますが。。)

以上、BigQuery の window frame の仕様に関するお話でした。

最後に

上記の通り、Belong では BigQuery を使って DWH を構築しております。 BigQuery が大好きだ！という方は、Entrance Book を是非ご覧ください。

参考

• Window function calls: Defining the window specification