BigQuery中带例外的Rank() Over()实现：特定条件返回Null

要实现**按identifier分区、original_nr排序，且水果为梨时返回null，后续行正常排名（跳过梨的计数）**的效果，普通CASE WHEN只会替换排名值，无法跳过梨的行参与排名计数，你可以试试以下两种方案：

方案一：子查询+左连接（推荐，逻辑清晰） #

先筛选出非梨的行计算正确排名，再左连接回原表，梨的行自然返回null：

WITH ranked_valid_rows AS (
  SELECT 
    identifier,
    original_nr,
    fruit,
    -- 仅对非梨的行计算Rank()
    RANK() OVER(PARTITION BY identifier ORDER BY original_nr) AS rank_over_except
  FROM your_table
  WHERE fruit != '梨'
)
SELECT 
  t.identifier,
  t.original_nr,
  t.fruit,
  rv.rank_over_except
FROM your_table t
LEFT JOIN ranked_valid_rows rv
  ON t.identifier = rv.identifier
  AND t.original_nr = rv.original_nr
  AND t.fruit = rv.fruit
ORDER BY t.identifier, t.original_nr;

方案二：窗口函数结合条件分组（适合简单场景） #

通过给非梨的行标记排序键，让梨的行不参与排名计算：

SELECT
  identifier,
  original_nr,
  fruit,
  CASE WHEN fruit != '梨' THEN
    -- 仅基于非梨的行的排序逻辑计算Rank()
    RANK() OVER(
      PARTITION BY identifier 
      ORDER BY 
        -- 梨的行排序键设为null，会被排在分区末尾，不影响非梨行的排名计数
        CASE WHEN fruit != '梨' THEN original_nr ELSE NULL END
    )
  ELSE NULL END AS rank_over_except
FROM your_table
ORDER BY identifier, original_nr;

效果验证 #

假设你的数据如下：

以上两种方案都能得到符合预期的rank_over_except列，梨的行返回null，非梨行的排名跳过了梨的计数，完全匹配需求。

内容的提问来源于stack exchange，提问作者Michel K