如何用SQL的Rank()函数实现Mark=0为0、Mark=1分段递增的排名？

问题描述 #

尝试用SQL的Rank()函数实现分段排名，当前使用的SQL语句如下，但Mark为1的行仍得到全局连续排名，仅Mark为0时能正确返回0：

rank() over (partition by (case when mark= '0' then '0' end), id order by date asc, case when mark = '0' then '0' end) end as rank

期望实现的排名效果如下：

核心需求：

• Mark为0时，Rank固定为0
• Mark为1时，在被0分隔的连续1分段中，Rank从1开始依次递增

解决方案 #

要实现这个分段排名，关键是先给每个连续的Mark=1区间生成唯一分组标识，再在分组内计算排名。可以用累计求和的方式生成分组：

SELECT
    id,
    date,
    mark,
    CASE
        WHEN mark = '0' THEN 0
        ELSE ROW_NUMBER() OVER (PARTITION BY id, group_id ORDER BY date ASC)
    END AS rank
FROM (
    SELECT
        id,
        date,
        mark,
        -- 累计统计当前行之前出现的Mark=0的次数，作为分组ID
        SUM(CASE WHEN mark = '0' THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM your_table_name
) t
ORDER BY date ASC;

逻辑说明 #

1. 子查询中，用SUM() OVER()窗口函数计算每个行之前（含当前行）Mark=0的累计次数，这个值作为group_id：
   • 每遇到一个Mark=0的行，group_id会加1
   • 连续的Mark=1的行，会共享同一个group_id（中间无0，累计值不变）
2. 外层查询中，对Mark=0的行直接返回0；对Mark=1的行，按id和group_id分区，用ROW_NUMBER()按日期排序生成从1开始的递增排名。

注意：如果mark字段是数值类型（非字符串），把SQL里的'0'改成0即可。

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