如何在BigQuery窗口函数内过滤后执行排名操作？

直接在窗口函数中结合条件判断，无需多次读取表或JOIN，一次扫描即可完成需求：

SELECT
  *,
  -- 计算same_store_rank：仅is_same_store=TRUE的行按task_timestamp降序排名，否则为NULL
  CASE 
    WHEN is_same_store = TRUE 
    THEN RANK() OVER (
      PARTITION BY sale_id 
      ORDER BY CASE WHEN is_same_store = TRUE THEN task_timestamp END DESC
    ) 
  END AS same_store_rank,
  -- 计算retail_task_rank：仅is_retail_task=TRUE的行按task_timestamp降序排名，否则为NULL
  CASE 
    WHEN is_retail_task = TRUE 
    THEN RANK() OVER (
      PARTITION BY sale_id 
      ORDER BY CASE WHEN is_retail_task = TRUE THEN task_timestamp END DESC
    ) 
  END AS retail_task_rank
FROM
  `your-project.your-dataset.taskperformance`

原理说明 #

• 内层CASE WHEN确保仅符合条件的行参与排序：不符合条件的行在排序时返回NULL，在ORDER BY DESC规则中NULL会被排在最后，不会干扰目标行的排名序号。
• 外层CASE WHEN将不符合条件的行的排名结果置为NULL，完全匹配需求。
• 整个查询仅扫描一次原表，避免了CTE+JOIN带来的重复读取和性能损耗，适配大型BigQuery表场景。

替代优化（极致性能场景） #

如果对执行效率有更高要求，可改用COUNT()窗口函数实现（逻辑与RANK一致，部分场景下执行计划更优）：

SELECT
  *,
  CASE 
    WHEN is_same_store = TRUE 
    THEN COUNT(CASE WHEN is_same_store = TRUE THEN 1 END) OVER (
      PARTITION BY sale_id 
      ORDER BY task_timestamp DESC 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) 
  END AS same_store_rank,
  CASE 
    WHEN is_retail_task = TRUE 
    THEN COUNT(CASE WHEN is_retail_task = TRUE THEN 1 END) OVER (
      PARTITION BY sale_id 
      ORDER BY task_timestamp DESC 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) 
  END AS retail_task_rank
FROM
  `your-project.your-dataset.taskperformance`

内容的提问来源于stack exchange，提问作者Etienne Neveu