MySQL高级应用窗口函数（三）

Leefs 2021-11-21 PM 1478℃ 0条

[TOC]

### 前言

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

### 一、排序函数说明

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

### 二、需求示例

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

#### 2.1 需求

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

**思路**

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

**实现**

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

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

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

```mysql
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;
```

**运行结果**

![04.MySQL高级应用窗口函数（三）01.jpg](https://lilinchao.com/usr/uploads/2021/11/1394953120.jpg)

#### 2.2 需求

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

**思路**

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

**实现**

第一步：过滤基础数据

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

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

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

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

```mysql
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;
```

**运行结果**

![04.MySQL高级应用窗口函数（三）02.jpg](https://lilinchao.com/usr/uploads/2021/11/2594731186.jpg)

#### 2.3 需求

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

**思路**

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

**实现**

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

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

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

```mysql
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的用户

```mysql
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 );
```

**运行结果**

![04.MySQL高级应用窗口函数（三）03.jpg](https://lilinchao.com/usr/uploads/2021/11/657545681.jpg)

标签: MySQL

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://lilinchao.com/archives/1646.html

上一篇 MySQL高级应用窗口函数（二）

下一篇 MySQL高级应用窗口函数（四）

MySQL高级应用窗口函数（三）

评论已关闭

栏目分类

标签云

友情链接申请

MySQL高级应用窗口函数（三）

评论已关闭

 栏目分类

标签云

友情链接申请

栏目分类

标签云

友情链接申请