SQL计算分位数时在百度搜到的结果通常是这样:
SELECT
percentile_DISC(0.9) WITHIN GROUP (ORDER BY amount )
OVER () AS percentile_disc
FROM sales;
这样在实际运用中通常会遇到两个问题:
1、不能对指定范围内的数据进行计算,即无法在函数中增加过滤条件
2、不能先分类再计算分位数,如同时计算各部门的销售额90分位数
以上两个问题可以这样解决:
1、在ORDER BY 后面增加CASE WHEN来设置条件,如
SELECT
percentile_DISC(0.9) WITHIN GROUP (ORDER BY CASE WHEN year IN (2019, 2020) AND some_condition THEN amount ELSE NULL END)
OVER () AS percentile_disc
FROM sales;
以上amount为需要计算分位数的字段
2、在OVER中增加partition by deparment,如
SELECT deparment,
percentile_DISC(0.9) WITHIN GROUP (ORDER BY CASE WHEN year IN (2019, 2020) AND some_condition THEN amount ELSE NULL END)
OVER (partition by deparment) AS percentile_disc
FROM sales;
以上deparment为分类字段