常用窗口函数

忍者乱太郎

1.前言

又到了年底，最近在做年报数据拉取的需求，在这期间有一些数据的计算需要用到窗口函数，就去了解了一下常用的窗口函数的用法。
1.1.什么是窗口函数

窗口函数是 SQL 中的一种特殊函数，它允许你在查询结果中的某个窗口或窗口范围上执行计算，而不是单独针对整个结果集进行计算。
窗口函数通常与 OVER() 子句结合使用，用于定义窗口的范围。这个范围可以是整个结果集、分组的行、特定的物理行数范围，或者是基于值范围的窗口。
窗口函数通常在查询的最后阶段执行，即在结果集形成之后对结果集中的行应用窗口函数。窗口函数计算的结果是基于最终结果集的行，而不影响原始数据的分组，也就是说窗口函数的结果会添加到原查询结果的每一行上，这是个人觉得这是OVEE(PARTITION BY COLUMN_NAME ORDER BY COLUMN_NAME) 与 传统的 GROUP BY COLUMN ORDER BY COLUMN 差别最大的地方。
2.常用窗口函数

可以使用以下sql进行初始化

1. CREATE TABLE `video_play` (
2.   `id` int NOT NULL AUTO_INCREMENT,
3.   `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
4.   `video_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '视频类型',
5.   `duration` int DEFAULT NULL COMMENT '播放时间',
6.   `date` datetime DEFAULT NULL COMMENT '开始播放时间',
7.   PRIMARY KEY (`id`)
8. );
10. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (1, 'zhangsan', 'cartoon', 126, '2023-12-20 10:23:23');
11. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (2, 'zhangsan', 'movie', 38, '2023-12-20 12:40:32');
12. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (3, 'zhangsan', 'teleplay', 98, '2023-12-20 13:25:53');
13. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (4, 'zhangsan', 'news', 45, '2023-12-20 15:24:43');
14. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (5, 'lisi', 'cartoon', 83, '2023-12-20 09:28:17');
15. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (6, 'lisi', 'movie', 56, '2023-12-20 12:11:11');
16. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (7, 'lisi', 'teleplay', 79, '2023-12-20 13:56:23');
17. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (8, 'wangwu', 'cartoon', 156, '2023-12-20 08:17:14');
18. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (9, 'wangwu', 'movie', 89, '2023-12-20 09:25:35');
19. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (10, 'wangwu', 'teleplay', 986, '2023-12-20 11:45:11');
20. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (11, 'wangwu', 'news', 124, '2023-12-20 16:56:01');
21. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (12, 'xiaoming', 'cartoon', 123, '2023-12-20 07:08:34');
22. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (13, 'xiaoming', 'movie', 963, '2023-12-20 09:13:23');

复制代码

2.1.SUM()

用法：SUM(COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);
如果存在PARTITION BY COLUMN_NAME, 则代表查询结果根据COLUMN_NAME进行分组求和。
如果存在ORDER BY COLUMN_NAME, 则代表查询结果则根据COLUMN_NAME排序后, 按照排序的结果进行累积求和。这里的累积求和是指在当前窗口内，当前行的值加上前行的值得到的结果。如果不加ORDER BY同一窗口内每一行的值都相同，为当前窗口内所有值的和。
sql举例：

1. SELECT
2.         *,
3.         SUM(duration) OVER() AS exp1,
4.         SUM(duration) OVER(PARTITION BY user_name) AS exp2,
5.         SUM(duration) OVER(ORDER BY `date` ASC) AS exp3,
6.         SUM(duration) OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4
7. FROM
8.         video_play;

复制代码

2.2.COUNT()

用法：COUNT([DISTINCT] COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME])
整体用法跟SUM()函数类似，只是由求和变成了计数，并且支持DISTINCT去重，目前已知的是Mysql不支持。
sql举例：

1. SELECT
2.         *,
3.         COUNT(duration) OVER() AS exp1,
4.         COUNT(duration) OVER(PARTITION BY video_type) AS exp2,
5.         COUNT(duration) OVER(ORDER BY duration) AS exp3,
6.         COUNT(duration) OVER(PARTITION BY video_type ORDER BY duration ASC) AS exp4
7. FROM
8.         video_play;

复制代码

2.3.AVG()

用法：AVG(COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);
整体用法跟SUM()函数类似，只是由求和变成了求平均值。
sql举例：

1. SELECT
2.         *,
3.         AVG(duration) OVER() AS exp1,
4.         AVG(duration) OVER(PARTITION BY user_name) AS exp2,
5.         AVG(duration) OVER(ORDER BY `date` ASC) AS exp3,
6.         AVG(duration) OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4
7. FROM
8.         video_play;

复制代码

2.4.ROW_NUMBER()

用法：ROW_NUMBER() OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME])
这个函数的功能是求结果的行号, 可以使用ORDER BY 进行内部排序之后再去求行号, 行号是连续的不会出现空缺
sql举例：

1. SELECT
2.         *,
3.         ROW_NUMBER() OVER() AS exp1,
4.         ROW_NUMBER() OVER(PARTITION BY user_name) AS exp2,
5.         ROW_NUMBER() OVER(ORDER BY `date` ASC) AS exp3,
6.         ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4
7. FROM
8.         video_play;

复制代码

这个函数是这次年报中用的比较多的函数，简化版的需求就是求每个用户播放时长TOPN的类型及播放时间。实现思路是现根据用户分组，再使用该函数对时长进行排序并生成行号，最后取每个用户的前N行数据，下面是实现的sql及结果
[code]WITH user_video_play_index AS (        SELECT                user_name,                video_type,                duration,                ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY duration DESC, date ASC) AS row_num        FROM                 video_play)        SELECT         user_name,        video_type,        duration,        row_numFROM        user_video_play_indexWHERE        row_num