Hive之--窗口函数

最新推荐文章于 2024-08-05 11:49:09 发布
原创 最新推荐文章于 2024-08-05 11:49:09 发布 · 457 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文介绍了Hive中的窗口函数，包括OVER()、LAG和LEAD函数、NTILE函数以及Rank函数的使用，通过实例展示了如何进行窗口分析，以解决更复杂的查询需求。

背景：

平常我们使用 hive或者 mysql时，一般聚合函数用的比较多。但对于某些偏分析的需求，group by可能很费力，子查询很多，这个时候就需要使用窗口分析函数了~
注：hive、oracle提供开窗函数，mysql8之前版本不提供，但Oracle发布的 MySQL 8.0版本支持窗口函数（over）和公用表表达式（with）这两个重要的功能！

一、相关函数说明

  1. OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
  2. CURRENT ROW：当前行
  3. n PRECEDING：往前n行数据
  4. n FOLLOWING：往后n行数据
  5. UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
  6. LAG(col,n,default_val)：往前第n行数据
  7. LEAD(col,n, default_val)：往后第n行数据
  8. NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

二、数据准备

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
--------------------------------------------------------------------

create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/datas/business.txt" into table business;

--------------------------------------------------------------------
hive (test)> desc business;
OK
col_name	data_type	comment
name                	string              	                    
orderdate           	string              	                    
cost                	int             
---------------------------------------------------------------------
hive (test)> select * from business;
OK
business.name	business.orderdate	business.cost
jack	2017-01-01	10
tony	2017-01-02	15
jack	2017-02-03	23
tony	2017-01-04	29
jack	2017-01-05	46
jack	2017-04-06	42
tony	2017-01-07	50
jack	2017-01-08	55
mart	2017-04-08	62
mart	2017-04-09	68
neil	2017-05-10	12
mart	2017-04-11	75
neil	2017-06-12	80
mart	2017-04-13	94

三、OVER

   --OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

1、比如：查询在2017年4月份购买过的顾客及总人数

查询HQL如下：

select 
   distinct name,
   count(*) over()
from 
   business
where 
   substring(orderdate,1,7) ="2017-04";

结果如下：

name	count_window_0
mart	2
jack	2

2、例如：查询顾客的购买明细及月购买总额

执行HQL如下：

select 
   name,
   orderdate,
   cost,
   sum(cost) over(partition by substring(orderdate,1,7))  month_cost
from 
   business;

注：根据substring(orderdate,1,7)) 字段分区，相同的月份放在同一个区中。

查询结果如下：

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

3. 

注：先根据name分区，然后再根据月份substring(orderdate,1,7))分区

select 
   name,
   orderdate,
   cost,
   sum(cost) over(partition by name,substring(orderdate,1,7))  month_cost
from 
   business;

-----------------------------------------------------------------------------

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

4. 

注：先根据name进行分区，然后再根据orderdate再进行区内排序。

如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行;如果不指定ORDER BY，则将分组内所有值累加;

关键是理解: 
ROWS BETWEEN 含义,也叫做window子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点
–其他AVG，MIN，MAX，和SUM用法一样
 

select 
   name,
   orderdate,
   cost,
   sum(cost) over(partition by name order by orderdate
   rows between unbounded preceding and current row)  month_cost
from 
   business;
------------------------------------------------------------------

name	orderdate	cost	month_cost
jack	2017-01-01	10	10
jack	2017-01-05	46	56
jack	2017-01-08	55	111
jack	2017-02-03	23	134
jack	2017-04-06	42	176
mart	2017-04-08	62	62
mart	2017-04-09	68	130
mart	2017-04-11	75	205
mart	2017-04-13	94	299
neil	2017-05-10	12	12
neil	2017-06-12	80	92
tony	2017-01-02	15	15
tony	2017-01-04	29	44
tony	2017-01-07	50	94

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 business;

------------------------------------------------------------------------------------------

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

四、LAG 和 LEAD 函数

1、LAG(col,n,default_val)：往前第n行数据

      第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

2、LEAD(col,n, default_val)：往后第n行数据

      第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

select 
   name,
   orderdate,
   cost,
   lag(orderdate,1,"1970-01-01") over(partition by name order by orderdate) last_order,
   lead(orderdate,1,"2050-01-01") over(partition by name order by orderdate) next_order
from 
   business;

------------------------------------------------------------------------------

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

五、NTILE 函数

 

NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
注1：如果切片不均匀，默认增加第一个切片的分布
注2：NTILE不支持ROWS BETWEEN

注3：n必须为int类型。

select 
   name,
   orderdate,
   cost,
   ntile(2) over(partition by name order by orderdate) nt1,      --分组内数据分为2片
   ntile(3) over(partition by name order by orderdate) nt2       --分组内数据分为3片
from 
   business;
------------------------------------------------------------------------------------

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


=======================================================================================

select 
   name,
   orderdate,
   cost,
   ntile(5) over(order by orderdate) nt3                         --将所有数据分为5片
from 
   business;

----------------------------------------------------------------------------------------

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

六、Rank函数

 

1、函数说明

  • RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
  • DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
  • ROW_NUMBER() 会根据顺序计算，无脑排序

2 、数据准备：

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/score.txt' into table score;

-------------------------------------------------------------------------------

hive (test)> select * from score;
OK
score.name	score.subject	score.score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78

例如计算每门学科的排名：

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;


--------------------------------------------------------------------------------------

name	subject	score	rp	drp	rmp
孙悟空	数学	95	1	1	1
宋宋	数学	86	2	2	2
婷婷	数学	85	3	3	3
大海	数学	56	4	4	4
宋宋	英语	84	1	1	1
大海	英语	84	1	1	2
婷婷	英语	78	3	2	3
孙悟空	英语	68	4	3	4
大海	语文	94	1	1	1
孙悟空	语文	87	2	2	2
婷婷	语文	65	3	3	3
宋宋	语文	64	4	4	4

参考链接：https://blog.youkuaiyun.com/Abysscarry/article/details/81408265

Hive 窗口函数LEAD LAG FIRST_VALUE LAST_VALUE
SmartSi
02-22 9074
窗口函数（window functions）对多行进行操作，并为查询中的每一行返回一个值。 OVER()子句能将窗口函数与其他分析函数（analytical functions）和报告函数（reporting functions）区分开来。1. 常用窗口函数下表列出了一些窗口函数以及描述信息：窗口函数描述LAG()LAG()窗口函数返回分区中当前行之前行（可以指定第几行）的值。 如果没有行，则返回
6Hive Sql 大全-窗口函数
最新发布
qq_45972323的博客
01-13 1123
lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
Hive 窗口函数的相关讲解和代码展示
Brave_heart4pzj的博客
12-16 4158
一、窗口函数的概念 在不同的窗口执行的函数 在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by子句之前 可以想象成sql的输出结果，就是窗口函数输入的结果。 主要的函数有： over() partition_by order_by window函数 row_number() rank() dense_rank() lag()lead() ...
Hive——窗口函数的总结
m0_46657040的博客
10-24 349
Hive窗口函数 over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化 而变化； 放在over()里面的： unbounded preceding表示从前面的起点; n preceding：往前 n 行数据； current row：当前行； n following：往后 n 行数据； unbounded following表示到后面的终点； 用法：（…rows between xxx and yyy）xxx表示前面的要填的行 yyy表示后面的要填的行,不能反过来 放在over
Hive---窗口函数
大呱的博客
03-09 389
窗口函数
Hive窗口函数
weixin_44516261的博客
10-03 1261
Hive窗口函数 1）定义 窗口函数属于sql中比较高级的函数 mysql从8.0版本才支持窗口函数，5.6,5.7都没有窗口函数 oracle 里面一直支持窗口函数 hive也支持窗口函数 以下函数才是窗口函数 窗口函数（13个）： LEAD LEAD(col,n, default_val)：往后第n行数据 col 列名 n 往后第几行 默认为1 默认值 默认null LAG LAG(col,n,default_val)：往前第n行数据 col 列名 n 往前第几行 默认为1
02.hive内置函数--窗口分析函数--row_number_over.mp4
04-29
02.hive内置函数--窗口分析函数--row_number_over.mp4
apache-hive-3.1.2-bin.tar.zip
10-02
HQL支持SELECT、FROM、WHERE、GROUP BY、JOIN等标准SQL操作，同时也提供了适合大数据处理的特殊函数，如LATERAL VIEW、OVER()窗口函数等。 4. **数据分区与桶**：为了提高查询性能，Hive支持数据分区（Partitioning...
apache-hive-0.14.0-bin.tar.gz
04-22
5. **HiveQL**：Hive的查询语言，与SQL高度兼容，但有其特有的语法和功能，如支持动态分区、窗口函数等。 6. **Hive UDF（User Defined Functions）**：允许用户自定义函数来扩展Hive的功能，包括UDF（单行函数）、...
hive窗口函数
01-09
### Hive窗口函数详解 #### 一、概述 在大数据处理领域，Apache Hive 是一个广泛使用的数据仓库工具，它提供了一种SQL-like的语言——HiveQL，使得用户能够更方便地进行数据汇总、特殊查询和分析。其中，窗口函数...
Hive--开窗函数--窗口位置函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE
热门推荐
qq_46893497的博客
11-22 1万+
窗口函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE 总结 FIRST_VALUE 功能：取每个分区内某列的第一个值 语法：FIRST_VALUE(col) over (partition by col1 order by col2) LAST_VALUE 功能：取每个分区内某列的最后一个值 语法：LAST_VALUE(col) over (partition by col1 order by col2) 注意：默认窗口是从第一条到当前条 LAG 功能：取每个分区内某列的
复习Hive之窗口函数
qq_45450889的博客
02-22 1042
分析函数 over（）：指定数据窗口大小 current now：当前行 preceding：前n行 following： 后n行 lag(col, n): 往前第n行数据 lead（col，n）:往后第n行数据 分析函数要和窗口函数配合使用 常用排序函数 rank: 排序相同时会重复，总数不会改变 dense rank()：排序相同时会重复，总数会减少 row_number()：会根据顺序计算 select name, subject, score, rank() over(partiti.
Hive窗口函数（开窗函数）
Markble
07-30 3819
一、相关函数说明 over()：指定聚合函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：起点：unbounded preceding 表示从前面的起点开始，unbounded following表示到后面的终点结束 lag(col,n)：往前第n行数据 lead(col,n)：往后第n行数据 ntile(n)：把有
hive窗口函数总结
gongpulin的博客
03-25 1888
一：前言 根据官网的介绍，hive推出的窗口函数功能是对hive sql的功能增强，确实目前用于离线数据分析逻辑日趋复杂，很多场景都需要用到。以下就是对hive窗口函数的一个总结附上案例。 二：理解下什么是WINDOW子句（灵活控制窗口的子集） PRECEDING：往前 FOLLOWING：往后 CURRENT ROW：当前行 UNBOUNDED：起点（一般结合PRECEDING，FOLLO...
2023.11.16 hivesql高阶函数之开窗函数
白白的wj的博客
11-16 1058
-当前行至最后一行--第一行到最后一行 也就是分组内的所有行。
Hive常用窗口分析函数
hyunbar的博客
07-02 468
大数据技术AIFlink/Spark/Hadoop/数仓，数据分析、面试，源码解读等干货学习资料121篇原创内容公众号FIRST_VALUE：取分组内排序后，截止到当前行，第一个值LAST_VALUE：取分组内排序后，截止到当前行，最后一个值LEAD(col,n,DEFAULT) ：用于统计窗口内往后第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）LAG(col,n,DEFAULT) ：用于统计窗口内往前
笔记-Hive窗口函数
Noxiousli的博客
04-18 540
最全面的Hive开窗函数讲解和实战指南（必看） - 白程序员的自习室
Hive-函数-窗口函数
坚持自己的梦想
08-05 1532
Hive中常用的窗口函数（也称为开窗函数）丰富多样，这些函数能够在进行数据分析时提供强大的支持，特别适用于需要对分组数据进行复杂计算和排序的场景。
大数据之Hive：Hive 开窗函数（二）
weixin_43597208的博客
05-31 1093
目录前言1.last_value开窗函数2.lag开窗函数3.lead开窗函数4.cume_dist开窗函数 前言 书接上回，上回重点讲了聚合函数之count开窗函数，first_value开窗函数； 言归正传，这次我们重点讲解lag开窗函数和cume_dist开窗函数； 1.last_value开窗函数 语义：返回分区中最后一个值（某一列属性的最后一个值） 同first-value开窗函数； 2.lag开窗函数 语义：lag(col,n,default) 用于统计窗口内往上第n个值。 col:列名 n:
Hive窗口函数详解与示例
"Hive窗口函数是Hive SQL中一种强大的分析工具，允许用户在特定的数据窗口上执行聚合操作，而不像传统的聚合函数那样只对整个分区或者表进行计算。窗口函数通过`OVER()`子句定义数据窗口，并可以结合`PARTITION BY`...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值