mysql开窗函数

已于 2024-07-24 18:43:55 修改
阅读量1.1w 收藏 40
点赞数 3
分类专栏： # sql数据库 文章标签： sql 数据库 database
于 2022-04-18 08:36:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_46926492/article/details/124236167
版权

mysql开窗函数

1.定义

  • MySQL 的开窗函数（Window Functions）是一类特殊的 SQL 函数，用于在查询结果的某个窗口（即结果集的一个分区）中进行计算。
  • 窗口函数为了解决想要即显示聚集前的数据，又要显示聚集后的数据。
  • 开窗函数对一组值进行操作，不需要group by 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列
  • 开窗函数不会压缩行数，而是在原始行数的基础上进行计算。
  • 这允许对数据进行复杂的分析和排序操作，而不影响结果集的完整性。
  • 开窗函数不会修改源数据表的结构，会在表的最后一列添加想要的结果，如果分组存在多行数据，则重复显示

注意：开窗函数在mysql8.0后才有

2.语法

函数名(列) over(选项【选项可以为 partition by 列 order by 列】)

  • 方式一：按照列所有行进行分组
    over(partition by 列)
  • 方式二：按照列排序
    over(order by 列)
  • 方式三：按照列1分组，按照列2排序
    over(partition by 列1 order by 列2)

3. 开窗函数汇总

4.聚合开窗函数

  • 常见聚合函数

    常见聚合函数含义
    min()最小值
    max()最大值
    avg()平均值
    sum()求和
    count()计数

  • 排序开窗函数

    排序开窗函数含义描述例子
    row_number( )行数生成连续的序号，不考虑分数相同例如：值：15556，排序：123456…
    rank( )排名相同分数的排名一样，后面排名为真正的序号，排名存在跳跃性;例子：值：13335，排序:12225
    dense_rank( )密集排序相同分数的排名一样，是连续的排名 ;例如：值：15558，排序：12223
    ntile( )分组排序ntile有桶的概念; 排序的结果数字大小只能用于桶与桶之间，桶内部虽然序号相同，但是num不一定相同ntile(6)，将总记录划分为6桶，如果是12条记录，就是一桶2条记录;排名就是：112233445566

  • 前后函数

    函数描述
    lag(col,n)用于获取当前行之前第个值,即统计窗口内向上第n行值
    lead(col,n)用于获取当前行之后第个值,即统计窗口内往下第n行值

  • 头尾函数

    函数描述
    first_value(column)取窗口中字段col的第一个值
    last_value(column)取窗口中字段col的最后一个值
    nth_value(column,n)取窗口中第n顺序的值

  • 分布函数

    函数描述
    cume_dist()row/sum(row);用于查询小于或等于某个值的比例
    percent_rank()等级值百分比:(rank-1) / (rows-1)。rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数

1. 常见聚合函数

常见聚合函数含义
min()最小值
max()最大值
avg()平均值
sum()求和
count()计数

  • 函数名如果是聚合函数，则成为聚合开窗函数

  • 语法：

    聚合函数(列)  over(partition by 列 order by 行) as 列名

  • 不同聚合函数对比

    SELECT
	a.*,
	max(s_score) over(PARTITION by c_id ) max_score,
	min(s_score) over(PARTITION by c_id ) min_score,
	avg(s_score) over(PARTITION by c_id ) avg_score,
	sum(s_score) over(PARTITION by c_id ) sum_score,
	count(s_score) over(PARTITION by c_id ) count_score
FROM
	score a

    在这里插入图片描述

  • 例一：计算每个学生的及格数

    • 使用聚合函数
      SELECT 
	student_id, 
	count(sid) 
FROM 
	score 
WHERE 
	num >= 60 
GROUP BY 
	student_id;
    • 使用开窗函数
      SELECT distinct
	student_id, 
	count(sid) over ( PARTITION BY student_id ORDER BY student_id ) 及格数 
FROM 
	score 
WHERE 
	num >= 60;

    在这里插入图片描述

2. 排序开窗函数

排序开窗函数含义描述例子
row_number( )行数生成连续的序号，不考虑分数相同例如：值：15556，排序：123456…
rank( )排名相同分数的排名一样，后面排名为真正的序号，排名存在跳跃性;例子：值：13335，排序:12225
dense_rank( )密集排序相同分数的排名一样，是连续的排名 ;例如：值：15558，排序：12223
ntile( )分组排序ntile有桶的概念; 排序的结果数字大小只能用于桶与桶之间，桶内部虽然序号相同，但是num不一定相同ntile(6)，将总记录划分为6桶，如果是12条记录，就是一桶2条记录;排名就是：112233445566
  • 例子：各种排名的区别
    SELECT
	a.*,
	rank() over(PARTITION by c_id order by s_score desc) rank排名,
	row_number() over(PARTITION by c_id order by s_score desc) row_number排名,
	dense_rank() over(PARTITION by c_id order by s_score desc) dense_rank排名,
	ntile(3) over(PARTITION by c_id order by s_score desc) 	ntile排名
FROM
	score a
    在这里插入图片描述
  • 例子：查询各科成绩前三名的学生成绩信息
    SELECT 
	* 
FROM 
	( SELECT 
			s.sid, 
			s1.sname, 
			s1.gender, 
			c.cname, 
			s.num, 
			dense_rank () over ( PARTITION BY c.cname ORDER BY num DESC ) AS dense_rank排名 
		FROM 
			score AS s 
		JOIN 
			student s1 
		ON 
			s.student_id = s1.sid 
		LEFT JOIN 
			course c 
		ON 
			s.course_id = c.cid ) AS e 
WHERE 
	dense_rank排名 <= 3;
    在这里插入图片描述

3. 前后函数

函数描述
lag(col,n)用于获取当前行之前第个值,即统计窗口内向上第n行值
lead(col,n)用于获取当前行之后第个值,即统计窗口内往下第n行值

  • 这两种函数可以用于同列中相邻行的数据相减操作

  • 例子

    SELECT
	* ,
	lag(s_score, 1) over ( PARTITION BY s_id ORDER BY c_id ) lag_score,
	lead(s_score, 1) over ( PARTITION BY s_id ORDER BY c_id ) lead_score
from 
	score;

    在这里插入图片描述

  • 案例：对于下面的数据，对于同一用户（uid）如果在2分钟之内重新登录，则判断为作弊，统计哪些用户有作弊行为，并计算作弊次数

    SELECT uid,count(1) 作弊次数
	FROM 
		( SELECT id, 
         		uid, 
         login_time, 
         -- 加一列时间
         lead (login_time, 1) over ( PARTITION BY uid ORDER BY login_time ) lead_time, 
         -- 求2列时间的差值
				timestampdiff( 
								SECOND, 
								login_time, 
								( lead (login_time, 1) over ( PARTITION BY uid ORDER BY login_time ))) 相差秒数 
		FROM lag_table ) as e
where 
	format(相差秒数 / 60, 3)<=2 
group by 
	uid;

    在这里插入图片描述

4. 头尾函数

函数描述
first_value(column)取窗口中字段col的第一个值
last_value(column)取窗口中字段col的最后一个值
nth_value(column,n)取窗口中第n顺序的值

  • nth_value函数依赖row_number来排序。故存在相等但排序不重复的情况。

  • 例子：查询成绩第二和第三的分数

    SELECT
	*,
	row_number() over(PARTITION by c_id order by s_score desc) as rn,
	nth_value(s_score,2) over(PARTITION by c_id order by s_score desc) as second_score,
	nth_value(s_score,3) over(PARTITION by c_id order by s_score desc) as third_score
from 
	score;

    在这里插入图片描述

  • 例一：不分组

    SELECT
	* ,
	first_value(s_score) over ( ORDER BY s_id) max_score,
	last_value(s_score) over ( ORDER BY s_id) min_score,
	nth_value(s_score,2) over(order by  s_id) as nth_score	
from 
	score;

    在这里插入图片描述

  • 例二：分组

    SELECT
	c_id,
	s_score,
	first_value(s_score) over ( PARTITION by c_id ORDER BY s_score desc) max_score,
	last_value(s_score) over ( PARTITION by c_id ORDER BY s_score desc) min_score,
	nth_value(s_score,2) over(PARTITION by c_id order by  s_score desc) as nth_score	
from 
	score;

    在这里插入图片描述

  • 例一：取科目成绩最大值(first_value)

    -- 根据降序，第一个值是最大值
select 
   s.sid,
   s1.sname,
   s1.gender,
   c.cname,
   s.num,
   first_value(num) over(partition by c.cname order by num desc) as firstvalue
from 
   score s 
join 
   student s1 
on 
   s.student_id=s1.sid
left join 
   course c 
on s.course_id=c.cid;

    在这里插入图片描述

5. 分布函数

函数描述
cume_dist()row/sum(row);用于查询小于或等于某个值的比例
percent_rank()等级值百分比:(rank-1) / (rows-1)。rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数
  • 例一
    SELECT
	*,
	rank() over(PARTITION by c_id order by s_score) as "rk",
	count(s_score) over(PARTITION by c_id) as "count",
	PERCENT_RANK() over(PARTITION by c_id order by s_score) as "PERCENT_RANK",
	cume_dist() over(PARTITION by c_id order by s_score) as "cume_dist"
from 
	score;
    在这里插入图片描述

6. order by修改范围

属性含义
unbounded无限制的
preceding分区的当前记录向前的偏移量
current当前
following分区的当前记录向后偏移量

  • 当前分组数据与数据进行比较，取最后一个值

    order by rows between unbounded preceding and unbounded following

  • 例子

    -- 取科目成绩最小值
-- 根据降序，最后一个值是最小值，要修改范围
  select 
  	s.sid,
  	s1.sname,
  	s1.gender,
  	c.cname,
  	s.num,
  	last_value(num) over(partition by c.cname order by num desc rows between unbounded preceding and unbounded following) as lastvalue
  from 
  	score s 
  join 
  	student s1 
  on 
  	s.student_id=s1.sid
  left join 
  	course c 
  on 
  	s.course_id=c.cid;

在这里插入图片描述

荼靡~
关注
专栏目录
mysql开窗函数使用
JustFor_N的博客
08-06 738
MySql在8.0的版本增加了对开窗函数的支持，终于可以在MySql使用开窗函数了。开窗函数又称OLAP函数（Online Analytical Processing） 开窗函数语法结构 #Key word :Partiton by & order by <开窗函数> over ([PARTITION by <分区列>] Order by <排序列>) 开窗函数分类 a. 能够作为开窗函数的聚合函数：（sum,
MySQL开窗函数
m0_67391377的博客
09-11 848
*窗口函数：**窗口 + 函数窗口： 函数运行时 计算的数据集的范围函数：运行时的函数聚合函数：COUNT,SUM,MIN,MAX,AVG内置窗口函数：取值：取窗口第一个值；LAST_VALUE：取窗口最后一个值；串行LEAD:窗口内 向下 第n行的值；LAG：窗口内 向上 第n行的值；排序NTILE：把数据平均分配 指定 N个桶 ，如果不能平均分配 ，优先分配到 编号 小的里面；RANK。
mySQL开窗函数
weixin_46331355的博客
12-18 278
函数名（列） over （partition by 列，order by 列）取分组内排序后，截止到当前行，最后一个值。取分组内排序后，截止到当前行，第一个值。dense_rank（密集排名）row_number（行号）用于统计窗口内往上第n行值。用于统计窗口内往下第n行值。ntile（分组排名）
MySQL三种开窗函数详细用法，图文详解
最新发布
baomingshu的博客
03-25 3296
over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。所以sum(score) over(partition by name ) ，就是先根据 name 分组(如图)，当前面加了sum（score）后就把根据name分组后的，每个（组）窗口里面的字段 score进行求和操作。rank()是排名，也为每一组的行生成一个序号,如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。
mysql实现开窗函数
ckqq46580的博客
08-06 525
学习过oracle的应该知道，oracle中的分析函数功能十分强大，包括mssql、postgresql等数据库都支持开窗函数。然而mysql至今都没有提供这样的功能，今天就来探讨下用mysql实现一些开窗功能需求。 实验数据...
mysql 开窗函数
01-07
场景1： 取max 表结构 create table tb_test_info( student_id char(4) comment '学号', grade_id int comment '课程号', score int comment '分数', primary key (student_id,grade_id) );...
MySQL开窗函数练习SQL文件
04-18
MySQL开窗函数练习SQL文件
mysql开窗函数有哪些_mysql开窗函数
weixin_29955689的博客
01-20 2443
开窗函数：它可以理解为记录集合，开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。开窗函数和普通聚合函数的区别聚合...
MySQL：开窗函数
weixin_48719464的博客
04-17 2800
当查询条件需要用到复杂子查询时，聚合函数操作起来非常麻烦，因此使用开窗函数能够轻松实现。注意：在Oracle中称为分析函数。在MySQL中称为开窗函数，使用于MySQL8.0以上版本，sql sever、hive、Oracle等。
Mysql实现开窗函数
liyang_007的博客
12-17 624
原始oracle代码 select row_number() over(partition by a.statis_day,a.app_version order by to_number(a.all_consume/a.cnt) desc) as rn from ( select statis_day ,app_version ,cast(action_consume+gl_cons...
MYSQL开窗函数详解
qq_34184505的博客
06-08 4784
MYSQL8.0支持窗口函数（Window Function），也称分析函数。窗口函数与组分聚合函数类似，但是每一行数据都会生成一个结果。如果我们将mysql与pandas中的DataFrame做类比学习的话他们的对应关系如下：所以以下函数：SUM/AVG/COUNT/MAX/MIN等既能做聚合函数又能做窗口函数，可称聚合窗口函数。如果对pandas的DataFrame中agg()/apply()/transform()这三个方法比较清楚的小伙伴，下面学习开窗函数会特别简单。 function(args）o
mysql有没有开窗函数_mysql实现开窗函数
weixin_36308404的博客
02-02 1388
mysql实现开窗函数开窗分析函数简单来讲就是相似partition by aaa, bbb order by ccc这样的形式mysql在使用mysql的过程当中遇到过这样的需求，须要求出一段时间内每一个item的最大值及对应的那一条记录。最大值很容易使用group by item求出来，可是求出那一条对应的全部字段及值却很差实现。通过查资料及探索发现mysql也能够实现，思路是人为构造一个字段...
MySql数据库高级函数—窗口函数（开窗函数）
qq_39754641的博客
05-10 1917
灵活运用窗口函数
mysql over partition by
03-20
MySQL中的OVER PARTITION BY是一种用于分析函数的语法，它允许在查询结果集中进行分组和排序，并对每个分组应用聚合函数。通过使用OVER PARTITION BY，您可以在不使用GROUP BY子句的情况下对结果集进行分组和聚合操作。 具体来说，OVER PARTITION BY子句用于指定分组的条件，可以是一个或多个列。然后，您可以在分组的基础上使用各种聚合函数，如SUM、AVG、COUNT等。 以下是一个示例，演示了如何在MySQL中使用OVER PARTITION BY： ``` SELECT column1, column2, SUM(column3) OVER (PARTITION BY column1) AS sum_column3 FROM your_table; ``` 在上面的示例中，我们选择了column1、column2和column3三列，并使用OVER PARTITION BY column1对结果集进行分组。然后，我们使用SUM函数计算每个分组中column3的总和，并将其命名为sum_column3。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

荼靡~

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值