HQL窗口函数

HQL窗口函数

前言

更多往期文章请查看
大数据学习目录

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能
强大的函数，很多场景都需要用到。窗口函数用于计算基于组的某种聚合值，它和
聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一
行。
窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的
变化而变化。

OVER关键字

查询emp表工资总和

select sum(sal) from emp;

错误语法

select ename, sal, sum(sal) salsum from emp;

使用over()函数

使用窗口函数，查询员工姓名、薪水、薪水总和

select ename, sal, sum(sal) over() salsum, concat(round(sal / sum(sal) over()*100, 1) || '%') ratiosal from emp;

partition by子句

在over窗口中进行分区，对某一列进行分区统计，窗口的大小就是分区的大小

查询员工姓名、薪水、部门薪水总和

select ename, sal, sum(sal) over(partition by deptno) salsum from emp;

Order By

增加了order by子句；sum：从分组的第一行到当前行求和

select ename, sal, deptno, sum(sal) over(partition by deptno order by sal) salsum from emp;

Window子句

rows between ... and ...

如果要对窗口的结果做更细粒度的划分，使用window子句，有如下的几个选
项：

• unbounded preceding 组内第一行数据
• n preceding 组内当前行的前n行数据
• current row 当前行数据
• n following 组内当前行的后n行数据
• unbounded following 组内最后一行数据

查询部门工资总和

select ename, sal, deptno, sum(sal) over(partition by deptno order by sal) salsum from emp;

等价于

select ename, sal, deptno, sum(sal) over(partition by deptno order by sal rows between unbounded preceding  and current row ) salsum from emp;

排名函数

都是从1开始，生成数据项在分组中的排名。

• row_number()。排名顺序增加不会重复；如1、2、3、4、… …
• RANK()。 排名相等会在名次中留下空位；如1、2、2、4、5、… …
• DENSE_RANK()。 排名相等会在名次中不会留下空位 ；如1、2、2、3、4、…

--数据准备
class1	s01	100
class1	s03	100
class1	s05	100
class1	s07	99
class1	s09	98
class1	s02	98
class1	s04	97
class2	s21	100
class2	s24	99
class2	s27	99
class2	s22	98
class2	s25	98
class2	s28	97
class2	s26	96

数据放到/opt/data/t2.dat

注意:以上数据之间tab空开

--创建表加载数据
create table t2( cname string, sname string, score int ) 
row format delimited fields terminated by '\t'; 
load data local inpath '/opt/data/t2.dat' into table t2;

按照班级，使用3种方式对成绩进行排名

select cname, sname, score, 
row_number() over (partition by cname order by score desc) rank1, 
rank() over (partition by cname order by score desc) rank2, 
dense_rank() over (partition by cname order by score desc) rank3 
from t2;

求每个班级前3名的学员–前3名的定义是什么–假设使用dense_rank

select cname, sname, score, rank from 
(select cname, sname, score, dense_rank() over (partition by cname order by score desc) rank from t2) 
tmp where rank <= 3;

序列函数

• lag。返回当前数据行的上一行数据
• lead。返回当前数据行的下一行数据
• first_value。取分组内排序后，截止到当前行，第一个值
• last_value。分组内排序后，截止到当前行，最后一个值
• ntile。将分组的数据按照顺序切分成n片，返回当前切片值

--测试数据
cookie1,2019-04-10,1
cookie1,2019-04-11,5
cookie1,2019-04-12,7
cookie1,2019-04-13,3
cookie1,2019-04-14,2
cookie1,2019-04-15,4
cookie1,2019-04-16,4
cookie2,2019-04-10,2
cookie2,2019-04-11,3
cookie2,2019-04-12,5
cookie2,2019-04-13,6
cookie2,2019-04-14,3
cookie2,2019-04-15,9
cookie2,2019-04-16,7

--建表
create table userpv( cid string, ctime date, pv int )row format delimited fields terminated by ",";
Load data local inpath '/opt/data/userpv.dat' into table userpv;

lag。返回当前数据行的上一行数据

lead。返回当前数据行的下一行数据

select cid, ctime, pv, 
lag(pv) over(partition by cid order by ctime) lagpv, 
lead(pv) over(partition by cid order by ctime) leadpv 
from userpv;

first_value 取分组内排序后，截止到当前行，第一个值

last_value 分组内排序后，截止到当前行，最后一个值

select cid, ctime, pv, 
first_value(pv) over (partition by cid order by ctime rows between unbounded preceding and unbounded following) as firstpv, 
last_value(pv) over (partition by cid order by ctime rows between unbounded preceding and unbounded following) as lastpv 
from userpv;

ntile。按照cid进行分组，每组数据分成2份

select cid, ctime, pv, ntile(2) over(partition by cid order by ctime) ntile from userpv;