基本语法为：Function (aRg1,…, aRgn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [
Function (aRg1,…, aRgn) 可以归类为以下四种函数：
聚合函数：如：suM(…)、Max(…)、Min(…)、avg(…)等；排序函数：如：Rank(…)、Row_nuMbeR(…)等；分析函数：如：lead(…)、lag(…)、 fiRst_value(…)等。
数据准备
样例数据：
[职工姓名|部门编号|职工ID|工资|岗位类型|入职时间] Michael|1000|100|5000|full|2014-01-29 Will|1000|101|4000|full|2013-10-02 Wendy|1000|101|4000|paRt|2014-10-02 Steven|1000|102|6400|paRt|2012-11-03 LUCy|1000|103|5500|full|2010-01-03 Lily|1001|104|5000|paRt|2014-11-29 JeSS|1001|105|6000|paRt|2014-12-02 Mike|1001|106|6400|paRt|2013-11-03 Wei|1002|107|7000|paRt|2010-04-03 Yun|1002|108|5500|full|2014-01-29 RichaRd|1002|109|8000|full|2013-09-01
建表语句：
CREATE TABLE IF NOT EXISTS employee (naMe stRing, dept_nuM int, employee_id int, salaRy int, type stRing, staRt_date date) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘|’ STorED as TEXTfile;
加载数据：
load data local inpath ‘/opt/datas/data/employee_contRact.txt’ into table employee;
窗口聚合函数
(1)查询姓名、部门编号、工资及部门人数：
select naMe, dept_nuM as deptno , salaRy, count(*) OVeR (paRtITion by dept_nuM) as cnt fRoM employee ;
查询结果：
naMe deptno salaRy cnt LUCy 1000 5500 5 Steven 1000 6400 5 Wendy 1000 4000 5 Will 1000 4000 5 Michael 1000 5000 5 Mike 1001 6400 3 JeSS 1001 6000 3 Lily 1001 5000 3 RichaRd 1002 8000 3 Yun 1002 5500 3 Wei 1002 7000 3
(2)查询姓名、部门编号、工资以及每个部门的总工资，按总工资降序输出：
select naMe , dept_nuM as deptno, salaRy, suM(salaRy) OVeR (paRtITion by dept_nuM oRdeR by dept_nuM) as suM_dept_salaRy fRoM employee oRdeR by suM_dept_salaRy desc;
查询结果：
naMe deptno salaRy suM_dept_salaRy Michael 1000 5000 24900 Will 1000 4000 24900 Wendy 1000 4000 24900 Steven 1000 6400 24900 LUCy 1000 5500 24900 Wei 1002 7000 20500 Yun 1002 5500 20500 RichaRd 1002 8000 20500 Lily 1001 5000 17400 JeSS 1001 6000 17400 Mike 1001 6400 17400
窗口排序函数
简介
窗口排序函数提供了排序信息，如行号和排名。常用的排序函数包括：
Row_nuMbeR：为每行数据生成唯一序列号，起始值为1；
Rank：对分组数据进行排名，相同名次的排名相同，但后续名次可能不连续；
dense_Rank：功能与Rank类似，但生成的序号是连续的；
peRcent_Rank：计算公式为：(cuRRent Rank – 1)/(tOTAl nuMbeR of Rows – 1)；
ntile：将有序数据集划分为多个桶，为每行分配桶号。
(1)查询姓名、部门编号、工资及按工资排名的编号：
select naMe , dept_nuM as dept_no , salaRy, Row_nuMbeR() OVeR (oRdeR by salaRy desc ) RnuM fRoM employee;
查询结果：
naMe dept_no salaRy RnuM RichaRd 1002 8000 1 Wei 1002 7000 2 Mike 1001 6400 3 Steven 1000 6400 4 JeSS 1001 6000 5 Yun 1002 5500 6 LUCy 1000 5500 7 Lily 1001 5000 8 Michael 1000 5000 9 Wendy 1000 4000 10 Will 1000 4000 11
(2)查询每个部门工资最高的两个人信息：
select naMe, dept_nuM, salaRy fRoM ( select naMe , dept_nuM , salaRy, Row_nuMbeR() OVeR (paRtITion by dept_nuM oRdeR by salaRy desc ) RnuM fRoM employee ) t1 wheRe RnuM <= 2;
查询结果：
naMe dept_nuM salaRy Steven 1000 6400 LUCy 1000 5500 Mike 1001 6400 JeSS 1001 6000 RichaRd 1002 8000 Wei 1002 7000
(3)查询每个部门员工工资排名信息：
select naMe , dept_nuM as dept_no , salaRy,Row_nuMbeR() OVeR (paRtITion by dept_nuM oRdeR by salaRy desc ) RnuM fRoM employee;
查询结果：
naMe dept_no salaRy RnuM Steven 1000 6400 1 LUCy 1000 5500 2 Michael 1000 5000 3 Wendy 1000 4000 4 Will 1000 4000 5 Mike 1001 6400 1 JeSS 1001 6000 2 Lily 1001 5000 3 RichaRd 1002 8000 1 Wei 1002 7000 2 Yun 1002 5500 3
(4)使用Rank函数进行排名：
select naMe, dept_nuM, salaRy, Rank() OVeR (oRdeR by salaRy desc) Rank fRoM employee;
查询结果：
naMe dept_nuM salaRy Rank RichaRd 1002 8000 1 Wei 1002 7000 2 Mike 1001 6400 3 Steven 1000 6400 3 JeSS 1001 6000 5 Yun 1002 5500 6 LUCy 1000 5500 6 Lily 1001 5000 8 Michael 1000 5000 8 Wendy 1000 4000 10 Will 1000 4000 10
(5)使用dense_Rank进行排名：
select naMe, dept_nuM, salaRy, dense_Rank() OVeR (oRdeR by salaRy desc) Rank fRoM employee;
查询结果：
naMe dept_nuM salaRy Rank RichaRd 1002 8000 1 Wei 1002 7000 2 Mike 1001 6400 3 Steven 1000 6400 3 JeSS 1001 6000 4 Yun 1002 5500 5 LUCy 1000 5500 5 Lily 1001 5000 6 Michael 1000 5000 6 Wendy 1000 4000 7 Will 1000 4000 7
(6)使用peRcent_Rank()进行排名：
select naMe, dept_nuM, salaRy, peRcent_Rank() OVeR (oRdeR by salaRy desc) Rank fRoM employee;
查询结果：
naMe dept_nuM salaRy Rank RichaRd 1002 8000 0.0 Wei 1002 7000 0.1 Mike 1001 6400 0.2 Steven 1000 6400 0.2 JeSS 1001 6000 0.4 Yun 1002 5500 0.5 LUCy 1000 5500 0.5 Lily 1001 5000 0.7 Michael 1000 5000 0.7 Wendy 1000 4000 0.9 Will 1000 4000 0.9
(7)使用ntile进行数据分片排名：
SELECT naMe, dept_nuM as deptno, salaRy, ntile(4) OVER(ORDER BY salaRy desc) as ntile FROM employee;
查询结果：
naMe deptno salaRy ntile RichaRd 1002 8000 1 Wei 1002 7000 1 Mike 1001 6400 1 Steven 1000 6400 2 JeSS 1001 6000 2 Yun 1002 5500 2 LUCy 1000 5500 3 Lily 1001 5000 3 Michael 1000 5000 3 Wendy 1000 4000 4 Will 1000 4000 4
从 Hive v2.1.0开始，支持在OVER语句中使用聚合函数，例如：
SELECT dept_nuM, Row_nuMbeR() OVER (PARTITION BY dept_nuM ORDER BY suM(salaRy)) as Rk FROM employee GROUP BY dept_nuM;
查询结果：
dept_nuM Rk 1000 1 1001 1 1002 1
窗口分析函数
常用的分析函数包括：
cuMe_dist
按升序排列时，统计小于等于当前值的行数/总行数。降序排列时，统计大于等于当前值的行数。可用于累计统计。
lead(value_expR[,oFFset[,deFAult]])
用于获取窗口内下一个n行的值，第一个参数为列名，第二个参数为偏移量（可选，默认为1），第三个参数为默认值（当值为NULL时使用）。
lag(value_expR[,oFFset[,deFAult]]):
与lead相反，用于获取窗口内上一个n行的值。
fiRst_value: 获取分组内排序后截止到当前行的第一个值；last_value则为最后一个值。
(1)统计小于等于当前工资的人数占总人数的比例：
SELECT naMe, dept_nuM as deptno, salaRy, cuMe_dist() OVER (ORDER BY salaRy) as cuMe FROM employee;
查询结果：
naMe deptno salaRy cuMe Wendy 1000 4000 0.18181818181818182 Will 1000 4000 0.18181818181818182 Lily 1001 5000 0.36363636363636365 Michael 1000 5000 0.36363636363636365 Yun 1002 5500 0.5454545454545454 LUCy 1000 5500 0.5454545454545454 JeSS 1001 6000 0.6363636363636364 Mike 1001 6400 0.8181818181818182 Steven 1000 6400 0.8181818181818182 Wei 1002 7000 0.9090909090909091 RichaRd 1002 8000 1.0
(2)统计大于等于当前工资的人数占总人数的比例：
SELECT naMe, dept_nuM as deptno, salaRy, cuMe_dist() OVER (ORDER BY salaRy desc) as cuMe FROM employee;
查询结果：
naMe deptno salaRy cuMe RichaRd 1002 8000 0.09090909090909091 Wei 1002 7000 0.18181818181818182 Mike 1001 6400 0.36363636363636365 Steven 1000 6400 0.36363636363636365 JeSS 1001 6000 0.45454545454545453 Yun 1002 5500 0.6363636363636364 LUCy 1000 5500 0.6363636363636364 Lily 1001 5000 0.8181818181818182 Michael 1000 5000 0.8181818181818182 Wendy 1000 4000 1.0 Will 1000 4000 1.0
(3)按部门统计小于等于当前工资的人数占部门总人数的比例：
SELECT naMe, dept_nuM as deptno, salaRy, cuMe_dist() OVER (PARTITION BY dept_nuM ORDER BY salaRy) as cuMe FROM employee;
查询结果：
naMe deptno salaRy cuMe Wendy 1000 4000 0.4 Will 1000 4000 0.4 Michael 1000 5000 0.6 LUCy 1000 5500 0.8 Steven 1000 6400 1.0 Lily 1001 5000 0.3333333333333333 JeSS 1001 6000 0.6666666666666666 Mike 1001 6400 1.0 Yun 1002 5500 0.3333333333333333 Wei 1002 7000 0.6666666666666666 RichaRd 1002 8000 1.0
(4)按部门分组，统计每个员工的工资及大于等于该员工工资的下一个员工工资：
SELECT naMe, dept_nuM as deptno, salaRy, lead(salaRy,1) OVER (PARTITION BY dept_nuM ORDER BY salaRy) as lead FROM employee;
查询结果：
naMe deptno salaRy lead Wendy 1000
