SQL技术问题：如何为循环复用物品生成HISTORY连续序列列

原始数据表 #

业务背景 #

这张表记录物品（用KEYID区分）的生命周期：

• 物品第一次发布时（STAGE为PUBLISHEDDATE），会分配一个唯一ID
• 之后物品有两种走向：要么被查看（记录VIEWEDDATE）后循环复用，分配新ID和新的PUBLISHEDDATE；要么没被查看直接复用，同样分配新ID和PUBLISHEDDATE

需求与期望结果 #

需要新增一列HISTORY，满足：

• 按STAGEDATE的先后顺序，给每一次物品的循环复用分配连续的序号
• 同一个ID对应的PUBLISHEDDATE和VIEWEDDATE记录，必须用同一个HISTORY序号

期望得到的结果表：

之前的尝试问题 #

之前试过用下面的语句生成HISTORY：

DENSE_RANK() OVER(PARTITION BY KEYID, RECVRID ORDER BY STAGEDATE) AS HISTORY

但因为ID没有递增的保证，这个方法出来的结果不对。

解决方案 #

核心思路是：先找到每个ID对应的生命周期的起始日期（也就是这个ID第一次出现的PUBLISHEDDATE日期），再基于这个起始日期来排序生成连续序号。

方法一：用窗口函数获取每个ID的起始日期 #

WITH lifecycle_start AS (
    SELECT 
        ID,
        MIN(STAGEDATE) OVER(PARTITION BY ID) AS lifecycle_start_date
    FROM your_table
)
SELECT 
    t.ID,
    t.KEYID,
    t.RECVRID,
    t.STAGE,
    t.STAGEDATE,
    DENSE_RANK() OVER(PARTITION BY t.KEYID, t.RECVRID ORDER BY ls.lifecycle_start_date) AS HISTORY
FROM your_table t
JOIN lifecycle_start ls ON t.ID = ls.ID
ORDER BY t.STAGEDATE;

方法二：分组获取每个ID的起始日期 #

WITH id_start_dates AS (
    SELECT 
        ID,
        KEYID,
        RECVRID,
        MIN(STAGEDATE) AS start_date
    FROM your_table
    GROUP BY ID, KEYID, RECVRID
)
SELECT 
    t.*,
    DENSE_RANK() OVER(PARTITION BY t.KEYID, t.RECVRID ORDER BY isd.start_date) AS HISTORY
FROM your_table t
JOIN id_start_dates isd ON t.ID = isd.ID
ORDER BY t.STAGEDATE;

说明 #

• 两种方法都是先给每个ID确定它的生命周期起始时间，也就是这个ID对应的最早的STAGEDATE（也就是发布日期）
• 然后在同一个KEYID和RECVRID的分组里，根据这个起始日期做DENSE_RANK()排序，这样同一个ID的所有记录都会拿到相同的HISTORY序号，而且序号会按照生命周期的先后顺序连续递增
• 哪怕ID本身不递增，只要起始日期的顺序是对的，就能得到符合要求的结果

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