修改Postgres查询，使用generate_series计算多连续区间的累计总和

开发者阿泽

2026-10-5

实现思路

先用generate_series生成所有需要统计的连续时间区间的起始时间，拼接为完整的tsrange统计区间
将生成的统计区间与event表关联，筛选出与当前区间重叠的事件
对每个统计区间单独执行时间裁剪、单事件权重计算、汇总求和逻辑
最终输出每个时间区间对应的总累计值

实现代码

WITH target_intervals AS (
    -- 生成所有待统计的连续时间区间，示例为2021-08-01到2021-08-05的每日区间
    SELECT 
        tsrange(
            day_start,
            day_start + INTERVAL '1 day',
            '[)'
        ) AS stat_interval,
        day_start AS interval_sort
    FROM generate_series(
        '2021-08-01T00:00:00'::timestamp, -- 统计起始时间
        '2021-08-04T00:00:00'::timestamp, -- 最后一个统计区间的起始时间
        INTERVAL '1 day' -- 单个统计区间的跨度，可按需调整为1小时、1周等
    ) AS t(day_start)
)
SELECT 
    ti.stat_interval,
    SUM(
        EXTRACT(epoch FROM (
            LEAST(UPPER(e.order_dates), UPPER(ti.stat_interval)) 
            - GREATEST(LOWER(e.order_dates), LOWER(ti.stat_interval))
        ))::INTEGER * e.flow
    ) AS total_weight
FROM target_intervals ti
LEFT JOIN event e
    ON e.order_dates && ti.stat_interval
GROUP BY ti.stat_interval, ti.interval_sort
ORDER BY ti.interval_sort;