SQL排名时如何添加与前一行状态关联的额外排序规则

结论 #

无法直接将「避免连续相同状态」的规则集成到单次rank() over (partition by ... order by ...)的逻辑中。因为rank的排序依据是行本身的固定属性，而避免连续状态重复的规则依赖于已排序结果中前序行的状态，属于有上下文依赖的迭代判断，单次窗口函数无法直接支持该逻辑。

可行实现方案 #

可以通过多层CTE+窗口函数组合的方式实现需求，代码示例如下：

WITH base_data AS (
    SELECT 
        *,
        -- 按时间戳分组，相同时间戳的记录归属同一组
        dense_rank() OVER (ORDER BY timestamp ASC) AS ts_group,
        -- 获取上一个时间戳分组的最后一个状态
        lag(status) OVER (ORDER BY timestamp ASC) AS last_prev_status
    FROM 你的工单表名
),
group_inner_sorted AS (
    SELECT 
        *,
        -- 同时间戳组内排序：和前序状态不同的记录优先排在前面
        row_number() OVER (
            PARTITION BY ts_group 
            ORDER BY CASE WHEN status != last_prev_status THEN 0 ELSE 1 END
        ) AS inner_rank
    FROM base_data
)
-- 生成最终连续稳定的排序序号
SELECT 
    *,
    row_number() OVER (ORDER BY ts_group ASC, inner_rank ASC) AS final_sort_num
FROM group_inner_sorted

逻辑说明 #

1. 第一层CTE先完成基础时间维度的排序分组，同时拿到前一个时间点的最后工单状态
2. 第二层CTE处理相同时间戳的记录排序，优先排和前序状态不同的记录，确保不会出现连续相同状态
3. 最终生成的final_sort_num是连续不重复的稳定序号，完全符合排序要求

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