Oracle Analytic Rolling Percentile

Question

Is it possible to use windowing with any of the percentile functions? Or do you know a work around to get a rolling percentile value?

It is easy with a 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 figure out how to get the median (50% percentile) over the same window.

good question, but should'nt your avg example read avg(n) over (order by foo_date rows between 20 preceding and 1 **following**) — GWu, Jul 21 '12 at 17:10
not necessarily. this achieves what i want, a moving average from 20 values in the past to 1 value in the past. the median is the issue. i can accomplish a moving median by lagging dates (1 ago and 20 ago) and then doing a subquery calculating median for the date range. but this is not very efficient. looking for other ideas. — anti_ml, Jul 21 '12 at 18:04
I see, point taken. Have you tried experimenting with nth_value(foo,n)? If the size of your window is static this could be an idea. — GWu, Jul 21 '12 at 21:30

Alessandro Rossi · Accepted Answer · 2012-07-24T11:32:51.843

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

PERCENTILE_CONT is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation.

...

PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.

...

The following example computes the median 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 a computed result after doing linear interpolation. PERCENTILE_DISC simply returns a value from the set of values that are aggregated over. When the percentile value is 0.5, as in this example, PERCENTILE_CONT returns the average of the two middle values for groups with even number of elements, whereas PERCENTILE_DISC returns the value of the first one among the two middle values. For aggregate groups with an odd number of elements, both functions return the value of the middle element.

a SAMPLE with windowing simulation trough range self-join

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
/

enter image description here

Those *would* be the right functions, except they don't allow the windowing_clause. — Jon Heller, Jul 22 '12 at 17:10
Wrong/irrelevant answer. The question clearly states: *Is it possible to use **windowing** with any of the percentile functions?* — GWu, Jul 23 '12 at 06:27
The OP didn't mention anything about this before making his comment and the official documentation of the function didn't say anything about it too. I've personally never had a need to evaluate a rolling median, and I don't always have an oracle 11g in my pocket to test everything people ask. It was not my fault, I just pointed the OP to the specific functions for his purpose. Now I edited my answer anyway. — Alessandro Rossi, Jul 24 '12 at 11:38
Sorry, I thought you were implying that those functions could be used with the `rows between` syntax. The documentation for those features is confusing. You have to look at the "Analytic Functions" page in the SQL Language Reference, scroll down to the bottom, and look for function names followed by an asterisk: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#i81407 Anyway, +1 for a solution and example. — Jon Heller, Jul 24 '12 at 17:56

Oracle Analytic Rolling Percentile

1 Answers1