Hive之窗口函数

最新推荐文章于 2022-02-22 18:40:48 发布
最新推荐文章于 2022-02-22 18:40:48 发布
阅读量142 收藏
点赞数
分类专栏： Hive

有关开窗函数的一些练习与案例https://blog.csdn.net/wangpei1949/article/details/81437574

 

概念：执行SQL时都会遇到sum()、avg()、max()等等函数，这些函数后面往往需要添加条件，这个时候便引入了窗口函数over（）

给定一堆数据

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

这是order表

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

 

实例

聚合函数+over

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

有两种方法：

第一种：

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

第二种：

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子句

我们想要去看顾客的购买明细及月购买总额,可以执行如下的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子句

我们在上面的代码中加入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
tony    2015-01-04  29  54
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-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

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

ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
–比如，按照pv降序排列，生成分组内每天的pv名次
ROW_NUMBER() 的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。

select
  cookieid,
  createtime,
  pv,
  row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2;

查询结果：

cookie1	2015-04-12	7	1
cookie1	2015-04-11	5	2
cookie1	2015-04-16	4	3
cookie1	2015-04-15	4	4
cookie1	2015-04-13	3	5
cookie1	2015-04-14	2	6
cookie1	2015-04-10	1	7
cookie2	2015-04-15	9	1
cookie2	2015-04-16	7	2
cookie2	2015-04-13	6	3
cookie2	2015-04-12	5	4
cookie2	2015-04-11	3	5
cookie2	2015-04-14	3	6
cookie2	2015-04-10	2	7

 

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 cookie2 
where cookieid='cookie1';

执行结果：

row_number： 按顺序编号，不留空位
rank： 按顺序编号，相同的值编相同号，留空位
dense_rank： 按顺序编号，相同的值编相同的号，不留空位

cookie1	2015-04-12	7	1	1	1
cookie1	2015-04-11	5	2	2	2
cookie1	2015-04-16	4	3	3	3
cookie1	2015-04-15	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

LAG和LEAD函数

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

time1取的为按照name进行分组,分组内升序排列,取上一行数据的值.

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

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

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

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

查询结果

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

 

FangStar8Jeff
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【大数据Hive】Hive 窗口函数使用详解
congge_study的博客
07-02 5739
hive窗口函数使用总结
Hive - 聚合函数+ 窗口函数
G090909的博客
06-20 1705
1.partition by 定义的分组，具有累计的功能，会返回每个组中的成员 。 另外partition by字句的优点是：在同一个select语句中，一个窗口函数的计算独立于按其他列分区的其他窗口函数的计算。 当在窗口函数over字句中使用order by 字句时，就指定了两件事： 1、分区中的行如何排序 2、在计算时对哪些行进行累计 1、创建表 create database if not ...
复习Hive之窗口函数
qq_45450889的博客
02-22 932
分析函数 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之窗口函数
lxc950318
03-10 169
sum(),avg(),count(),max(),min()等聚合函数，可以直接使用over()进行分区计算 基本语句 rows between n preceding | unbounded preceding and current row | n flowing |unbounded flowing 窗口函数的限定语法为：rows between 一个时间点 and 一个时间点，时间点可以...
Hive之--窗口函数
fczheng的博客
08-05 389
背景： 平常我们使用 hive或者 mysql时，一般聚合函数用的比较多。但对于某些偏分析的需求，group by可能很费力，子查询很多，这个时候就需要使用窗口分析函数了~ 注：hive、oracle提供开窗函数，mysql8之前版本不提供，但Oracle发布的 MySQL 8.0版本支持窗口函数（over）和公用表表达式（with）这两个重要的功能！ 一、相关函数说明 OVER()：指定分...
Hive的窗口函数
Jalen big data analysis的博客
12-08 1141
窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。 大家都知道聚合函数是将某列中多行的值合并为一行，比如sum、count等。 而窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。 通用的窗口函数可以用下面的语法来概括： 分析函数 over([partition by 列名] [order by 列名 [rows between 开始位置 and 结束位置]])
hive窗口函数
01-09
### Hive窗口函数详解 #### 一、概述 在大数据处理领域，Apache Hive 是一个广泛使用的数据仓库工具，它提供了一种SQL-like的语言——HiveQL，使得用户能够更方便地进行数据汇总、特殊查询和分析。其中，窗口函数...
hive窗口函数.docx
07-08
Hive 窗口函数详解 Hive 窗口函数是 Hive 中的一种强大的分析函数，它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口...
大数据hive中窗口函数的一些常用函数
10-19
窗口函数可以像聚合函数一样对一组数据进行分析并返回结果，但不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。 窗口函数的基础语法为：<窗口函数> over (partition by ...
使用R语言中的Fuzzy C-Means算法对iris数据集进行模糊聚类分析
09-03
在这项分析中，我们使用R语言对经典的iris数据集进行了模糊聚类分析，采用了Fuzzy C-Means（FCM）算法。Fuzzy C-Means是一种改进的聚类算法，允许每个数据点以不同的隶属度（概率）属于多个簇，而不仅仅是硬性分配到单个簇。这种方法特别适用于数据点之间界限不清晰的情况，通过计算每个数据点到各个簇中心的距离来确定其隶属度，从而生成更灵活的聚类结果。 在该分析中，我们首先从iris数据集中提取了四个数值变量——花萼长度、花萼宽度、花瓣长度和花瓣宽度。接着，我们加载了R中的cluster包，并使用其中的fanny函数执行Fuzzy C-Means聚类。我们设定了聚类数为3，这是因为iris数据集中的物种数为3个，虽然我们事先知道这些类别，但我们通过算法进行盲分类，以便验证其效果。 fanny函数的一个重要特点是其模糊指数参数（memb.exp），该参数决定了隶属度的模糊性。设置为较高的数值会使聚类结果更加模糊，即数据点可能较均匀地分布在多个簇中；而较低的数值则使得聚类结果更接近传统的硬聚类。
【中科院1区】Matlab实现粒子群优化算法PSO-SAE实现故障诊断算法研究.rar
09-03
1.版本：matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 替换数据可以直接使用，注释清楚，适合新手 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
基于Web的农产品直卖平台+源代码+开题报告+演示视频.zip
09-03
计算机网络发展到现在已经好几十年了，在理论上面已经有了很丰富的基础，并且在现实生活中也到处都在使用，可以说，经过几十年的发展，互联网技术已经把地域信息的隔阂给消除了，让整个世界都可以即时通话和联系，极大的方便了人们的生活。所以说，农产品直卖平台用计算机技术来进行设计，不仅在管理方面更加的系统化，操作性强，最重要的是关于数据的保存和使用都能节约大量的时间，该系统非常的好用。 农产品直卖平台管理数据的工具是MySQL，编码的语言是Java，运用的框架是Spring Boot框架。该系统可以实现商家信用类型管理，农产品信息管理，农产品评价管理，商家管理，农产品订单管理，公告信息管理，用户管理等功能。 农产品直卖平台不仅能让操作人员使用更加地方便，并且设计的也很合理，能有效的避免误操作，让数据在录入的环节就符合设计需要，极大的规避了源头性的输入误差，顺利的让数据变得更加可控并且可靠，让出错的几率降到最低。 关键词：农产品直卖平台；农产品；评价；订单
PHP基于Linux的远程管理系统服务器端的实现(源代码+LW).rar
09-03
PHP基于Linux的远程管理系统服务器端的实现(源代码+LW).rar
基于javaweb的流浪宠物管理系统+源代码+开题报告+演示视频.zip
09-03
当下，正处于信息化的时代，许多行业顺应时代的变化，结合使用计算机技术向数字化、信息化建设迈进。传统的流浪宠物信息管理模式，采用人工登记的方式保存相关数据，这种以人力为主的管理模式已然落后。本人结合使用主流的程序开发技术，设计了一款基于Java Web的流浪宠物管理系统，可以较大地减少人力、财力的损耗，方便相关人员及时更新和保存信息。本系统主要使用B/S开发模式，在idea集成开发环境下，采用Java语言编码设计系统功能，MySQL数据库管理相关的系统数据信息，SSM框架设计和开发系统功能架构，最后通过使用Tomcat服务器，在浏览器中发布设计的系统，并且完成系统与数据库的交互工作。本文对系统的需求分析、可行性分析、技术支持、功能设计、数据库设计、功能测试等内容做了较为详细的介绍，并且在本文中也展示了系统主要的功能模块设计界面和操作界面，并对其做出了必要的解释说明，方便用户对系统进行操作和使用，以及后期的相关人员对系统进行更新和维护。通过设计基于Java Web的流浪宠物管理系统，可以极大地提高流浪宠物管理的工作效率，提升用户的使用体验。 关键词：流浪宠物管理，Java语言，B/S结
基于物联网的户外环境检测装置（STM32、APP、WIFI）
09-03
基于物联网的户外环境检测装置（STM32、APP、WIFI）
基于SpringBoot的医院药品管理系统+源代码+演示视频.zip
09-03
身处网络时代，随着网络系统体系发展的不断成熟和完善，人们的生活也随之发生了很大的变化，人们在追求较高物质生活的同时，也在想着如何使自身的精神内涵得到提升，而读书就是人们获得精神享受非常重要的途径。为了满足人们随时随地只要有网络就可以看书的要求，医院药品管理系统被开发研究了出来。 本文主要描述了该医院药品管理系统的具体开发过程，在SSM框架的基础上，采用vue技术和MYSQL数据库，使该医院药品管理系统具有很好的稳定性和安全性。本设计重点从系统概述、系统分析、系统设计、数据库设计、系统测试和总结这几个方面对该医院药品管理系统进行阐述，用户通过该医院药品管理系统可以查询自己喜欢的信息。 该医院药品管理系统不仅能够稳定的运行，快捷方便的操作，界面简洁清晰，而且功能齐全，实用性强。 关键词：医院药品管理系统，SSM框架，系统分析，数据库设计
c++课程大作业-基于c++与QT的俄罗斯方块游戏源代码+项目文档（高分项目）
09-03
c++课程大作业-基于c++与QT的俄罗斯方块游戏源代码+项目文档（高分项目），含有代码注释，新手也可看懂，个人手打98分项目，导师非常认可的高分项目，毕业设计、期末大作业和课程设计高分必看，下载下来，简单部署，就可以使用。 c++课程大作业-基于c++与QT的俄罗斯方块游戏源代码+项目文档（高分项目），含有代码注释，新手也可看懂，个人手打98分项目，导师非常认可的高分项目，毕业设计、期末大作业和课程设计高分必看，下载下来，简单部署，就可以使用。 c++课程大作业-基于c++与QT的俄罗斯方块游戏源代码+项目文档（高分项目），含有代码注释，新手也可看懂，个人手打98分项目，导师非常认可的高分项目，毕业设计、期末大作业和课程设计高分必看，下载下来，简单部署，就可以使用。 c++课程大作业-基于c++与QT的俄罗斯方块游戏源代码+项目文档（高分项目），含有代码注释，新手也可看懂，个人手打98分项目，导师非常认可的高分项目，毕业设计、期末大作业和课程设计高分必看，下载下来，简单部署，就可以使用。c++课程大作业-基于c++与QT的俄罗斯方块游戏源代码+项目文档（高分项目），含有代码注释
【中科院1区】Matlab实现鹈鹕优化算法POA-SAE实现故障诊断算法研究.rar
最新发布
09-03
1.版本：matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 替换数据可以直接使用，注释清楚，适合新手 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
Hive窗口函数详解与示例
"Hive窗口函数是Hive SQL中一种强大的分析工具，允许用户在特定的数据窗口上执行聚合操作，而不像传统的聚合函数那样只对整个分区或者表进行计算。窗口函数通过`OVER()`子句定义数据窗口，并可以结合`PARTITION BY`...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值