SQL实现为重复行分配相同Day标签并按ID分组生成日期增量标签

实现逻辑 #

直接通过组内基准日期差值计算即可天然匹配所有规则，不需要额外去重、排序生成序列操作：

1. 先按ID分组取每组的最早日期作为基准日
2. 计算每行日期与对应组基准日的天数差，加1即为Day标签

代码实现 #

窗口函数版本（适配所有支持窗口函数的主流数据库：MySQL 8.0+、PostgreSQL、SQL Server、Hive、Spark SQL等） #

假设你的表名为data_table，分组字段为id，日期字段为dt（DATE类型）：

SELECT
    id,
    dt,
    -- 不同数据库仅需替换日期差计算逻辑即可
    -- MySQL写法
    DATEDIFF(dt, MIN(dt) OVER (PARTITION BY id)) + 1 AS day_label
    -- PostgreSQL写法：
    -- (dt - MIN(dt) OVER (PARTITION BY id))::INTEGER + 1 AS day_label
    -- SQL Server写法：
    -- DATEDIFF(day, MIN(dt) OVER (PARTITION BY id), dt) +1 AS day_label
    -- Oracle写法：
    -- TRUNC(dt) - TRUNC(MIN(dt) OVER (PARTITION BY id)) +1 AS day_label
FROM data_table;

低版本MySQL兼容版本（不支持窗口函数时使用） #

SELECT
    a.id,
    a.dt,
    DATEDIFF(a.dt, b.min_dt) + 1 AS day_label
FROM data_table a
INNER JOIN (
    SELECT id, MIN(dt) AS min_dt
    FROM data_table
    GROUP BY id
) b ON a.id = b.id;

规则匹配验证 #

• 按ID分组后组内首个日期和基准日差值为0，加1后值为1，符合第一条规则
• 同组相同日期与基准日的差值完全一致，因此相同日期所有行的Day标签完全相同，且不会过滤任何原始行，符合第二条规则
• 天数差为两个日期的实际间隔，日期不连续时标签会按实际间隔自动递推，不需要额外处理间隔逻辑，符合第三条规则

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