如何用DENSE_RANK实现按id和status生成目标d_rank列？

当前问题 #

需要实现与目标d_rank列一致的排名逻辑，目前使用的代码未达到预期效果：

DENSE_RANK() OVER (PARTITION BY id, status ORDER BY created) as d_rank

期望结果 #

解决方案 #

原代码的问题在于同时按id和status分区，导致同一id下相同status的记录会被单独排名，不符合需求中“同一id下，按status出现顺序分配连续排名，相同status共享同一排名”的逻辑。以下两种方法可实现目标：

方法1：基于状态首次出现时间的DENSE_RANK #

先统计每个id下各status的最早出现时间，再基于该时间对id内的状态做连续排名：

WITH ranked_status AS (
    SELECT 
        id, 
        status, 
        MIN(created) AS first_created
    FROM your_table
    GROUP BY id, status
)
SELECT 
    t.created,
    t.id,
    t.status,
    DENSE_RANK() OVER (PARTITION BY t.id ORDER BY rs.first_created) AS d_rank
FROM your_table t
JOIN ranked_status rs ON t.id = rs.id AND t.status = rs.status
ORDER BY t.id, t.created;

方法2：基于状态变化的累加排名 #

用LAG()函数标记状态变化，再对变化标记做累加得到连续排名：

WITH status_changes AS (
    SELECT 
        created,
        id,
        status,
        CASE 
            WHEN LAG(status) OVER (PARTITION BY id ORDER BY created) = status THEN 0
            ELSE 1
        END AS is_new_status
    FROM your_table
)
SELECT 
    created,
    id,
    status,
    SUM(is_new_status) OVER (PARTITION BY id ORDER BY created) AS d_rank
FROM status_changes
ORDER BY id, created;

说明 #

• 方法1适合需要明确状态首次出现时间的场景，排名严格按状态出现的先后顺序分配。
• 方法2更高效，直接通过相邻行对比标记变化，累加后得到的排名完全匹配预期结果。

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