在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. 分区排序窗口函数
1. row_number() over(partition by A order by B)
2. rank() over(partition by A order by B)
3. desc_rank() over(partition by A order by B)
返回相应规则的排序序号
1. 生成1 2 3 4 5 6 7 8 9 ... : row_number() 查询出来的每一行生成一个序号，依次排序，且不重复
2 .生成1 1 1 4 5 6 7 7 9 ... : rank() 生成的序号相同时，下一个不同的会跳跃，跳跃排序
3. 生成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 ...
文章来源: 博客园
- 还没有人评论，欢迎说说您的想法！
客服
