如何基于client_id、code及日期顺序生成唯一newid

原始数据集 #

以下是已按date1排序的数据集：

字段说明：

• client_id：客户ID
• code：状态码
• date1：起始日期
• date2：结束日期
• t：两个日期的差值

期望结果 #

需要生成newid字段，规则为：同一客户连续使用相同code时newid相同；当客户变更code时分配新的newid；后续客户再次使用之前的code，仍需分配新的唯一newid。最终效果如下：

实现方法 #

可以使用SQL窗口函数实现，核心思路是通过对比当前行与前一行的code值，标记状态变更点，再累加标记值得到分组ID。

SQL代码示例 #

SELECT 
    client_id,
    code,
    date1,
    date2,
    t,
    SUM(flag) OVER (PARTITION BY client_id ORDER BY date1) AS newid
FROM (
    SELECT 
        client_id,
        code,
        date1,
        date2,
        t,
        CASE 
            WHEN LAG(code) OVER (PARTITION BY client_id ORDER BY date1) = code THEN 0
            ELSE 1
        END AS flag
    FROM your_table_name
) AS subquery;

代码说明 #

1. 内层子查询：用LAG(code)获取当前客户上一行的code值，若与当前行code不同则标记为1，否则标记为0；
2. 外层查询：按客户分组、日期排序，对标记值进行累加，累加结果就是连续相同code分组的唯一newid。

该方法适用于支持窗口函数的SQL数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）。

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