Hive的窗口函数

目录

一、窗口函数语法及说明

什么是窗口函数?

常见分析函数：

over()函数

二、hive窗口函数应用案例(1)

1、准备数据(1)

2、函数操作

2.1、聚合型窗口函数的使用[count(...) over()/sum(...) over()/ avg(...) over()/...]

2.2、over()函数中的窗口范围使用

2.3、lag(col,n)、lead(col,n)、ntile(n) 、first_value、last_value分析函数的使用

三、hive窗口函数应用案例(2)

1、准备数据(2)

2、函数操作

2.1、row_number()函数、rank()函数、dense_rank()函数与over()函数合用。

2.2、over()函数中的窗口范围使用

---

一、窗口函数语法及说明

什么是窗口函数?

窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。 大家都知道聚合函数是将某列中多行的值合并为一行，比如sum、count等。 而窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。 通用的窗口函数可以用下面的语法来概括：

分析函数 over([partition by 列名] [order by 列名 [rows between 开始位置 and 结束位置]])

常见分析函数：

聚合类
avg()、sum()、count()、max()、min()

排名类
row_number() 按照值排序时产生一个自增编号，不会重复
rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位

其他类

lag(列名,往前的行数n,[行数为null时的默认值，不指定为null]) --往前第n行
lead(列名,往后的行数n,[行数为null时的默认值，不指定为null])  --往后第n行
first_value取分组内排序后，截止到当前行，第一个值。 
last_value取分组内排序后，截止到当前行，最后一个值。
ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号

over()函数

over()全局扫描，over(order by col)按照行(字段排序)扫描，over(partition by col)按照分区(组)扫描

COVER() ：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化 -- cover()

over()函数中的窗口范围说明：

current row ：当前行 
n percending ：往前n行数据 
n following ：往后n行数据  
unbounded ：起点 
unbounded perceding ：表示从前面的起点 
unbounded following ：表示到后面的终点 

二、hive窗口函数应用案例(1)

1、准备数据(1)

--数据字段说明：用户名(username),购买日期(buydate),购买价格(buycost)
[hdp@hdp02 demo]$ vi demo09.txt 
张飞,2019-01-01,10
关羽,2019-01-02,15
张飞,2019-02-03,23
关羽,2019-01-04,29
张飞,2019-01-05,46
张飞,2019-04-06,42
关羽,2019-01-07,50
张飞,2019-01-08,55
刘备,2019-04-08,62
刘备,2019-04-09,68
诸葛亮,2019-05-10,12
刘备,2019-04-11,75
诸葛亮,2019-06-12,80
刘备,2019-04-13,94

hive > create database demodb03; 
hive > use demodb03; 
hive > create table demo01 (username string,buydate string,buycost string)
 row format delimited fields terminated by ',' lines terminated by '\n';
hive > load data local inpath '/home/hdp/demo/demo09.txt' into table demo01;
hive > select * from demo01 limit 10;