hive中的高阶函数-窗口函数

最新推荐文章于 2024-05-14 15:23:36 发布
最新推荐文章于 2024-05-14 15:23:36 发布
阅读量301 收藏 1
点赞数
分类专栏： hive 文章标签： hive
原文链接：https://blog.csdn.net/Samaritan_H/article/details/79107751
版权

窗口函数的使用，配合聚合函数使用，能够更加灵活的规约表的格式，大大减少工作量

说在前面

窗口函数，执行顺序是最后执行仅仅是在order by之前执行。

over函数子句的使用

准备的测试数据

jackma,2018-01-01,10
tonyma,2018-01-02,15
jackma,2018-02-03,23
tonyma,2018-01-04,29
jackma,2018-01-05,46
jackma,2018-04-06,42
tonyma,2018-01-07,50
jackma,2018-01-08,55
martma,2018-04-08,62
martma,2018-04-09,68
neilma,2018-05-10,12
martma,2018-04-11,75
neilma,2018-06-12,80
martma,2018-04-13,94

create table t_window(name string,orderdate string,cost double)
row format delimited fields terminated by ',' ;
 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

测试函数

查看每个用户购买的总次数
select name,count(*) as num from t_window group by name ;

select name,count(*)  over(partition by name ) as num from t_window ;

 
 

 
 
  • 1
  • 2
  • 3
  • 4

结果：
jackma 5
martma 4
neilma 2
tonyma 3

partition by子句

查看每个用户的消费，每个月的消费的全部用户的消费额

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

 
 
  • 1

jackma 1 10.0 205.0
jackma 1 55.0 205.0
tonyma 1 50.0 205.0
jackma 1 46.0 205.0
tonyma 1 29.0 205.0
tonyma 1 15.0 205.0
jackma 2 23.0 23.0
martma 4 94.0 341.0
jackma 4 42.0 341.0
martma 4 75.0 341.0
martma 4 68.0 341.0
martma 4 62.0 341.0
neilma 5 12.0 12.0
neilma 6 80.0 80.0

查询结果 可以看到都是按照月 每个人的消费都是按照月进行汇总的，需要注意的是总的条数和原来的t_window表是完保持一致的。

order by 子句

在上面的例子中增加，要是需要累计日期每日的累计消费额 可以增加order by子句

我们首先要理解两个概念:
- 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.
- 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.

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

 
 
  • 1

jackma 2018-01-01 10.0 10.0
tonyma 2018-01-02 15.0 25.0
tonyma 2018-01-04 29.0 54.0
jackma 2018-01-05 46.0 100.0
tonyma 2018-01-07 50.0 150.0
jackma 2018-01-08 55.0 205.0
jackma 2018-02-03 23.0 23.0
jackma 2018-04-06 42.0 42.0
martma 2018-04-08 62.0 104.0
martma 2018-04-09 68.0 172.0
martma 2018-04-11 75.0 247.0
martma 2018-04-13 94.0 341.0
neilma 2018-05-10 12.0 12.0
neilma 2018-06-12 80.0 80.0

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

我们首先要理解两个概念:
- 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.
- 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.

window子句

上面是使用了partition by 进行了分组，如果想进行更加细的划分，可以使用window子句。

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

几个名词的解释：
window子句：
- PRECEDING：往前
- FOLLOWING：往后
- CURRENT ROW：当前行
- UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

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分组，按照日期从小到大进行 cost的累加
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;

jackma 2018-01-01 10.0 661.0 176.0 10.0 10.0 10.0 56.0 176.0
jackma 2018-01-05 46.0 661.0 176.0 56.0 56.0 56.0 111.0 166.0
jackma 2018-01-08 55.0 661.0 176.0 111.0 111.0 101.0 124.0 120.0
jackma 2018-02-03 23.0 661.0 176.0 134.0 134.0 78.0 120.0 65.0
jackma 2018-04-06 42.0 661.0 176.0 176.0 176.0 65.0 65.0 42.0
martma 2018-04-08 62.0 661.0 299.0 62.0 62.0 62.0 130.0 299.0
martma 2018-04-09 68.0 661.0 299.0 130.0 130.0 130.0 205.0 237.0
martma 2018-04-11 75.0 661.0 299.0 205.0 205.0 143.0 237.0 169.0
martma 2018-04-13 94.0 661.0 299.0 299.0 299.0 169.0 169.0 94.0
neilma 2018-05-10 12.0 661.0 92.0 12.0 12.0 12.0 92.0 92.0
neilma 2018-06-12 80.0 661.0 92.0 92.0 92.0 92.0 92.0 80.0
tonyma 2018-01-02 15.0 661.0 94.0 15.0 15.0 15.0 44.0 94.0
tonyma 2018-01-04 29.0 661.0 94.0 44.0 44.0 44.0 94.0 79.0
tonyma 2018-01-07 50.0 661.0 94.0 94.0 94.0 79.0 79.0 50.0

序列函数
NTILE
  • NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
  • NTILE不支持ROWS BETWEEN，

比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

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 ;

jackma 2018-01-01 10.0 3 1 1 1
jackma 2018-02-03 23.0 3 1 1 1
jackma 2018-04-06 42.0 2 2 2 2
jackma 2018-01-05 46.0 2 2 2 2
jackma 2018-01-08 55.0 2 3 2 3
martma 2018-04-08 62.0 2 1 2 1
martma 2018-04-09 68.0 1 2 3 1
martma 2018-04-11 75.0 1 3 3 2
martma 2018-04-13 94.0 1 1 3 3
neilma 2018-05-10 12.0 1 2 1 1
neilma 2018-06-12 80.0 1 1 3 2
tonyma 2018-01-02 15.0 3 2 1 1
tonyma 2018-01-04 29.0 3 3 1 2
tonyma 2018-01-07 50.0 2 1 2 3

最一列=1的就是消费最少的人的1/3 ，因为一共是有14条记录，所以之后分布不均的，分配大了第一部分。

row_number、rank、dense_rank

这三个窗口函数的使用场景非常多
- row_number()从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
- RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
- DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

    select name,orderdate,cost,
    row_number() over(partition by name order by cost asc) ,
    rank() over(partition by name order by cost asc),
    dense_rank() over(partition by name order by cost asc)
    from t_window;

jackma 2018-01-01 10.0 1 1 1
jackma 2018-01-01 10.0 2 1 1
jackma 2018-02-03 23.0 3 3 2
jackma 2018-04-06 42.0 4 4 3
jackma 2018-01-05 46.0 5 5 4
jackma 2018-01-08 55.0 6 6 5
martma 2018-04-08 62.0 1 1 1
martma 2018-04-09 68.0 2 2 2
martma 2018-04-11 75.0 3 3 3
martma 2018-04-13 94.0 4 4 4
neilma 2018-05-10 12.0 1 1 1
neilma 2018-06-12 80.0 2 2 2
tonyma 2018-01-02 15.0 1 1 1
tonyma 2018-01-02 15.0 2 1 1
tonyma 2018-01-04 29.0 3 3 2
tonyma 2018-01-07 50.0 4 4 3

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;

jackma 2018-01-01 10.0 1900-01-01 NULL
jackma 2018-01-01 10.0 2018-01-01 NULL
jackma 2018-01-05 46.0 2018-01-01 2018-01-01
jackma 2018-01-08 55.0 2018-01-05 2018-01-01
jackma 2018-02-03 23.0 2018-01-08 2018-01-05
jackma 2018-04-06 42.0 2018-02-03 2018-01-08
martma 2018-04-08 62.0 1900-01-01 NULL
martma 2018-04-09 68.0 2018-04-08 NULL
martma 2018-04-11 75.0 2018-04-09 2018-04-08
martma 2018-04-13 94.0 2018-04-11 2018-04-09
neilma 2018-05-10 12.0 1900-01-01 NULL
neilma 2018-06-12 80.0 2018-05-10 NULL
tonyma 2018-01-02 15.0 1900-01-01 NULL
tonyma 2018-01-02 15.0 2018-01-02 NULL
tonyma 2018-01-04 29.0 2018-01-02 2018-01-02
tonyma 2018-01-07 50.0 2018-01-04 2018-01-02

lag(orderdate,1,’1900-01-01’ )，参数的定义 orderdate 选定的字段，往上查找的行数，默认值（不给就是null）

lead函数就是向下取多少行，使用方式都是一致的。

first_value和last_value

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

select name,orderdate,cost ,
first_value(cost) over(partition by name order by cost),--取分组内的第一个值
first_value(cost) over (partition by name order by cost desc) --取分组内最后垂岸的一个值（一定要排序不能按照自然顺序来去不然会报错）
from t_window;

要是取分组内的第一个值和最后一个值（都是采用first value来实现）：
jackma 2018-01-08 55.0 10.0 55.0
jackma 2018-01-05 46.0 10.0 55.0
jackma 2018-04-06 42.0 10.0 55.0
jackma 2018-02-03 23.0 10.0 55.0
jackma 2018-01-01 10.0 10.0 55.0
jackma 2018-01-01 10.0 10.0 55.0
martma 2018-04-13 94.0 62.0 94.0
martma 2018-04-11 75.0 62.0 94.0
martma 2018-04-09 68.0 62.0 94.0
martma 2018-04-08 62.0 62.0 94.0
neilma 2018-06-12 80.0 12.0 80.0
neilma 2018-05-10 12.0 12.0 80.0
tonyma 2018-01-07 50.0 15.0 50.0
tonyma 2018-01-04 29.0 15.0 50.0
tonyma 2018-01-02 15.0 15.0 50.0
tonyma 2018-01-02 15.0 15.0 50.0

在使用窗口函数中的分析函数，比如row_number，last_value函数d的时候一定要试用order by，不然最后的结果可能和你想要的结果有出入。

@nanami
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive中的高级函数
weixin_54379276的博客
05-28 908
一.空字段赋值 1）函数说明 NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。 2）样例 select comm,nvl(comm,mqrid) from emp; 如果员工的comm为Null,则用领导的id来代替 二. CASE WHEN THEN ELSE ...
窗口函数之 lead() over(partition by ) 和 lag() over(partition by )
热门推荐
weixin_43792309的博客
10-03 1万+
lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前 N 行的数据 (lag) 和后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且 LAG 和 LEAD 有更高的效率。         over() 表示 lag() 与 lead() 操作的数据都在 over() 的范围内，他里面可以使用 partition by 语句（
Hive高级函数（从入门到精通 看这一篇就够了）
最新发布
分享~
05-14 402
摘要：炸裂函数（ explode）行列转换 开窗函数 内置相关面试题
2023.11.16 hivesql高阶函数之开窗函数
白白的wj的博客
11-16 846
-当前行至最后一行--第一行到最后一行 也就是分组内的所有行。
Hive之函数篇（使用函数看这篇足够了）
&volume的博客
07-10 1572
时间戳：我们常说的时间戳是unix时间戳----指从1970年1月1日00:00:00（UTC--世界协调时间，便于统一时区---可以认为是0时区）起算的秒数，它用于表示某个时间点相对于UNIX纪元时间的时间差。完整函数有三个参数，参数1：字符串 参数2：从什么位置开始截取（从1开始，正数表示从左往右数数，找到指定位置，负数表示从右往左数数，从-1开始，找到指定位置）参数3：表示截取的字符数，不写默认截取到最后。参数2：传入的是需要查找的位置（加一个$(指代的前边传入的字符串).符号）
Hive2.0函数大全(中文版)
weixin_30740295的博客
06-26 1613
摘要 Hive内部提供了很多函数给开发者使用，包括数学函数，类型转换函数，条件函数，字符函数，聚合函数，表生成函数等等，这些函数都统称为内置函数。 目录 数学函数 集合函数 类型转换函数 日期函数 条件函数 字符函数 聚合函数 表生成函数 数学函数 Return Type ...
05.hive中如何自定义函数--json解析函数示例.mp4
04-29
05.hive中如何自定义函数--json解析函数示例.mp4
hive窗口函数.docx
07-08
Hive 窗口函数是 Hive 中的一种强大的分析函数，它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口函数的基本语法结构...
hive-jdbc-3.1.2-standalone
07-18
hive-jdbc-3.1.2-standalone适用于linux
hive窗口函数
01-09
hive窗口系列函数
Hive 窗口函数
01-07
over()：指定分析函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：...
hive函数大全(举例和目录)
07-12
几乎所有hive函数一览无余,每个函数均有例子
Hive 函数高阶函数使用
毛毛的博客
06-01 642
explode接收map、array类型的数据作为输入，然后把输入数据中的每个元素拆开变成一行数据，一个元素一行。 explode执行效果正好满足于输入一行输出多行，所有叫做UDTF函数。 窗口聚合函数 在sum(…) over( partition by… order by … )语法完整的情况下，进行累积聚合操作，默认累积聚合行为是：从第一行聚合到当前行。 Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行。 语法如下：用于给每个分组内的数据打上排序的
Hive高级进阶与优化
小江专栏
10-18 4828
HiveServer2 参考地址：http://blog.csdn.net/czw698/article/details/44394923 1、启动hiveserver2服务 $HIVE_HOME/bin/hive --service hiveserver2 2、测试连接是否以连上 不用写jdbc程序,运行 bin/beeline.sh 然后输入 !connect jdbc
python几个常用的内置高阶函数
u014761412的博客
01-15 461
一、filter函数 语法 此函数是将可迭代对象中符合function的对象进行过滤，2.7是输出列表，3是输出可迭代对象 filter(function, iterable) #function函数，iterable可迭代对象 简单例子 def is_odd(n): return n % 2 == 1 newlist = filter(is_odd, [1, 2, 3, 4, 5,...
大数据Hive数据查询语言DQL
赵广陆
04-10 842
目录1 基础查询1.1 语法树1.2 案例：美国Covid-19新冠select查询1.3 select_expr1.4 ALL 、DISTINCT1.5 WHERE1.6 分区查询、分区裁剪1.7 GROUP BY1.8 HAVING1.9 LIMIT1.10 Hive SQL查询执行顺序2 高阶查询2.1 SORT/ORDER/CLUSTER/DISTRIBUTE BY2.1.1 ORDER BY2.1.2 CLUSTER BY2.1.3 DISTRIBUTE BY +SOR
HiveSQL高级进阶10大技巧
zjjcchina的博客
02-14 652
直接上干货，HiveSQL高级进阶技巧，重要性不言而喻。掌握这10个技巧，你的SQL水平将有一个质的提升，达到一个较高的层次！ 1.删除： insert overwrite tmp select * from tmp where id != '666'; 2.更新： 直接上干货，HiveSQL高级进阶技巧，重要性不言而喻。掌握这10个技巧，你的SQL水平将有一个质的提升，达到一个较高的层次！ insert overwrite tmp select id,label, if(i
hive中的窗口函数
09-16
在Hive中，窗口函数是一种用于实现基于窗口的聚合计算的功能。它可以对查询结果集中的一部分数据进行分析，并输出聚合结果或者为每个行返回一个计算结果。窗口函数通常与OVER()关键字一起使用。 窗口函数的语法结构...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值