Hive 之 分析窗口函数

一 分析函数介绍

1.1分析函数定义

分析函数又被称为窗口函数。在联机分析(OLAP)的时候，传统是SQL可能需要进行多个自连接，从而导致查询新能很差。又或者有时候需要多维分析需要在不同层级进行聚合，并且聚合后数据行和密钥聚合的数据行都必须返回。传统是SQL都有一定的局限性。分析函数很好的可以解决这些问题，可以不使用自联结就能在一行中取出聚合和非聚合的行

1.2分析函数组成

function(argument1, argument2…)over([partition-by-cluase][order-by-clasue][windowing-clause])

分析函数有三个部分组成：分区子句，排序子句，以及窗口子句。

分区子句： 其实可以理解为根据什么分组

排序子句：对每一个分区的结果集排序

窗口子句：

[ROWS|RANGE] BETWEEN <开始表达式> AND <结束表达式>

<开始表达式>:[UNBOUNDED PRECEDING| CURRENT ROW|

nRECEDING | n FOLLOWING]

<结束表达式>:[UNBOUNDED FOLLOWING|CURRENT ROW|

nRECEDING | n FOLLOWING]

UNBOUNDEDPRECEDING：针对当前分区里面的当前行而言的前面所有行

UNBOUNDEDFOLLOWING：针对当前分区里面的当前行而言的后面所有行

CURRENTROW： 当前行

nRECEDING： 针对当前行而言，前n行

nFOLLOWING：针对当前行而言，后n行

 

1.3分析函数举例子

数据准备：

CREATETABLE IF NOT EXISTS nicky(

      id INT,

      num INT,

      group STRING

)

ROWFORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOADDATA LOCAL INPATH '/opt/data/num.txt' OVERWRITE INTO TABLE nicky;

1    10  A

2    20  A

3    40  E

4    100     A

5    20  B

6    40  E

7    60  B

8    50  B

9    20  C

10  40  D

11  60  C

12  50  D

ROWS是物理窗口，现根据ORDERBY 子句排序之后，取前N行和后N行数据计算。

 

例1：ROWSBETWEEN UNBOUNDED PRECEDING AND CURRENT

Sum(num)= 当前行之前所有行num+当前行num

SELECTgroup,SUM(num) OVER(PARTITION BY group ORDER BY num ROWS BETWEEN UNBOUNDEDPRECEDING AND CURRENT ROW) range_sum FROM nicky;

分析：

处理第一行的时候，第一行是CURRENTROW，UNBOUNDEDPRECEDING没有值，sum值应该是10；

处理第二行的时候，第二行是CURRENTROW，UNBOUNDEDPRECEDING是前1行；累加值应该是第一行num+第二行num

也就是10+20=30；

处理第三行的时候，第三行是CURRENTROW，UNBOUNDEDPRECEDING是前2行，累加值应该是第一行num+第二行num+第三行num,也就是10+20+100=130；

至此当前分区数据处理完毕；然后开始处理B组，也是同理，大家可以自己推断。

例2：ROWSBETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING

Sum(num)= 当前行前面所有行num+当前行num+当前行下一行num

每一行累计都是计算第一行到当前行的下一行

SELECTid, num, group,SUM(num) OVER(PARTITION BY group ORDER BY num ROWS BETWEENUNBOUNDED PRECEDING AND 1 FOLLOWING) range_sum FROM nicky;

分析：

处理第一行，当前行是第一行，UNBOUNDEDPRECEDING没有值，1FOLLOWING是当前行的下一行是第二行，所以第一行的sum值应该是第一行num+第二行num =10+20=30;

处理第二行，当前行是第二行，UNBOUNDEDPRECEDING是第一行，1FOLLOWING是当前行的下一行，第三行，所以结果是

第一行num+第二行num+第三行num=10+20+100=130；

处理第三行，当前行是第二行，UNBOUNDEDPRECEDING是前2行，1FOLLOWING是当前行的下一行，已经没有下一行了，所以结果还是第一行num+第二行num+第三行num=10+20+100=130；

例3:ROWSBETWEEN 2 PRECEDING AND CURRENT ROW

每一行累计都是计算当前行前2行到当前行的num值

SELECTgroup,SUM(num) OVER(PARTITION BY group ORDER BY num ROWS BETWEEN 2 PRECEDINGAND CURRENT ROW) range_sum FROM nicky;

 

RANGE:是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内.所以它会计算取值范围，根据这个范围来取值

 

例1：RANGEBETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Sum(num)= 当前行之前所有行num+当前行num

SELECTid,num,group,SUM(num) OVER(PARTITION BY group ORDER BY num RANGE BETWEENUNBOUNDED PRECEDING AND CURRENT ROW) range_sum FROM nicky;

例2：RANGEBETWEEN 10 PRECEDING AND 20 FOLLOWING

首先计算取值范围

开始位置：当前行num值-10

结束位置：当前行num值+20

只要当前分区所有记录num字段属于这个范围都应该被计算。

SELECTid,num,group,SUM(num) OVER(PARTITION BY group ORDER BY num RANGE BETWEEN 10PRECEDING AND 20 FOLLOWING) range_sum FROM nicky;

当前行为第一行：num=10,开始位置10-10=0；结束位置：10+20=30；

所以A这个组num字段属于0-30都会被计算，我们知道只有第一行和第二行满足要求：所以sum=10+20=30.

当前行为第二行：num=20,开始位置20-10=10；结束位置20+20=40；

所以A这个组num字段属于20-40会被计算，我们知道只有第一行和第二行满足要求，所以sum=10+20=30.

当前行为第三行：num=100,开始位置100-10=90；结束位置100+20=120；

所以A这个组num字段属于90-120会被计算，我们知道只有第三行满足要求，所以sum=100；

如果为B分区，以此类推。

 

1.4partition by 和 groupby 比较

Partitionby 和 groupby 都可以对多个字段进行分组；

但是partitionby 分组结果会显示每一条记录，并且显示分析结果

Groupby只是显示分组信息，并不显示每一条记录

举个例子：

SELECTd.dname,AVG(sal) avg_sal FROM emp e LEFT JOIN dept d ON e.deptno = d.deptnoGROUP BY d.dname;

结果：

ACCOUNTING  2916.6666666666665

RESEARCH  2175.0

SALES     1566.6666666666667

 

SELECTd.dname, AVG(e.sal) OVER(PARTITION BY d.dname) avg_sal FROM emp e LEFT JOINdept d ON e.deptno = d.deptno;

ACCOUNTING  2916.6666666666665

ACCOUNTING  2916.6666666666665

ACCOUNTING  2916.6666666666665

RESEARCH  2175.0

RESEARCH  2175.0

RESEARCH  2175.0

RESEARCH  2175.0

RESEARCH  2175.0

SALES     1566.6666666666667

SALES     1566.6666666666667

SALES     1566.6666666666667

SALES     1566.6666666666667

SALES     1566.6666666666667

SALES     1566.6666666666667

 

二SUM AVG MIN MAX 聚合函数

2.1SUM 聚合统计求和

SELECTd.dname, SUM(e.sal) OVER(PARTITION BY d.dname) sum_sal FROM emp e LEFT JOINdept d ON e.deptno = d.deptno;

2.2AVG 聚合统计求平均

SELECTd.dname, AVG(e.sal) OVER(PARTITION BY d.dname) avg_sal FROM emp e LEFT JOINdept d ON e.deptno = d.deptno;

2.3MIN 聚合统计求最小数

SELECTd.dname, MIN(e.sal) OVER(PARTITION BY d.dname) min_sal FROM emp e LEFT JOINdept d ON e.deptno = d.deptno;

2.4 MAX聚合统计求最大数

SELECTd.dname, MAX(e.sal) OVER(PARTITION BY d.dname) max_sal FROM emp e LEFT JOINdept d ON e.deptno = d.deptno;

 

三 Lag/Lead/First_Value/Last_Value

这几个分析函数都是针对分区结果集的行进行访问

3.1Lag (字段，前N行，默认值)

访问分区域结果集中字段的前N行，如果没有则显示默认值，如果没有默认值则显示NULL

SELECTe.empno,e.ename,e.job,e.sal,d.dname, LAG(e.hiredate,2) OVER(PARTITION BYd.dname) lag FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

比较不使用Lag的情况：

 

分析：lag访问分区结果集当前行之前第2行的数据

第一条记录的前2行没有值，我们又没有提供默认值，所以该值为NULL；

第二条记录的前2行没有值，我们又没有提供默认值，所以该值为NULL；

第三条记录的前2行是当前分区第一行，所以该值为1982-1-23

以此类推其他dept.

 

3.2Lead(字段，后N行，默认值)

访问分区域结果集中某个字段之后第N行，如果没有，则显示默认值，如果没有提供默认值，则显示NULL.

SELECTe.empno,e.ename,e.job,e.sal,d.dname, LEAD(e.hiredate,2,'2000-1-1')OVER(PARTITION BY d.dname) lag FROM emp e LEFT JOIN dept d ON e.deptno =d.deptno;

分析：

第一条记录后2行是当前分区第三行，所以lead是1981-6-9

第二条记录后2行没有，所以lead是默认值2000-1-1

第三条记录后2行没有，所以lead是默认值2000-1-1

 

3.3FIRST_VALUE(字段)

当前分区第一行该字段的值

SELECTe.empno,e.ename,e.job,e.sal,d.dname, FIRST_VALUE(e.ename) OVER(PARTITION BYd.dname) lag FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

 

3.4LAST_VALUE(字段)

当前分区最后一行该字段的值

SELECTe.empno,e.ename,e.job,e.sal,d.dname, LAST_VALUE(e.ename) OVER(PARTITION BYd.dname) lag FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

 

四 ROW_NUMBER/RANK/DENSE_RANK

4.1ROW_NUMBER

对行进行排序并为一行增加一个唯一编号。

SELECTe.empno,e.ename,e.job,e.sal,d.dname, ROW_NUMBER() OVER(PARTITION BY d.dname) row_numFROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

4.2RANK

将数据行值按照排序后的顺序进行排名，在有并列的情况下排名值将被跳过，留下空位

SELECTe.empno,e.ename,e.job,e.sal,d.dname, RANK() OVER

(PARTITIONBY d.dname ORDER BY e.sal) rank FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

分析：比如7654，7521,7499薪水都是1600，所以rank都是3，但是到7698并不是从4开始计算，而是把4,5过滤掉了，直接显示6

 

4.3 DENSE_RANK

跟RANK差不多，但是遇到相同的值的列，并不跳过排名值。

SELECTe.empno,e.ename,e.job,e.sal,d.dname, RANK() OVER(PARTITION BY d.dname ORDER BYe.sal) dense_rank FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

这个时候7698就是从4开始计算排名而不是6了

 

4.4PERCENT_RANK

将计算得到的排名值标准化到0-1之间的值

4.5CUME_DIST

计算小于或者等于当前行值的行数/当前分区总行数，一般用于计算某个值占分组内数据百分比。

SELECTe.empno,e.ename,e.job,e.sal,d.dname, CUME_DIST() OVER(PARTITION BY d.dnameORDER BY e.sal) cume_dist FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

分析：

首先看Account分组，按照薪水排序，第一行薪水为1300,小于等于1300就这一行，分组总行数为3，所以第一行cume_list的值是1/3；

第二行薪水为2450，小于等于2450就第一行和第二行2行，分组总行数为3，计算结果为2/3；

第二行薪水为5000，小于等于5000就第一行和第二行和第三行共计三行，分组总行数为3，计算结果为3/3；

其他分组以此类推。

4.6NTILE(N)

将数据行分组为N个单元。比如统计某个部门薪水前50%的人员

SELECTe.empno,e.ename,e.job,e.sal,d.dname,NTILE(2) OVER(PARTITION BY d.dname ORDER BYe.sal) ntile

FROMemp e LEFT JOIN dept d ON e.deptno = d.deptno;

然后ntile=1的就是当前分组50%或者接近50%. 如果不是均匀的，那么会增加第一个单元。比如总共5条记录在当前分区，然后，分为2个单元，那么默认2.5的那行也会分到第一个单元。

 

五GROUPING SETS/WITH CUBE/WITH ROLLUP

5.1GROUPING SETS

在GROUPBY后面的GROUPINGSETS从句，允许针对相同的结果集指定多个GROUPBY 选项，然后GROUPINGSETS从句指定的选项，会分成多个单个GROUPBY分组 然后进行UNIONALL,没有的那个GROUP 选项用NULL填充

SELECTd.dname,d.loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ON e.deptno =d.deptno GROUP BY d.dname,d.loc GROUPING SETS(d.dname,d.loc);

相当于

SELECTd.dname,NULL AS loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ONe.deptno = d.deptno GROUP BY d.dname

UNIONALL

SELECTNULL AS dname,d.loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ONe.deptno = d.deptno GROUP BY d.loc;

 

5.2WITH CUBE

针对GROUPBY 选项，然后针对该选项可能的组合进行联合，一旦我们针对某一套dimension，我们能获取到所有可能聚合的结果，按照文档所述：

GROUPBY a, b, c WITH CUBE is equivalent to 
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b),(c), ( )).

SELECTd.dname,d.loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ON e.deptno =d.deptno GROUP BY d.dname,d.loc

WITHCUBE;

它会针对()(d.dname),(d.loc),(d.dname,d.loc)进行分组，然后联合结果

相当于

SELECTNULL dname,NULL loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ONe.deptno = d.deptno

UNIONALL

SELECTNULL dname,d.loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ON e.deptno= d.deptno GROUP BY d.loc

UNIONALL

SELECTd.dname,NULL loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ON e.deptno= d.deptno GROUP BY d.dname

UNIONALL

SELECTd.dname,d.loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ON e.deptno =d.deptno GROUP BY d.dname,d.loc;

 

5.3WITH ROLLUP

进行聚合计算基于dimension的层级：

GROUPBY a, b, c, WITH ROLLUP is equivalent to GROUP BY a, b, c GROUPING SETS ( (a,b, c), (a, b), (a), ( ))。

 

SELECTd.dname,d.loc,SUM(e.sal) AS sum_sal FROM emp e LEFT JOIN dept d ON e.deptno =d.deptno GROUP BY d.dname,d.loc

WITHROLLUP;