如何用SQL RANK()窗口函数实现对话轮次排序需求？

原始表结构 #

question_id    message_id    responder
          1             1    user_a
          1             2    user_b
          1             3    user_b
          1             4    user_a

当前SQL及结果 #

使用以下SQL语句：

RANK() OVER (PARTITION BY question_id, responder ORDER BY message_id)

得到结果：

question_id    message_id    responder    rank()
          1             1    user_a            1
          1             2    user_b            1
          1             3    user_b            2
          1             4    user_a            2

期望输出 #

question_id    message_id    responder    rank()
          1             1    user_a            1
          1             2    user_b            2
          1             3    user_b            2
          1             4    user_a            3

修改后的SQL语句 #

要实现「对话者切换时排名递增，同一对话者的连续消息保持同一排名」的效果，需要先识别对话者的切换标记，再通过累加标记得到目标排名：

SELECT
    question_id,
    message_id,
    responder,
    SUM(switch_flag) OVER (PARTITION BY question_id ORDER BY message_id) AS rank
FROM (
    SELECT
        question_id,
        message_id,
        responder,
        -- 标记当前消息是否切换了对话者
        CASE 
            WHEN LAG(responder) OVER (PARTITION BY question_id ORDER BY message_id) != responder 
            THEN 1 
            ELSE 0 
        END AS switch_flag
    FROM your_table_name -- 替换为你的实际表名
) AS sub_query
ORDER BY message_id;

逻辑说明 #

1. 子查询中用LAG(responder)获取当前消息的上一条消息的对话者，与当前responder对比：若不同则标记为1（表示对话轮次切换），否则标记为0。
2. 外层通过SUM(switch_flag)按question_id分区、message_id排序做累加运算，每次切换对话者时累加值加1，同一对话者的连续消息累加值保持一致，最终得到符合需求的排名。

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