← 返回知识库← 上一题下一题 →
SQL (ANSI)编程数据工程师

在为传感器遥测建立强健的异常值阈值时，你如何使用严格的 ANSI SQL 窗口函数来计算每个类别的 **中位数绝对偏差（MAD）**，而不使用相关子查询？

用 Hintsage AI 助手通过面试

问题的回答

问题的历史

**中位数绝对偏差（MAD）**是高斯于1816年提出的统计分散性的强健度量，后来在1970年代被汉佩尔正式化，用于抗异常值分析。与标准偏差不同，标准偏差平方偏差，因此对极端值极为敏感，而 MAD 能够容忍多达50%的污染数据而不失真。在 ANSI SQL 中，计算 MAD 变得实际可行，自 SQL:2003 标准引入了 有序集聚合函数如 PERCENTILE_CONT，使得在不使用过程循环的情况下进行声明性中位数计算。

问题

计算 MAD 需要嵌套的中位数操作：首先确定数据集的中位数，然后找到每个观察值与该中位数之间的绝对差的中位数。在 ANSI SQL 中，由于在同一 SELECT 子句中引用聚合结果以计算各个偏差需要自连接或相关子查询，这使得在大型时间序列数据集上性能下降。此外，标准的 STDDEV 函数在传感器数据包含传输尖峰或校准错误时会产生膨胀的阈值，因此强健的 MAD 对于准确的异常检测至关重要。

解决方案

使用 公共表表达式（CTE） 管道将计算分为逻辑阶段。首先，使用 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category) 计算每组的中位数。其次，计算每行相对其组中位数的绝对偏差。最后，再次应用 PERCENTILE_CONT 到这些偏差上以得出 MAD。这种方法纯粹基于集合，利用数据库引擎的优化器来进行窗口函数处理，并避免逐行处理。

WITH group_medians AS (
  SELECT 
    sensor_id,
    reading,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY reading) 
      OVER (PARTITION BY sensor_id) AS median_val
  FROM telemetry
),
deviations AS (
  SELECT 
    sensor_id,
    ABS(reading - median_val) AS abs_dev
  FROM group_medians
)
SELECT DISTINCT
  sensor_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY abs_dev) 
    OVER (PARTITION BY sensor_id) AS mad
FROM deviations;

生活中的情况

一家制造工厂在传送带上部署了数千个振动传感器，以预测轴承故障。静态警报阈值失效，因为冬季温度自然降低基线，导致寒冷月份出现误报，炎热月份出现漏警报。工程团队需要一种统计方法，该方法适应每个传感器独特的历史分布，而不受偶尔传输故障的影响。

团队考虑了三种架构方法。

客户端统计处理涉及将每日 CSV 转储导出到 Python，使用 Pandas 和 SciPy 库。这提供了丰富的统计函数和快速原型，但引入了24小时数据延迟，并且通过将敏感操作数据移出 SQL 数据库防火墙而带来了安全风险。

过程 SQL 解决方案利用游标和临时表迭代每个传感器的历史，排序值以识别中间行。这种方法在缺乏现代窗口函数的遗留系统上有效，但由于 O(n²) 复杂度和过度的锁定竞争，性能严重下降，需要超过45分钟处理一百万行。

通过 CTE 实现的 ANSI SQL 窗口函数 通过 PERCENTILE_CONT 计算中位数，进行基于集合的操作。此解决方案在数据库引擎内完全执行，处理5000万条记录耗时不足800毫秒，最大限度减少了网络开销，并利用了优化器的并行性，尽管它需要 SQL:2003 或更新的兼容性。

团队选择了 ANSI SQL 窗口函数 方法，因为它在实时性能和严格的数据治理要求之间平衡，这些要求禁止数据导出。最终的 MAD 值建立了动态阈值，超出 median ± 3 * MAD 的任何读取都会触发立即维护警报。这将误报减少了94％，并比以前的静态系统提前两天检测到三起即将发生的轴承故障。

候选人常常忽略的内容

为什么在基于 SQL 的遥测系统中异常检测时更倾向使用 MAD 而非标准偏差？

标准偏差计算平均平方偏差的平方根，当存在异常值时，该指标会爆炸，因为平方放大了大距离。相反，MAD 使用中位数，这是一个抗破坏点估计量，忽略多达50%数据量的极端异常值的大小。对于 ANSI SQL 实现，这意味着一个传感器故障发送的值为 9999 会大幅膨胀 STDDEV，但几乎不会改变 MAD，从而防止了虚假阈值膨胀，这会掩盖未来的微妙异常。

PERCENTILE_CONT 和 PERCENTILE_DISC 在计算离散传感器读数的中位数时有什么区别，应该使用哪个计算 MAD？

PERCENTILE_CONT(0.5) 在行数为偶数时对两个中心值进行线性插值，返回一个可能在表中不存在的假设值（例如，平均 20 和 30 返回 25）。PERCENTILE_DISC(0.5) 返回累积分布大于或等于 0.5 的数据集中的最小实际值。对于离散整数传感器读数的 MAD 计算，使用 PERCENTILE_DISC 通常更安全，因为它保证阈值对应于一个真实的观察值，从而避免了复杂化解释的分数偏差。

MAD 能否在不使用CTE的情况下通过单一自连接计算，以及性能权衡是什么？

可以，但效率低下。你可以在 sensor_id 上自连接表，将每一行与其他每一行进行比较以找到中位数，但这会导致 O(n²) 复杂度。或者，使用衍生子查询先计算中位数，然后再连接回来计算偏差，将强迫数据库物化中间结果或多次重新扫描表。CTE 允许优化器将中位数计算视为一个存储或工作表，这个计算结果只需计算一次并重用，通常会导致一次排序操作和线性 O(n log n) 复杂度。候选人常常忘记 ANSI SQL 优化器可以将 CTE 转换为内部工作表，使其比关联子查询更高效。