hive 窗口函数_hive的窗口函数

cbb61c8734df8fb6c6283432bb95519b.png

1.什么是窗口函数

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

窗口函数也称为OLAP(Online Analytical Processing)函数,是对一组值进行操作,不需要使用Group by子句对数据进行分组,还能在同一行返回原来行的列和使用聚合函数得到的聚合列

那为什么叫窗口函数呢?因为窗口函数将表以窗口为单位进行分割,并在其中进行各种分析操作,为了让大家快速形成直观印象,才起了这样一个容易理解的名称

2.sql语法

() OVER ( [PARTITION BY ] [ORDER BY ] [ASC/DESC] (ROWS | RANGE) )

如上代码所示,窗口函数的语法分为四个部分

函数子句:指明具体操作,如sum-求和,first_value-取第一个值;

partition by子句:指明分区字段,如果没有,则将所有数据作为一个分区;

order by子句:指明了每个分区排序的字段和方式,也是可选的,没有就是按照表中的顺序;

窗口子句:指明相对当前记录的计算范围,可以向上(preceding),可以向下(following),也可以使用between指明,上下边界的值,没有的话默认为当前分区。有些场景比较特殊,后文会讲到这种场景。

3.窗口函数分类

下面的思维导图基本包含了Hive所有的窗口函数,按照窗口函数的功能分为:计算、取值、排序、序列四种,前三种的使用场景比较常见,容易理解,最后一种(序列)的使用场景比较少。

46be4c27ab4f0ae7fdd2a37d997f9901.png

4.窗口函数使用场景

结合实际场景看看怎么用窗口函数来解决问题。下面针对不同的使用场景,将窗口函数的使用呈现给大家。所有例子的数据均来自下图这张表。

0e6122a41f8d2a82fc629ed9922cfbaf.png

<1>用于辅助计算

主要的用法是在原有表的基础上,增加一列聚合后的值,辅以后续的计算。

例如:统计出不同产品类型售价最高的产品。

具体代码如下:

--使用窗口函数maxselect a.product_type,a.product_namefrom( select product_name,product_type,sale_price ,max(sale_price) over  ( partition by product_type ) as max_sale_price  --增加一列为聚合后的最高售价 from product) a where a.sale_price = a.max_sale_price; --保留与最高售价相同的记录数

执行结果:

<2>累积计算

标准聚合函数作为窗口函数配合order by使用,可以实现累积计算。

例如:sum窗口函数配合order by,可以实现累积和。

具体代码如下:

SELECT product_id,product_name ,product_type,sale_price ,SUM(sale_price) OVER  ( ORDER BY product_id ) AS current_sumFROM product;

执行结果:

ad114125e182711043ff8dccf62ef718.png

相应的AVG窗口函数配合order by,可以实现累积平均,max可以实现累积最大值,min可以实现累积最小值,count则可以实现累积计数。注意,只有计算类的窗口函数可以实现累积计算

这里提出一个问题,为什么增加了order by就可以实现累积计算呢?读者可以停顿思考一下!

答案马上揭晓:标准聚合函数作为窗口函数使用的时候,在指明order by的情况下,如果没有Window子句,则Window子句默认为:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(上边界不限制,下边界到当前行)。

<3>移动计算

移动计算是在分区和排序的基础上,对计算范围进一步做出限定。

例如:按照产品ID排序,将最近3条的销售价格进行汇总平均。

具体代码如下:

SELECT product_id,product_name ,sale_price ,AVG(sale_price)  over  (  ORDER BY product_id  rows 2 preceding  ) AS moving_avgFROM product;

rows 2 preceding的意思就是“截止到之前2行”。也就是将作为汇总对象的记录限定为如下的最靠近的3行

执行结果如下:

158c5617f0750dba744778f4be48600f.png

使用关键字FOLLOWING(“之后”)替换PRECEDING,就可以指定截止到之后~行

<4>取任一字段值

取值的窗口函数有:first_value/last_value、lag/lead,其中first_value和lag在开篇的例子中已经使用到了,这里就不举例说明了。只细化说明下他们的语法。

first_value(字段名)-取出分区中的第一条记录的任意一个字段的值,可以排序也可以不排序,此处也可以进一步指明Window子句。

lag(字段名,N,默认值)-取出当前行之上的第N条记录的任意一个字段的值,这里的N和默认值都是可选的,默认N为1,默认值为null。

<5>排序

排序对应的四个窗口函数为:rank、dense_rank、row_number、ntitle

rank:计算排序时,如果存在相同位次的记录,则会跳过之后的位次。

e.g. 有三条记录排在第1位时:1位、1位、1位、4位......

dense_rank:计算排序时,即使存在相同位次的记录,也不会跳过之后的位次。

e.g. 有三条记录排在第1位时:1位、1位、1位、2位......

row_number:赋予唯一的连续位次。

e.g. 有三条记录排在第1位时:1位、2位、3位、4位...

ntitle:用于将分组数据按照顺序切分成n片,返回当前切片值

e.g. 对于一组数字(1,2,3,4,5,6),ntile(2)切片后为(1,1,1,2,2,2)

1)统计所有产品的售价排名

具体代码如下:

SELECT product_name,product_type ,sale_price, RANK () OVER  ( ORDER BY sale_price  ) AS rankingFROM product;

执行结果如下:

87cbddc771818b180c5dd3488b68a505.png

2)统计各产品类型下各产品的售价排名

具体代码如下:

SELECT product_name,product_type ,sale_price, RANK () OVER  ( PARTITION BY product_type  ORDER BY sale_price  ) AS rankingFROM product;

执行结果如下:

2626493670fba137645c4188a9f7e616.png

对比一下dense_rank、row_number、ntile

具体代码如下:

SELECT product_name,product_type,sale_price, RANK () OVER (ORDER BY sale_price) AS ranking, DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking, ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num, ntile(3) OVER (ORDER BY sale_price) as nt1, ntile(30) OVER (ORDER BY sale_price) as nt2  --切片大于总记录数FROM product;

执行结果如下:

14e772e162ae827bc228af5100c81787.png

从结果可以发现,当ntile(30)中的切片大于了总记录数时,切片的值为记录的序号

<6>序列

序列中的两个窗口函数cume_dist和percent_rank,通过实例来看看它们是怎么使用的。

1)统计小于等于当前售价的产品数,所占总产品数的比例

具体代码如下:

SELECT product_type,product_name,sale_price,CUME_DIST() OVER(ORDER BY sale_price) AS rn1,CUME_DIST() OVER( PARTITION BY product_type  ORDER BY sale_price) AS rn2 FROM product;

执行结果如下:

d81ed332e2f81344544c088b1697e0f3.png

rn1: 没有partition,所有数据均为1组,总行数为8,

第一行:小于等于100的行数为1,因此,1/8=0.125

第二行:小于等于500的行数为3,因此,3/8=0.375

rn2: 按照产品类型分组,product_type=厨房用品的行数为4,

第三行:小于等于500的行数为1,因此,1/4=0.25

2)统计每个产品的百分比排序

当前行的RANK值-1/分组内总行数-1

具体代码如下:

SELECT product_type,product_name,sale_price,percent_rank() OVER (ORDER BY sale_price) AS rn1,percent_rank() OVER ( PARTITION BY product_type  ORDER BY sale_price) AS rn2 FROM product;

执行结果如下:

6cd0cb47746e74c943940e9973c4cc5e.png

rn1: 没有partition,所有数据均为1组,总行数为8,

第一行:排序为1,因此,(1-1)/(8-1)= 0

第二行:排序为2,因此,(2-1)/(8-1)= 0.14

rn2: 按照产品类型分组,product_type=厨房用品的行数为4,

第三行:排序为1,因此,(1-1)/(4-1)= 0

第四行:排序为1,因此,(2-1)/(4-1)= 0.33

总结

以上就是hive窗口函数的几种常用场景,希望大家看完以后可以对窗口函数有更进一步的了解。

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
程序员的必经之路! 【限时优惠】 现在下单,还享四重好礼: 1、教学课件免费下载 2、课程案例代码免费下载 3、专属VIP学员群免费答疑 4、下单还送800元编程大礼包 【超实用课程内容】  根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。使用量大同时,掌握MySQL早已是运维、DBA的必备技能,甚至部分IT开发岗位也要求对数据库使用和原理有深入的了解和掌握。 学习编程,你可能会犹豫选择 C++ 还是 Java;入门数据科学,你可能会纠结于选择 Python 还是 R;但无论如何, MySQL 都是 IT 从业人员不可或缺的技能!   套餐中一共包含2门MySQL数据库必学的核心课程(共98课时)   课程1:《MySQL数据库从入门到实战应用》   课程2:《高性能MySQL实战课》   【哪些人适合学习这门课程?】  1)平时只接触了语言基础,并未学习任何数据库知识的人;  2)对MySQL掌握程度薄弱的人,课程可以让你更好发挥MySQL最佳性能; 3)想修炼更好的MySQL内功,工作中遇到高并发场景可以游刃有余; 4)被面试官打破沙锅问到底的问题问到怀疑人生的应聘者。 【课程主要讲哪些内容?】 课程一:《MySQL数据库从入门到实战应用》 主要从基础篇,SQL语言篇、MySQL进阶篇三个角度展开讲解,帮助大家更加高效的管理MySQL数据库。 课程二:《高性能MySQL实战课》主要从高可用篇、MySQL8.0新特性篇,性能优化篇,面试篇四个角度展开讲解,帮助大家发挥MySQL的最佳性能的优化方法,掌握如何处理海量业务数据和高并发请求 【你能收获到什么?】  1.基础再提高,针对MySQL核心知识点学透,用对; 2.能力再提高,日常工作中的代码换新貌,不怕问题; 3.面试再加分,巴不得面试官打破沙锅问到底,竞争力MAX。 【课程如何观看?】  1、登录CSDN学院 APP 在我的课程中进行学习; 2、移动端:CSDN 学院APP(注意不是CSDN APP哦)  本课程为录播课,课程永久有效观看时长 【资料开放】 课件、课程案例代码完全开放给你,你可以根据所学知识,自行修改、优化。  下载方式:电脑登录课程观看页面,点击右侧课件,可进行课程资料的打包下载。
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页