如何使用generate_series按周区间统计数值总和？

问题背景 #

使用 generate_series 生成时间区间做统计时，按月区间统计能正常返回每个月份对应的 facts.sends 字段总和，但按周区间统计无法正确累加数值。

问题根源 #

这类问题大多出在周区间的匹配逻辑或日期截断方式上：

• 按月统计时，date_trunc('month', created_at) 能精准将数据映射到对应月份，和 generate_series 生成的月份区间匹配无歧义；
• 按周统计时，PostgreSQL 默认周起始为周一（部分环境可能是周日），若 generate_series 生成的周区间起始和数据截断后的周起始不一致，会导致关联匹配错误；另外，若只用等值判断而非区间包含的关联条件，也会出现数据漏匹配的情况，最终导致累加异常。

正确的按周统计实现示例 #

WITH weekly_intervals AS (
  SELECT 
    generate_series(
      '2023-01-01'::date, 
      '2023-12-31'::date, 
      '1 week'::interval
    ) AS week_start,
    generate_series(
      '2023-01-01'::date + '6 days'::interval, 
      '2023-12-31'::date + '6 days'::interval, 
      '1 week'::interval
    ) AS week_end
)
SELECT 
  wi.week_start,
  wi.week_end,
  COALESCE(SUM(f.sends), 0) AS total_sends
FROM weekly_intervals wi
LEFT JOIN facts f 
  ON f.created_at >= wi.week_start 
  AND f.created_at < wi.week_end + '1 day'::interval -- 覆盖周最后一天的所有时间点
GROUP BY wi.week_start, wi.week_end
ORDER BY wi.week_start;

关键说明 #

1. 明确周区间范围：用 week_start 和 week_end 定义完整周区间，避免仅依赖 date_trunc 带来的起始日歧义；
2. 关联条件用区间包含：不用等值判断，而是通过范围判断确保所有符合条件的数据都能被匹配到对应周区间；
3. 处理空值场景：用 COALESCE 将无数据周的统计结果转为 0，保证输出结果的完整性。

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