理解与应用:窗口函数中的row_number技巧与开窗机制
1、背景
窗口函数的引入是为了解决 想要既显示聚集前的数据,又要显示聚集后的数据。
开窗函数对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。
2、常见用法
over() 按所有行进行分组
over(partition by xxx) 按xxx分组的所有行进行分组
over(partition by xxx order by aaa) 按xxx分组,按列aaa排序 的按到当前行(含当前行)进行分组
over前可以加聚合函数 例如sum count avg min max等
over前也可以加 first_value last_value等
windows 字句 用来制定累加的方式
rank() over等的用法
参考资料:https://blog.****.net/qq_26937525/article/details/54925827
1、LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
2、LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
3、FIRST_VALUE(column)取分组内排序后,截止到当前行,第一个值
4、LAST_VALUE(column)取分组内排序后,截止到当前行,最后一个值
5、row_number() 分组排序功能,row_number()从1开始,为每一条分组记录返回一个数字 (在row_number中排序的时候,可以通过NULLS LAST、NULLS FIRST来控制有NULL的输出)
这里LAG、LEAD统计窗口内往上或者往下第N行值的情况 是基于自己当前行去做的操作
参考资料:http://lxw1234.com/archives/2015/04/190.htm
3、row_number()用法
用法
ROW_NUMBER() OVER()函数用来为每条记录返回一个行号,可以用来对记录进行排序并返回该序号,序号从1开始排序
over()是聚集函数,可以给记录进行分组、排序;row_number()不能单独使用,必须搭配over()才能使用
具体用例
1、给数据添加序号
比如示例
hive> select * from test;
OK
河北 沧州 1001
北京 北京 1002
辽宁 沈阳 1003
吉林 长春 1004
河北 秦皇岛 1005
辽宁 大连 1006
河北 廊坊 1007
如果需要随便添加一个序号的话 可以使用 row_number() over()
如果需要排序分组的话 在over 中添加想要分组或者排序的字段
上一篇: 快速掌握:SQL中的窗口函数简介
下一篇: SQL Window Functions
推荐阅读
-
Oracle应用中的窗口函数详解与实战案例解析
-
理解与应用:窗口函数中的row_number技巧与开窗机制
-
SQL中的窗口函数:累计求和示例与sumover应用
-
玩转 SQL 窗口函数 (Window Function):OVER 的关键作用与应用指南" - 分组小能手:理解 PARTITION BY,就像在数据分块后为每个组执行聚合操作,不同窗口函数的分组互不影响 - 排序达人:在 OVER 之后加入 ORDER BY,就像 SQL 中一样轻松设定行的排序顺序 - 计算范围说明:引入 ROWS 子句,就像给窗口函数设置边界条件,无论是从哪一行开始计算,到哪一行结束,都能精准控制,例如要求前5行和后5行的总和,只需用 ROWS BETWEEN...AND... 指定界限规则即可。
-
理解窗口函数与聚合函数:两者的差异与应用
-
理解与运用:SQL中的窗口函数详解
-
理解Hive中的窗口函数:从LAG到LEAD,再到FIRST_VALUE和LAST_VALUE的作用与应用
-
理解Hive中的窗口函数:ROW_NUMBER、RANK、DENSE_RANK与NTILE的实践应用与剖析
-
理解 SQL 窗口函数: over()与 sum, AVG, MIN, MAX 的实战应用
-
玩转Oracle:理解与应用窗口函数技巧