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

Leefs 2021-11-18 PM 1251℃ 0条

[TOC]

### 前言

本篇将介绍通过`聚合函数+over()`示例，对窗口函数做一个更好的了解。

常用的聚合函数包括：

+ sum()：累加求和
+ avg()：求平均值
+ max/min()：求最大/最小值
+ count()：统计行的数量

### 一、数据准备

+ 2020~2021年电商平台订单信息表user_order

创建语句

```sql
CREATE TABLE `user_order` (
  `user_name` varchar(20) DEFAULT NULL,
  `piece` int DEFAULT NULL,
  `price` double DEFAULT NULL,
  `pay_amount` double DEFAULT NULL,
  `goods_category` varchar(20) DEFAULT NULL,
  `pay_time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

表结构如下：

| 列名           | 释义     |
| -------------- | -------- |
| user_name      | 用户名   |
| piece          | 购买数量 |
| price          | 价格     |
| pay_amount     | 支付金额 |
| goods_category | 商品品类 |
| pay_time       | 支付日期 |

+ 部分数据

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

*注意：该数据只是模拟数据，并不是真实有效数据*

### 二、案例实操

#### 2.1 sum()over():累计计算函数

> 需求1：查询出2021年每月的支付总额和当年累计支付总额

+ 第一步：过滤出2021年数据

```mysql
SELECT
	* 
FROM
	user_order 
WHERE
	YEAR ( pay_time ) = 2021;
```

+ 第二步：在第一步的基础上,按照月份进行group by分组,统计每个月份的支付总额

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

+ 第三步：在第二步的基础上应用窗口函数实现需求

```mysql
SELECT
	a.MONTH AS `month`,-- 月份
	a.pay_amount,-- 当月总支付金额
	sum( a.pay_amount ) over ( ORDER BY a.MONTH ) AS total_amount 
FROM
	(
	SELECT MONTH
		( pay_time ) `month`,
		sum( pay_amount ) pay_amount 
	FROM
		user_order 
	WHERE
		YEAR ( pay_time ) = 2021 
	GROUP BY
		MONTH ( pay_time ) 
	) a
```

**查询结果**

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

> 需求2：查询出2020-2021年每月的支付总额和当年累计支付总额

+ 第一步：根据年和月进行group by分组,得到2020和2021年每月支付总额

```mysql
SELECT YEAR
	( pay_time ) YEAR,
	MONTH ( pay_time ) MONTH,
	sum( pay_amount ) pay_amount 
FROM
	user_order 
WHERE
	YEAR ( pay_time ) IN ( 2020, 2021 ) 
GROUP BY
	YEAR ( pay_time ),
	MONTH ( pay_time )
```

+ 第二步：通过sum()over()窗口函数得到累计支付总额

```mysql
SELECT
	a.YEAR,
	a.MONTH,
	a.pay_amount,
	sum( a.pay_amount ) over ( PARTITION BY a.YEAR ORDER BY a.MONTH ) total_amount 
FROM
	(
	SELECT YEAR
		( pay_time ) YEAR,
		MONTH ( pay_time ) MONTH,
		sum( pay_amount ) pay_amount 
	FROM
		user_order 
	WHERE
		YEAR ( pay_time ) IN ( 2020, 2021 ) 
	GROUP BY
		YEAR ( pay_time ),
		MONTH ( pay_time ) 
```

**查询结果**

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

#### 2.2 avg() over():移动平均函数

> 需求3：查询出2021年每个月的近三月移动平均支付金额

```mysql
SELECT
	a.MONTH,
	a.pay_amount,
	avg( a.pay_amount ) over ( ORDER BY a.MONTH rows BETWEEN 2 preceding AND current ROW ) AS avg_amount 
FROM
	(
	SELECT MONTH
		( pay_time ) `month`,
		sum( pay_amount ) pay_amount 
	FROM
		user_order 
	WHERE
		YEAR ( pay_time ) = 2021 
	GROUP BY
	MONTH ( pay_time ) 
	) a
```

**查询结果**

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

#### 2.3 max()/min() over():最大最小值

> 需求4：查询出每四个月的最大月总支付金额

+ 第一步：通过分组聚合获得各个月的月度支付总额

```mysql
SELECT
		SUBSTRING( pay_time, 1, 7 ) mon,
		round( sum( pay_amount ), 2 ) pay_amounts 
	FROM
		user_order
	GROUP BY
		SUBSTRING( pay_time, 1, 7 ) 
```

+ 第二步：通过窗口max() over()函数得到结果

```mysql
SELECT
	a.mon,
	a.pay_amounts,
	max( a.pay_amounts ) over ( ORDER BY a.mon rows BETWEEN 3 preceding AND CURRENT ROW ) max_pay_amount 
FROM
	(
	SELECT
		SUBSTRING( pay_time, 1, 7 ) mon,
		round( sum( pay_amount ), 2 ) pay_amounts 
	FROM
		user_order
	GROUP BY
		SUBSTRING( pay_time, 1, 7 ) 
	) a;
```

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

### 结尾

如果需要本篇开头示例的表和数据，可以在微信公众号:【Java和大数据进阶】回复【sql】即可获取。

标签: MySQL

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

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

上一篇 MySQL高级应用窗口函数(一)

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

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

评论啦~

栏目分类

标签云

友情链接申请

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

 评论啦~

 栏目分类

标签云

友情链接申请

评论啦~

栏目分类

标签云

友情链接申请