场景：日报表出现日期断点

按天统计订单或事件时，业务表通常只在有数据的日子产生记录。直接 GROUP BY 会漏掉零单日期，折线图或柱状图的 X 轴就不连续。稳妥做法是：先按天聚合业务表，再生成完整日期序列，最后 LEFT JOIN 并在缺失日补 0。

最小可运行 SQL

假设业务表如下，实际使用时替换表名、字段和占位日期：

CREATE TABLE biz_event (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL,
  amount numeric(12,2) NOT NULL DEFAULT 0
);

查询示例：

WITH params AS (
  SELECT DATE '2025-01-01' AS start_day,
         DATE '2025-01-31' AS end_day
),
days AS (
  SELECT (p.start_day + gs)::date AS stat_day
  FROM params p
  CROSS JOIN LATERAL generate_series(
    0,
    (p.end_day - p.start_day)
  ) AS g(gs)
),
daily AS (
  SELECT
    date_trunc('day', e.created_at AT TIME ZONE 'Asia/Shanghai')::date AS stat_day,
    COUNT(*) AS cnt,
    COALESCE(SUM(e.amount), 0) AS total_amount
  FROM biz_event e
  CROSS JOIN params p
  WHERE e.created_at >= (p.start_day::timestamp AT TIME ZONE 'Asia/Shanghai')
    AND e.created_at <  ((p.end_day + 1)::timestamp AT TIME ZONE 'Asia/Shanghai')
  GROUP BY 1
)
SELECT
  d.stat_day,
  COALESCE(a.cnt, 0) AS cnt,
  COALESCE(a.total_amount, 0) AS total_amount
FROM days d
LEFT JOIN daily a USING (stat_day)
ORDER BY d.stat_day;

关键点：

1. days 生成闭区间 [start_day, end_day]，每个日期一行。
2. daily 先聚合，保证每天最多一行，后续 LEFT JOIN 不会放大结果。
3. 业务过滤使用半开区间 [start_day, end_day + 1)，不要对 created_at 外层套函数，便于利用索引。
4. COALESCE(..., 0) 负责补零；COUNT 的零值是 bigint，SUM 的零值是 numeric。

运行方法与时区口径

在 psql 或任意 PostgreSQL 客户端中执行上述 SQL。先确认数据库会话的 TimeZone 与业务日统计口径。示例用 Asia/Shanghai 作为占位时区，应按实际业务时区替换。若 created_at 是 timestamp without time zone，去掉 AT TIME ZONE 转换，只保留 date_trunc('day', created_at)::date，并相应调整时间范围条件。

索引建议

按时间范围过滤并聚合时，优先建立时间列索引：

CREATE INDEX idx_biz_event_created_at
  ON biz_event (created_at);

若查询常带租户、门店等等值条件，可建复合索引，把等值列放前、时间列放后：

CREATE INDEX idx_biz_event_tenant_created_at
  ON biz_event (tenant_id, created_at);

当查询只需要时间与金额且表较宽时，可评估覆盖索引：

CREATE INDEX idx_biz_event_created_at_include_amount
  ON biz_event (created_at) INCLUDE (amount);

如果经常直接按本地日期等值查询，可评估表达式索引：

CREATE INDEX idx_biz_event_local_day
  ON biz_event (((created_at AT TIME ZONE 'Asia/Shanghai')::date));

本方案的主过滤仍是时间范围，通常不需要为了 date_trunc 再建表达式索引。建议用 EXPLAIN 查看实际计划。也尽量不要在大表上写 WHERE date_trunc('day', created_at) = ...，这会让普通时间索引难以直接用于范围裁剪。

边界与限制

• 日期序列两端都包含；业务范围用 end_day + 1 排除次日零点。极端最大日期附近要注意加一天溢出，生产查询应限制合理区间。
• generate_series 的行数等于天数；跨多年补缺口时行数会增长，建议限制报表区间，或改用预生成日历表。
• 补零只针对整体每天。若要按门店、渠道分别补齐，需要先交叉生成「维度 × 日期」，再 LEFT JOIN 聚合结果。
• 聚合结果中的 stat_day 不应为 NULL；若业务时间列允许 NULL，应先过滤或单独处理。
• 示例不依赖特定小版本特性；执行前请在测试环境验证时区、字段类型与索引计划。