基于多类型事件时间戳生成时间序列并计算分组累计和的实现方案咨询

我是SQL新手，目前的实现是靠Stack Overflow的帮助才完成的。我有一张记录各类事件发生情况的表，现在想把这些数据转换成填补事件间时间间隙的时间序列，同时按事件类型分组计算累计值。简单来说，就是要从单个事件生成完整时间序列，并按事件组计算滚动/累计和。

源数据示例 #

期望输出 #

当前已实现的单类型效果 #

目前我只能实现单事件类型的时间序列和累计和，输出如下：

对应的PostgreSQL代码：

SELECT
generate_series AS timestamp,
-- 硬编码事件类型
COALESCE(events.type, 1) AS type,
COALESCE(events.value, 0) AS value,
COALESCE(SUM(td.value) OVER (ORDER BY generate_series), 0) AS cumulative_sum
FROM
generate_series('2023-01-01'::timestamp, '2023-01-07'::timestamp, '1 day') AS generate_series
LEFT JOIN
-- 硬编码事件类型
events ON generate_series = events.event_timestamp AND event.type = 1
ORDER BY
generate_series;

我的疑问 #

• 是否建议结合SQL和Python（比如用Python循环执行单类型语句再插入结果）来完成这个需求？
• 是否应该把时间序列生成和累计和计算分开处理？
• 如果推荐纯SQL方案，怎么实现支持多事件类型的分组计算？

针对你的问题，我的建议如下： #

1. 纯SQL vs SQL+Python：优先选纯SQL

从性能和维护性来说，纯SQL方案更优。数据库在处理时间序列和窗口函数这类操作上是经过优化的，比用Python循环调用SQL再合并结果要高效得多，尤其是数据量较大的时候。而且纯SQL代码更集中，后续维护也更方便，不需要额外维护Python脚本的逻辑。

2. 是否分开处理时间序列和累计和？

不需要分开，PostgreSQL可以在一个查询里完成这两个步骤，逻辑上更连贯，也能减少中间数据的生成。

3. 纯SQL实现多类型分组的方案

核心思路是先生成所有日期和所有事件类型的笛卡尔积，再关联原始事件数据，最后用窗口函数按类型分组计算累计和。具体代码如下：

WITH date_series AS (
    -- 生成完整的时间序列
    SELECT generate_series AS event_timestamp
    FROM generate_series('2023-01-01 10:00'::timestamp, '2023-01-07 10:00'::timestamp, '1 day') AS generate_series
),
all_type_dates AS (
    -- 生成日期和事件类型的笛卡尔积，确保每个日期下所有类型都有记录
    SELECT 
        ds.event_timestamp,
        t.type
    FROM date_series ds
    CROSS JOIN (SELECT DISTINCT type FROM events) t
),
event_values AS (
    -- 关联原始事件数据，填充value值，无事件则为0
    SELECT 
        atd.event_timestamp,
        atd.type,
        COALESCE(e.value, 0) AS value
    FROM all_type_dates atd
    LEFT JOIN events e 
        ON atd.event_timestamp = e.event_timestamp 
        AND atd.type = e.type
)
-- 计算分组累计和
SELECT
    event_timestamp,
    type,
    value,
    SUM(value) OVER (PARTITION BY type ORDER BY event_timestamp) AS cumulative_sum
FROM event_values
ORDER BY event_timestamp, type;

代码解释：

• date_series：生成你需要的完整时间区间序列，确保没有时间间隙。
• all_type_dates：用CROSS JOIN把每个日期和所有事件类型组合起来，这样每个日期下每个类型都会有一条记录，解决了多类型的覆盖问题。
• event_values：把笛卡尔积的结果和原始事件表关联，没有对应事件的记录value填0。
• 最后一步用SUM() OVER (PARTITION BY type ORDER BY event_timestamp)按事件类型分组，按时间排序计算累计和，正好符合你的需求。

这个方案会直接输出你期望的结果，不需要额外的脚本处理，而且能灵活适配事件类型的新增（不需要修改代码硬编码类型）。

备注：内容来源于stack exchange，提问作者Michel