1. Toy模板网首页
  2. > Toy博客

常用窗口函数

10月前 作者：人之为言 分类：Toy博客 阅读(27) 违法举报

这篇具有很好参考价值的文章主要介绍了常用窗口函数。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1.前言

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

1.1.什么是窗口函数

窗口函数是 SQL 中的一种特殊函数，它允许你在查询结果中的某个窗口或窗口范围上执行计算，而不是单独针对整个结果集进行计算。

窗口函数通常与 OVER() 子句结合使用，用于定义窗口的范围。这个范围可以是整个结果集、分组的行、特定的物理行数范围，或者是基于值范围的窗口。

窗口函数通常在查询的最后阶段执行，即在结果集形成之后对结果集中的行应用窗口函数。窗口函数计算的结果是基于最终结果集的行，而不影响原始数据的分组，也就是说窗口函数的结果会添加到原查询结果的每一行上，这是个人觉得这是OVEE(PARTITION BY COLUMN_NAME ORDER BY COLUMN_NAME) 与 传统的 GROUP BY COLUMN ORDER BY COLUMN 差别最大的地方。

2.常用窗口函数

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

CREATE TABLE `video_play` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
  `video_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '视频类型',
  `duration` int DEFAULT NULL COMMENT '播放时间',
  `date` datetime DEFAULT NULL COMMENT '开始播放时间',
  PRIMARY KEY (`id`)
);

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

SELECT 
	*, 
	SUM(duration) OVER() AS exp1, 
	SUM(duration) OVER(PARTITION BY user_name) AS exp2, 
	SUM(duration) OVER(ORDER BY `date` ASC) AS exp3, 
	SUM(duration) OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4 
FROM 
	video_play;

常用窗口函数

2.2.COUNT()

用法：COUNT([DISTINCT] COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME])

整体用法跟SUM()函数类似，只是由求和变成了计数，并且支持DISTINCT去重，目前已知的是Mysql不支持。

sql举例：

SELECT 
	*, 
	COUNT(duration) OVER() AS exp1, 
	COUNT(duration) OVER(PARTITION BY video_type) AS exp2, 
	COUNT(duration) OVER(ORDER BY duration) AS exp3, 
	COUNT(duration) OVER(PARTITION BY video_type ORDER BY duration ASC) AS exp4 
FROM 
	video_play;

常用窗口函数

2.3.AVG()

用法：AVG(COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

整体用法跟SUM()函数类似，只是由求和变成了求平均值。

sql举例：

SELECT 
	*, 
	AVG(duration) OVER() AS exp1, 
	AVG(duration) OVER(PARTITION BY user_name) AS exp2, 
	AVG(duration) OVER(ORDER BY `date` ASC) AS exp3, 
	AVG(duration) OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4 
FROM 
	video_play;

常用窗口函数

2.4.ROW_NUMBER()

用法：ROW_NUMBER() OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME])

这个函数的功能是求结果的行号, 可以使用ORDER BY 进行内部排序之后再去求行号, 行号是连续的不会出现空缺

sql举例：

SELECT 
	*, 
	ROW_NUMBER() OVER() AS exp1, 
	ROW_NUMBER() OVER(PARTITION BY user_name) AS exp2, 
	ROW_NUMBER() OVER(ORDER BY `date` ASC) AS exp3, 
	ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4 
FROM 
	video_play;

常用窗口函数

这个函数是这次年报中用的比较多的函数，简化版的需求就是求每个用户播放时长TOPN的类型及播放时间。实现思路是现根据用户分组，再使用该函数对时长进行排序并生成行号，最后取每个用户的前N行数据，下面是实现的sql及结果

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_num
FROM
	user_video_play_index
WHERE
	row_num <= 2

常用窗口函数

2.5.RANK()和DENSE_RANK()

用法：RANK()/DENSE_RANK() OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME])

这个函数的功能是求排名, 如果不使用ORDER BY指明要排序的列, 值会固定为1。如果排序之后的值相同, RANK值也会相同, 如果是RANK()函数，后面的值会跳过相同数量的间隔，DENSE_RANK()则不会（这次的数据集中的值没有相同的，没办法体现出来这一点）。

sql举例

SELECT 
	*, 
	RANK() OVER() AS exp1, 
	RANK() OVER(PARTITION BY user_name) AS exp2, 
	RANK() OVER(ORDER BY duration ASC) AS exp3, 
	RANK() OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	DENSE_RANK() OVER() AS exp5, 
	DENSE_RANK() OVER(PARTITION BY user_name) AS exp6, 
	DENSE_RANK() OVER(ORDER BY duration ASC) AS exp7, 
	DENSE_RANK() OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8
FROM 
	video_play

常用窗口函数

2.6.FIRST_VALUE()和LAST_VALUE()

用法：FIRST_VALUE(COLUMN_NAME)/LAST_VALUE(COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

这两个函数是用来求分组和排序之后的第一个值和最后一个值, LAST_VALUE会随着窗口大小的增加而变化, 所以如果进行排序一般是当前行的值。

sql举例：

SELECT 
	*, 
	FIRST_VALUE(duration) OVER() AS exp1, 
	FIRST_VALUE(duration) OVER(PARTITION BY user_name) AS exp2, 
	FIRST_VALUE(duration) OVER(ORDER BY duration ASC) AS exp3, 
	FIRST_VALUE(duration) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	LAST_VALUE(duration) OVER() AS exp5,
	LAST_VALUE(duration) OVER(PARTITION BY user_name) AS exp6, 
	LAST_VALUE(duration) OVER(ORDER BY duration ASC) AS exp7, 
	LAST_VALUE(duration) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8 
FROM 
	video_play

常用窗口函数

2.7.LAG()和LEAD()

用法：LAG(COLUMN_NAME, MOVE_LINE_NUM, DEFAULT_VALUE)/LEAD(COLUMN_NAME, MOVE_LINE_NUM, DEFAULT_VALUE) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

LAG和LEAD是分别用来向前或向后取值的函数; 函数里面三个参数含义是从当前行向前或向后推MOVE_LINE_NUM行, 然后去取该行的COLUMN_NAME字段, 如果向前或向后移动失败(一般是超过窗口的头部或超过窗口的尾部)则去取DEFAULT_VALUE的值。

sql举例：

SELECT 
	*, 
	LAG(duration, 2, 0) OVER() AS exp1, 
	LAG(duration, 2, 0) OVER(PARTITION BY user_name) AS exp2, 
	LAG(duration, 2, 0) OVER(ORDER BY duration ASC) AS exp3, 
	LAG(duration, 2, 0) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	LEAD(duration, 2, 0) OVER() AS exp5,
	LEAD(duration, 2, 0) OVER(PARTITION BY user_name) AS exp6, 
	LEAD(duration, 2, 0) OVER(ORDER BY duration ASC) AS exp7, 
	LEAD(duration, 2, 0) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8 
FROM 
	video_play

常用窗口函数

2.8.NTILE()

用法：NTILE(PART_NUM) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

NTILE函数是用来对数据进行分组的, 它会尽可能的把数据平均分为PART_NUM组，结果为当前行的组号。如果遇到无法均分的情况，mysql的逻辑时后面的组会比前面的组少1行数据，其他的数据库暂时没有测试。

sql举例：

SELECT 
	*, 
	NTILE(2) OVER() AS exp1, 
	NTILE(2) OVER(PARTITION BY user_name) AS exp2, 
	NTILE(2) OVER(ORDER BY duration ASC) AS exp3, 
	NTILE(2) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4
FROM 
	video_play

常用窗口函数

3.others

当使用窗口函数时，ROWS、RANGE 和 GROUPS 是用于定义窗口框架的不同方式，它们用于指定窗口函数操作中所考虑的数据范围。

  • ROWS

    • SELECT
    id,
    value,
    SUM(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_values
FROM
    your_table;

    • 在这个例子中，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定了窗口函数 SUM() 的范围，在当前行的前一行到后一行（共三行）的范围内计算 value 的总和。

    • ROWS 是基于物理行数来定义窗口范围，独立于分组逻辑。

  • RANGE

    • SELECT
    date_column,
    value,
    SUM(value) OVER (ORDER BY date_column RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 3 DAY FOLLOWING) AS sum_values
FROM
    your_table;

    • 在这个例子中，RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 3 DAY FOLLOWING 指定了窗口函数 SUM() 的范围，以当前行日期为中心，计算前七天到后三天内的 value 总和。

  • GROUPS

    • SELECT
    id,
    value,
    SUM(value) OVER (PARTITION BY category ORDER BY id GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_values
FROM
    your_table;

    • 在这个例子中，GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定了窗口函数 SUM() 的范围，以当前行所属的 category 分组为基础，在每个分组内的前一行到后一行的范围内计算 value 的总和。

      这些不同的窗口框架定义方式允许在窗口函数中灵活地选择不同的数据范围进行计算和分析。

    • GROUPS 根据分组条件定义窗口范围，对应于分组的逻辑；

github原文链接：https://github.com/YS1109/Note/blob/master/Mysql/window_func/window_func.md文章来源地址https://www.toymoban.com/news/detail-760299.html

到了这里，关于常用窗口函数的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • mysql日期函数（查询最近n（天/月/年）、计算日期之间的天数等）

    mysql日期函数 目录 mysql查询最近一个月数据 返回当前日期和时间 将字符串转变为日期 日期 d 减去 n 天后的日期 计时间差，返回 datetime_expr2 − datetime_expr1 的时间差算 查询当天数据 ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期 ADDTIME(t,n) n 是一个时间表达式，时间 t 加上时间表达

    2024年02月13日
    浏览(41)
  • MYSQL窗口函数（Rows & Range）——滑动窗口函数用法

    MYSQL窗口函数（Rows & Range）——滑动窗口函数用法

    窗口函数语法： 窗口函数 over (partition by 用于分组的列名 order by 用于排序的列名 rows/range子句用于定义窗口大小 ) 窗口函数可以放以下两种函数： 1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。 2） 聚合函数，如sum. avg, count, max, min等 1）专用窗口

    2024年02月10日
    浏览(39)

  • hive窗口分析函数使用详解系列二之分组排序窗口函数

    我们讨论面试中各大厂的SQL算法面试题，往往核心考点就在于窗口函数，所以掌握好了窗口函数，面对SQL算法面试往往事半功倍。 已更新第一类聚合函数类，点击这里阅读 hive窗口函数聚合函数类 本节介绍Hive聚合函数中的第二类聚合函数：分组排序窗口函数。 这些函数的用

    2024年04月13日
    浏览(36)

  • SQL窗口分析函数使用详解系列三之偏移量类窗口函数

    本文以HiveSQL语法进行代码演示。 对于其他数据库来说同样也适用，比如SparkSQL，FlinkSQL以及Mysql8，Oracle，SqlServer等传统的关系型数据库。 已更新第一类聚合函数类，点击这里阅读 ①SQL窗口函数系列一之聚合函数类 ②SQL窗口函数系列二之分组排序窗口函数 本节介绍Hive窗口分

    2024年04月26日
    浏览(43)
  • SQL函数 - 开窗(窗口)函数

    SQL函数 - 开窗(窗口)函数

    开窗函数对一组值进行操作，它不像普通聚合函数那样需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列 开窗函数的语法形式为：函数 + over(partition by 分组用列 order by 排序用列)，表示对数据集按照分组用列进行分区，并且并且对每个分区按

    2024年02月14日
    浏览(53)

  • 聚合函数与窗口函数

    聚合函数（Aggregate Functions）是SQL中的函数，用于对一组数据进行计算，并返回单个结果。聚合函数通常用于统计和汇总数据，包括计算总和、平均值、计数、最大值和最小值等。 以下是一些常见的聚合函数： SUM()：计算指定列或表达式的总和。 AVG()：计算指定列或表达式的

    2024年02月10日
    浏览(44)

  • SparkSQL函数定义——UDF函数，窗口函数

    目录 1 定义UDF函数  1.1  返回值是数组类型的UDF定义 1.2 返回字典类型的UDF定义 2 窗口函数 目前python仅支持UDF 两种定义方式： 1. sparksession.udf.register() 注册的UDF可以用于DSL和SQL 返回值用于DSL风格，传参内给的名字用于SQL风格         方法一语法： udf对象 =  sparksession.ud

    2024年02月06日
    浏览(52)

  • ChatGPT 在做什么，为什么有效？

    原文：What Is ChatGPT Doing … and Why Does It Work? 译者：飞龙 协议：CC BY-NC-SA 4.0 这本简短的书试图从第一原理解释 ChatGPT 是如何工作的。在某种程度上，这是关于技术的故事。但它也是关于科学的故事。以及关于哲学的故事。为了讲述这个故事，我们将不得不汇集许多世纪以来的

    2024年04月09日
    浏览(52)
  • Flink处理函数（3）—— 窗口处理函数

    Flink处理函数（3）—— 窗口处理函数

    窗口处理函数包括：ProcessWindowFunction 和 ProcessAllWindowFunction 基础用法 这里的 MyProcessWindowFunction 就是 ProcessWindowFunction 的一个实现类； ProcessWindowFunction 是一个典型的全窗口函数，把数据全部收集保存在窗口内，等到触发窗口计算时再统一处理 源码解析 类型参数如下： IN：i

    2024年01月20日
    浏览(43)

  • Hive窗口函数-lead/lag函数

    前面我们学习的first_value和last_value 取的是排序后的数据截止当前行的第一行数据和最后一行数据 Lag和Lead分析函数可以在一次查询中取出当前行后N行和前N行的数据，虽然可以不用排序，但是往往只有在排序的场景下取前面或者后面N 行数据才有意义 这种操作可以代替表的自

    2024年02月16日
    浏览(62)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包