基于起止时间按秒聚合列，计算并发值的SQL方案问询

现有聚合表样例如下：

start_time                end_time                  duration_s    some_id     total_val
2023-03-30 01:03:19.000    2023-03-30 01:03:58.699    39.699        A          3098.214
2023-03-30 00:41:50.000    2023-03-30 01:03:59.920    1329.92       A          1264.4286
2023-03-30 01:01:48.000    2023-03-30 01:04:00.759    132.759       B          2323.6356
2023-03-30 00:56:55.000    2023-03-30 01:04:00.946    425.946       B          1676.4208
2023-03-30 01:03:50.000    2023-03-30 01:04:02.711    12.711        A          1211.4553

需求说明 #

需要按秒分组生成连续时间序列的聚合结果：

• 将每条记录的total_val（整个时段的总成本）平均分配到每秒，即计算total_val/duration_s作为单条记录的每秒单位成本
• 必须覆盖事件从start_time到end_time的所有秒级时段，不能仅按start_time分组

现有代码（按分钟聚合） #

我目前有一段按分钟聚合的可运行代码，但速度较慢：

WITH temp AS (
        SELECT
            total_val / duration_s AS usage_per_sec,
            SEQUENCE(start_time, end_time, INTERVAL '1' MINUTE) exploded_times
        FROM tbl
    ),
    temp2 AS (
        SELECT
            exploded_times,
            TRANSFORM(exploded_times, x -> usage_per_sec * 60) usage_per_min
        FROM temp
    ),
    temp3 AS (
        SELECT
            ts_min,
            usage
        FROM temp2
        CROSS JOIN UNNEST(exploded_times, usage_per_min) AS t (ts_min, usage)
    )

SELECT
    ts_min,
    SUM(usage)
FROM temp3
GROUP BY
    ts_min

按秒聚合的实现方案 #

基于现有思路调整，生成秒级时间序列并展开计算，优化后的代码如下：

WITH temp AS (
    -- 计算每秒单位成本，同时生成秒级时间序列
    SELECT
        total_val / duration_s AS usage_per_sec,
        -- 生成从start_time到end_time的每秒时间点序列（对齐到整秒）
        SEQUENCE(
            DATE_TRUNC('second', start_time),
            DATE_TRUNC('second', end_time),
            INTERVAL '1' SECOND
        ) exploded_seconds
    FROM tbl
),
temp2 AS (
    -- 展开时间序列，关联对应的每秒成本
    SELECT
        ts_sec,
        usage_per_sec
    FROM temp
    CROSS JOIN UNNEST(exploded_seconds) AS t (ts_sec)
)
-- 按秒级时间戳分组求和，得到每个秒的总并发成本
SELECT
    ts_sec AS ts,
    SUM(usage_per_sec) AS sum_total_val_per_sec
FROM temp2
GROUP BY ts_sec
ORDER BY ts_sec;

代码说明 #

1. 时间对齐：用DATE_TRUNC('second', ...)将开始、结束时间对齐到整秒，避免毫秒级的序列冗余
2. 秒级序列生成：将SEQUENCE步长改为INTERVAL '1' SECOND，直接生成每秒的时间点
3. 简化计算：无需额外转换分钟成本，直接用每秒单位成本展开后求和，减少中间步骤

期望输出样例 #

ts                        sum_total_val_per_sec
2023-03-29 23:59:56.000   196.93959475378
2023-03-29 23:59:57.000   46216.598554771
2023-03-29 23:59:58.000   57332.587457153
2023-03-29 23:59:59.000   404240.20792671
2023-03-30 00:00:00.000   252365.2397336
2023-03-30 00:00:01.000   665889.58236006
2023-03-30 00:00:02.000   587338.31764963

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