如何筛选出占总错误数Top 60%的作业记录？

问题回顾 #

现有数据集：

select * from (
    select 'A' as JOB, 15 as errors from dual union all
    select 'B' as JOB, 17 as errors from dual union all
    select 'C' as JOB, 29 as errors from dual union all
    select 'D' as JOB, 27 as errors from dual union all
    select 'E' as JOB, 35 as errors from dual union all
    select 'F' as JOB, 32 as errors from dual union all
    select 'G' as JOB, 75 as errors from dual union all
    select 'H' as JOB, 31 as errors from dual union all
    select 'I' as JOB, 12 as errors from dual union all
    select 'J' as JOB, 10 as errors from dual
)

需求：筛选出占总错误数Top 60%的作业。计算得总错误数的40%为截断值（113），将作业按错误数降序排列后，累计求和小于等于该截断值的作业即为目标结果：

要求避免使用WITH语句，实现累计求和的过滤逻辑。

之前尝试的问题分析 #

SQL1的问题 #

• 用errors + LAG(errors,1,0)计算的不是全局累计和，只是当前行与前一行的错误数之和，无法反映到当前行的累计总量；
• 过滤条件previous >= cutoff逻辑颠倒，应该保留累计和小于等于截断值的行。

SQL2的问题 #

• 错误添加partition by JOB，每个JOB单独分区后，每个分组仅一行数据，累计求和结果等于该行自身的错误数，无法实现全局累计，完全偏离需求。

正确SQL实现 #

SELECT job, errors
FROM (
    SELECT 
        t.*,
        -- 按错误数降序计算累计求和
        SUM(errors) OVER(ORDER BY errors DESC) AS running_total,
        -- 计算总错误数的40%作为截断值
        SUM(errors) OVER() * 0.4 AS cutoff
    FROM (
        select 'A' as JOB, 15 as errors from dual union all
        select 'B' as JOB, 17 as errors from dual union all
        select 'C' as JOB, 29 as errors from dual union all
        select 'D' as JOB, 27 as errors from dual union all
        select 'E' as JOB, 35 as errors from dual union all
        select 'F' as JOB, 32 as errors from dual union all
        select 'G' as JOB, 75 as errors from dual union all
        select 'H' as JOB, 31 as errors from dual union all
        select 'I' as JOB, 12 as errors from dual union all
        select 'J' as JOB, 10 as errors from dual
    ) t
)
WHERE running_total <= cutoff
ORDER BY errors DESC;

逻辑说明 #

1. 最内层子查询是原始数据集；
2. 中间层通过窗口函数实现两个关键计算：
   • SUM(errors) OVER(ORDER BY errors DESC)：按错误数从高到低计算累计求和，得到截至当前行的累计错误总数；
   • SUM(errors) OVER()：无分区无排序，计算所有行的错误总数，乘以0.4得到截断值113；
3. 外层过滤出累计求和小于等于截断值的行，即为符合要求的Top 60%错误数的作业。

内容的提问来源于stack exchange，提问作者Christian Bongiorno