开窗函数的使用
开窗函数(over子句)用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用group by 子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。
开窗函数与聚合函数一样,也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗口。
语法:主要是over( PARTITION BY (根据某条件分组,形成一个小组)….ORDER BY(再组内进行排序) …. ) 常用函数:(最常用的应该是1.2.3 的排序)
1、row_number() over(partition by … order by …) 增加一列,类似与增加伪列
2、rank() over(partition by … order by …)
3、dense_rank() over(partition by … order by …)
rank(): 跳跃排序,如果有两个第一级时,接下来就是第三级。
dense_rank(): 连续排序,如果有两个第一级时,接下来仍然是第二级。
4、count() over(partition by … order by …)
5、max() over(partition by … order by …)
6、min() over(partition by … order by …)
7、sum() over(partition by … order by …)
8、avg() over(partition by … order by …)
9、first_value() over(partition by … order by …)
10、last_value() over(partition by … order by …) 与函数的功能一致,只是是开窗函数
11、lag() over(partition by … order by …)
12、lead() over(partition by … order by …)
lag 和lead 可以 获取结果集中,按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联);
lag ,lead 分别是向前,向后
lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值)
注意:
1、开窗函数VS聚合函数:开窗函数对于每个组返回多行,而聚合函数对于每个组只返回一行
2、rank() VS dense_rank():rank可以将并列第一名的都查找出来;rank()和dense_rank() 区别:rank()是跳跃排序,有两个第二名时接下来就是第四名,dense_rank()是连续排序 ,有两个第二名时仍然跟着第三名。
3、 lag() over(partition by ... order by ...):取出前n行数据。 lead() over(partition by ... order by ...):取出后n行数据。
4、over partition by和 group by的区别 group by是对检索结果的保留行进行单纯分组,一般和聚合函数一起使用如max、min等 partition by 虽然也具有分组功能,但同时也具有其他的高级功能
例子:sum() over (PARTITION BY ...) 是一个分析函数。 他执行的效果跟普通的sum ...group by ...不一样,它计算组中表达式的累积和,而不是简单的和。
推荐阅读
-
MATLAB 计算和建模的常用函数:5.曲线拟合
-
C++ 学习笔记 ----8, 掌握类和对象 (II) ---- 更多关于成员函数的知识 (2)
-
如何使用工具移除 iPhone 上的图片背景
-
[人工智能知识点] 损失函数(损失函数) - 如何选择合适的损失函数?
-
特征工程和选择:优化模型性能的关键步骤 ---- 示例:泰坦尼克号生存预测中的特征工程,使用递归特征消除 (RFE) 进行特征选择
-
第 22 节:学习使用拦截器(自学 Spring boot 3.x 的第 6 天)--第 2 步:注册拦截器
-
使用 Mybatis 框架的主要优势 - 1. 灵活性
-
在 WPF 下使用 FreeRedis 操作 RedisStream 的简单消息队列。
-
使用 xpath 在 HTML 中精确定位元素的基本方法
-
解释 JavaScript 中函数的实参和形参