sql计算前top n%的数据

• 背景

实际工作中经常遇见求top10或者top10%此类的需求，下面针对这些情况做一个小小的总结。

• 实现代码
  • 先来说top n这种情况，这是比较常见的排序需求，使用窗口函数可以解决，常见的窗口函数见链接hive sql 专用窗口函数

这其中细微的差别，使用的时候需要注意：

-- 常见的分组排序窗口函数
rank() OVER([partition_by_clause] order_by_clause);
row_number() OVER([partition_by_clause] order_by_clause);
dense_rank() OVER([partition_by_clause] order_by_clause);
-- 三者之间的区别
row_number()：不间断，序号不重复，如 1、2、3、4、5;
rank()：间断，相同值同序号，如 1、1、3、4、5;
dense_rank()：不间断，相同值同序号，如 1、1、2、3、3;
-- 比如计算分数排名top10的用户
SELECT 
user_id,
score,
row_number() over(ORDER BY score DESC) AS rn
FROM table
HAVING rn<=10;

• • 再来说求top10%的情况， 这个可以通过比率计算，也可以通过分桶函数计算，分桶函数详解hive sql 分组切片函数

分桶函数的特征【借鉴】：ntile 函数用于将分组数据按照顺序切分成n组，并返回当前切片值。如果切片不均匀，默认增加第一个切片的分布。它把有序的数据集合「平均分配」到指定的数量（n）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差 1。

-- 比如计算分数排名top10%的用户
SELECT 
user_id,
score,
ntile(10) over(ORDER BY score DESC) AS level
FROM table
HAVING level=1;

比率计算的方法，基本就是使用窗口函数排序/总数据量

-- 比如计算分数排名top10%的用户
SELECT 
user_id,
score,
row_number() over(ORDER BY score) as rn ,
count(1) over() rn_count
FROM table
HAVING rn / rn_count <= 0.1;