PostgreSQL按MM-DD/MM-DD-HH聚合多年度数据（含零值）问题

针对你在PostgreSQL 9.4.18 + PostGIS 2.2环境下的需求，我来一步步拆解解决方案，同时兼顾8亿行数据的性能和后续的均值、百分位数计算需求。

核心思路 #

要实现忽略年份的日期/小时分组+保留零值行，关键是先生成完整的全年日期/小时序列，再通过左连接关联闪电数据的统计结果——这样没有数据的日期/小时就会显示0值。同时要解决类型匹配问题，确保序列和分组字段的类型一致。

1. 按'MM-DD'分组统计（含零值） #

第一步：生成全年MM-DD序列（含闰年2月29日） #

先生成一个包含所有可能MM-DD组合的序列，选一个闰年作为基准，确保覆盖2月29日的情况：

WITH all_mmdd AS (
  SELECT to_char(d, 'MM-DD') AS mm_dd
  FROM generate_series(
    '2020-01-01'::date, -- 闰年确保包含2-29
    '2020-12-31'::date,
    '1 day'::interval
  ) AS d
)

第二步：关联闪电数据统计 #

把ltg_data的time字段提取成相同格式的MM-DD文本，分组统计行数后和序列左连接，用COALESCE把空值转为0：

WITH all_mmdd AS (
  SELECT to_char(d, 'MM-DD') AS mm_dd
  FROM generate_series(
    '2020-01-01'::date,
    '2020-12-31'::date,
    '1 day'::interval
  ) AS d
),
ltg_counts AS (
  SELECT 
    to_char(time, 'MM-DD') AS mm_dd,
    COUNT(*) AS row_count
  FROM ltg_data
  GROUP BY to_char(time, 'MM-DD')
)
SELECT 
  am.mm_dd,
  COALESCE(lc.row_count, 0) AS row_count
FROM all_mmdd am
LEFT JOIN ltg_counts lc ON am.mm_dd = lc.mm_dd
ORDER BY am.mm_dd;

性能优化建议 #

因为ltg_data有8亿行，一定要给分组字段建表达式索引，避免全表扫描：

CREATE INDEX idx_ltg_time_mmdd ON ltg_data (to_char(time, 'MM-DD'));

2. 按'MM-DD-HH'分组统计（含零值） #

逻辑和上面一致，只是把序列粒度细化到小时：

第一步：生成全年MM-DD-HH序列 #

WITH all_mmddhh AS (
  SELECT to_char(d, 'MM-DD-HH24') AS mm_dd_hh
  FROM generate_series(
    '2020-01-01 00:00:00'::timestamp,
    '2020-12-31 23:00:00'::timestamp,
    '1 hour'::interval
  ) AS d
)

第二步：关联统计（包含均值和百分位数） #

直接在分组统计时计算均值和百分位数，后续左连接后补零：

WITH all_mmddhh AS (
  SELECT to_char(d, 'MM-DD-HH24') AS mm_dd_hh
  FROM generate_series(
    '2020-01-01 00:00:00'::timestamp,
    '2020-12-31 23:00:00'::timestamp,
    '1 hour'::interval
  ) AS d
),
ltg_stats AS (
  SELECT 
    to_char(time, 'MM-DD-HH24') AS mm_dd_hh,
    COUNT(*) AS row_count,
    AVG(intensity) AS avg_intensity,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY intensity) AS p95_intensity -- 95%百分位数
  FROM ltg_data
  GROUP BY to_char(time, 'MM-DD-HH24')
)
SELECT 
  amh.mm_dd_hh,
  COALESCE(ls.row_count, 0) AS row_count,
  COALESCE(ls.avg_intensity, 0) AS avg_intensity,
  COALESCE(ls.p95_intensity, 0) AS p95_intensity
FROM all_mmddhh amh
LEFT JOIN ltg_stats ls ON amh.mm_dd_hh = ls.mm_dd_hh
ORDER BY amh.mm_dd_hh;

性能优化 #

给小时级分组字段建表达式索引：

CREATE INDEX idx_ltg_time_mmddhh ON ltg_data (to_char(time, 'MM-DD-HH24'));

你之前遇到的问题解析 #

1. date_trunc分组带年份：date_trunc('day', time)会保留年份信息，所以分组是按“年-月-日”而非跨年度的“月-日”。改用to_char(time, 'MM-DD')提取月日文本就能忽略年份。
2. 类型不匹配错误：如果generate_series生成的是日期/时间类型，直接和文本类型的分组字段关联就会报错。解决方案是把序列和分组字段统一转成文本格式（如上述示例），或者用date_part('month', time)/date_part('day', time)分别匹配序列的月和日（文本格式更直观）。

后续扩展建议 #

• 百分位数可选PERCENTILE_CONT（连续型）或PERCENTILE_DISC（离散型），根据业务需求选择。
• 如果需要结合counties表做空间统计，可在分组中加入counties.gid，并通过ST_Intersects(ltg_data.ltg_geom, counties.geom)关联过滤，利用空间索引提升性能。

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