📚 课程目录第4章聚合函数与特征统计

4. 聚合函数与特征统计：COUNT、SUM、AVG、STDDEV、PERCENTILE_CONT 在特征分布分析中的应用

在模型解释性分析中，理解每个特征的分布形态、离散程度与缺失情况是构建可信模型的前提。SQL 聚合函数不仅用于报表汇总，更是特征工程与分布诊断的核心工具。本节将系统讲解 COUNT、SUM、AVG、STDDEV 以及窗口排序函数 PERCENTILE_CONT 在特征分布分析中的具体用法与业务含义。

4.1 聚合函数在特征分析中的角色

特征分布分析通常从三个维度展开：完整性、集中趋势、离散程度。下表列出了各聚合函数对应的分析维度与典型应用场景：

函数	分析维度	典型应用场景
`COUNT(*)`	样本总量	确认数据集行数，判断样本是否充足
`COUNT(column)`	非空值数量	检测特征缺失率，评估数据质量
`COUNT(DISTINCT column)`	唯一值基数	判断特征是否为高基数类别或连续型
`SUM(column)`	总量/频次	计算二值特征的正样本数，或数值特征的累计值
`AVG(column)`	均值（集中趋势）	衡量特征的中心位置，与中位数对比判断偏态
`STDDEV(column)`	标准差（离散程度）	评估特征波动性，识别异常值敏感特征
`PERCENTILE_CONT(...)`	分位数（分布形态）	计算中位数、四分位数，描述分布偏度与尾部分布

实践要点：在模型解释性分析中，建议先使用 COUNT(column) / COUNT(*) 计算每个特征的缺失率，缺失率超过 30% 的特征需要谨慎处理，避免引入偏差。

4.2 完整性分析：COUNT 与缺失率计算

特征缺失率直接影响模型的可解释性与稳定性。通过 COUNT 与 CASE WHEN 组合，可以快速生成全特征的缺失率报告。

-- 计算所有数值特征的缺失率
SELECT
  COUNT(*) AS total_rows,
  COUNT(age) AS age_non_null,
  ROUND(1.0 - COUNT(age) * 1.0 / COUNT(*), 4) AS age_missing_rate,
  COUNT(salary) AS salary_non_null,
  ROUND(1.0 - COUNT(salary) * 1.0 / COUNT(*), 4) AS salary_missing_rate,
  COUNT(credit_score) AS credit_score_non_null,
  ROUND(1.0 - COUNT(credit_score) * 1.0 / COUNT(*), 4) AS credit_score_missing_rate
FROM customer_features;

对于高基数类别特征，使用 COUNT(DISTINCT) 判断其是否适合直接入模：

-- 检查类别特征的基数
SELECT
  'occupation' AS feature_name,
  COUNT(DISTINCT occupation) AS unique_values
FROM customer_features
UNION ALL
SELECT
  'education_level',
  COUNT(DISTINCT education_level)
FROM customer_features;

注意：当 COUNT(DISTINCT column) 的结果接近 COUNT(*) 时，该特征可能是唯一标识符（如用户ID），不应作为模型特征使用，否则会导致过拟合。

4.3 集中趋势与离散程度：AVG 与 STDDEV 联合诊断

均值与标准差是描述特征分布的基础统计量。在模型解释性分析中，常通过 AVG 与 STDDEV 的比值（变异系数）来判断特征是否需要标准化或对数变换。

-- 计算数值特征的均值、标准差与变异系数
SELECT
  'age' AS feature,
  ROUND(AVG(age), 2) AS mean_value,
  ROUND(STDDEV(age), 2) AS std_value,
  ROUND(STDDEV(age) / NULLIF(AVG(age), 0), 4) AS cv  -- 变异系数
FROM customer_features
UNION ALL
SELECT
  'salary',
  ROUND(AVG(salary), 2),
  ROUND(STDDEV(salary), 2),
  ROUND(STDDEV(salary) / NULLIF(AVG(salary), 0), 4)
FROM customer_features;

变异系数（CV）大于 1 的特征通常存在极端值或长尾分布，建议在建模前进行截尾或分箱处理。

  解释性分析提示：当某个特征的 STDDEV 远大于 AVG 时，该特征对模型输出的贡献可能集中在少数极端样本上，此时应结合 SHAP 或 LIME 进行局部解释验证。

4.4 分布形态描述：PERCENTILE_CONT 与分位数分析

PERCENTILE_CONT 是 SQL 标准中的连续百分位函数，用于计算中位数、四分位数等分位点，比 AVG 更能抵抗异常值干扰。在特征分布分析中，分位数是判断偏态与尾部分布的核心指标。

-- 计算 salary 特征的关键分位数
SELECT
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1,
  PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY salary) AS median,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3,
  PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY salary) AS p90,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) AS p95
FROM customer_features;

通过比较 AVG 与 MEDIAN（即 0.5 分位数）的差异，可以快速判断分布偏态：

均值 > 中位数：右偏分布，存在高值异常点。
均值 < 中位数：左偏分布，存在低值异常点。
均值 ≈ 中位数：近似对称分布。

结合四分位距（IQR = Q3 - Q1），可以定义异常值阈值：

-- 基于 IQR 识别 salary 特征的异常值
WITH stats AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3
  FROM customer_features
)
SELECT
  COUNT(*) AS outlier_count,
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM customer_features), 2) AS outlier_pct
FROM customer_features, stats
WHERE salary < q1 - 1.5 * (q3 - q1)
   OR salary > q3 + 1.5 * (q3 - q1);

最佳实践：在模型解释性报告中，建议同时展示特征的均值、中位数与标准差，并标注异常值比例。这有助于业务方理解特征分布是否合理，以及模型是否对少数极端样本过度敏感。

4.5 综合特征统计报告模板

将上述函数整合为一张完整的特征统计表，是模型解释性分析的标准输出之一。以下 SQL 生成一个包含完整性、集中趋势、离散程度与分位数的综合报告：

-- 综合特征统计报告
SELECT
  'age' AS feature_name,
  COUNT(*) AS total,
  COUNT(age) AS non_null,
  ROUND(COUNT(age) * 1.0 / COUNT(*), 4) AS completeness,
  ROUND(AVG(age), 2) AS mean,
  ROUND(STDDEV(age), 2) AS std,
  ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY age), 2) AS median,
  ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY age), 2) AS q1,
  ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY age), 2) AS q3
FROM customer_features
UNION ALL
SELECT
  'salary',
  COUNT(*),
  COUNT(salary),
  ROUND(COUNT(salary) * 1.0 / COUNT(*), 4),
  ROUND(AVG(salary), 2),
  ROUND(STDDEV(salary), 2),
  ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY salary), 2),
  ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary), 2),
  ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary), 2)
FROM customer_features;

该报告可直接用于模型可解释性文档的“特征分布概览”章节，帮助数据科学家与业务方快速定位数据质量问题。

4.6 聚合函数在特征分布分析中的工作流程

下图展示了从原始数据到特征分布诊断的完整流程，以及各聚合函数在其中的作用位置：

该流程强调：先检查完整性，再评估集中趋势与离散度，最后通过分位数判断分布形态，形成完整的特征诊断闭环。

4.7 分组聚合与特征对比分析

在模型解释性分析中，经常需要对比不同分组的特征分布差异（例如：好客户 vs 坏客户）。通过 GROUP BY 结合聚合函数，可以快速发现特征在不同目标类别下的分布偏移。

-- 按目标变量分组对比特征分布
SELECT
  target,
  COUNT(*) AS group_size,
  ROUND(AVG(age), 2) AS avg_age,
  ROUND(STDDEV(age), 2) AS std_age,
  ROUND(AVG(salary), 2) AS avg_salary,
  ROUND(STDDEV(salary), 2) AS std_salary,
  ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY salary), 2) AS median_salary
FROM customer_features
GROUP BY target;

当某个特征在不同目标组中的均值或标准差差异显著时，该特征很可能对模型预测有重要贡献，应在后续的 SHAP 或特征重要性分析中重点关注。

  解释性分析价值：分组聚合统计是“全局可解释性”的基础。通过对比特征在不同预测结果下的分布差异，可以直观地向业务方解释模型为何对某些样本给出特定预测。

4.8 常见陷阱与注意事项

忽略缺失值处理：直接使用 AVG(column) 会忽略 NULL 值，导致均值计算不准确。建议先通过 COUNT(column) 确认非空比例。
标准差对异常值敏感：在存在极端值时，STDDEV 会被拉高，此时应优先使用 PERCENTILE_CONT 计算四分位距来描述离散程度。
PERCENTILE_CONT 与 PERCENTILE_DISC 的区别：PERCENTILE_CONT 返回连续插值结果，适合连续型特征；PERCENTILE_DISC 返回实际存在的离散值，适合整数或类别编码特征。
大数据量下的性能：PERCENTILE_CONT 在超大规模表上可能较慢，建议先对特征进行分箱或采样后再计算分位数。

掌握以上聚合函数的组合用法，能够帮助数据科学家在建模前快速完成特征分布诊断，为后续的模型解释性分析奠定坚实的数据基础。