如何将单日期列拆分为成对关联的两列（SQL场景）

需求说明 #

需将单个日期列拆分为两列，每行的Column1为当前行日期，Column2为下一行日期，具体示例如下：

原始数据列 #

Column
-------------------
28.10.2022 00:25:13
02.11.2022 10:20:23
08.11.2022 08:25:26
29.11.2022 09:50:21
02.12.2022 01:01:13
27.12.2022 22:30:02

目标效果 #

Column1             | Column2
--------------------+--------------------
28.10.2022 00:25:13 | 02.11.2022 10:20:23
02.11.2022 10:20:23 | 08.11.2022 08:25:26
08.11.2022 08:25:26 | 29.11.2022 09:50:21
29.11.2022 09:50:21 | 02.12.2022 01:01:13
02.12.2022 01:01:13 | 27.12.2022 22:30:02

注：目标效果中部分日期疑似笔误，已修正为原始数据中的对应日期

背景补充 #

table_date表存储操作ID（id）、日期（DATE）及随日期变化的BUCKET值，现有一段用于分组处理的SQL代码，需结合该表结构实现上述列拆分需求：

WITH groups as 
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY DATE) AS rn,
        (DATE - (ROW_NUMBER() OVER (PARTITION BY id ORDER BY DATE))) AS grp,
        DATE,
        d.BUCKET, 
        d.id
    FROM 
        table_date d
    WHERE 
        d.id = '123'
)
SELECT
    MIN(g.DATE) AS DATE_IN,
    MAX(g.DATE) AS DATA_OUT, 
    g.id, 
    g.BUCKET
FROM 
    groups g
GROUP BY 
    g.grp, g.id, g.GROUP_ID, g.BUCKET_ID
ORDER BY 
    MIN(g.DATE)

解决方案 #

要实现当前行与下一行日期的配对，最直接的方式是使用SQL的LEAD()窗口函数——它能在同一结果集中，获取当前行之后指定偏移量的行数据。以下是结合表结构和需求的实现方案：

基础实现（直接生成日期对） #

SELECT
    DATE AS Column1,
    LEAD(DATE) OVER (PARTITION BY id ORDER BY DATE) AS Column2,
    id,
    BUCKET
FROM table_date
WHERE id = '123'
ORDER BY DATE

• LEAD(DATE) OVER (PARTITION BY id ORDER BY DATE)：按id分组、日期排序后，获取当前行的下一行日期作为Column2
• 结果集中最后一行的Column2会返回NULL，若需过滤该行，可在外层添加筛选：

WITH date_pairs AS (
    SELECT
        DATE AS Column1,
        LEAD(DATE) OVER (PARTITION BY id ORDER BY DATE) AS Column2,
        id,
        BUCKET
    FROM table_date
    WHERE id = '123'
)
SELECT Column1, Column2, id, BUCKET
FROM date_pairs
WHERE Column2 IS NOT NULL
ORDER BY Column1

结合原有分组逻辑的实现 #

如果需要保留原SQL中的分组（grp）逻辑，同时生成日期对，可修改CTE部分：

WITH groups AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY DATE) AS rn,
        (DATE - ROW_NUMBER() OVER (PARTITION BY id ORDER BY DATE)) AS grp,
        DATE AS Column1,
        LEAD(DATE) OVER (PARTITION BY id ORDER BY DATE) AS Column2,
        BUCKET,
        id
    FROM table_date d
    WHERE d.id = '123'
)
SELECT
    Column1,
    Column2,
    id,
    BUCKET,
    grp
FROM groups
WHERE Column2 IS NOT NULL
ORDER BY Column1

这个查询既保留了原有的分组标识，又实现了日期列的拆分需求，最终结果会按日期排序输出每一行的当前日期与下一行日期配对。

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