百分位数的另一种方法

Question

我有一个数据集，它基本上由作业批次列表、每个批次中包含的作业数量以及每个作业批次的持续时间组成。下面是一个样本数据集:

CREATE TABLE test_data
(
   batch_id    NUMBER,
   job_count   NUMBER,
   duration    NUMBER
);
INSERT INTO test_data VALUES (1, 37, 9);
INSERT INTO test_data VALUES (2, 47, 4);
INSERT INTO test_data VALUES (3, 66, 6);
INSERT INTO test_data VALUES (4, 46, 6);
INSERT INTO test_data VALUES (5, 54, 1);
INSERT INTO test_data VALUES (6, 35, 1);
INSERT INTO test_data VALUES (7, 55, 9);
INSERT INTO test_data VALUES (8, 82, 7);
INSERT INTO test_data VALUES (9, 12, 9);
INSERT INTO test_data VALUES (10, 52, 4);
INSERT INTO test_data VALUES (11, 3, 9);
INSERT INTO test_data VALUES (12, 90, 2);

现在，我想为duration字段计算一些百分位数。通常，这是通过以下方式完成的:

SELECT
       PERCENTILE_DISC( 0.75 )
          WITHIN GROUP (ORDER BY duration ASC)
          AS third_quartile
FROM
       test_data;

(结果为9)

我这里的问题是，我们不想获得基于批次的百分位数，我想获得基于单个作业的百分位数。通过生成job_count:

的运行总数，我可以很容易地手动计算出这一点。

SELECT
       batch_id,
       job_count,
       SUM(
            job_count
       )
       OVER (
              ORDER BY duration
              ROWS UNBOUNDED PRECEDING
             )
          AS total_jobs,
        duration
FROM
       test_data
ORDER BY
       duration ASC;
BATCH_ID     JOB_COUNT    TOTAL_JOBS   DURATION     
6            35           35           1            
5            54           89           1            
12           90           179          2            
2            47           226          4            
10           52           278          4            
3            66           344          6            
4            46           390          6            
8            82           472          7            
9            12           484          9            
1            37           521          9            
11           3            524          9            
7            55           579          9           

由于我有579个工作，那么第75个百分位数将是工作434。看看上面的结果集，它对应的持续时间为7，与标准函数不同。

本质上，我想将批处理中的每个作业视为单独的观察结果，并根据这些而不是批处理来确定百分位数。

是否有相对简单的方法来完成这一点?

Answer 1

我认为这是"加权"百分位数。我不知道在Oracle中是否有内置的分析函数，但它很容易计算。而你正在去那里的路上。

另一个想法是计算作业的总数，然后使用算术来选择所需的值。对于第75个百分位数，该值是使累积作业数大于总作业数的0.75倍的最小持续时间。

下面是SQL中的示例:

select pcs.percentile, min(case when cumjobs >= totjobs * percentile then duration end)
from (SELECT batch_id, job_count,
             SUM(job_count) OVER (ORDER BY duration) as cumjobs,
             sum(job_count) over () as totjobs,
             duration
      FROM test_data
     ) t cross join
     (select 0.25 as percentile from dual union all
      select 0.5 from dual union all
      select 0.75 from dual
     ) pcs
group by pcs.percentile;

这个示例为您提供了百分位数值(作为额外的奖励，对于三个不同的百分位数)，每个值都在自己的行上。如果需要每行上的值，则需要连接回原始表。

Answer 2

OK。我想我有答案了。这是我的主意。实现借鉴自Ask Tom的文章

SELECT PERCENTILE_DISC( 0.75 )
       WITHIN GROUP (ORDER BY duration ASC)
       AS third_quartile
FROM(
with data as
  (select level l
   from dual, (select max(job_count) max_jobs from test_data)
   connect by level <= max_jobs
  )
  select *
  from test_data, data
  where l <= job_count
  --ORDER BY duration, batch_id
  ) inner
;