PostgreSQL求按MMDD分组的count统计极值、分位数等指标

环境信息 #

Postgres版本9.4.18，PostGIS版本2.2。

数据表结构（无法修改） #

表ltg_data（覆盖1988-2018年，约8亿行） #

Column | Type | Modifiers
----------+--------------------------+-----------
intensity | integer | not null
time | timestamp with time zone | not null
lon | numeric(9,6) | not null
lat | numeric(8,6) | not null
ltg_geom | geometry(Point,4269) | 
Indexes: "ltg_data2_ltg_geom_idx" gist (ltg_geom)
"ltg_data2_time_idx" btree ("time")
-- 表大小
ltg=# select pg_relation_size('ltg_data');
pg_relation_size
------------------
149729288192

表counties #

Column | Type | Modifiers
-----------+-----------------------------+---------------------------------
gid | integer | not null default nextval('counties_gid_seq'::regclass)
objectid_1 | integer | 
objectid | integer | 
state | character varying(2) | 
cwa | character varying(9) | 
countyname | character varying(24) | 
fips | character varying(5) | 
time_zone | character varying(2) | 
fe_area | character varying(2) | 
lon | double precision | 
lat | double precision | 
the_geom | geometry(MultiPolygon,4269) | 
Indexes: "counties_pkey" PRIMARY KEY, btree (gid)
"counties_gix" gist (the_geom)
"county_cwa_idx" btree (cwa)
"countyname_cwa_idx" btree (countyname)

当前已实现的查询 #

你已经通过f_mmdd函数实现了按日历年日期（MMDD）统计30年总count的查询：

函数定义 #

CREATE FUNCTION f_mmdd(date) RETURNS int
LANGUAGE sql IMMUTABLE AS $$SELECT to_char($1, 'MMDD')::int$$;

查询语句 #

SELECT d.mmdd, COALESCE(ct.ct, 0) AS total_count
FROM (
 SELECT f_mmdd(d::date) AS mmdd -- 忽略年份
 FROM generate_series(timestamp '2018-01-01' -- 任意虚拟年份
 , timestamp '2018-12-31'
 , interval '1 day') d
 ) d
LEFT JOIN (
 SELECT f_mmdd(time::date) AS mmdd, count(*) AS ct
 FROM counties c
 JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
 WHERE cwa = 'MFR'
 GROUP BY 1
 ) ct USING (mmdd)
ORDER BY 1;

查询结果 #

mmdd total_count
725 | 2126
726 | 558
727 | 2
728 | 2
729 | 2
730 | 0
731 | 0
801 | 0
802 | 10

需求目标 #

需要针对每个MMDD，计算该日历年日期的以下统计指标：

• 单日最大count值（max daily）
• 最大count对应的年份（year_max_daily）
• count非零的年份占比（percent_years_count_not_zero）
• 单日最小count值
• 10/25/50/75/90分位数
• 标准差

期望结果示例：

mmdd total_count max daily year_max_daily percent_years_count_not_zero 10th percentile_daily 90th percentile_daily
725 | 2126 1000 1990 30 15 900
726 | 558 120 1992 20 10 80
...

尝试的错误查询 #

你之前的查询问题出在没有先按年份+MMDD拆分计算每年单日的count，直接基于总count统计，导致结果和总count一致。错误查询如下：

SELECT AVG(CAST(total_count as FLOAT)), day
FROM (
 SELECT d.mmdd as day, COALESCE(ct.ct, 0) as total_count
 FROM (
 SELECT f_mmdd(d::date) AS mmdd
 FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31', interval '1 day') d
 ) d
 LEFT JOIN (
 SELECT mmdd, avg(q.ct)
 FROM (
 SELECT f_mmdd((time at time zone 'utc+12')::date) as mmdd, count(*) as ct
 FROM counties c
 JOIN ltg_data d on ST_contains(c.the_geom, d.ltg_geom)
 WHERE cwa = 'MFR'
 GROUP BY 1
 )
 ) as q ct USING (mmdd)
 ORDER BY 1

正确查询语句 #

核心思路是：先计算每个年份每个MMDD的单日count，再基于这个中间结果按MMDD聚合所有统计指标。这样才能得到每年单日的统计值，而不是总count的统计。

WITH daily_counts AS (
  -- 第一步：计算每个年份每个MMDD的单日闪电数量（针对指定CWA区域）
  SELECT
    EXTRACT(YEAR FROM d.time)::int AS year,
    f_mmdd(d.time::date) AS mmdd,
    COUNT(*) AS daily_count
  FROM counties c
  JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
  WHERE c.cwa = 'MFR'
    -- 限定年份范围，避免扫描无关数据（1988-2018共31年）
    AND d.time BETWEEN '1988-01-01'::timestamptz AND '2018-12-31'::timestamptz
  GROUP BY year, mmdd
),
all_mmdd AS (
  -- 生成所有可能的MMDD值（用2020年这个闰年，覆盖2月29日）
  SELECT f_mmdd(d::date) AS mmdd
  FROM generate_series(timestamp '2020-01-01', timestamp '2020-12-31', interval '1 day') d
),
total_counts AS (
  -- 计算每个MMDD的总count（和你原来的查询结果一致）
  SELECT
    mmdd,
    SUM(daily_count) AS total_count
  FROM daily_counts
  GROUP BY mmdd
)
SELECT
  am.mmdd,
  COALESCE(tc.total_count, 0) AS total_count,
  -- 1. 单日最大count值
  COALESCE(MAX(dc.daily_count), 0) AS max_daily,
  -- 2. 最大count对应的年份（如果多个年份有相同最大值，取最早的）
  COALESCE(
    (ARRAY_AGG(dc.year ORDER BY dc.daily_count DESC, dc.year ASC))[1],
    NULL
  ) AS year_max_daily,
  -- 3. count非零的年份占比（总年份为31年：1988-2018）
  ROUND(
    (COUNT(DISTINCT CASE WHEN dc.daily_count > 0 THEN dc.year END)::FLOAT / 31) * 100,
    2
  ) AS percent_years_count_not_zero,
  -- 4. 单日最小count值（无数据则为0）
  COALESCE(MIN(dc.daily_count), 0) AS min_daily,
  -- 5. 各分位数（用PERCENTILE_CONT连续型，如需离散型可换PERCENTILE_DISC）
  PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY dc.daily_count) AS "10th percentile_daily",
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY dc.daily_count) AS "25th percentile_daily",
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY dc.daily_count) AS "50th percentile_daily",
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY dc.daily_count) AS "75th percentile_daily",
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY dc.daily_count) AS "90th percentile_daily",
  -- 6. 标准差（无数据则为0）
  COALESCE(STDDEV(dc.daily_count), 0) AS std_dev
FROM all_mmdd am
LEFT JOIN daily_counts dc ON am.mmdd = dc.mmdd
LEFT JOIN total_counts tc ON am.mmdd = tc.mmdd
GROUP BY am.mmdd, tc.total_count
ORDER BY am.mmdd;

关键说明 #

1. daily_counts CTE：这是整个查询的核心，拆分到每年的单日count，所有统计指标都基于这个数据集计算，解决了你之前直接聚合总count的问题。
2. 年份范围过滤：加上d.time BETWEEN '1988-01-01' AND '2018-12-31'可以利用ltg_data2_time_idx索引，大幅减少扫描的数据量，提升8亿行表的查询效率。
3. 最大年份处理：用ARRAY_AGG按count降序、年份升序排列，取第一个元素得到最早出现最大值的年份；如果需要所有出现最大值的年份，可以去掉[1]，返回数组。
4. 分位数选择：PERCENTILE_CONT返回连续型分位数（适合数值型统计），如果需要离散的实际存在的count值，可以换成PERCENTILE_DISC。

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