2024年最全【Hive】（九）Hive 窗口函数总结_9 ^hivekdw4vp^，2024年最新从零开始系统化学大数据开发

最新推荐文章于 2024-06-13 10:01:09 发布
最新推荐文章于 2024-06-13 10:01:09 发布
阅读量1k 收藏 16
点赞数 18
分类专栏： 程序员 文章标签： 大数据 面试 学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2401_84182146/article/details/138832545
版权

img
img

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

jack 6
mart 6


第二种方式：**distinct**

select distinct name,count(*) over() from order where substring(orderdate,1,7)=‘2015-04’;


结果如下：

jack 2
mart 2


### 五、partition by 子句


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


实例


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

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


结果如下：

tony 2015-01-07 50 205
jack 2015-01-01 10 205
jack 2015-01-05 46 205
tony 2015-01-04 29 205
tony 2015-01-02 15 205
jack 2015-01-08 55 205
jack 2015-02-03 23 23
mart 2015-04-13 94 364
mart 2015-04-11 75 364
mart 2015-04-09 68 364
mart 2015-04-08 62 364
jack 2015-04-06 42 364
jack 2015-04-03 23 364
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 order;


结果如下：

jack 2015-01-01 10 10 // 10
tony 2015-01-02 15 25 // 10+15
tony 2015-01-04 29 54 // 25+29
jack 2015-01-05 46 100
tony 2015-01-07 50 150
jack 2015-01-08 55 205
jack 2015-02-03 23 23
jack 2015-04-03 23 23
jack 2015-04-06 42 65
mart 2015-04-08 62 127
mart 2015-04-09 68 195
mart 2015-04-11 75 270
mart 2015-04-13 94 364
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 fullagg, --所有行相加
sum(cost) over(partition by name) as fullaggbyname, --按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as fabno, --按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1 --和fabno一样,由最前面的起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as mw3, --当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as mw4 --当前行及后面所有行
from order;


结果如下：

jack 2015-01-01 10 684 199 10 10 10 56 199
jack 2015-01-05 46 684 199 56 56 56 111 189
jack 2015-01-08 55 684 199 111 111 101 124 143
jack 2015-02-03 23 684 199 134 134 78 101 88
jack 2015-04-03 23 684 199 157 157 46 88 65
jack 2015-04-06 42 684 199 199 199 65 65 42
mart 2015-04-08 62 684 299 62 62 62 130 299
mart 2015-04-09 68 684 299 130 130 130 205 237
mart 2015-04-11 75 684 299 205 205 143 237 169
mart 2015-04-13 94 684 299 299 299 169 169 94
neil 2015-05-10 12 684 92 12 12 12 92 92
neil 2015-06-12 80 684 92 92 92 92 92 80
tony 2015-01-02 15 684 94 15 15 15 44 94
tony 2015-01-04 29 684 94 44 44 44 94 79
tony 2015-01-07 50 684 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 order


得到的数据如下：

jack 2015-01-01 10 3 1 1 1
jack 2015-02-03 23 3 2 1 1
jack 2015-04-03 23 3 3 1 2
jack 2015-04-06 42 2 1 2 2
jack 2015-01-05 46 2 3 2 3
jack 2015-01-08 55 2 2 2 3
mart 2015-04-08 62 2 1 3 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 1 2 2
tony 2015-01-07 50 2 3 2 3


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


#### 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()是连续排序，有两个第一名时仍然跟着第二名。


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


* `row_number()`：从1开始，按照顺序，生成分组内记录的序列，row\_number()的值不会存在重复，当排序的值相同时，按照表中记录的顺序进行排列
* `rank()` ：生成数据项在分组中的排名，排名相等会在名次中**留下空位**
* `dense_rank()` ：生成数据项在分组中的排名，排名相等会在名次中**不会留下空位**


**注意：  
 rank和dense\_rank的区别在于排名相等时会不会留下空位**

select name,orderdate,cost,
row_number() over(partition by name order by cost) as rn1,
rank() over(partition by name order by cost) as rn2,
dense_rank() over(partition by name order by cost) as rn3
from order;

jack 2015-01-01 10 1 1 1
jack 2015-02-03 23 2 2 2
jack 2015-04-03 23 3 2 2
jack 2015-04-06 42 4 4 3
jack 2015-01-05 46 5 5 4
jack 2015-01-08 55 6 6 5
mart 2015-04-08 62 1 1 1
mart 2015-04-09 68 2 2 2
mart 2015-04-11 75 3 3 3
mart 2015-04-13 94 4 4 4
neil 2015-05-10 12 1 1 1
neil 2015-06-12 80 2 2 2
tony 2015-01-02 15 1 1 1

img
img

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

(img-E1NiSZVN-1715635977636)]
[外链图片转存中…(img-3hASQHOS-1715635977636)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

2401_84182146
关注
  • 18
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
从后端开发转大数据开发怎么样？
Blue92120的博客
07-05 1010
你为什么从后端开发（Java/golang）转到大数据开发（Hadoop/Spark）？转大数据的最初原因很简单，就是好几个同事都转了，他们的收入瞬间提高了好多，于是在同事的内推我也就跟着转了，转完以后发现自己真的转对了。......
2024年最新，Hive SQL经典面试题，大数据SQL经典面试题
04-28
2024年最新，Hive SQL经典面试题，大数据SQL经典面试题. ## 1：连续登录n天的用户 ## 2：留存问题 ## 3：Top N问题 ## 4：Explode问题 ## 5：行转列
最全【Hive】（九）Hive 窗口函数总结_9 ^hivekdw4vp^(1)
2401_84186109的博客
05-04 722
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1 --和fabno一样,由最前面的起点到当前行的聚合。sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2, --当前行和前面一行做聚合。
【Hive】（九）Hive 窗口函数总结_9 ^hivekdw4vp^
2401_84170414的博客
04-27 525
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1 --和fabno一样,由最前面的起点到当前行的聚合。sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2, --当前行和前面一行做聚合。
【Hive】（九）Hive 窗口函数总结_9 ^hivekdw4vp^(2)
2401_84170391的博客
04-27 909
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1 --和fabno一样,由最前面的起点到当前行的聚合。sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2, --当前行和前面一行做聚合。
HIVE：窗口函数，用sql语句查询MySQL安装路径和版本
Jack_2085
09-11 745
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for test_table -- ---------------------------- DROP TABLE IF EXISTS `test_table`; CREATE TABLE `test_table` ( `id` bigint(6) NULL DEFAULT NULL, `prov...
每天一道大厂SQL题【Day07】教育领域SQL实战_现在需要将元oracle数据库中数据迁移至hive仓库,请写出图书(1)
2401_84181164的博客
04-18 235
（8） 考虑到数据安全的需要，需定时将“借阅记录”中数据进行备份，请使用一条SQL语句，在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.井且将“借阅记录”中现有数据全部复制到BORROW_L0G_ BAK中。（4） 查找价格介于10元和20元之间的图书种类(SORT）出版单位（OUTPUT）和单价（PRICE），结果按出版单位（OUTPUT）和单价（PRICE）升序排序。（5） 查找所有借了书的读者的姓名（NAME）及所在单位（COMPANY）。
分布式 Data Warebase - 探索数据系统物理极限
Protonbase的博客
04-26 697
本文从一个民宿应用出发阐述现有数据系统的弊端，总结出业务的三个核心需求和四个典型场景。然后探索在这四个典型场景下满足最苛刻业务需求的数据系统分别是什么。最后解释什么是分布式 Data Warebase 以及它意味着什么。当前数据系统的弊端业务的核心需求探索各场景数据系统的物理极限分布式 Data Warebase一、当前数据系统的弊端假设现在有一个民宿应用，房东可以将自己的房源上架民宿平台，潜在住客可以通过该应用查找心仪的民宿，并且在应用平台上完成预定。
怒肝3W字Java学习路线！从入门到封神全包了（建议收藏）
一行Java的博客
10-21 3483
大家好！我是一航！ 长文警告！！！预计阅读耗时30分钟，你忍一下，但相信看完你不会后悔的；如果没有充足的时间，可以考虑先收藏着；公众号【一行Java】后台回复【Java路线】，即可获取完整的思维导图。 最近和各位小伙伴儿私下聊的比较多，各个阶段的朋友都有；因为大环境的内卷，导致大家在求学、求职、提升自己的各个方面都多多少少有些迷茫焦虑； 这些其实是一个非常普遍且正常的现象，会焦虑的人，往往都是对自己要求比较严苛的好同志；如果躺平了，何来焦虑可言！ 鉴于其中关于学习方向，学习路线的问题，其实网络如此发达.
打造工业级推荐系统
hebeind100的博客
04-26 587
及商业化价值的应用主要有 7 个大方向： 语音识别 NLU 及 NLP 图像识别 (特别是人脸识别) 金融行业的信用评分和反欺诈 推荐系统 搜索系统 广告 (精准) 投放 (即计算广告) 在这 7 个大方向中，推荐，搜索，广告投放是互联网公司最普及也是最能产生现金流的三个方向。 广告投放自不用说，这是互联网最重要的变现手段，基本每个互联网公司都会利用广告来变现。只要是提供大量“标的物”给海量用户的产品就一定会用到搜索和推荐两种技术，代表了用户的两种不同诉求。 搜索是用户的主动需求，用
hive高级分析函数与优化.rar_hive_hive 函数优化_hive 分析 大数据_hive高级优化_oldwlc
09-23
大数据分析工具 hive 高级分析函数的使用与优化，笔记整理！
hive窗口函数
01-09
hive窗口系列函数
Hive 窗口函数
01-07
over()：指定分析函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：...
hive窗口函数.docx
07-08
数据蛙hive窗口函数 - 精心总结
影子，介绍一下自己
yingzi的技术博客
06-09 457
个人介绍
人工智能（AI）的基础核心技术
qqk808的博客
06-12 328
人工智能（AI）的基础核心技术
某文旅集团定岗定编项目成功案例纪实
最新发布
weixin_47375858的博客
06-13 851
但是与此同时，目前的整体人数较多，存在冗员现象，并且原先由村民转化而来的员工，文化素质相对比较低，和目前业务发展不匹配，存在明显的忙闲不均、工作效率不高等问题。项目组建议优化配套的考核激励机制，集团可以鼓励员工承担多岗位职责，并针对工作量的不同给予相应的激励，有助于激发员工的工作热情，也能够提高员工在工作中的投入和责任感。针对该集团现有人员能力素质相对低，无法适应转型发展的问题，建议加强培训，增加人员一专多能的能力和专业能力，从而承担更多职责，提高工作饱和度。
(精品推荐）培训机构xx教育大型市场活动策划方案
qq253476994的博客
06-13 439
为使本次活动圆满完成，严防各类事故的发生，把不安全因素消灭在萌芽状态，成立组织机构：现场总指挥1人，联络协调员1人,安全工作人员7人。工作人员均配统一服装.宽胶布3个、双面胶2个、矿泉水3箱/天，共9箱、小推车、2盒圆珠笔、小喇叭、剪刀、裁纸刀、绳子、观众凳子、桌签、电视播放的DVD。熟悉场地做到心中有数，各安全工作人员实行岗位负责制，分工明确，职责明了、责任到人，工作人员已到现场实地的检查场地，熟悉疏散路线；1. 市场活动执行的意义：市场策划对市场活动的成功占30%的影响，市场活动执行占70%。
hive中的concat_ws('^^^'
08-28
在Hive中，concat_ws('^^^', ...)函数用于将多个字符串按照指定的分隔符进行拼接。这个函数可以接受多个参数，包括数组和单个字符串。如果传入的参数是一个数组，那么数组中的每个元素将会被拼接在一起，以指定的分隔符分隔。如果传入的参数是单个字符串，则直接返回该字符串。 例如，如果使用concat_ws('^^^', 'a', 'b', 'c')，则会返回'a^^^b^^^c'。如果使用concat_ws('^^^', array('a', 'b', 'c'))，同样会返回'a^^^b^^^c'。 然而，需要注意的是，当数组中存在空值（null）时，concat_ws函数会将空值视为有效字符串并拼接在一起。比如，concat_ws('^^^', 'a', null, 'b')会返回'a^^^null^^^b'，而concat_ws('^^^', array('a', null, 'b'))会返回'a^^^null^^^b'。 所以，要想跳过数组中的空值，建议在使用concat_ws函数之前，先对数组进行处理，去除其中的空值。例如，可以使用Hive内置的filter函数来过滤掉数组中的空值，然后再将处理后的数组作为参数传入concat_ws函数。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* *3* [hive中concat_ws的秘密](https://blog.csdn.net/u012861792/article/details/125532456)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 100%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值