文章目录
- 概念、语法规则
- 普通常规聚合操作 求出每个用户总pv数 sum+group by
- 窗口函数聚合函数
- 窗口表达式
- 窗口排序函数--row_number家族
- 找出每个用户访问pv最多的Top3,重复并列的不考虑。--row_number()
- NTILE()函数--几分之几
- 窗口分析函数
进一步理解窗口函数可以看相关案例:【大数据Hive3.x数仓开发】窗口函数案例
概念、语法规则
窗口函数( Window functions)也叫做开窗函数、0LAP函数,其最大特点是:输入值是从SELECT语句的结果集中的行或多行的“窗口”中获取的。
如果函数具有OVER子句,则它是窗口函数。
窗口函数可以简单地解释为类似于聚合函数的计算函数,但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行,最终输出一行,窗口函数聚合后还可以访问当中的各个行,并且可以将这些行中的某些属性添加到结果集中.
语法规则:
普通常规聚合操作 求出每个用户总pv数 sum+group by
select cookieid,sum(pv) as total_pv from website_pv_infogroup by cookieid;
窗口函数聚合函数
总共有四种用法 注意是整体聚合 还是累积聚合
- sum(…) over( )对表所有行求和
–需求:求出网站总的pv数 所有用户所有访问加起来
–输出:每行都保留,最后一列是总和
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as total_pv
from website_pv_info;
–输出:每行都保留,按cookieid分组求和,一组内的数这个值是一样的
- sum(…) over( partition by… order by …) 在每个分组内,连续累积求和
–需求:求出每个用户截止到当天,累积的总pV数
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as total_pv
from website_pv_info;
输出结果对比:
有了order by就变成了累计求和,默认从第一行到当前行
窗口表达式
提供了控制行范围的能力,比如向前2行,后3行
关键字是rows between,包括下面这几个选项
preceding: 往前
following: 往后
current row :当前行
unbounded: 边界
unbounded preceding: 表示从前面的起点
unbounded following :表示到后面的终点
窗口排序函数–row_number家族
适合TopNotch业务分析
- rank(),在每个分组中,为每行分配一个从1开始的序列号,考虑重复,挤占后续位置;
- dese_rank(),为每行分配一个从1开始的序列号,考虑重复,不挤占后续位置;
- row_number(),为每行分配一个从1开始的唯一序列号,递增,不考虑重复。
select
cookieid
,createtime
,pv
,rank()over(partition by cookied order by pv desc)as rn1
,dese_rank() over(partition by cookied order by pv desc)as rn2
,row_number() over(partition by cookied order by pv desc) as rn3
from website_pv_info
where cookied='cookie1';
找出每个用户访问pv最多的Top3,重复并列的不考虑。–row_number()
NTILE()函数–几分之几
窗口分析函数
- LAG(col,n,DEFAULT) 用于统计窗口内往 上n行值。第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL);
- LEAD(col,nDEFAULT) 用于统计窗口内往 下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL);
- FIRST VALUE 取分组内排序后,截止到当前行,第一个值;
- ==LAST VALUE ==取分组内排序后,截止到当前行,最后一个值。
LAG为例: