如何计算设备与服务器非周期性时间序列的分组重叠时长？

我有两张无固定时间间隔的时序表（示例用10秒间隔仅为简化），无法使用TimeScaleDB或其他工具，需要计算24小时内设备表中各设备在线时间段与ServerOnline表在线时间段的重叠时长，精度到1秒。

我试过用generateSeries将数据重采样到1秒并补全空缺，但都失败了：要么结果不符合预期，要么PostgreSQL无响应、pgadmin崩溃。

如果用C类语言实现，我会给两个表的行分别创建游标，单遍遍历整张表。请问在PostgreSQL中能不能实现这种逻辑？

示例表与数据 #

DeviceOnline表

ServerOnline表

预期输出 #

方案1：纯SQL区间计算（推荐，性能最优） #

不需要生成每秒的采样行，直接通过时间区间的数学计算获取重叠时长，避免generateSeries带来的性能问题。核心逻辑是计算两个时间段的重叠部分：对于设备时间段[ds, de]和服务器时间段[ss, se]，重叠时长为GREATEST(0, LEAST(de, se) - GREATEST(ds, ss))，再按设备分组求和。

WITH time_window AS (
    -- 定义目标24小时时间窗口，可根据实际需求调整
    SELECT '2023-07-03 00:00:00'::timestamp AS window_start,
           '2023-07-04 00:00:00'::timestamp AS window_end
)
SELECT
    do.device_id AS Device,
    CONCAT(COALESCE(SUM(
        EXTRACT(EPOCH FROM GREATEST(0::interval, 
            LEAST(do.end, tw.window_end) - GREATEST(do.start, tw.window_start, so.start)
        ))
    ), 0), 's') AS Duration
FROM DeviceOnline do
CROSS JOIN time_window tw
LEFT JOIN ServerOnline so
    -- 过滤出时间窗口内的重叠时间段
    ON GREATEST(do.start, tw.window_start) < LEAST(do.end, tw.window_end)
    AND GREATEST(so.start, tw.window_start) < LEAST(so.end, tw.window_end)
    AND GREATEST(do.start, so.start) < LEAST(do.end, so.end)
GROUP BY do.device_id
ORDER BY do.device_id;

方案2：PL/pgSQL双游标遍历（模拟C类语言逻辑） #

如果需要严格模拟双游标单遍遍历的逻辑，可以用PL/pgSQL编写函数，同时遍历排序后的设备和服务器在线记录，逐段计算重叠时长。

CREATE OR REPLACE FUNCTION calculate_overlap_duration()
RETURNS TABLE(device_id text, duration text) AS $$
DECLARE
    cur_device CURSOR FOR 
        SELECT device_id, start, end 
        FROM DeviceOnline 
        ORDER BY start;
    cur_server CURSOR FOR 
        SELECT start, end 
        FROM ServerOnline 
        ORDER BY start;
    rec_device record;
    rec_server record;
    current_server record;
    total_sec bigint := 0;
    current_device text;
BEGIN
    OPEN cur_server;
    FETCH cur_server INTO current_server;

    OPEN cur_device;
    FETCH cur_device INTO rec_device;
    current_device := rec_device.device_id;

    LOOP
        EXIT WHEN rec_device IS NULL;

        -- 跳过设备记录早于当前服务器记录的情况
        WHILE current_server IS NOT NULL AND rec_device.end <= current_server.start LOOP
            FETCH cur_device INTO rec_device;
            IF rec_device IS NULL THEN
                EXIT;
            END IF;
            -- 切换设备时输出上一个设备的统计结果
            IF rec_device.device_id != current_device THEN
                RETURN NEXT (current_device, CONCAT(total_sec, 's'));
                current_device := rec_device.device_id;
                total_sec := 0;
            END IF;
        END LOOP;

        -- 服务器记录遍历完毕，剩余设备无重叠
        IF current_server IS NULL THEN
            LOOP
                RETURN NEXT (rec_device.device_id, '0s');
                FETCH cur_device INTO rec_device;
                EXIT WHEN rec_device IS NULL;
            END LOOP;
            EXIT;
        END IF;

        -- 计算当前设备与服务器记录的重叠时长
        IF GREATEST(rec_device.start, current_server.start) < LEAST(rec_device.end, current_server.end) THEN
            total_sec := total_sec + EXTRACT(EPOCH FROM 
                LEAST(rec_device.end, current_server.end) - GREATEST(rec_device.start, current_server.start)
            );
        END IF;

        -- 移动游标：设备记录结束早则移动设备游标，否则移动服务器游标
        IF rec_device.end > current_server.end THEN
            FETCH cur_server INTO current_server;
        ELSE
            FETCH cur_device INTO rec_device;
            IF rec_device IS NOT NULL AND rec_device.device_id != current_device THEN
                RETURN NEXT (current_device, CONCAT(total_sec, 's'));
                current_device := rec_device.device_id;
                total_sec := 0;
            END IF;
        END IF;
    END LOOP;

    -- 输出最后一个设备的统计结果
    IF current_device IS NOT NULL THEN
        RETURN NEXT (current_device, CONCAT(total_sec, 's'));
    END IF;

    CLOSE cur_device;
    CLOSE cur_server;
END;
$$ LANGUAGE plpgsql;

-- 调用函数获取结果
SELECT * FROM calculate_overlap_duration();

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