窗口函数

BigQuery 入门

Matthew Forrest

Field CTO

什么是窗口函数

带滚动 SUM 的窗口示意图

何时使用窗口函数

按类型分组的窗口函数

¹ 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,
  -- 每行的序号排名
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- 每行的百分位排名
  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,
  -- 返回上一行的值
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- 返回下一行的值
  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 
-- 使用 QUALIFY 过滤
QUALIFY rank < 4
ORDER BY product_photos_qty DESC;

使用 QUALIFY 查找排名为 3 或更高的值的查询结果

不能用 HAVING，因为这不是聚合

Vamos praticar!

BigQuery 入门