如何将SQL查询中计算前top n%的数据转换为长尾？

本文共计888个文字，预计阅读时间需要4分钟。

背景+实际工作中经常遇到的需求top10或top10%的需求，下面针对这些情况做一个小小的总结。

实现代码+先来说说top+n这种情况，这是比较常见的需求，通常涉及排序需求。我们可以使用窗口函数来解决。

常见需求：

1.查询每个部门员工工资排名前3的记录。

2.查询每个季度销售额排名前5的记录。

3.查询每个城市订单数量排名前10的记录。

实现代码示例：

sql

-- 查询每个部门员工工资排名前3的记录SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rankFROM employees;

-- 查询每个季度销售额排名前5的记录SELECT quarter, product_id, sales_amount, ROW_NUMBER() OVER (PARTITION BY quarter ORDER BY sales_amount DESC) AS rankFROM sales;

-- 查询每个城市订单数量排名前10的记录SELECT city, order_id, order_quantity, ROW_NUMBER() OVER (PARTITION BY city ORDER BY order_quantity DESC) AS rankFROM orders;

使用窗口函数ROW_NUMBER()可以根据指定列的值对数据进行排序，并为每个分区中的行分配一个唯一的序号。在上述示例中，我们分别对每个部门、季度和城市进行了分区，并按照工资、销售额和订单数量的降序排列，从而实现了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;