企业官网建设流程全解析

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_{k+1}}{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 万行用户流水上对比不同中位数写法：

生产落地的三条核心红线

1. OLAP 实时监控场景全面拥抱 T-Digest 近似算法（quantile）：在实时大屏监控 P99 接口延迟时，业务对 100 毫秒和 100.1 毫秒的微小差异不敏感；使用quantile(0.99)相比精确排序提速25 倍以上！
2. 严防动态 Frame 边界未指定排序列（ORDER BY缺失）：在开窗函数中使用ROWS BETWEEN时，必须显式指定ORDER BY event_date，否则窗口将退化为无序全分区，导致计算出的移动中位数彻底失真。
3. 区分PERCENTILE_CONT与PERCENTILE_DISC的数据类型契约：CONT输出的是连续浮点数（DOUBLE），而DISC输出的是与原字段完全一致的原始数据类型（如INT或STRING），在建表时必须精确对齐下游字段类型。