Oracle Analytic Rolling Percentile

Question

Can I use a window with any percentile function? Or do you know how to work to get a moving percentile value?

Easy with moving average:

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

But I can’t understand how to get the median (50% percentile) from the same window.

Answer 1

You can use PERCENTILE_CONT or the PERCENTILE_DISC function to find the median.

PERCENTILE_CONT is an inverse distribution function that assumes a continuous distribution model. The percentile value and specification type are required and returns the interpolated value, which is the percentile value with respect to the sort specification. Zero in the calculation are ignored.

...

PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model. The percentile value and specification type is required and returns an element from the set. Zeros are ignored in the calculation.

...

The following example calculates the average salary in each department:

 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 and PERCENTILE_DISC may return different results. PERCENTILE_CONT returns the calculated result after performing linear interpolation. PERCENTILE_DISC simply returns a value from a set of values ​​that are aggregated. When the percentile value is 0.5, in this example PERCENTILE_CONT returns the average of two average values ​​for groups with an even number of elements, while PERCENTILE_DISC returns the value of the first of two average values. For aggregate groups with an odd number of elements, both functions return the value of the middle element.

a SAMPLE with self-diagnosis of the simulation window using the self-learning module

 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 /