在Presto SQL中按分钟时间区间汇总数值的实现咨询

现有数据集包含Start（开始时间戳）、Stop（结束时间戳）、Int 1、Int 2等字段，需求是按分钟维度，汇总所有该分钟处于Start与Stop区间内的Int 1和Int 2值。

示例场景 #

Record 1:
Start = 2023-12-01 00:00:22.000
Stop = 2023-12-01 00:05:20.000
Int 1 = 2384881920
Int 2 = 286304

Record 2:
Start = 2023-12-01 00:00:23.000
Stop = 2023-12-01 00:06:40.000
Int 1 = 132286544
Int 2 = 13107

Record 3:
Start = 2023-12-01 00:00:31.000
Stop = 2023-12-01 00:36:00.000
Int 1 = 6373964800
Int 2 = 533764

• 00:00分钟：三条记录都覆盖该分钟，Int1总和为三者相加，Int2同理
• 00:05分钟：Record1在00:05:20结束，仍覆盖该分钟，三条记录都计入汇总
• 00:06分钟：Record1已结束，仅Record2、3计入汇总

最初尝试自连接生成时间桶但未成功，SQL如下：

Select id, timestamp From dB as a
Join dB as b using (id)
Where a.timestamp BETWEEN a.timestamp AND b.timestamp - INTERVAL '1' minute

提问：是否可通过窗口函数、转换为Unix时间并拆分分钟行的方式实现该汇总？

完全可以实现，以下两种方法满足需求：

方法1：生成分钟时间桶 + 关联汇总 #

思路 #

1. 生成覆盖所有数据时间范围的分钟级时间桶，从最早Start取整到分钟，到最晚Stop取整到分钟，确保无遗漏。
2. 将时间桶与原始数据关联，筛选出覆盖当前分钟的记录。
3. 按时间桶分组汇总Int1和Int2。

示例SQL（PostgreSQL） #

WITH minute_buckets AS (
    -- 生成所有需要的分钟桶
    SELECT generate_series(
        date_trunc('minute', MIN(Start)),
        date_trunc('minute', MAX(Stop)),
        INTERVAL '1 minute'
    ) AS bucket_time
    FROM dB
),
bucket_data AS (
    -- 关联筛选覆盖当前分钟的记录
    SELECT
        mb.bucket_time,
        d."Int 1",
        d."Int 2"
    FROM minute_buckets mb
    JOIN dB d
    ON mb.bucket_time <= d.Stop
    -- 记录开始时间的分钟 <= 当前桶的下一分钟，说明覆盖当前桶
    AND date_trunc('minute', d.Start) <= mb.bucket_time + INTERVAL '1 minute'
)
-- 按分钟汇总
SELECT
    bucket_time,
    SUM("Int 1") AS total_int1,
    SUM("Int 2") AS total_int2
FROM bucket_data
GROUP BY bucket_time
ORDER BY bucket_time;

方法2：窗口函数 + 事件流转换（基于时间戳） #

思路 #

1. 把每条记录拆成两个事件：启动事件（在Start的分钟点加上对应数值）、结束事件（在Stop的下一分钟点减去对应数值——因为Stop所在分钟仍需计入）。
2. 按时间排序后，用窗口函数计算累计总和，得到每个分钟的实时汇总值。

示例SQL（PostgreSQL） #

WITH events AS (
    -- 启动事件：添加数值
    SELECT
        date_trunc('minute', Start) AS event_time,
        "Int 1" AS delta_int1,
        "Int 2" AS delta_int2
    FROM dB
    UNION ALL
    -- 结束事件：减去数值（下一分钟生效）
    SELECT
        date_trunc('minute', Stop) + INTERVAL '1 minute' AS event_time,
        -"Int 1" AS delta_int1,
        -"Int 2" AS delta_int2
    FROM dB
),
running_totals AS (
    -- 计算累计总和
    SELECT
        event_time,
        SUM(delta_int1) OVER (ORDER BY event_time) AS total_int1,
        SUM(delta_int2) OVER (ORDER BY event_time) AS total_int2
    FROM events
    ORDER BY event_time
)
-- 输出每个分钟的汇总值
SELECT
    event_time AS minute_bucket,
    total_int1,
    total_int2
FROM running_totals;

补充说明 #

• 方法2性能更优，避免了全量关联的开销，适合大数据量场景。
• 如果需要补全无事件的空白分钟（比如某分钟没有启动/结束，但有持续记录），可以将running_totals与方法1的minute_buckets关联，用LAG函数填充前一个非空值。

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