Hive 二：窗口函数相关总结

最新推荐文章于 2023-08-02 14:28:39 发布
最新推荐文章于 2023-08-02 14:28:39 发布
阅读量187 收藏
点赞数
分类专栏： 大数据之组件
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cai_and_luo/article/details/106178285
版权

简介

本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析（在线分析处理）。

概念

我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.

在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。

数据准备

我们准备一张order表,字段分别为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

在hive中建立一张表t_window,将数据插入进去.

实例

聚合函数+over

假如说我们想要查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现

select name,count(*) over ()
from t_window
where substring(orderdate,1,7) = '2015-04'

得到的结果如下:

name    count_window_0
mart    5
mart    5
mart    5
mart    5
jack    5

可见其实在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式

第一种：distinct

select distinct name,count(*) over ()
from t_window
where substring(orderdate,1,7) = '2015-04'

第二种:group by

select name,count(*) over ()
from t_window
where substring(orderdate,1,7) = '2015-04'
group by name

执行后的结果如下:

name count_window_0 
mart 2 
jack 2

partition by子句

Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句，非常类似于Group By，都是将数据按照边界值分组，而Over之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算.

实例

我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql

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

执行结果如下:

name    orderdate   cost    sum_window_0
jack    2015-01-01  10  205
jack    2015-01-08  55  205
tony    2015-01-07  50  205
jack    2015-01-05  46  205
tony    2015-01-04  29  205
tony    2015-01-02  15  205
jack    2015-02-03  23  23
mart    2015-04-13  94  341
jack    2015-04-06  42  341
mart    2015-04-11  75  341
mart    2015-04-09  68  341
mart    2015-04-08  62  341
neil    2015-05-10  12  12
neil    2015-06-12  80  80

可以看出数据已经按照月进行汇总了.

order by子句

上述的场景,假如我们想要将cost按照月进行累加.这时我们引入order by子句.

order by子句会让输入的数据强制排序（文章前面提到过，窗口函数是SQL语句最后执行的函数，因此可以把SQL结果集想象成输入数据）。Order By子句对于诸如Row_Number()，Lead()，LAG()等函数是必须的，因为如果数据无序，这些函数的结果就没有任何意义。因此如果有了Order By子句，则Count()，Min()等计算出来的结果就没有任何意义。

我们在上面的代码中加入order by

select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate )
from t_window

得到的结果如下：(order by默认情况下聚合从起始行到当前行的数据)

name    orderdate   cost    sum_window_0
jack    2015-01-01  10  10
tony    2015-01-02  15  25 //10+15
tony    2015-01-04  29  54 //10+15+29
jack    2015-01-05  46  100 //10+15+29+46
tony    2015-01-07  50  150
jack    2015-01-08  55  205
jack    2015-02-03  23  23
jack    2015-04-06  42  42
mart    2015-04-08  62  104
mart    2015-04-09  68  172
mart    2015-04-11  75  247
mart    2015-04-13  94  341
neil    2015-05-10  12  12
neil    2015-06-12  80  80

window子句

我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分，我们就要引入window子句了.

我们首先要理解两个概念:

  • 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.
  • 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.

当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.

window子句：

  • PRECEDING：往前
  • FOLLOWING：往后
  • CURRENT ROW：当前行
  • UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

我们按照name进行分区,按照购物时间进行排序,做cost的累加.
如下我们结合使用window子句进行查询

select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,--和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from t_window;

得到查询结果如下：

name    orderdate   cost    sample1 sample2 sample3 sample4 sample5 sample6 sample7
jack    2015-01-01  10  661 176 10  10  10  56  176
jack    2015-01-05  46  661 176 56  56  56  111 166
jack    2015-01-08  55  661 176 111 111 101 124 120
jack    2015-02-03  23  661 176 134 134 78  120 65
jack    2015-04-06  42  661 176 176 176 65  65  42
mart    2015-04-08  62  661 299 62  62  62  130 299
mart    2015-04-09  68  661 299 130 130 130 205 237
mart    2015-04-11  75  661 299 205 205 143 237 169
mart    2015-04-13  94  661 299 299 299 169 169 94
neil    2015-05-10  12  661 92  12  12  12  92  92
neil    2015-06-12  80  661 92  92  92  92  92  80
tony    2015-01-02  15  661 94  15  15  15  44  94
tony    2015-01-04  29  661 94  44  44  44  94  79
tony    2015-01-07  50  661 94  94  94  79  79  50

窗口函数中的序列函数

主要序列函数是不支持window子句的.

hive中常用的序列函数有下面几个:

NTILE

  • NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值

  • NTILE不支持ROWS BETWEEN， 比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

  • 如果切片不均匀，默认增加第一个切片的分布

这个函数用什么应用场景呢?假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数.

select name,orderdate,cost,
       ntile(3) over() as sample1 , --全局数据切片
       ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
       ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
       ntile(3) over(partition by name order by cost ) --按照name分组，在分组内按照cost升序排列,数据切成3份
from t_window

得到的数据如下：

name    orderdate   cost    sample1 sample2 sample3 sample4
jack    2015-01-01  10  3   1   1   1
jack    2015-02-03  23  3   1   1   1
jack    2015-04-06  42  2   2   2   2
jack    2015-01-05  46  2   2   2   2
jack    2015-01-08  55  2   3   2   3
mart    2015-04-08  62  2   1   2   1
mart    2015-04-09  68  1   2   3   1
mart    2015-04-11  75  1   3   3   2
mart    2015-04-13  94  1   1   3   3
neil    2015-05-10  12  1   2   1   1
neil    2015-06-12  80  1   1   3   2
tony    2015-01-02  15  3   2   1   1
tony    2015-01-04  29  3   3   1   2
tony    2015-01-07  50  2   1   2   3

如上述数据，我们去sample4 = 1的那部分数据就是我们要的结果

row_number

rank

dense_rank

这三个窗口函数的使用场景非常多

  • row_number()从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
  • RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
  • DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

注意：
rank和dense_rank的区别在于排名相等时会不会留下空位.

举例如下:

SELECT 
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM lxw1234 
WHERE cookieid = 'cookie1';
 
cookieid day           pv       rn1     rn2     rn3 
 
cookie1 2015-04-12      7       1       1       1
cookie1 2015-04-11      5       2       2       2
cookie1 2015-04-15      4       3       3       3
cookie1 2015-04-16      4       3       3       4
cookie1 2015-04-13      3       5       4       5
cookie1 2015-04-14      2       6       5       6
cookie1 2015-04-10      1       7       6       7

rn1: 15号和16号并列第3, 13号排第5
rn2: 15号和16号并列第3, 13号排第4
rn3: 如果相等，则按记录值排序，生成唯一的次序，如果所有记录值都相等，或许会随机排吧。

LAG和LEAD函数

这两个函数为常用的窗口函数,可以返回上下数据行的数据.
以我们的订单表为例,假如我们想要查看顾客上次的购买时间可以这样去查询

select name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1,
lag(orderdate,2) over (partition by name order by orderdate) as time2
from t_window;

查询后的数据为:

name    orderdate   cost    time1   time2
jack    2015-01-01  10  1900-01-01  NULL
jack    2015-01-05  46  2015-01-01  NULL
jack    2015-01-08  55  2015-01-05  2015-01-01
jack    2015-02-03  23  2015-01-08  2015-01-05
jack    2015-04-06  42  2015-02-03  2015-01-08
mart    2015-04-08  62  1900-01-01  NULL
mart    2015-04-09  68  2015-04-08  NULL
mart    2015-04-11  75  2015-04-09  2015-04-08
mart    2015-04-13  94  2015-04-11  2015-04-09
neil    2015-05-10  12  1900-01-01  NULL
neil    2015-06-12  80  2015-05-10  NULL
tony    2015-01-02  15  1900-01-01  NULL
tony    2015-01-04  29  2015-01-02  NULL
tony    2015-01-07  50  2015-01-04  2015-01-02

time1取的为按照name进行分组,分组内升序排列,取上一行数据的值，见下图。

time2取的为按照name进行分组，分组内升序排列,取上面2行的数据的值,注意当lag函数未设置行数值时,默认为1行.设定取不到时的默认值时,取null值.

lead函数与lag函数方向相反,取向下的数据.

在这里插入图片描述

first_value和last_value

first_value取分组内排序后，截止到当前行，第一个值
last_value取分组内排序后，截止到当前行，最后一个值

select name,orderdate,cost,
first_value(orderdate) over(partition by name order by orderdate) as time1,
last_value(orderdate) over(partition by name order by orderdate) as time2
from t_window

查询结果如下:

name    orderdate   cost    time1   time2
jack    2015-01-01  10  2015-01-01  2015-01-01
jack    2015-01-05  46  2015-01-01  2015-01-05
jack    2015-01-08  55  2015-01-01  2015-01-08
jack    2015-02-03  23  2015-01-01  2015-02-03
jack    2015-04-06  42  2015-01-01  2015-04-06
mart    2015-04-08  62  2015-04-08  2015-04-08
mart    2015-04-09  68  2015-04-08  2015-04-09
mart    2015-04-11  75  2015-04-08  2015-04-11
mart    2015-04-13  94  2015-04-08  2015-04-13
neil    2015-05-10  12  2015-05-10  2015-05-10
neil    2015-06-12  80  2015-05-10  2015-06-12
tony    2015-01-02  15  2015-01-02  2015-01-02
tony    2015-01-04  29  2015-01-02  2015-01-04
tony    2015-01-07  50  2015-01-02  2015-01-07

原文参考：https://blog.csdn.net/qq_26937525/article/details/54925827

扩展：

  • row_number的用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

  • rank函数用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。

  • dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

借助实例能更直观地理解：

假设现在有一张学生表student，学生表中有姓名、分数、课程编号。

select * from student;

在这里插入图片描述

现在需要按照课程对学生的成绩进行排序：

--row_number() 顺序排序
select name,course,row_number() over(partition by course order by score desc) rank from student;

--rank() 跳跃排序，如果有两个第一级别时，接下来是第三级别
select name,course,rank() over(partition by course order by score desc) rank from student;

--dense_rank() 连续排序，如果有两个第一级别时，接下来是第二级别 
select name,course,dense_rank() over(partition by course order by score desc) rank from student;

取得每门课程的第一名：

--每门课程第一名只取一个： 
select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有： 
select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有：
select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;

附：每门课程第一名取所有的其他方法（使用group by 而不是partition by）：

select s.* from student s
　　inner join(select course,max(score) as score from student group by course) c
　　on s.course=c.course and s.score=c.score;　
--或者使用using关键字简化连接
select * from student s
　　inner join(select course,max(score) as score from student group by course) c
　　using(course,score);
​

关于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开始排序。

参考：https://www.cnblogs.com/qiuting/p/7880500.html

蔡大远
关注
专栏目录
Hive SQL ——窗口函数源码阅读
爱吃辣条的博客
08-04 1747
Hive SQL ——窗口函数源码阅读
(07)Hive——窗口函数详解
最新发布
爱吃辣条的博客
02-11 4028
Hive窗口函数详解
Hive（四）窗口函数应用大全
zp17834994071的博客
06-16 1062
什么是窗口函数？ 在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数. 作用 窗口函数是一组特殊函数 ■扫描多个输入行来计算每个输出值，为每行数据生成一行结果 ■可以通过窗口函数来实现复杂的计算和聚合 hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析（在线分析处理
Hive分析窗口函数 LAG,LEAD,FIRST_VALUE,LAST_VALUE
duncan
05-25 942
问题导读1.LAG功能是什么？2.LEAD与LAG功能有什么相似的地方那个？3.FIRST_VALUE与LAST_VALUE分别完成什么功能？接上篇Hive分析窗口函数(二、三) NTILE,ROW_NUMBER,RANK,DENSE_RANK继续学习这四个分析函数。 注意： 这几个函数不支持WINDOW子句。（什么是WINDOW子句，Hive分析窗口函数（一）SUM,AVG,MIN,M...
hive所有窗口函数详情总结
jokertiger的博客
08-02 2008
开窗函数用于为行定义一个窗口（指运算将要操作的行的集合），它对一组值进行操作，不需要使用 Group By 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。percent_rank ：窗口内当前行的RANK值-1/窗口内总行数-1(这里的rank值就是指的是rank 函数的的返回值)有3条排在第1位时，排序为：1，1，1，2······有3条排在第1位时，排序为：1，1，1，4······这个函数赋予唯一的连续位次。例如，有3条排在第1位时，排序为：1，2，3，4······
hive 窗口/分析 函数汇总
zhaolq1024的博客
02-25 641
<一>row_number(),rank,dense_rank() 一般用来分组取topN等 依次为部门 dep，员工 emp，月薪 sal a 101 7 a 103 11 a 102 9 a 109 14 b 105 12 b 104 12 b 106 6 b 107 13 b ...
hive窗口函数大全_hive窗口函数
weixin_39586649的博客
01-08 124
简介窗口函数又名开窗函数，属于分析函数的一种，用于解决复杂报表统计需求的功能强大的函数。窗口函数用来计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。归纳：就是从现有的表上，额外多加一列出来展示，这也是从视觉上来讲最直观的体验。在深入研究Over字句之前，一定要注...
【大数据Hive】Hive 窗口函数使用详解
congge_study的博客
07-02 5872
hive窗口函数使用总结
hive复习之窗口函数的整理
默主归沙的博客
04-30 712
hive复习篇：窗口函数（刷sql要一直使用！） 文章目录1、排名函数2 SUM(), MIN(),MAX(),AVG()等聚合函数3.ntile(n)+over4、lag(col,n)和lead(col,n)5、min函数和max函数 +over()6 first_value和last_value+over 窗口函数功能 sum(col) over() : 分组对col累计求和 count(col) over() : 分组对col累计 min(col) over() : 分组对col求最小值 max(co
Hive与数据分析：窗口函数与排序
Hive是一个建立在Hadoop之上的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL语言查询功能，同时支持自定义函数。Hive的优势在于可以将SQL语句转换为MapReduce任务进行运行，在处理海量结构...
hive窗口函数
01-09
hive窗口系列函数
hive函数大全
01-09
hive函数大全,hive函数大全,hive函数大全,hive函数大全hive函数大全
hive函数大全(举例和目录)
07-12
几乎所有hive函数一览无余,每个函数均有例子
Hive 窗口函数大全
suly_liping的博客
01-03 3709
hive的窗口函数使用集合和使用示例
Hive窗口函数的运用
爱码士的博客
04-15 994
1. 相关函数说明 over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：起点，unbounded preceding表示从前面的起点， unbounded following表示到后面的终点 lag(col,n)：往前第...
hive中的窗口函数以及应用
LFQ244117370的博客
07-13 299
1.sum(col2) over(partition by col1 order by col2) : 通过col1分组对col2累计求和 2.min(col2) over(partition by col1 order by col2) : 通过col1分组对col2累计求每次的最小值 3.max(col2) over(partition by col1 order by col2) : 通过col1分组对col2累计求每次的最大值 4.avg(col2) over(partition
hive窗口函数最全总结
热门推荐
数据挖掘+大数据研发+算法学习
09-02 1万+
hive窗口函数使用
六十八、Hive——hive窗口函数大全
tiantang2renjian的博客
11-12 1297
hive窗口函数大全
[hive] 窗口函数详解
鹜骜的博客
05-01 483
前言：面试官：窗口函数使用过吗？ ：了解过啊。窗口函数应用场景：（1）用于分区排序（2）动态Group By（3）Top N（4）累计计算（5）层次查询 比如 RANK() 排序相同时会重复，总数不会变 DENSE_RANK() 排序相同时会重复，总数会减少 ROW_NUMBER() 会根据顺序计算 OVER()：指定分析函数工作的数据窗口大小 面试官：小伙子理论还行啊，来用我这电脑建张表实际操作...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值