MySQL 窗口函数

最新推荐文章于 2024-10-01 12:47:02 发布

澄续缘

最新推荐文章于 2024-10-01 12:47:02 发布

阅读量306

点赞数 7

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixuan_/article/details/142624344

版权

MySQL 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

1、MySQL 窗口函数参考链接

https://blog.csdn.net/CoderSharry/article/details/135063960
https://blog.csdn.net/be_racle/article/details/125181320
https://blog.csdn.net/weixin_44852067/article/details/119570082

在这里插入图片描述

1）常用窗口函数

在这里插入图片描述

2）ROWS BETWEEN子句常见关键字含义

在这里插入图片描述

2、数据集

用户对电影的评级数据集
http://files.grouplens.org/datasets/movielens/

3、MySQL窗口函数案例

1）ntile、percent_rank、rank、row_number、cume_dist、lag

select departmentId,salary,
ntile(3) over() r1,
percent_rank() over(partition by departmentId order by salary) r2,
rank() over(partition by departmentId order by salary) r3,
dense_rank() over(partition by departmentId order by salary) r4,
row_number() over(partition by departmentId order by salary) r5,
cume_dist() over(partition by departmentId order by salary) r6,
lag(salary, 1) over(partition by departmentId order by salary) r7
from Employee

在这里插入图片描述

2）lead、first_value、last_value、nth_value

select departmentId,salary,
lead(salary, 1) over(partition by departmentId order by salary) r8,
first_value(salary) over(partition by departmentId order by salary) r9,
last_value(salary) over(partition by departmentId order by salary) r10,
nth_value(salary,2) over(partition by departmentId order by salary) r11
from Employee