按比例快速排序出数值的Top N%
需求背景
想要查看某数值在指定维度下聚合后的Top N%。例如，得到每月销售额总和位于整体排名前15%的门店列表，本文将基于该场景介绍实现方法。
关键函数
cume_dist()over(partition by [分组维度] order by [排序维度])
-
cume_dist 计算逻辑：小于等于当前排序维度值的行数 除以 对应分组下的总行数。
-
默认升序排列，"order by [排序维度] desc "实现降序排列。
-
优点：
a. 可直观看到每行数据在分组下排序的具体百分比；
b. 如果改变想要的百分比数值，无需修改数据，只修改筛选条件即可。
- 注意点：由于计算受数据行数影响，建议用ETL把数据聚合到适合颗粒度后再使用该窗口函数计算。
实现步骤
1. 在ETL里使用“分组聚合”，把数据聚合到需要的颗粒度。本案例里，按“门店名”和“月份”分组聚合，对销售金额求和，确保每个门店每个月只有一行数据。
2. 新建计算字段，分组字段和分组聚合的维度相对应，每月各个门店金额汇总降序排列并计算百分位。
cume_dist()over(partition by [年月] order by [金额] desc)
3.制作卡片，数值栏要使用“分位排序”字段时，聚合方式要选择无处理，根据需求筛选数值小于等于0.15即可，参考下图。
注意：
当某个分组里的15%不是整数时，比如以上图中的2022年12月的门店数是19个，19*15%=2.85，向下取整（即19个门店中想保留2个）则筛选条件设置为小于等于0.15；如果需要向上取整（即19中保留3个），筛选前15%的条件改为小于0.16或者0.159999，反之后15%也是如此。
其他实现函数
分位数 percentile
percentile([金额],0.85)over(partition by [年月])
--返回这个分组下的真正分位数
等频划分的分位点percentile_approx
percentile_approx([金额],0.85)over(partition by [年月])
--返回最接近这个分组下真正分位数的字段值
ntile函数
ntile(n) over(partition by [分组字段] order by [排序字段])
--
a. n是将当前有序数据集平均分为n个桶，为每行按排序分配一个适当的桶序号（即切片值，第几个切片，第几个分区等概念）。它可用于将数据划分为相等的小切片，为每一行分配该小切片的数字序号。
b. n可根据前N%判断：比如想看前25%，切片数记为4；想看前10%，切片数记为10。
c. 如果切⽚不均匀，默认增加第⼀个切⽚的分布：当无法均分时，按顺序从第一个桶开始依次增加切片分布。比如一共10行数据，①分3个组，那么第一个组4行，其他两个组里都是3行；②分4个组，那么第一、二个组里3行，其他组里都是2行。
以上几个函数使用效果对比如上图。在本案例中，计算Top 15%时，percentile、ntile只能向上取整（19中取3个），cume_dist和percentile_approx 向上、下取整都可以实现。如果要改为计算Top 10%或者其他百分比，只有cume_dist 可以不修改ETL公式，只修改卡片里筛选条件，其他3个函数都需要修改ETL。请根据实际场景选择合适的函数。