Hive之窗口函数

最新推荐文章于 2021-11-09 08:55:39 发布
最新推荐文章于 2021-11-09 08:55:39 发布
阅读量425 收藏 2
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhikanjiani/article/details/101054656
版权

一、相关函数说明：

1、cover（）：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

2、current row：当前行

  • n PRECEDING：往前n行数据
  • n FOLLOWING：往后n行数据

3、UNBOUNDED：起点，UNBOUNDED PROCEDING 表示从前面的起点，UNBOUNDED FOLLOWING：表示到后面的终点

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

5、LEAD(col，n)：往后第n行数据

6、NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回慈航所属的组的编号。注意：n必须为int类型。

二、数据准备：

  • 在/home/hadoop/data目录下创建一份business.txt文件

2.1、建表语句：

create table business （
name string,
oderdate string,
cost int
）ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

2.2、导入数据到表中：

load data local inpath '/home/hadoop/data/business,txt' into table business;

2.3、business.txt的表内容

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

三、需求：

（1）、查询在2019年4月份购买过的顾客及人数总数
（2）、查询顾客的购买明细及月购买总额
（3）、上述的场景，要将cost按照日期进行累加
（4）、查询顾客上次的购买时间
（5）、查询前20%时间的订单信息

四、开始进行需求分析

4.1、查询在2019年4月份购买过的顾客及人数总数

select name,count(*) over()
from business
where substring(orderdate,1,7) = '2019-04'
group by name;

输出结果：

  • mart 2
  • jack 2

4.2、查询顾客的购买明细及月购买总额

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

输出结果如下：

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

4.3、上述的场景中，我们要将cost（消费金额）按照日期进行累加：

select name,orderdate,cost,sum(cost)
over() as sample 1			//所有行数相加
from business;

输出结果如下：

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

4.3.2、按照name分组，组内数据相加

select name,orderdate,cost,sum(cost)
over(partition by name) as sample2
from business;

输出结果：

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

4.3.3、按照name分组，组内数据累加：

select name,orderdate,cost,
sun(cost) over(partition by name order by orderdate) as sample3
from business;

输出结果如下：

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

4.3.4、和sample3一样，由七点到当前行的聚合

select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row) as sample4

输出结果：

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

4.3.5、当前行和前面一行做聚合

select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5
from business;

输出结果：

jack    2019-01-01      10      10
jack    2019-01-05      46      56
jack    2019-01-08      55      101
jack    2019-02-03      23      78
jack    2019-04-06      42      65
mart    2019-04-08      62      62
mart    2019-04-09      68      130
mart    2019-04-11      75      143
mart    2019-04-13      94      169
neil    2019-05-10      12      12
neil    2019-06-12      80      92
tony    2019-01-02      15      15
tony    2019-01-04      29      44
tony    2019-01-07      50      79

4.3.6、当前行和前面一行及后面一行

select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING) as sample6
from business;

输出结果：

jack    2019-01-01      10      56
jack    2019-01-05      46      111
jack    2019-01-08      55      124
jack    2019-02-03      23      120
jack    2019-04-06      42      65
mart    2019-04-08      62      130
mart    2019-04-09      68      205
mart    2019-04-11      75      237
mart    2019-04-13      94      169
neil    2019-05-10      12      92
neil    2019-06-12      80      92
tony    2019-01-02      15      44
tony    2019-01-04      29      94
tony    2019-01-07      50      79

4.3.6、当前行及后面所有行

select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING) as sample7
from business;

输出结果：

jack    2019-01-01      10      176
jack    2019-01-05      46      166
jack    2019-01-08      55      120
jack    2019-02-03      23      65
jack    2019-04-06      42      42
mart    2019-04-08      62      299
mart    2019-04-09      68      237
mart    2019-04-11      75      169
mart    2019-04-13      94      94
neil    2019-05-10      12      92
neil    2019-06-12      80      80
tony    2019-01-02      15      94
tony    2019-01-04      29      79
tony    2019-01-07      50      50

五、查看顾客的上次的购买时间：

SQL代码如下：

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

输出结果：

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

六、查询前20%时间的订单信息

select * from (
	select name,orderdate,cost,ntile(5) over(order by orderdate) sorted
	from business
	) t
	where sorted = 1;

输出结果如下：

jack    2019-01-01      10      1
tony    2019-01-02      15      1
tony    2019-01-04      29      1
zhikanjiani
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
HIVE窗口函数按日期累加 (取消费满50的日期，满100的日期)
ganghaodream的博客
09-16 3550
取每个顾客消费满50、满100时的日期 表如下图： name costdate num 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 201...
hive:窗口函数
ximen_zhou的博客
05-26 599
简介 本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析（在线分析处理）。 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数. 在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且
大数据快速入门（10）：Hive窗口函数
天道酬勤
11-07 411
一、窗口函数的概念 首先，需要认识到，窗口函数并不是只有 hive 才有的，SQL 语法标准中，就有窗口函数。 并且 mysql，oracle等数据库都实现了窗口函数。 而 hive 自带的窗口函数功能，则是对原有 hive sql 语法的补充和加强。 那么什么时候，会用到窗口函数？ 举两个小栗子： 排名问题：每个部门按业绩排名 topN 问题：找出每个部门排名前 N 的员工进行奖励 面对这类需求，就需要使用窗口函数了。 窗口函数的基本语法如下： <窗口函数> over (partition
Hive 窗口函数的相关讲解和代码展示
Brave_heart4pzj的博客
12-16 4079
一、窗口函数的概念 在不同的窗口执行的函数 在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by子句之前 可以想象成sql的输出结果，就是窗口函数输入的结果。 主要的函数有： over() partition_by order_by window函数 row_number() rank() dense_rank() lag()lead() ...
hive窗口函数
01-09
### Hive窗口函数详解 #### 一、概述 在大数据处理领域，Apache Hive 是一个广泛使用的数据仓库工具，它提供了一种SQL-like的语言——HiveQL，使得用户能够更方便地进行数据汇总、特殊查询和分析。其中，窗口函数...
hive窗口函数.docx
07-08
Hive 窗口函数详解 Hive 窗口函数是 Hive 中的一种强大的分析函数，它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口...
Hive 窗口函数
01-07
over()：指定分析函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：...
大数据hive中窗口函数的一些常用函数
最新发布
10-19
窗口函数可以像聚合函数一样对一组数据进行分析并返回结果，但不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。 窗口函数的基础语法为：<窗口函数> over (partition by ...
hive——lag函数使用
luo981695830的博客
12-15 1万+
环境 hive2.x 主要内容 lag函数 应用场景 对某个字段排序，对排序后的结果计算相邻行的差值 模型 diff_age(id string,age int) ("1",10) ("2",30) ("3",100) 中间过程测试 #求相邻id年龄插值 select id, age, lag(age,1,0) over(order by age) as syh from diff_age ; -- 结果是全部是Null select id, age, l.
hive之窗口函数理解与实践
xiepeifeng的专栏
01-13 4万+
rt_data表存放了当天每半小时的店铺销售数据 ------------------------------------------------------------ 1.统计截止到当前时间段的店铺累计销售金额 select shop_id, stat_date, ordamt, sum(ordamt) over(partition by shop_id order by stat_da
SQL中LAG和OVER函数的意义
热门推荐
hongchangfirst
04-04 4万+
做BI查询时，有时会需要看上一条纪录的情况，那么需要怎么做呢？现代的数据仓库系统或者RDBMS系统一般都会提供一个一个函数来供你分析上几条或下几条的数据。比如LAG和LEAD。LAG( shorten_url IGNORE NULLS ) OVER ( PARTITION BY hit_day, session_id ORDER BY click_id )  Oracle从8.1.6开始提供分析函...
Hive窗口函数
qq_44540985的博客
11-09 196
在sql中有一类函数叫做`聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。（开创函数，我们一般用于分组中求 TopN问题） OVER()：用于指定分析函数工作时的数据窗口大小，这个数据窗口大小可能会随着行的变而变化； OVER()函数之中： n PRECEDING：往前n行数据； CURRENT ROW：当前行； n FO..
Row Prefeching（行预取）
csdn1031506997的博客
07-19 119
行预取(Row Prefetching)，当客户端从数据库获取数据的时候，可以一行一行的读取，也可以同时读取多行。[@more@]行预取(Row Prefetching)，当客户端从数据库获取数据的时候，可以一行一行的读取，也可...
HIVE 窗口及分析函数 应用场景
helloxiaozhe的博客
02-28 8277
窗口函数应用场景： （1）用于分区排序 （2）动态Group By （3）Top N （4）累计计算 （5）层次查询 hive中一般取top n时，row_number(),rank,dense_rank()这三个函数就派上用场了， 先简单说下这三函数都是排名的，不过呢还有点细微的区别。 通过代码运行结果一看就明白了。 ROW_NUMBER() OVER函数的基本用法 语法：R...
Hive语法规范
m0_48379126的博客
09-28 3258
库操作 1.增 CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] // 库的注释说明 [LOCATION hdfs_path] // 库在hdfs上的路径 [WITH DBPROPERTIES (property_name=property_value, ...)]; // 库的属性 例： create database if not exists ...
Hive之常用函数
zhijunming的博客
08-10 623
Hive常用的内置函数 2.1空字段赋值 1)空字段赋值 nvl() -- 给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值， select nvl(null,'空值'); 2)if(表达式,default,default) -- if(表达式,value1,value2) 如果表达式为true 则取value1,否则取value2 select if('' is null,'空值',
HiveSQL窗口函数详解
"HiveSQL窗口函数.pdf" 在大数据处理领域，HiveSQL是Apache Hive的查询语言，它提供了一种方便的方式来对存储在Hadoop分布式文件系统(HDFS)中的大量数据进行分析。窗口函数是HiveSQL中的一种高级功能，它们允许用户...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值