基于唯一ID拆分日期列为两列 计算同ID相邻Bucket日期差

现有存储ID对应Bucket状态与日期的明细表，原始表结构及样例数据如下：

需要将表转换为相邻状态对齐的格式，把每次Bucket变更的前后状态、对应日期放在同一行，方便后续计算两次状态的日期间隔，目标输出样例如下：

最简洁高效的实现方式是使用窗口函数LEAD()，不需要做复杂的自连接，所有支持SQL 2003标准的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Hive、Spark SQL等）都可以直接用：

WITH temp AS (
    SELECT
        ID,
        Bucket1,
        Date1,
        -- 按ID分组、日期升序排列后，取下一条记录的Bucket和日期
        LEAD(Bucket1) OVER (PARTITION BY ID ORDER BY Date1) AS Bucket2,
        LEAD(Date1) OVER (PARTITION BY ID ORDER BY Date1) AS Date2
    FROM your_original_table
)
SELECT ID, Bucket1, Date1, Bucket2, Date2
FROM temp
-- 排除每个ID下没有后续变更的最后一条记录
WHERE Bucket2 IS NOT NULL;

逻辑说明 #

• PARTITION BY ID：按唯一ID拆分数据分组，避免跨ID错误匹配状态
• ORDER BY Date1：每个分组内按日期从早到晚排序，保证前后状态是时间上相邻的两次变更
• LEAD()函数直接获取排序后当前行的下一行指定字段值，比自连接写法性能更高
• 最后过滤掉Bucket2为空的行，得到的结果和目标样例完全一致

如果是用不支持窗口函数的老版本MySQL（5.x及以下），可以用用户变量模拟行号后做自连接实现：

-- 给每个ID下的记录按日期生成递增序号
SET @row := 0;
SET @pre_id := '';
CREATE TEMPORARY TABLE ranked_tab AS
SELECT
    ID,
    Bucket1,
    Date1,
    @row := IF(@pre_id = ID, @row + 1, 1) AS rn,
    @pre_id := ID
FROM your_original_table
ORDER BY ID, Date1;

-- 相邻序号关联得到前后状态对
SELECT
    t1.ID,
    t1.Bucket1,
    t1.Date1,
    t2.Bucket1 AS Bucket2,
    t2.Date1 AS Date2
FROM ranked_tab t1
INNER JOIN ranked_tab t2
ON t1.ID = t2.ID AND t1.rn + 1 = t2.rn;

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