一、hive窗口函数语法
在前言中我们已经说了avg()、sum()、max()、min()是分析函数,
而over()才是窗口函数,下面我们来看看over()窗口函数的语法结构、
及常与over()一起使用的分析函数
1 | 1、over()窗口函数的语法结构 |
1、over()窗口函数的语法结构
分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
over()函数中包括三个函数:包括分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置。我们在使用over()窗口函数时,over()函数中的这三个函数可组合使用也可以不使用。
over()函数中如果不使用这三个函数,窗口大小是针对查询产生的所有数据,如果指定了分区,窗口大小是针对每个分区的数据。
1.1、over()函数中的三个函数讲解
order by
order by是排序的意思,是该窗口中的
A、partition by
partition by可理解为group by 分组。over(partition by 列名)搭配分析函数时,分析函数按照每一组每一组的数据进行计算的。B、rows between 开始位置 and 结束位置
是指定窗口范围,比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时,分析函数按照这个范围进行计算的。
窗口范围说明:
我们常使用的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(表示从起点到当前行),常用该窗口来计算累加。
1 | PRECEDING:往前 |
2、常与over()一起使用的分析函数:
2.1、聚合类
avg()、sum()、max()、min()
2.1、排名类
row_number()按照值排序时产生一个自增编号,不会重复(如:1、2、3、4、5、6)
rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位(如:1、2、3、3、3、6)
dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位(如:1、2、3、3、3、4)
2.1、其他类
lag(列名,往前的行数,[行数为null时的默认值,不指定为null]),可以计算用户上次购买时间,或者用户下次购买时间。
lead(列名,往后的行数,[行数为null时的默认值,不指定为null])
ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号
3、练习
练习1
建表
1 | -- 创建表 |
插入数据
1 | -- 创建数据 |
1、使用 over() 函数进行数据统计, 统计每个用户及表中数据的总数
1 | select *, count(userid)over() as total from izk_tmpdb.test_window_01_scx ; |
2、求用户明细并统计每天的用户总数
1 | select *,count()over(partition by logday)as day_total from izk_tmpdb.test_window_01_scx ; |
3、计算从第一天到现在的所有 score 大于80分的用户总数
1
2
3
4
5
6
7
8
9
10 -- 筛选数据
select *,count(*)over(order by logday rows between unbounded preceding and unbounded following )as total
from izk_tmpdb.test_window_01_scx
where score > 80
and userid='11111';
-- 全部数据
select *
from izk_tmpdb.test_window_01_scx
where score > 80
and userid='11111';
4、计算每个用户到当前日期分数大于80的天数
1 | select *, |
练习2
建表
1 | -- 创建表 |
插入数据
1 | -- 创建数据 |
1、查询在2017年4月份购买过的顾客及总人数
1 | select *,count(*)over() as total from izk_tmpdb.test_window_02_scx |
2、查询顾客的购买明细及月购买总额
1 | select *,sum(cost)over(partition by name,substr(orderdate,1,7)) as total from izk_tmpdb.test_window_02_scx |
3、查询顾客的购买明细及到目前为止每个顾客购买总金额
1 | select *,sum(cost) over(partition by name order by orderdate |
4、查询顾客上次的购买时间—-lag()over()偏移量分析函数的运用
1 | select |
5、查询前20%时间的订单信息
1 | select * |
练习3
建表
1 | -- 创建表 |
插入数据
1 | -- 创建数据 |
1、每门学科学生成绩排名(是否并列排名、空位排名三种实现)
1 | select *, |
2、每门学科成绩排名top n的学生
1 | select |
1 | select logday,score, |