如何在Oracle中按进程分组剔除耗时数据的异常值？

针对你遇到的极端值干扰标准差、均值统计结果的问题，结合Oracle环境和分组需求，以下是两种实用的异常值剔除方案：

方法一：四分位距法（IQR，稳健性更强） #

该方法不受极端值影响，适合处理偏态数据，核心逻辑是通过四分位数确定合理数据范围，过滤超出范围的异常值：

1. 计算每组的下四分位数（Q1）、上四分位数（Q3），得到四分位距（IQR = Q3 - Q1）
2. 设定异常值边界：下限 = Q1 - 1.5*IQR，上限 = Q3 + 1.5*IQR
3. 过滤掉超出边界的数据后，再计算目标统计量

实现SQL #

WITH proc_run_times AS (
    -- 计算每个进程的运行时长（转换为秒）
    SELECT 
        p.PROC_ID,
        ROUND((ps.end - ps.start)*24*60*60) AS run_time_sec
    FROM PROC p
    LEFT JOIN PROC_STAT ps ON ps.PROC_ID = p.PROC_ID
    WHERE ps.start IS NOT NULL AND ps.end IS NOT NULL -- 过滤无效时间数据
),
proc_quartiles AS (
    -- 计算每个进程的四分位数与IQR
    SELECT
        PROC_ID,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY run_time_sec) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY run_time_sec) AS q3,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY run_time_sec) - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY run_time_sec) AS iqr
    FROM proc_run_times
    GROUP BY PROC_ID
),
filtered_data AS (
    -- 剔除异常值
    SELECT
        prt.PROC_ID,
        prt.run_time_sec
    FROM proc_run_times prt
    JOIN proc_quartiles pq ON prt.PROC_ID = pq.PROC_ID
    WHERE prt.run_time_sec BETWEEN (pq.q1 - 1.5*pq.iqr) AND (pq.q3 + 1.5*pq.iqr)
)
-- 计算剔除异常值后的统计量
SELECT
    PROC_ID,
    AVG(run_time_sec) AS avg_run_time,
    STDDEV(run_time_sec) AS std_dev_run_time,
    STATS_MODE(run_time_sec) AS mode_run_time
FROM filtered_data
GROUP BY PROC_ID;

方法二：Z-score法（基于正态分布假设） #

假设数据近似正态分布，通过Z-score衡量数据点与均值的偏离程度，通常剔除|Z-score| > 3的极端值（阈值可根据业务调整为2）：

实现SQL #

WITH proc_run_times AS (
    -- 计算每个进程的运行时长（转换为秒）
    SELECT 
        p.PROC_ID,
        ROUND((ps.end - ps.start)*24*60*60) AS run_time_sec
    FROM PROC p
    LEFT JOIN PROC_STAT ps ON ps.PROC_ID = p.PROC_ID
    WHERE ps.start IS NOT NULL AND ps.end IS NOT NULL -- 过滤无效时间数据
),
proc_stats AS (
    -- 计算每个进程的均值与标准差
    SELECT
        PROC_ID,
        AVG(run_time_sec) AS avg_rt,
        STDDEV(run_time_sec) AS std_rt
    FROM proc_run_times
    GROUP BY PROC_ID
),
filtered_data AS (
    -- 计算Z-score并剔除异常值
    SELECT
        prt.PROC_ID,
        prt.run_time_sec
    FROM proc_run_times prt
    JOIN proc_stats ps ON prt.PROC_ID = ps.PROC_ID
    WHERE ABS((prt.run_time_sec - ps.avg_rt)/ps.std_rt) <= 3 -- 保留Z-score绝对值≤3的数据
)
-- 计算剔除异常值后的统计量
SELECT
    PROC_ID,
    AVG(run_time_sec) AS avg_run_time,
    STDDEV(run_time_sec) AS std_dev_run_time,
    STATS_MODE(run_time_sec) AS mode_run_time
FROM filtered_data
GROUP BY PROC_ID;

注意事项 #

• 若某进程数据量极少（如少于3条），异常值剔除可能导致统计结果无意义，可添加HAVING COUNT(*) >= 3这类条件过滤
• 异常值阈值（1.5*IQR或3倍Z-score）可根据业务场景调整，对稳定性要求高的场景可缩小阈值

内容的提问来源于stack exchange，提问作者Travis Heeter