Hive窗口函数实战使用

1、row_number：

　　分组排序+序号标记

　　假如我们有这样一组数据，我们需要求出不同性别的年龄top2的人的信息。这个时候怎么做？可能我们会首先想到分组，但是分组只能值top1，怎么样能求出top2，top3呢？这时候我们想如果分组后能够按照年龄排序然后标出来序号就好了！

id   age  name sex

1，18，xiaoli,male
2，19，wang,male
3，22，liu,female
4，16，dawei,male
5，30，erbao,male
6，26，xiao,female
7，18，chengua,male

　　比如以上求解不同性别的年龄top2，我们可以这样做：

　　建表导入数据：

create table rownumber(id string,age int,name string,sex string)
row format delimited
fields terminated by ',';
load data local inpath '/root/mytest/rowover.dat' into table rownumber;

select id,age,name,sex,
row_number() over(partition by sex order by age desc) as rownumber
from rownumber;

　　可以看到 row_number() over(partition by sex order by age desc) as rownumber就相当于增加了一列序号,over()中partition by sex是按照sex分组，order by age desc按照年龄降序排序，然后row_number()在加上序号。

　　进一步应用：

select id,age,name,sex
from
(select id,age,name,sex,
row_number() over(partition by sex order by age desc) as rownumber
from rownumber ) temp
where rownumber<3;

参考博客：https://blog.csdn.net/weixin_39043567/article/details/90612526 

2、row_number()，Rank()，Dense_Rank()的区别

row_number():从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列;通常用于获取分组内排序第一的记录;获取一个session中的第一条refer等。

rank()：生成数据项在分组中的排名，排名相等会在名次中留下空位。

dense_rank():生成数据项在分组中的排名，排名相等会在名次中不会留下空位。

示例：数据准备

select * from dcx1234;

 

 

 

3、sum() ：

首先理解下什么是WINDOW子句

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

 

4、avg()

计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来

select row_number() over(partition by mgr order by empno) rn,
       empno,
       mgr,
       avg(sal) over(partition by mgr order by empno rows between 1 preceding and 1 following) avg_sal,
       sal
from emp;

 RN EMPNO   MGR    AVG_SAL       SAL
---------- ----- ----- ---------- ---------
         1  7788  7566       3000   3000.00
         2  7902  7566       3000   3000.00
         1  7499  7698       1425   1600.00
         2  7521  7698 1366.66666   1250.00
         3  7654  7698 1333.33333   1250.00
         4  7844  7698 1233.33333   1500.00
         5  7900  7698       1225    950.00
         1  7934  7782       1300   1300.00
         1  7876  7788       1100   1100.00
         1  7566  7839     2912.5   2975.00
         2  7698  7839 2758.33333   2850.00
         3  7782  7839       2650   2450.00
         1  7369  7902        800    800.00
         1  7839             5000   5000.00
14 rows selected

5、ntile：

6、LEAD,LAG,FIRST_VALUE,LAST_VALUE：

7、CUME_DIST，PERCENT_RANK

https://blog.csdn.net/dingchangxiu11/article/details/83145151