开窗函数可用于组内数据分析排序。
- 开窗函数的语法
Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [])
hive常用的开窗函数Function ：
-- 聚合开窗函数 count(); -- 窗口内总条数 sum(); -- 窗口内数据的和 min(); -- 窗口内最小值 max(); -- 窗口内最大值 avg(); -- 窗口内的平均值 -- 排序开窗函数 row_number(); -- 从1开始，按照顺序，生成分组内记录的序列 rank(); -- 生成数据项在分组中的排名，排名相等会在名次中留下空位 dense_rank(); -- 生成数据项在分组中的排名，排名相等会在名次中不会留下空位 ntile(n); -- 将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组， -- 并返回给定行所在的组的排名。 percent_rank(); -- 计算给定行的百分比排名。可以用来计算超过了百分之多少的人。(当前行的rank值-1)/(分组内的总行数-1) -- 如360小助手开机速度超过了百分之多少的人。 cume_dist(); -- 计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布： -- 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。 -- 其他窗口函数 FIRST_VALUE(); -- 返回分区中的第一个值。 LAST_VALUE(); -- 返回分区中的最后一个值。 LAG(col,n,default); -- 用于统计窗口内往上第n个值。 LEAD(col,n,default);-- 用于统计窗口内往下第n个值。
PARTITION BY ：分区字段，可已有多个
ORDER BY ：排序字段，可以有多个
window_expression ：窗口规范
-- window_expression为空时取所有数据 -- 不指定order by 则将分组内的所有数据进行计算 -- 指定范围row between； -- 如果不指定rows between,默认为从起点到当前行; -- preceding：往前 -- following：往后 -- current row：当前行 -- unbounded：起点 -- unbounded preceding 表示从前面的起点 -- unbounded following：表示到后面的终点 -- 从无边界到当前行 rows between unbounded preceding and current row -- 从前三行到当前行，共四行 rows between 3 preceding and current row -- 前三行+当前行+后一行，共五行 rows between 3 preceding and 1 following -- 从当前行到左后一行 rows between current row and unbounded following
hive开窗函数的简单使用，附带测试数据
- 1.数据准备
索隆,2011,62,85 索隆,2012,78,56 索隆,2013,78,98 索隆,2014,99,12 乌索普,2012,95,87 乌索普,2011,43,54 乌索普,2014,75,78 乌索普,2013,99,99 山治,2011,96,54 山治,2014,67,87 山治,2013,23,98 山治,2012,96,89 路飞,2014,21,76 路飞,2012,22,97 路飞,2011,34,54 路飞,2013,45,34
- 2.建表与数据导入
create table one_piece( name string, year_str string, score int ) row format delimited fields terminated by ','; load data local inpath "/export/data/hivedata/win_func.txt" into table one_piece; select * from one_piece;
- 3.聚合函数
- 3.1 sum()
select *, -- 获取每个人分数的平均值 avg(score) over(partition by name) sum1, -- 获取每个人分数的平均值,并按照年份升序 avg(score) over(partition by name order by year_str) sum2, -- 获取每个人分数到当前年的平均分，并按照年份升序(在有order by 下的默认规则) avg(score) over(partition by name order by year_str rows between unbounded preceding and current row ) sum3, -- 获取每个人分数今年和去年的平均分,并按照年份升序 avg(score) over(partition by name order by year_str rows between 1 preceding and current row ) sum4 from one_piece ;
- 4.排序函数
- 4.1 row_number();
- 从1开始，按照顺序，生成分组内记录的序列
select *, row_number() over(partition by name order by score) r2 from one_piece ;
- 4.2 rank();
- 生成数据项在分组中的排名，排名相等会在名次中留下空位
select *, rank() over(partition by name order by score) r2 from one_piece ;
- 4.3 dense_rank();
- 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
select *, dense_rank() over(partition by name order by score) r2 from one_piece ;
- 4.4 ntile(n);
- 将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组,并返回给定行所在的组的排名。
select *, ntile(2) over(partition by name order by score) r2 from one_piece ;
- 4.5 percent_rank();
- 计算给定行的百分比排名。可以用来计算超过了百分之多少的人。(当前行的rank值-1)/(分组内的总行数-1)
-- 根据时间分组，获取海贼们每年的成绩占比 select *, percent_rank() over(partition by year_str order by score) r2 from one_piece ;
- 4.6 cume_dist();
- 计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
- 计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
select *, cume_dist() over(partition by year_str order by score) r2 from one_piece ; ```  - 4.1 row_number();
- 5.其他函数
- 5.1 first_value();last_value();
select *, -- 根据成绩排序获取最高的成绩 first_value(score) over (partition by name order by score desc) f1, -- 根据成绩排序，获取前一年、当前年、后一年三年内最高成绩 first_value(score) over (partition by name order by score desc rows between 1 preceding and 1 following) f2, -- 根据成绩排序获取最低的成绩 last_value(score) over (partition by name order by score desc) l1, -- 根据成绩排序，获取前一年、当前年、后一年三年内最低成绩 last_value(score) over (partition by name order by score desc rows between 1 preceding and 1 following) l2 from one_piece ;
select *, -- 获取每一个人两年前的成绩，没有则返回0 LAG(score,2,0) over (partition by name order by year_str desc) f1, -- 获取每一个人两年后的成绩，没有则返回0 LEAD(score,2,0) over (partition by name order by year_str desc) l1 from one_piece ;
