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 * FROM 
(SELECT 
user_id,
score,
row_number() over(ORDER BY score DESC) AS rn
FROM table) a
WHERE a.rn<=10;

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

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

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

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

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

__EOF__

本文作者：BaiYueKui
本文链接：https://www.cnblogs.com/bai-yue-kui/p/16482441.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！