使用SQL为组/分区计算百分位数

Question

我想计算SQL中给定分区/组的百分位数。例如，输入数据看起来像-

CustID     Product ID     quantity_purchased    
1          111                2
2          111                3
3          111                2 
4          111                5
1          222                2
2          222                6
4          222                7
6          222                2

我想得到每个产品ID组的百分位数。输出应为-

Product ID    min      25%      50%      75%     max
111        2        2       2.5      3.5      5
222        2        2        4       6.25     7

如何使用SQL实现这一点？

Answer 1

您可以使用percentile_cont():

select product_id, min(quantity_purchased), max(quantity_purchased),
percentile_cont(0.25) within group (order by quantity_purchased),
percentile_cont(0.50) within group (order by quantity_purchased),
percentile_cont(0.75) within group (order by quantity_purchased)
from t
group by product_id;