hive的开窗函数

hive的开窗函数

开窗函数可用于组内数据分析排序。

开窗函数的语法

Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>]
[<window_expression>])

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. 数据准备

```shell
索隆,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 ;
  	```
  	![在这里插入图片描述](https://img-blog.csdnimg.cn/edcf5431934249a9bd71c38d870d4c55.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAQ2xpbWJlcl9YTA==,size_20,color_FFFFFF,t_70,g_se,x_16)
  
  

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 ;
  

  

• LAG LEAD

  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 ;
  

  

6. 自定义函数

Hive自定义函数UDF、UDTF