Другой подход к процентилям?

У меня есть набор данных, который по существу состоит из списка пакетов заданий, количества заданий, содержащихся в каждом пакете, и продолжительности каждого пакета заданий. Вот пример набора данных:

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);

Теперь я хочу рассчитать несколько процентилей для поля продолжительности. Как правило, это делается примерно так:

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, отличной от того, что делает стандартная функция.

По сути, я хочу рассматривать каждое задание в пакете как отдельное наблюдение и определять процентили на их основе, а не на основе пакетов.

Есть ли относительно простой способ сделать это?


sql oracle analytic-functions
person emiller42    schedule 08.05.2013    source источник
comment
Вы имеете в виду, что ищете per job продолжительность? Если да, можно ли использовать duration/job_count в качестве меры? Пожалуйста, уточните ваши требования. Ваш второй подход не имеет особого смысла (по крайней мере, математически).   -  person PM 77-1    schedule 09.05.2013
comment
Хотя это правильно, это все еще оставляет проблему на месте. (Я опустил это для простоты в фиктивных данных). Если я это сделаю, то сообщаемый 75-й процентиль из приведенного выше набора данных будет 0,16, но желаемый 75-й процентиль должен быть 0,13, потому что он по-прежнему определяет 75-й процентиль на основе пакетов, а не заданий.   -  person emiller42    schedule 09.05.2013
comment
Также стоит отметить, что с функциональной точки зрения ни одно задание в пакете не считается завершенным, пока не будет завершен весь пакет. Таким образом, с точки зрения конечного пользователя, все задания в пакете занимают одинаковое количество времени.   -  person emiller42    schedule 09.05.2013

Ответы (2)


arrow_upward
3
arrow_downward

Я бы подумал об этом как о «взвешенных» процентилях. Я не знаю, есть ли для этого встроенная аналитическая функция в 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;

В этом примере представлены значения процентилей (и, в качестве дополнительного бонуса, для трех разных процентилей) с каждым значением в отдельной строке. Если вам нужны значения в каждой строке, вам нужно вернуться к исходной таблице.

person Gordon Linoff    schedule 08.05.2013
comment
Оба этих ответа дают мне именно то, что я хочу, но я принимаю этот, поскольку он работает намного быстрее с большим набором данных. (Проверено на 600 тыс. пакетов, до 1800 заданий на пакет) Я бы проголосовал за оба, но пока не могу. Спасибо вам обоим за ответы! - person emiller42; 09.05.2013
comment
На всякий случай, если кого-то еще смущает таблица dual, используемая в этом ответе, это системная таблица Oracle по умолчанию, часто используемая для выбора констант (поскольку оператор SELECT требует FROM). См. статью Википедии. - person revengeoftheants; 09.12.2015

arrow_upward
0
arrow_downward

ХОРОШО. Кажется, у меня есть твой ответ. Идея моя. Реализация заимствована из этого Спросите Тома в статье

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
;

Вот скрипт SQL.

person PM 77-1    schedule 08.05.2013