翼度科技»论坛 编程开发 mysql 查看内容

常用窗口函数

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
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. );
  9. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (1, 'zhangsan', 'cartoon', 126, '2023-12-20 10:23:23');
  10. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (2, 'zhangsan', 'movie', 38, '2023-12-20 12:40:32');
  11. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (3, 'zhangsan', 'teleplay', 98, '2023-12-20 13:25:53');
  12. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (4, 'zhangsan', 'news', 45, '2023-12-20 15:24:43');
  13. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (5, 'lisi', 'cartoon', 83, '2023-12-20 09:28:17');
  14. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (6, 'lisi', 'movie', 56, '2023-12-20 12:11:11');
  15. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (7, 'lisi', 'teleplay', 79, '2023-12-20 13:56:23');
  16. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (8, 'wangwu', 'cartoon', 156, '2023-12-20 08:17:14');
  17. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (9, 'wangwu', 'movie', 89, '2023-12-20 09:25:35');
  18. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (10, 'wangwu', 'teleplay', 986, '2023-12-20 11:45:11');
  19. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (11, 'wangwu', 'news', 124, '2023-12-20 16:56:01');
  20. INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (12, 'xiaoming', 'cartoon', 123, '2023-12-20 07:08:34');
  21. 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

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具