PostgreSQL：如何通过关联多列生成指定时间分段的起止数据

表结构与测试数据 #

CREATE TABLE table1 (
    id integer,
    date_strt timestamp,
    date_end timestamp,
    strt_unit integer,
    end_unit integer
);

INSERT INTO table1 (id, date_strt, date_end,strt_unit,end_unit)
VALUES
    (1, '2023-10-27 12:00:00','2023-10-31 12:00:00', 5,72),
    (2, '2023-10-30 12:15:00','2023-11-02 00:00:00', 78,90);
    
    
CREATE TABLE table2 (
    id integer,
    dates timestamp,
    unit integer
);

INSERT INTO table2 (id, dates, unit)
VALUES
    (1, '2023-10-28 00:00:00', 55),
    (1, '2023-10-29 00:00:00', 60),
    (1, '2023-10-30 00:00:00', 65),
    (1, '2023-10-31 00:00:00', 70),
    (2, '2023-10-30 00:00:00', 75),
    (2, '2023-10-31 00:00:00', 80),
    (2, '2023-11-01 00:00:00', 85),
    (2, '2023-11-02 00:00:00', 90);

需求说明 #

需要以table1的事件起止时间为范围，按每日午夜00:00:00分段，生成每段的起止时间及对应数值，输出格式如下：

id start_time          start_value    end_time            end_value
1, '2023-10-27 12:00:00', 5,      '2023-10-28 00:00:00', 55
1, '2023-10-28 00:00:00', 55,     '2023-10-29 00:00:00', 60
1, '2023-10-29 00:00:00', 60,     '2023-10-30 00:00:00', 65
1, '2023-10-30 00:00:00', 65,     '2023-10-31 00:00:00', 70
1, '2023-10-31 00:00:00', 70,     '2023-10-31 12:00:00', 72
2, '2023-10-30 12:15:00', 78,     '2023-10-31 00:00:00', 80    
2, '2023-10-31 00:00:00', 80,     '2023-11-01 00:00:00', 85
2, '2023-11-01 00:00:00', 85,     '2023-11-02 00:00:00', 90

解决方案 #

通过整合事件起始、午夜节点和结束时间，结合窗口函数关联相邻时间点，可实现需求，具体SQL如下：

WITH time_points AS (
    -- 提取事件起始时间及对应数值
    SELECT id, date_strt AS point_time, strt_unit AS point_value
    FROM table1
    UNION ALL
    -- 提取事件范围内的所有午夜时间点及对应数值
    SELECT t1.id, t2.dates AS point_time, t2.unit AS point_value
    FROM table1 t1
    JOIN table2 t2 
        ON t1.id = t2.id
        AND t2.dates > t1.date_strt
        AND t2.dates < t1.date_end
    UNION ALL
    -- 提取事件结束时间及对应数值
    SELECT id, date_end AS point_time, end_unit AS point_value
    FROM table1
),
ordered_points AS (
    -- 按id和时间排序，获取每个时间点的下一个节点信息
    SELECT 
        id, 
        point_time, 
        point_value,
        LEAD(point_time) OVER (PARTITION BY id ORDER BY point_time) AS next_point_time,
        LEAD(point_value) OVER (PARTITION BY id ORDER BY point_time) AS next_point_value
    FROM time_points
)
-- 过滤无后续节点的记录，输出最终分段结果
SELECT 
    id,
    point_time AS start_time,
    point_value AS start_value,
    next_point_time AS end_time,
    next_point_value AS end_value
FROM ordered_points
WHERE next_point_time IS NOT NULL
ORDER BY id, start_time;

思路说明 #

1. 整合时间节点：用UNION ALL将事件起始时间、范围内的午夜时间点、事件结束时间合并成完整的时间序列；
2. 关联相邻节点：使用LEAD窗口函数，按id分组、时间排序，获取每个时间点对应的下一个时间点及数值；
3. 过滤输出：排除没有后续节点的记录（即事件的结束时间点），得到符合要求的分段结果。

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