hivesql 窗口函数,hive中窗口函数

记录工作中和学习中的窗口函数方便以后使用本记持续更新和完善版本231019

文章目录 1.什么是窗口函数2.窗口函数的表达式3.窗口函数的类型1 排名函数2 聚合函数3 跨行取值函数 4.[frame]滑动窗口1窗口选择的两种模式2滑动模式的两种表达式

1.什么是窗口函数

窗口函数能为每行数据划分一个窗口然后对窗口范围内的数据进行计算 最后将计算结果返回给该行 数据 。

使用场景在一条数据中既想要展示明细又想要体现一部分数据整体的效果就可以使用窗口函数。这也是它和聚合函数的不同之处是对于每个组返回多行而聚合函数对于每个组只返回一行。

如下图式例既想要展示sc表每个sid学生的成绩又想要展示展示每个学生在整个年级的排名情况rw即为窗口所展示的数据

SELECT *,row_number() over( ORDER BY score desc ) as rw FROM sc

2.窗口函数的表达式

function(args) over([partition by expression] [order by [asc|desc]] [frame])

表达式解析
function<窗口函数名>
over ”关键字 用于指定窗口的范围
(partition by <用于分组的列名> order by <用于排序的列名>
asc|desc升序和降序不写默认是升序

frame计算行数的范围详细请看下面第四点[frame]滑动窗口

3.窗口函数的类型 1 排名函数

row_number()、 rank()、dense_rank() 

row_number()排序相同时不会重复会根据顺序排序 即 1,2,3rank()排序相同时会重复同一排名有几个后面排名就会跳过几次即 1,1,3dense_rank()排序相同时会重复排名相同的名次一样且后面名次不跳跃即1,1,2

示例

select *,row_number() over(partition by cid order by score desc) rw,rank() over(partition by cid order by score desc) rk,dense_rank() over(partition by cid order by score desc) dense_rkfrom sc where cid  01

2 聚合函数

max()、 min()、sum()、avg()、count()

max ()最大值min ()最小值sum ()求和avg()求平均值count()计数 3 跨行取值函数

  lead()、lag()

语法

lead(expression,n)获取当前行的下边n行 、n个字段的值lag(expression,n)获取当前行的上边n行 、n个字段的值

expression行数命
n:代表便宜量想要偏移的行数

实例如下

select * from activeselect   uid,dt,lag(dt,1) over(PARTITION by uid ORDER BY dt) lg,lead(dt,1) over(PARTITION by uid ORDER BY dt) ldfrom active

使用范围可以对数据进行同步和环比针对

4.[frame]滑动窗口

function(args) over([partition by expression] [order by [asc|desc]] [frame])

1窗口选择的两种模式 rows模式按物理行进行划分range模式按数据模式进行划分 2滑动模式的两种表达式

{range|row}frame_start 从自定行开始到当前行结束
{range|row}between frame_start and frame_end 指定开始行和结束行

滑动行范围的常用表达式

表达式解析unbounded preceding从开始行expression preceding从当前行往前推 n 行 expression 写为整数current row当前行expression following从当前行往后推n行 expression 写为整数unbounded following到结束行

示例通过改变row参数改变窗口的大小

图一select * from sc 图二窗口默认当前行之前的所有行select sid,cid,  sum(score) over(partition by sid)from sc图三使用row使窗口变成当前行的前一行到当前行窗口只有两行得出图三的效果select sid,cid,  sum(score) over(partition by sid  rows 1  preceding)from sc

#当order by 后面缺少窗口从句条件窗口规范默认是 从当前行到当前行以上的所有行
rows between unbounded preceding and current
#当order by 和 窗口从句条件都缺失窗口范围默认 当前行上面所有行以及下面的所有行
rows between unbounded preceding and unbounded following