Oracle Analytic Rolling Percentile

Question

Можно ли использовать управление окнами с любой из функций процентиля? Или вы знаете, как обойти скользящий процентиль?

Это легко с скользящей средней:

select avg(foo) over (order by foo_date rows 
                      between 20 preceding and 1 preceding) foo_avg_ma
from foo_tab

Но я не могу понять, как получить медиану (50% -ый процентиль) для того же окна.

Answer 1

Вы можете использовать функцию PERCENTILE_CONT или PERCENTILE_DISC, чтобы найти медиану.

PERCENTILE_CONT - это обратная функция распределения, которая предполагает модель непрерывного распределения. Он принимает значение процентиля и спецификацию сортировки и возвращает интерполированное значение, которое попадает в это значение процентиля относительно спецификации сортировки. Нули игнорируются при расчете.

...

PERCENTILE_DISC - это обратная функция распределения, которая предполагает дискретную модель распределения. Он принимает значение процентиля и спецификацию сортировки и возвращает элемент из набора. Нули игнорируются при расчете.

...

В следующем примере вычисляется средняя зарплата в каждом отделе:

SELECT department_id,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median cont",
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median disc"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;

...

PERCENTILE_CONT и PERCENTILE_DISC могут возвращать разные результаты. PERCENTILE_CONT возвращает вычисленный результат после выполнения линейной интерполяции. PERCENTILE_DISC просто возвращает значение из набора значений, которые агрегированы. Когда значение процентиля равно 0,5, как в этом примере, PERCENTILE_CONT возвращает среднее значение двух средних значений для групп с четным числом элементов, тогда как PERCENTILE_DISC возвращает значение первого из двух средних значений. Для агрегатных групп с нечетным числом элементов обе функции возвращают значение среднего элемента.

ОБРАЗЕЦ с оконным моделированием через самоподключение

with sample_data as (
        select /*+materialize*/ora_hash(owner) as table_key,object_name,
            row_number() over (partition by owner order by object_name) as median_order,
            row_number() over (partition by owner order by dbms_random.value) as any_window_sort_criteria
        from dba_objects
    )
select table_key,x.any_window_sort_criteria,x.median_order,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY y.median_order DESC) as rolling_median,
    listagg(to_char(y.median_order), ',' )WITHIN GROUP (ORDER BY y.median_order) as elements
from sample_data x
    join sample_data y using (table_key)
where y.any_window_sort_criteria between x.any_window_sort_criteria-3 and x.any_window_sort_criteria+3
group by table_key,x.any_window_sort_criteria,x.median_order
order by table_key, any_window_sort_criteria
/