在Hive中支持窗口函数,Mysql在8.0版本后也支持使用,用好之后犹如开挂!
Window Function又称为窗口函数、分析函数。聚合函数可以将多行数据按照规定聚合为一行,一般来讲聚集后的行数要少于聚集前的行数。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时便引入了窗口函数。
运行顺序:窗口函数是在select时执行的,位于order by之前。
1. 累计计算窗口函数
语法总结:
sum(A) over(partition by B order by C rows between D1 and D2)
avg(A) over(partition by B order by C rows between D1 and D2)
max(A) over(partition by B order by C rows between D1 and D2)
min(A) over(partition by B order by C rows between D1 and D2)
count(A) over(partition by B order by C rows between D1 and D2)
partition by:相当于分组
order by:按照什么顺序进行累加等。默认升序asc, 降序为desc。
A:需要被加工的字段名称,对指定字段计算
B:分组的字段名称
C:排序的字段名称
D1,D2:计算的行数范围
rows between:也叫window子句
unbounded:无界的
preceding:之前
following:之后
current row:当前行
unbounded preceding:前面的起点
unbounded following:后面的终点
rows between unbounded preceding and current row:之前所有行和本行,不写rows between为默认起点到当前行;
rows between 3 preceding and current row:前3行和本行(共4行);
range between current row and unbounded following:本行和之后的所有行;
rows between current row and 3 following:本行和后面3行(共4行);
rows between 3 preceding and 1 following: 从前3行到下一行(共4行)。
2. 分区排序窗口函数
row_number() over(partition by A order by B)
rank() over(partition by A order by B)
desc_rank() over(partition by A order by B)
返回相应规则的排序序号
生成1 2 3 4 5 6 7 8 9 ... : row_number() 查询出来的每一行生成一个序号,依次排序,且不重复
2 .生成1 1 1 4 5 6 7 7 9 ... : rank() 生成的序号相同时,下一个不同的会跳跃,跳跃排序
生成1 1 1 2 3 3 4 5 6... :desc_rank()生成的序号相同时,下一个不同的不会跳跃,是连续排序
3. 切片:分组排序窗口函数
ntile(n) over(partition by A order by B)
n:切分的片数
A:分组的字段名称
B:排序的字段名称
ntile(n):用于将分组数据按照顺序切分成n片,返回切片值
不支持 rows between...
如果切片不均匀,默认从第一个开始均分,如 5 5 4 4
4. 偏移分析窗口函数
lag:向上偏移 ---比如向上偏移2行 实则新行数据由原来的行整体下移两行,前两行出现空值可由默认值填充
lead:向下偏移 --同理
lag(A,offset,defval) over(partition by ... order by ...)
A:字段名称
offset:
- 偏移量,即是向上偏移一个或n个的值,假设当前行为第5行,offset为3,则表示要找的数据为数据行的第2行(5-3=2)
- 默认值为1
defavl:
- 指定默认值:当取得值超出表的范围,则将defavl指定的值作为默认值
- 没指定默认值则返回null
例如 : lag(A,1,A) over(partition by USER_NAME order by A)
lag(A) over(partition by USER_NAME order by A)
lag(A,2) over(partition by USER_NAME order by A)
lag(A,offset,defval) over(partition by ... order by ...)
注意:一定要习惯取别名
lag( ... ) over(...) as ...
lead( ... ) over(...) as ...