📚 课程目录第4章基础数据探索

4. 基础数据探索：使用 SQL 统计整体数据分布

在构建阈值分层试验之前，必须对原始数据集进行全面的数据探索。本章将使用 SQL 聚合函数与窗口函数，从集中趋势、离散程度、极值范围和百分位分布四个维度，对用户指标（如“近30日消费金额”）进行整体画像。

4.1 核心统计指标概览

以下表格列出了数据探索阶段最常用的 SQL 统计函数及其业务含义：

统计维度	SQL 函数	业务解读
计数	`COUNT(*)`	总用户数 / 非空记录数
均值	`AVG(amount)`	用户平均消费水平
标准差	`STDDEV(amount)`	消费金额的波动程度
最小值	`MIN(amount)`	最低消费门槛
最大值	`MAX(amount)`	最高消费上限
中位数	`PERCENTILE_CONT(0.5)`	50% 分位点，代表典型用户

提示：标准差越大，说明用户消费行为越分散，分层时需要考虑更细的阈值区间；标准差较小则适合使用等宽或等深分层。

4.2 基础聚合查询

以下 SQL 一次性输出整体数据的核心统计量：

SELECT
    COUNT(*)                                         AS total_users,
    AVG(amount)                                      AS avg_amount,
    STDDEV(amount)                                   AS stddev_amount,
    MIN(amount)                                      AS min_amount,
    MAX(amount)                                      AS max_amount
FROM user_behavior;

该查询返回一行记录，快速呈现数据全貌。例如：总用户数 100,000，平均消费 1,250 元，标准差 680 元，最小 0 元，最大 9,800 元。

4.3 百分位分布分析

仅靠均值与极值容易忽略数据偏态。百分位分析能揭示数据在不同区间的分布密度，是阈值分层的重要依据。

使用 PERCENTILE_CONT 计算连续百分位点（支持小数位）：

SELECT
    PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY amount)  AS p10,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount)  AS p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY amount)  AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount)  AS p75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY amount)  AS p90
FROM user_behavior;

  关键发现：若 p50（中位数）远小于均值，说明数据呈右偏分布，即少数高消费用户拉高了整体均值。此时分层应重点关注头部用户。

4.4 数据分布可视化：SQL 统计结果映射

下图展示了基于 SQL 统计结果绘制的数据分布特征，帮助理解集中趋势与离散程度的关系：

4.5 综合统计脚本

将基础聚合与百分位分析合并，输出一份完整的数据分布报告：

WITH stats AS (
    SELECT
        COUNT(*)                                         AS total_users,
        AVG(amount)                                      AS avg_amount,
        STDDEV(amount)                                   AS stddev_amount,
        MIN(amount)                                      AS min_amount,
        MAX(amount)                                      AS max_amount,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount)  AS p25,
        PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY amount)  AS p50,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount)  AS p75
    FROM user_behavior
)
SELECT
    total_users,
    ROUND(avg_amount, 2)          AS avg_amount,
    ROUND(stddev_amount, 2)       AS stddev_amount,
    min_amount,
    max_amount,
    ROUND(p25, 2)                 AS p25,
    ROUND(p50, 2)                 AS p50,
    ROUND(p75, 2)                 AS p75,
    ROUND((max_amount - min_amount), 2) AS range_span,
    ROUND((p75 - p25), 2)               AS iqr
FROM stats;

注意：当数据量极大（如千万级）时，PERCENTILE_CONT 可能消耗较多资源。可考虑先对 amount 字段建立索引，或使用近似百分位函数 APPROX_PERCENTILE（视数据库引擎而定）提升性能。

4.6 统计结果对分层策略的指导

基于上述 SQL 输出，可以快速制定分层策略：

若标准差较小且中位数接近均值：数据分布均匀，适合等宽分层（如每 500 元一层）。
若标准差较大且均值远大于中位数：数据右偏严重，应使用等深分层（按用户数均分）或对数变换后分层。
若 P25 与 P75 区间狭窄：大部分用户集中在中段，阈值设计应在此区间加密。