Oracle计算分组分位数

 

我们在进行分析过程中，经常会有计算某个特征的分位数这个需求。下面为大家介绍如何在oracle计算某一列数据的分位数。

需要求分位数的表结构如下：

select * from test_lizhen;

 

我们发现该表有两列，一列是代表不同产品，一列是代表每个用户的属性。我们可以通过如下方法计算特征的分位数

1）不分产品，计算全体用户的分位数

select PERCENTILE_CONT(0) within group(order by pltf_cnt_60m) as max_sal_0,
       PERCENTILE_CONT(0.2) within group(order by pltf_cnt_60m) as max_sal_20,
       PERCENTILE_CONT(0.4) within group(order by pltf_cnt_60m) as max_sal_40,
       PERCENTILE_CONT(0.6) within group(order by pltf_cnt_60m) as max_sal_60,
       PERCENTILE_CONT(0.8) within group(order by pltf_cnt_60m) as max_sal_80,
       PERCENTILE_CONT(1) within group(order by pltf_cnt_60m) as max_sal_100
  from test_lizhen;

结果如下： 

2）区分产品，计算不同产品用户的分位数

select distinct product_no,
                PERCENTILE_CONT(0) within group(order by pltf_cnt_60m) over(partition by product_no) max_sal_0,
                PERCENTILE_CONT(0.2) within group(order by pltf_cnt_60m) over(partition by product_no) max_sal_0,
                PERCENTILE_CONT(0.4) within group(order by pltf_cnt_60m) over(partition by product_no) max_sal_0,
                PERCENTILE_CONT(0.6) within group(order by pltf_cnt_60m) over(partition by product_no) max_sal_0,
                PERCENTILE_CONT(0.8) within group(order by pltf_cnt_60m) over(partition by product_no) max_sal_0,
                PERCENTILE_CONT(1) within group(order by pltf_cnt_60m) over(partition by product_no) max_sal_0
  from test_lizhen;

结果如下：

 

我们还可以计算某个用户该变量取值在总体的位置：

PERCENT_RANK() over(partition by product_no order by pltf_cnt_60m) p_rank