BigQuery のナビゲーション関数の ROWS句の検証

BigQuery のナビゲーション関数の ROWS句の使い方を理解するために検証してみました。

こちらのドキュメントの Produceテーブルを使って、ROWS句の引数を変更するとどう変わるかを、下記のクエリで検証用 Produceテーブル作成して検証します。

作成クエリ

WITH Produce AS
(
  SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'banana', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable'
)
SELECT
  *

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING
    ) AS avg_purchases_1

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS avg_purchases_2

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
    ) AS avg_purchases_3

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING
    ) AS avg_purchases_4

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS avg_purchases_5

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS avg_purchases_6

FROM Produce
ORDER BY purchases

Produce テーブル

item	purchases	category	avg_purchases_1	avg_purchases_2	avg_purchases_3	avg_purchases_4	avg_purchases_5	avg_purchases_6
banana	2	fruit	2.0	2.0	2.0	9.0	2.0	9.0
leek	2	vegetable	5.0	4.0	4.0	10.4	2.0	9.0
apple	8	fruit	8.5	6.3333	5.25	12.5	4.0	9.0
cabbage	9	vegetable	9.5	9.0	7.25	14.0	5.25	9.0
lettuce	10	vegetable	16.5	14.0	12.5	16.5	6.2	9.0
kale	23	vegetable	23.0	16.5	14.0	23.0	9.0	9.0

avg_purchases_1 〜 6 はどう計算されているかというと、

• avg_purchases_1 （ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING）だと下記のイメージ
  • 現在の行と、現在の行から1個後の範囲（赤枠内）の purchaces の平均

• avg_purchases_2 （ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING）だと下記のイメージ
  • 現在の行より１個前、1個後の範囲（赤枠内）の purchaces の平均

• avg_purchases_3 （ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING）だと下記のイメージ
  • 現在の行より2個前、1個後の範囲（赤枠内）の purchaces の平均

• avg_purchases_4 （ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING）だと下記のイメージ
  • 現在の行と、最後の行の範囲（赤枠内）の purchaces の平均

• avg_purchases_5 （ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）だと下記のイメージ
  • 最初の行と、現在の行の範囲（赤枠内）の purchaces の平均

• avg_purchases_6 （ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING）だと下記のイメージ
  • 最初の行と、最後の行の範囲（赤枠内）の purchaces の平均