在建立统计异常值边界以进行质量控制指标时，如何使用严格的 **ANSI SQL** 窗口函数和聚合方法计算分组数据集中的 **四分位数间距 (IQR)**，然后利用此度量筛选超出四分位数 1.5×IQR 的观察值？

问题的答案

问题的历史

IQR 的概念源于约翰·图基（John Tukey）在 1970 年代开发的探索性数据分析方法，为异常值检测提供了一种稳健的统计量，能够抵御极端值。随着数据仓库的发展，分析师从过程统计包转向基于集合的 SQL 查询，这需要本地数据库实现这些计算。ANSI SQL:2003 引入了逆分布函数，后来在 SQL:2011 中进行了改进，使得能够直接在数据库引擎内进行百分位数计算，而无需外部处理。

问题

该挑战需要计算数据集中每个子组的第一四分位数（Q1，25 百分位数）和第三四分位数（Q3，75 百分位数），以得出 IQR（Q3 减去 Q1）。一旦确定，统计异常值界限在 Q1 − 1.5×IQR 和 Q3 + 1.5×IQR 处定义。复杂性在于在单一集合操作中跨分区执行这些统计计算，同时保持准确性，然后根据这些动态计算的边界过滤原始数据集，而不诉诸于过程循环或应用层处理。

解决方案

使用 PERCENTILE_CONT(0.25) 和 PERCENTILE_CONT(0.75) 作为按组列分区的有序集合窗口函数，这些函数通过线性插值来确定确切的四分位数值。在 公用表表达式 (CTE) 中计算 IQR 和边界条件，然后将其与该结果连接或直接使用 WHERE 子句过滤，这些子句将测量值与计算出的界限进行比较。

WITH quartiles AS (
  SELECT 
    facility_zone,
    temperature,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY temperature) 
      OVER (PARTITION BY facility_zone) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY temperature) 
      OVER (PARTITION BY facility_zone) AS q3
  FROM sensor_readings
),
bounds AS (
  SELECT 
    facility_zone,
    temperature,
    (q3 - q1) AS iqr,
    q1 - 1.5 * (q3 - q1) AS lower_fence,
    q3 + 1.5 * (q3 - q1) AS upper_fence
  FROM quartiles
)
SELECT facility_zone, temperature, iqr
FROM bounds
WHERE temperature < lower_fence 
   OR temperature > upper_fence;

生活中的情况

一家制药公司监测 200 个设施区域存放疫苗库存的超低温冷冻柜。每个区域每天生成 10,000 条温度读数。简单的标准偏差异常值检测失败，因为偶尔的电网波动造成的极端峰值扭曲了均值，导致设备故障的微小现象出现了假阴性。质量团队需要一种稳健的统计方法来标记仅那些显著偏离区域典型操作范围的读数，直接在他们的 PostgreSQL 数据仓库中实施，以提供实时 Tableau 仪表板。

解决方案 1：使用 Python 和 Pandas 的应用层处理

通过 ODBC 将所有历史数据提取到 Python 服务中，使用 groupby().quantile() 计算四分位数，然后筛选并将结果写回。优点：极其灵活的统计库，逐步执行时易于调试，数据科学家熟悉的语法。缺点：需要巨大的网络开销传输数百万行，内存限制导致应用服务器出现错误，以及由于 45 分钟处理窗口导致数据陈旧，使得结果在完成时已过时。

解决方案 2：使用本地 ANSI SQL 的 PERCENTILE_CONT 窗口函数

使用 PERCENTILE_CONT 的按组聚合查询，使用 OVER 子句按 facility_zone 分区。优点：零数据传输，利用现有的 B-tree 索引，减少处理时间至 15 秒以内，并提供可直接被 BI 工具使用的实时结果。缺点：需要兼容 SQL:2003/2011 的数据库（在旧版 MySQL 中不可用），创建的临时排序操作在执行期间会急剧增加 CPU 使用率，并且许多应用程序开发人员对复杂的语法不熟悉。

解决方案 3：使用 NTILE(4) 的近似计算

使用 NTILE 窗口函数将每个区域的读数分为四个相等的桶，然后在 1 和 4 桶上使用 MIN() 和 MAX() 来近似 Q1 和 Q3 边界。优点：与缺乏逆分布函数的旧数据库版本兼容，由于近似计算执行更快。缺点：仅产生近似边界，不适用于法规遵从，在样本量小或重值时会崩溃，并且当边界位于离散传感器读数之间时引入非确定性行为。

选择的解决方案和结果

团队选择了 解决方案 2（PERCENTILE_CONT 方法），因为制药法规要求精确的统计计算，而不允许近似。数据库管理员在 (facility_zone, temperature) 上创建了复合索引，消除了排序操作。最终查询识别出 0.03% 的读数为真正的统计异常值，触发了自动冷冻柜检查，防止了价值约 200 万美元的库存损失，同时通过去除 Python ETL 层降低了基础设施成本。

候选人常常遗漏的内容

为什么 PERCENTILE_CONT 在计算四分位数时产生与 PERCENTILE_DISC 不同的结果，应使用哪个来计算 IQR？

PERCENTILE_CONT（连续型）在请求的百分位位置周围的两个最接近的值之间执行线性插值，返回一个可能在原始数据集中不存在的计算值。PERCENTILE_DISC（离散型）返回大于或等于百分位的最小累积分布值，有效地选择实际观察到的测量值。对于异常值检测中的 IQR 计算，通常首选 PERCENTILE_CONT，因为它提供了一个不太敏感于离散采样伪影的连续尺度，尽管当异常值边界必须对应于物理观察值而非数学插值时，PERCENTILE_DISC 变得必要。

如何处理包含少于四个不同值的组，使得 IQR 在数学上变为零或未定义？

当一个分区包含相同的值或少于四个数据点时，PERCENTILE_CONT 对 Q1 和 Q3 返回相同的值，导致 IQR 为零。这会导致异常值边界坍缩到中值，可能将每个不同的观察值标记为异常值。候选人必须实现 NULLIF 检查或 CASE 表达式来检测零 IQR 情况，返回 NULL 作为异常状态，回退到小组的标准偏差方法，或根据业务规则显式排除 COUNT(DISTINCT value) < 4 的组进行异常值分析。

在处理数十亿行、高基数分类分区时，优化逆分布函数性能的索引策略是什么？

由于 PERCENTILE_CONT 需要对每个分区进行排序以确定百分位位置，候选人经常忽视在 (category, measurement) 上创建复合索引的必要性。这种索引允许数据库引擎扫描预排序的索引叶页面，消除对磁盘的昂贵外部排序操作。如果没有这些索引，数据库将为每个分区执行单独的排序，造成严重的 I/O 争用和临时磁盘空间耗尽。此外，候选人还可能忽视将选择性 WHERE 子句推入早期 CTE 可以在昂贵的百分位数计算之前减少工作集，因为逆分布函数无法在聚合阶段开始后使用索引。