WINDOW 函式

BigQuery 入門

Matthew Forrest

Field CTO

什麼是 WINDOW 函式

帶有滾動 SUM 的 WINDOW 示意圖

何時使用 WINDOW 函式

依類型分組的 WINDOW 函式

¹ https://towardsdatascience.com/a-guide-to-advanced-sql-window-functions-f63f2642cbf9

WINDOW 結構、PARTITION 與 ORDER BY

SELECT
  customer_id,
  order_date,
  order_total,
  ROW_NUMBER() OVER(

    PARTITION BY customer_id

    ORDER BY order_date

  ) AS order_sequence
FROM orders;

ROW_NUMBER(): 傳回列編號的視窗函式
OVER(): 定義視窗框架
PARTITION BY customer_id: 依客戶分區資料
ORDER BY order_date: 在各分區內排序資料
order_sequence: 視窗函式的結果欄位

RANK 與 PERCENT_RANK

SELECT
  product_id,
  product_photos_qty,
  -- Ordinal rank for each row
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- Percentile rank for each row
  PERCENT_RANK() OVER(
    ORDER BY product_photos_qty
  ) as percent
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

RANK 與 PERCENT_RANK 查詢結果

LAG 與 LEAD

SELECT
  product_id,
  -- Returns value from previous row
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- Returns value from next row
  LEAD(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lead
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

查詢結果中 LAG 與 LEAD 的示意

RANGE BETWEEN 與 CURRENT ROW

SELECT
  order_id,
  order_timestamp,
  SUM(cost) OVER(
    ORDER BY order_timestamp 
    ROWS BETWEEN 2 PRECEDING 
    AND CURRENT ROW) as rolling_avg
FROM sales_data
ORDER BY order_timestamp

以列為基準的範圍選項：

UNBOUNDED PRECEDING: 目前列之前的所有列
UNBOUNDED FOLLOWING: 目前列之後的所有列
[INT] ROWS PRECEDING: 指定列數在之前
[INT] ROWS FOLLOWING: 指定列數在之後

QUALIFY

SELECT
  product_id,
  product_photos_qty,
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank
FROM ecommerce.ecomm_products 
-- Filter using QUALIFY
QUALIFY rank < 4
ORDER BY product_photos_qty DESC;

使用 QUALIFY 查詢排名為 3 或更高的結果

無法使用 HAVING，因為未進行聚合

一起來練習吧！

BigQuery 入門