PostgreSQL 14 GROUP BY查询计算中位数的方法及正确性验证

• 截止PostgreSQL 16正式版本，官方并没有内置median()聚合函数，你写的语句如果直接在原生PG环境运行会报函数不存在的错误。
• 如果你当前环境可以正常运行median()，大概率是安装了第三方扩展：要么是postgresql-contrib套件里的相关实现，要么是启用了TimescaleDB、Citus这类扩展自带的中位数函数实现。

如果你的运行环境确实支持median()聚合函数，那你写的查询逻辑是完全正确的：

• 用date_trunc('month', trading_day)把交易日统一截断到当月第一天，作为月度分组的标识，逻辑没问题
• where extract(year from trading_day) = 2000正确过滤出了2000年的所有交易数据
• 按截断后的月份分组后对每个分组的股价取中位数，刚好匹配你要的「2000年每月股价中位数」的统计需求。

如果是没有安装第三方扩展的原生PG环境，可以用内置的PERCENTILE_CONT或者PERCENTILE_DISC聚合函数实现中位数效果：

-- 连续中位数（结果为插值计算的连续值，和通用median定义一致）
SELECT 
  date_trunc('month', trading_day) AS dt_trunc,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY stock_price) AS monthly_median
FROM data_price
WHERE EXTRACT(YEAR FROM trading_day) = 2000
GROUP BY dt_trunc;

-- 离散中位数（结果为分组内的实际存在的某个股价值）
SELECT 
  date_trunc('month', trading_day) AS dt_trunc,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY stock_price) AS monthly_median
FROM data_price
WHERE EXTRACT(YEAR FROM trading_day) = 2000
GROUP BY dt_trunc;

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