目录
简介
本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。
概念
我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.
在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。
数据准备
我们准备一张orders表,字段分别为name,orderdate,cost.数据内容如下:
jack,2015-01-01,10
tony,2015-01-02,15
jack,2015-02-03,23
tony,2015-01-04,29
jack,2015-01-05,46
jack,2015-04-06,42
tony,2015-01-07,50
jack,2015-01-08,55
mart,2015-04-08,62
mart,2015-04-09,68
neil,2015-05-10,12
mart,2015-04-11,75
neil,2015-06-12,80
mart,2015-04-13,94
聚合函数+over
常见聚合函数(count(),sum(),max(),min(),avg()……)
查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现
select name,count(*) over ()
from orders
where substring(orderdate,1,7) = '2015-04'
在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式
第一种:
select distinct name, count(*) over ()
from orders
where substring(orderdate, 1, 7) = '2015-04';
第二种:
select name, count(*) over ()
from orders
where substring(orderdate, 1, 7) = '2015-04'
group by name;
partition by子句
Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算.
顾客的购买明细及月购买总额,可以执行如下的sql
select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
from orders
order by子句
上述的场景,假如我们想要将cost按照月进行累加.这时我们引入order by子句.
order by子句会让输入的数据强制排序(文章前面提到过,窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。Order By子句对于诸如Row_Number(),Lead(),LAG()等函数是必须的,因为如果数据无序,这些函数的结果就没有任何意义。因此如果有了Order By子句,则Count(),Min()等计算出来的结果就没有任何意义。
顾客的购买明细及月购买总额以及过程
select name, orderdate, cost, sum(cost) over (partition by name
order by orderdate) sum from orders;
window子句
我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分,我们就要引入window子句了.
我们首先要理解两个概念:
- 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.
- 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.
当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.
window子句:
- preceding:往前
- following:往后
- current row:当前行
- unbounded:起点
- unbounded preceding:表示从前面的起点
- unbounded following:表示到后面的终点
按照name进行分区,按照购物时间进行排序,做cost的累加.
select name,
orderdate,
cost,
-- 全表数据的cost总和
sum(cost) over () as row1,
--个人消费总和
sum(cost) over (partition by name) as row2,
sum(cost) over (partition by name
rows between unbounded preceding and unbounded following) as row22,
--截止到当前日期个人消费总和
sum(cost) over (partition by name order by orderdate) as row3,
--同row3
sum(cost) over (partition by name order by orderdate
rows between unbounded preceding and current row) as row4,
--当前消费额与上一次消费额的总和
sum(cost) over (partition by name order by orderdate
rows between 1 preceding and current row ) as row5,
--当前消费与上一次消费以及后一次的消费总额
sum(cost) over (partition by name order by orderdate
rows between 1 preceding and 1 following ) as row6,
--当前消费额与后面所有消费的总额
sum(cost) over (partition by name order by orderdate
rows between current row and unbounded following) as row7,
--当前日期以及后面三次的消费总额
sum(cost) over (partition by name order by orderdate
rows between current row and 3 following) as row8
from orders;
窗口函数中的序列函数
主要序列函数是不支持window子句的.
hive中常用的序列函数有下面几个:
ntile
-
ntile(n),用于将分组数据按照顺序切分成n片,返回当前切片值
-
ntile不支持rows between,比如 ntile(2) OVER(partition by name order by orderdate rows between 3 preceding and current row)
- 如果切片不均匀,默认增加第一个切片的分布
select name,
orderdate,
cost,
-- 全局数据切片
ntile(5) over () as row1,
-- 按照name进行分组,在分组内将数据切成3份
ntile(3) over (partition by name) as row2,
-- 全局按照name升序排列,数据切成3份
ntile(3) over (order by name ) as row3,
-- 按照name分组,在分组内按照cost升序排列,数据切成3份
ntile(3) over (partition by name order by cost desc ) as row4
from orders;
rank、dense_rank、row_number
函数名 | 描述 |
---|---|
RANK() | 分区中当前行的排名,带有间隙 |
DENSE_RANK() | 分区中当前行的排名,无间隙 |
ROW_NUMBER() | 其分区中的当前行数 |
row_number()从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
rank() 生成数据项在分组中的排名,排名相等会在名次中留下空位
dense_rank() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位
**注意:
rank和dense_rank的区别在于排名相等时会不会留下空位.**
为了更直观的看出他们区别,在加一行数据
insert into orders
values ('jack', '2015-02-04', 42);
select name,
orderdate,
cost,
row_number() over (partition by name order by cost desc ) as row1,
rank() over (partition by name order by cost desc ) as row2,
dense_rank() over (partition by name order by cost desc ) as row3
from orders;
LAG、LEAD
函数名 | 描述 |
---|---|
LAG() | 分区内滞后当前行的参数值 |
LEAD() | 分区内当前行前导行的参数值 |
select name,
cost,
lag(orderdate, 1, '1900-01-01') over (partition by name order by orderdate) as row1,
orderdate,
lead(orderdate) over (partition by name order by orderdate) as row2
from orders;
first_value、last_value
first_value取分组内排序后,截止到当前行,第一个值
last_value取分组内排序后,截止到当前行,最后一个值
select name,
orderdate,
cost,
first_value(orderdate) over (partition by name order by orderdate) as row1,
last_value(orderdate) over (partition by name order by orderdate) as row2
from orders;
关于Parttion by:
Parttion by关键字是Oracle中分析性函数的一部分,用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而Group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。
TIPS
- 使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果。
- 可以这样: rank over(partition by course order by score desc nulls last)
总结:
在使用排名函数的时候需要注意以下三点:
1、排名函数必须有 OVER 子句。
2、排名函数必须有包含 ORDER BY 的 OVER 子句。
3、分组内从1开始排序。