SQL结合CASE使用DENSE_RANK时如何让符合条件的排名从1开始

• 判定规则：当Amount字段值大于120且Stage ='W'时，才给对应记录计算排名
• 排序规则：基于Amount、date两个字段排序做密集排名
• 现存问题：当前写法下排名相对顺序符合预期，但不满足判定条件的记录也占用了排名序号，导致第一条符合条件的记录排名从3开始，需要调整为符合条件的记录独立从1开始排名

case when Amount>120 and stage='W' then
dense_rank() over(partition by stage,owner order by Amount,date)  end as first_120

运行效果参考：

窗口函数计算优先级高于外层case when：当前dense_rank()会对stage,owner分区下所有记录统一计算排名，外层case when仅负责把不符合条件的记录排名显示为null，并没有把这些记录排除在排名计算范围外，因此符合条件的记录会保留全量计算的排名值，无法从1起始。

通用SQL写法（兼容所有主流SQL引擎） #

把排名的判定条件加入窗口分区规则，让符合条件的记录在独立子分区内单独计算排名即可，代码如下：

case when Amount>120 and stage='W' then
  dense_rank() over(
    partition by stage, owner, case when Amount>120 and stage='W' then 1 else 0 end 
    order by Amount, date
  )  
end as first_120

调整后，不满足排名条件的记录会被划分到独立分区，不会占用符合条件记录的排名序号，符合条件的记录会在同个分区内从1开始计算排名，排序逻辑和原有预期完全一致。

简化写法（支持FILTER语法的引擎适用） #

如果使用PostgreSQL、Spark SQL、BigQuery等支持窗口函数FILTER子句的引擎，可以直接用FILTER把不符合条件的记录排除在窗口计算范围外，写法更简洁：

dense_rank() over(
  partition by stage,owner 
  order by Amount,date
) filter (where Amount>120 and stage='W') as first_120

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