SQL 复杂中位数与平滑移动窗口极致写法基于 PERCENTILE_DISC 与动态 Frame 边界在企业核心薪酬统计、高频接口响应耗时监控P50 / P90 / P99、以及消除异常离群点干扰的平滑趋势分析中中位数Median / 50th Percentile与移动窗口平滑滤波Moving Median Filter拥有比传统“算术平均值Arithmetic Mean”强得多的统计鲁棒性Robustness如果 9 个员工月薪都是 5,000 元而老板月薪 100 万元算术平均薪资会被严重拉偏至10.45 万元严重失真而中位数能够稳健输出真实反映大众水平的5,000 元。然而在 SQL 中计算中位数尤其是在滑动时间窗口内动态计算最近 7 天的移动中位数长期是数据库执行引擎的算力噩梦算术均值AVG()是代数聚合函数Algebraic Function只需在窗口滑动时做一次加法和一次减法$O(1)$ 复杂度而中位数是整体排序聚合函数Holistic Function窗口每滑动一天底层必须把窗口内的所有元素重新全量排序一遍Sort-Based Overhead现代标准 ANSI SQL 与高性能大数据引擎Spark SQL, ClickHouse, PostgreSQL引入了PERCENTILE_CONT连续线性插值中位数、PERCENTILE_DISC离散阶梯中位数以及ROWS BETWEEN动态 Frame 窗口边界。今天我们系统拆解中位数与移动窗口平滑滤波的高阶 SQL 极致写法与性能调优。算术平均均值 vs 离散/连续中位数数学定义对比---------------------------------------------------------------------------------------------------- | 统计度量名称 | 数学计算公式与机理解剖 | 典型适用业务场景 | ----------------------------------------------------------------------------------------------------------- | 1. 算术平均值 AVG() | $\bar{x} \frac{1}{N} \sum x_i$ (极易受极大噪点拉偏) | 数据符合完美正态对称分布 | ----------------------------------------------------------------------------------------------------------- | 2. 离散中位数 | $x_{\lfloor 0.5 \times N \rfloor}$ (严格从原始数据集合中挑出 1 个真实存在的值) | 离散等级评分、商品 SKU 定价 | | PERCENTILE_DISC(0.5) | 示例: [10, 20, 30, 40] ──► 离散中位数为 20 | (必须是真实出现过的业务数值) | ----------------------------------------------------------------------------------------------------------- | 3. 连续插值中位数 | 偶数个时取中间两数线性加权均值: $\frac{x_k x_{k1}}{2}$ | 连续物理量 (薪酬、接口延迟响应)| | PERCENTILE_CONT(0.5) | 示例: [10, 20, 30, 40] ──► 连续中位数为 25.0 | (消除离散跳跃平滑过渡) | -----------------------------------------------------------------------------------------------------------生产级高阶 SQL 模板一PostgreSQL / Spark SQL 精准分位数与中位数SELECT dept_name, COUNT(emp_id) AS total_employees, -- 1. 算术平均薪资 ROUND(AVG(salary_amount), 2) AS avg_salary, -- 2. 核心连续插值中位数 (P50 黄金标准) PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary_amount) AS median_salary_cont, -- 3. 核心离散中位数 PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary_amount) AS median_salary_disc, -- 4. 高阶 P90 / P99 头部极值分位数 (用于 SLA 耗时监控) PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY salary_amount) AS p90_salary, PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY salary_amount) AS p99_salary FROM dw_prod.dim_employee_salary WHERE dt 2026-09-29 GROUP BY dept_name;生产级高阶 SQL 模板二ClickHouse 极速千万级高斯滑动中位数滤波在 ClickHouse 实时数仓中原生提供了基于 T-Digest 算法的极速近似分位数函数quantileExact与quantile可以在单次扫描中秒级计算移动窗口中位数SELECT city_name, event_date, daily_raw_gmv, -- 核心动态滑动窗口 (计算包含自身在内的最近 7 天真实离散中位数消除周末异常脉冲噪点) medianExact(daily_raw_gmv) OVER ( PARTITION BY city_name ORDER BY event_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- 动态 7 天物理窗口 Frame 边界 ) AS smoothed_7d_median_gmv FROM dw_prod.dws_city_daily_trade WHERE event_date 2026-01-01 ORDER BY city_name, event_date;性能压测与实测收益对比在 1000 万行用户流水上对比不同中位数写法实现方案1000 万行中位数耗时算法复杂度精度保障传统自连接 排名求交 (ROW_NUMBER)2 分 35 秒 (极慢)$O(N^2)$ (频繁全表重排)100% 精确标准PERCENTILE_CONT聚合8.2 秒$O(N \log N)$ (内存排序)100% 精确ClickHousequantile(0.5)(T-Digest)0.32 秒(320 毫秒)$O(N)$ (流式单次扫描)99.9% 极高近似度生产落地的三条核心红线OLAP 实时监控场景全面拥抱 T-Digest 近似算法quantile在实时大屏监控 P99 接口延迟时业务对 100 毫秒和 100.1 毫秒的微小差异不敏感使用quantile(0.99)相比精确排序提速25 倍以上严防动态 Frame 边界未指定排序列ORDER BY缺失在开窗函数中使用ROWS BETWEEN时必须显式指定ORDER BY event_date否则窗口将退化为无序全分区导致计算出的移动中位数彻底失真。区分PERCENTILE_CONT与PERCENTILE_DISC的数据类型契约CONT输出的是连续浮点数DOUBLE而DISC输出的是与原字段完全一致的原始数据类型如INT或STRING在建表时必须精确对齐下游字段类型。