MySQL高级应用窗口函数之排序函数

前言

本篇将通过示例讲解窗口函数中的组内排序函数：排序函数+over()

一、排序函数说明

• row_number(): 会生成数据项在分组中的排名，排名即便相等也不会有并列排名，相同排名随机排序。
• rank(): 可以生成数据项在分组中的排名，排名相等时会产生并列排名，然后会在名次中留下空位。
• dense_rank(): 可以生成数据项在分组中的排名，排名相等时会产生并列排名，但不会在名次中留下空位。
• ntile(k): 函数的作用是等频分箱，把观测值进行有序排列（默认升序），根据观测值的总个数等分为k部分，每部分当作一个分箱，即百分位数的概念。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

二、需求示例

本次使用的数据和MySQL高级应用窗口函数（二）中使用的数据相同。

2.1 需求

2021年购买商品品类数的用户排名

思路

(1)先统计出各个用户所购买商品涉及的品类数
(2)在1的基础上进行排名，使用排名窗口函数

实现

第一步：先统计出各个用户所购买商品涉及的品类数

SELECT
	user_name,
	count( DISTINCT goods_category ) count_category 
FROM
	user_order 
WHERE
	substring( pay_time, 1, 4 ) = '2021' 
GROUP BY
	user_name;

第二步：在1的基础上进行排名，使用排名窗口函数

SELECT
	user_name,
	count( DISTINCT goods_category ) count_category,
	row_number() over (ORDER BY count( DISTINCT goods_category )) order1,
	rank() over (ORDER BY count( DISTINCT goods_category )) order2,
	dense_rank() over (ORDER BY count( DISTINCT goods_category )) order3 
FROM
	user_order 
WHERE
	substring( pay_time, 1, 4 ) = '2021' 
GROUP BY
	user_name;

运行结果

2.2 需求

将2021年的支付用户，按照支付金额分成3组后的结果

思路

(1)过滤获取买足条件的基础数据
(2)求出各个用户的支付总金额
(3)通过窗口函数将支付总金额切分成3部分，查询出各个用户消费等级

实现

第一步：过滤基础数据

SELECT
	* 
FROM
	user_order 
WHERE
	substring( pay_time, 1, 4 )= '2021';

第二步：求出各个用户的支付总金额

SELECT
	user_name,
	sum( pay_amount ) sum_amount 
FROM
	user_order 
WHERE
	substring( pay_time, 1, 4 ) = '2021' 
GROUP BY
	user_name;

第三步：通过窗口函数将支付总金额切分成3部分，查询出各个用户消费等级

SELECT
	user_name,
	sum( pay_amount ) sum_amount,
	ntile( 3 ) over ( ORDER BY sum( pay_amount ) DESC ) LEVEL 
FROM
	user_order 
WHERE
	substring( pay_time, 1, 4 ) = '2021' 
GROUP BY
	user_name;

运行结果

2.3 需求

查询出2021年支付金额排名前30%的所有用户

思路

(1)过滤基础数据,查询出各个用户支付总金额
(2)通过窗口函数ntile将数据分成10份
(3)对上步结果作为表进行子查询，查询出等级为前3的用户

实现

第一步：过滤基础数据,查询出各个用户支付总金额

SELECT
	user_name,
	sum( pay_amount ) sum_amount 
FROM
	user_order 
WHERE
	YEAR ( pay_time ) = '2021' 
GROUP BY
	user_name;

第二步：通过窗口函数ntile将数据分成10份

SELECT
	user_name,
	sum( pay_amount ) sum_amount,
	ntile( 10 ) over ( ORDER BY sum( pay_amount ) DESC ) LEVEL 
FROM
	user_order 
WHERE
	YEAR ( pay_time ) = '2021' 
GROUP BY
	user_name;

第三步：对上步结果作为表进行子查询，查询出等级为前3的用户

SELECT a.user_name,a.sum_amount,a.LEVEL
FROM
	(
	SELECT
		user_name,
		sum( pay_amount ) sum_amount,
		ntile( 10 ) over ( ORDER BY sum( pay_amount ) DESC ) LEVEL 
	FROM
		user_order 
	WHERE
		YEAR ( pay_time ) = '2021' 
	GROUP BY
		user_name 
	) a 
WHERE
	a.LEVEL IN ( 1, 2, 3 );

运行结果