Oracle 分析滚动百分位数

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 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.

带有窗口模拟槽范围自连接的示例

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
/