HIve窗口函数汇总整理全部带实例

窗口函数汇总

一、开窗

1. 窗口名词解析

OVER()： 指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val)：往前第 n 行数据
LEAD(col,n, default_val)：往后第 n 行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对
于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

对于over（）的理解至关只要，开窗的窗口大小完全由over（）控制
窗口范围：over() 表示整个数据表
over(partition by ) 窗口范围是某个分区内
over(partition by order by) 按照某个字段分区，并且分区内排序
over(order by rows between UNBOUNDED PRECEDING and CURRENT ROW)
按照某个字段排序，并且窗口范围是起始位置到当前位置，如果加分区就是分区内起始到当前位置
注意：rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量
group by :按照字段进行分组，分组前和分组后的字段要保持一致，不能出现按ID分组去查name的情况。按什么字段分组，查询结果就是什么字段，聚合函数除外。

2. over（）开窗实例

Over测试：
数据准备
字段名称：name,orderdate,cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
mart,2017-05-10,12
mart,2017-04-11,75
mart,2017-06-12,80
mart,2017-04-13,94

建表语句如下

create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

导入数据

load data local inpath "/temp/business.txt" into table business;

需求如下：
（1）查询在2017年4月份购买过的顾客及总人数
不使用over（）

select name,count(*) from business
where substring(orderdate,1,7)="2017-04"  group by name;

使用over（）开窗函数

（2）查询顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) 
over(partition by month(orderdate)) from business;

（3）查询每个顾客的cost按照日期进行累加
sum（窗口范围：从起始行到当前行）

select name,orderdate,cost ,sum(cost) over(partition by name order by orderdate rows 
between UNBOUNDED PRECEDING and current row ) as sample4 from business;

（4）查询顾客上次的购买时间
lag(orderdate,1)，第三个空默认值，如果不存在就是null，也可设置成字段值lag(orderdate,1，orderdate)

 select name,orderdate, lag(orderdate,1) over(partition by name order by orderdate) 
 as agodate from business;

（5）查询前20%时间的订单信息
ntile(5)表示将整个窗口范围进行5等分，取出第一份就是百分之20

select *from ( select name,orderdate,cost,ntile(5) over(order by orderdate) 
as sorted from business) t where t.sorted=1 ;

注意点
排序当中，如果2条数据一样，会被认为是同一个窗口

3. Rank函数

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

测试需求 计算每门学科成绩排名。
数据准备

name subject score
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78

建表并导入数据：

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/data/score.txt' into table score;

以下为3中rank排序的对照：

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

对比结果可以看到rank（）排序相同时候会重复 总数不变 顺序是 1 1 3 4
dense rank（）排序相同时候会重复 顺序是，总数减少 1 1 2 3
row_number() 排序相同时候不会重复，顺序是 1 2 3 4

二、其他常用函数总结（36个）

常用日期函数
unix_timestamp:返回当前或指定时间的时间戳

1. 查询当前时间戳

select unix_timestamp();

2. 将格式化的日期转化为时间戳

select unix_timestamp("2020-10-28",'yyyy-MM-dd');

3. 将时间戳转为日期格式

from_unixtime：

select from_unixtime(1603843200);

4. 当前日期

current_date

select current_date;

5. 当前的日期加时间

current_timestamp：

select current_timestamp;

6. 抽取格式化日期的日期部分

to_date：

select to_date('2020-10-28 12:12:12');

7. 获取年

year：

select year('2020-10-28 12:12:12');

8. 获取月

month：

select month('2020-10-28 12:12:12');

9. 获取日

day：

select day('2020-10-28 12:12:12');

10. 获取时

hour：

select hour('2020-10-28 12:12:12');

11. 获取分

minute：

select minute('2020-10-28 12:12:12');

12. 获取秒

second：

select second('2020-10-28 12:12:12');

13. 获取当前时间是一年中的第几周

weekofyear：

select weekofyear('2020-10-28 12:12:12');

14. 获取当前时间是一个月中的第几天

dayofmonth：

select dayofmonth('2020-10-28 12:12:12');

15. 获取两个日期间的月份

months_between：

select months_between('2020-04-01','2020-10-28');

16. 日期加减月

add_months：

select add_months('2020-10-28',-3);

17. 两个日期相差的天数

datediff：

select datediff('2020-11-04','2020-10-28');

18. 日期加天数

date_add：

select date_add('2020-10-28',4);

19. 日期减天数

date_sub：

select date_sub('2020-10-28',-4);

20. 日期的当月的最后一天

last_day：

select last_day('2020-02-30');

21. 格式化日期

date_format():

select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');

22. 四舍五入取整

round：

select round(3.14);

select round(3.54);

23. 向上取整

ceil：

select ceil(3.14);

select ceil(3.54);

24. 向下取整

floor：

select floor(3.14);
select floor(3.54);

25. 大小写转化

upper： 转大写

select upper('low');

lower： 转小写

select lower('LOW');

26. 查询字符串长度

length：

select length("atguigu");

27. 前后去空格

trim：

select trim(" atguigu ");

28. 向左补齐，到指定长度

lpad：

select lpad('atguigu',9,'g');

29. 向右补齐，到指定长度

rpad：

select rpad('atguigu',9,'g');

30. 使用正则表达式匹配目标字符串，匹配成功后替换

regexp_replace：

SELECT regexp_replace('2020/10/25', '/', '-');

集合操作

31. 集合中元素的个数

size：

select size(friends) from test;

32. 返回map中的key

map_keys：

select map_keys(children) from test;

33. 返回map中的value

map_values:

select map_values(children) from test;

34 判断array中是否包含某个元素

array_contains:

select array_contains(friends,'bingbing') from test;

35. 将array中的元素排序

sort_array：

select sort_array(friends) from test;

36. 多维分析

grouping_set:
在查询过程中，我们就需要获得已经下钻和上卷的数据；如果只有GROUP BY子句，那我们可以写出按各个维度或层次进行GROUP BY的查询语句，然后再通过UNION子句把结果集拼凑起来。-----使用GROUPINGSETS子句来简化查询语句的编写