17、MySQL基础之窗口函数

最新推荐文章于 2025-09-05 12:49:39 发布

原创最新推荐文章于 2025-09-05 12:49:39 发布 · 483 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

MySQL 专栏收录该内容

24 篇文章

订阅专栏

本文深入解析了窗口函数的概念、语法及应用场景，涵盖了统计、序号、分布、前后和头尾等多种函数类型，通过具体案例展示了如何在SQL中运用窗口函数进行高效的数据分析。

官方网站

什么是窗口函数？

能与开窗函数(over)结合使用的函数均称为窗口函数。
窗口函数的特点：针对每条记录返回一个函数值

窗口函数语法

function OVER ( 
               [ <PARTITION BY clause> ]     
               [ <ORDER BY clause> ]         
               [ <frame clause> ]   
 )

可以与over结合使用的函数有：
- 统计函数：sum、count、max、min、avg 等
- 序号函数：
  - row_number()：分区内当前行的行号
  - rank()：分区内当前行的排名（有间隔）
  - dense_rank()：分区内当前行的排名（无间隔）
- 分布函数：
  - percent_rank() ：百分比排名值
  - cume_dist()：累计分布值
- 前后函数：
  - lag() ：落后于分区内当前行的那一行的参数值
  - lead()：领先于分区内当前行的那一行的参数值
- 头尾函数：
  - first_value()：窗口帧中第一行的参数值
  - last_value()：窗口帧中最末行的参数值
- 其他函数：
  - nth_value() ：窗口帧中的第n行的参数值
  - ntile()：分区内当前行的桶的编号/ 返回分区内当前行所属的百分位
partition子句：用于通过指定字段对数据表（where过滤的记录除外）进行分区（分组），partition子句可以省略，若省略partition子句，则以where过滤后的数据作为一个分区（分组）。窗口函数是基于分区计算的。
order子句：用于通过指定字段对分区中的数据排序，窗口函数会按照记录在分区中的顺序进行编号，order子句可以省略，若省略order子句，则不能自定义窗口帧，窗口帧为整个分区
frame子句：用于从分区中选择指定的多条记录，供窗口函数处理，mysql提供了两种定义窗口帧的形式：rows或者 range。两种类型都需要配置上界和下界。

定义窗口帧的相关术语

unbounded preceding：表示分区第一行 
unbounded following：表示分区的末行
current row：表示当前行
num preceding：相对当前行的上num行
num following：相对当前行的下num行

常见的窗口帧

ROWS between num preceding and num following：以当前行为参照物，取前num行后num行包括当前行
ROWS between unbounded preceding and unbounded following：表示分区第一行到分区最后一行
ROWS between unbounded preceding and current row：表示分区第一行到当前行
ROWS between current row and unbounded following：表示当前行到分区最后一行

默认窗口帧

有order从句，但没有frame从句，窗口帧默认为：ROWS between unbounded preceding and current row（分区第一行到当前行）
order从句与frame从句都没有，窗口帧默认为：ROWS between unbounded preceding and unbounded  following（分区第一行到分区最后一行）

案例练习

现有销售表sale：

year	month	money
2018	1	50
2018	2	100
2018	4	150
2019	4	200
2019	5	250
2019	6	300
2019	7	350
2019	8	400
2019	9	450
2018	3	500
2018	5	550
2018	6	600
2018	7	650
2018	8	700
2018	9	750
2019	1	800
2019	2	850
2019	3	900
2018	10	950
2018	11	100
2018	12	150
2019	10	800
2019	11	400
2019	12	50

数据源

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sale
-- ----------------------------
DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale`  (
  `year` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `month` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `money` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sale
-- ----------------------------
INSERT INTO `sale` VALUES ('2018', '1', 50);
INSERT INTO `sale` VALUES ('2018', '2', 100);
INSERT INTO `sale` VALUES ('2018', '4', 150);
INSERT INTO `sale` VALUES ('2019', '4', 200);
INSERT INTO `sale` VALUES ('2019', '5', 250);
INSERT INTO `sale` VALUES ('2019', '6', 300);
INSERT INTO `sale` VALUES ('2019', '7', 350);
INSERT INTO `sale` VALUES ('2019', '8', 400);
INSERT INTO `sale` VALUES ('2019', '9', 450);
INSERT INTO `sale` VALUES ('2018', '3', 500);
INSERT INTO `sale` VALUES ('2018', '5', 550);
INSERT INTO `sale` VALUES ('2018', '6', 600);
INSERT INTO `sale` VALUES ('2018', '7', 650);
INSERT INTO `sale` VALUES ('2018', '8', 700);
INSERT INTO `sale` VALUES ('2018', '9', 750);
INSERT INTO `sale` VALUES ('2019', '1', 800);
INSERT INTO `sale` VALUES ('2019', '2', 850);
INSERT INTO `sale` VALUES ('2019', '3', 900);
INSERT INTO `sale` VALUES ('2018', '10', 950);
INSERT INTO `sale` VALUES ('2018', '11', 100);
INSERT INTO `sale` VALUES ('2018', '12', 150);
INSERT INTO `sale` VALUES ('2019', '10', 800);
INSERT INTO `sale` VALUES ('2019', '11', 400);
INSERT INTO `sale` VALUES ('2019', '12', 50);

SET FOREIGN_KEY_CHECKS = 1;

统计函数

问题一：显示2018年度每月销售额以及截止当前月的总销售额

方法一：

-- where选中的数据才会被over中的关键字处理，由于被over关键字处理的数据均为2018年，可以视为一个分区，所以可以省略partition子句。此处使用默认窗口帧：即分区第一行到当前行
SELECT sale.*,SUM(money) over( ORDER BY `month`)
FROM sale
WHERE `year` = '2018'

方法二：

-- 被over关键字处理的数据既有2018年的又有2019年的，所以通过partition子句按照year分区
SELECT sale.*,SUM(money) over(PARTITION BY `year` ORDER BY `month`)
FROM sale
LIMIT 12

问题二：显示2018年度每月销售额以及年度销售额
方法一：使用默认窗口帧

-- 没有order子句，就不能自定义窗口帧，窗口帧默认为分区第一行到分区最后一行
SELECT *,sum(money) OVER()
FROM sale
where year = '2018'
ORDER BY `month`

方法二：使用order子句

-- 如果只有order子句，没有frame子句，默认窗口帧为：分区第一行到当前行，因此需要自定义窗口帧为：分区第一行到分区最后一行
-- 使用rows关键字定义frame子句
SELECT *,sum(money) OVER(ORDER BY `month` ROWS between UNBOUNDED preceding and UNBOUNDED following)
FROM sale
where year = '2018';
-- 或者使用range关键字定义frame子句
SELECT *,sum(money) OVER(ORDER BY `month` RANGE between UNBOUNDED preceding and UNBOUNDED following)
FROM sale
where year = '2018';

window关键字的使用

多个字段共用相同的窗口，可以使用window关键字

问题三：显示2018年度每月销售额以及月销售额的最大值。

SELECT *,sum(money) OVER w, -- 求分区内momey的总和
MAX(money) OVER w  -- 求分区内money的最大值
FROM sale
where year = '2018'
window w AS (ORDER BY `month` RANGE between UNBOUNDED preceding and UNBOUNDED following)

序号函数

问题四：显示2018年度每月销售额以及排名

ROW_NUMBER()函数：返回分区内当前行的行号

SELECT *,ROW_NUMBER() over(PARTITION BY `year`),-- 返回分区内当前行的行号
RANK() over()
FROM sale
-- 若按照money对分区的数据进行降序排序，再显示行号，即可获取排名（无并列排名）
SELECT *,ROW_NUMBER() over(ORDER BY money DESC)-- 返回分区内当前行的行号
FROM sale
where year = '2018'

rank()函数：返回当前行在分区中的排名，允许并列排名，并列排名后的名次不是递增1而是递增并列数
dense_rank()函数：返回当前行在分区中的排名，允许并列排名，并列排名后的名次递增1
percent_rank()函数：分区内当前行的RANK值-1/分组内总行数-1
cume_dist()函数：小于等于当前行（排序字段的数据）的行数/分区内总行数
first_value(col)函数：返回窗口帧中第一行指定字段的数据
last_value(col)函数：返回窗口帧中最后一行指定字段的数据
lead(col,n,default)：返回相对于当前行的下n行的指定字段的数据，参数n，可以省略，默认为1，参数default可以省略，若省略，当当前行的下n行为null时，则返回null，否则返回default。
lag(col,n,default)函数：返回相对于当前行的上n行的指定字段的数据，参数n，可以省略，默认为1，参数default可以省略，若省略，当当前行的上n行为null时，则返回null，否则返回default。
nth_value(expression, n)函数：返回窗口帧中的第n行的指定字段的数据
ntile(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。ntile不支持rows between

-- DATE_SUB() 函数从日期减去指定的时间间隔。
select date_format(pay_time,'%Y-%m-%d') as date, count(distinct user_id) as num_users
from table
where pay_time>=date_sub(now(),interval 1 month) #过去一个月
group by date
order by num_users desc
limit 3;