观远 BI

按比例快速排序出数值的Top N%

创建于 2023-02-21 / 最近更新于 2023-02-21 / 3207
字体: [默认] [大] [更大]

需求背景

      想要查看某数值在指定维度下聚合后的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)

图片.png

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。请根据实际场景选择合适的函数。


5 人点赞过