MySQL高级应用窗口函数之聚合函数

前言

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

常用的聚合函数包括：

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

一、数据准备

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

创建语句

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	支付日期

• 部分数据

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

二、案例实操

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

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

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

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

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

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

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

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

查询结果

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

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

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()窗口函数得到累计支付总额

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 ) 

查询结果

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

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

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

查询结果

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

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

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

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()函数得到结果

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;