Hive窗口函数使用案例

最新推荐文章于 2024-08-04 14:17:30 发布
最新推荐文章于 2024-08-04 14:17:30 发布
阅读量173 收藏 1
点赞数 1
分类专栏： Hive/Kylin数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lihuazaizheli/article/details/113274899
版权

文章目录

1.数据源

vim /opt/data/user_purchase_detail.txt

Semi,2021-09-01,500,10.10.10.9
Edward,2021-09-02,3500,10.10.10.10
Semi,2021-02-03,46,10.10.10.9
Edward,2021-09-04,578,10.10.10.10
Semi,2021-09-05,345,10.10.10.9
Semi,2021-04-06,235,10.10.10.9
Edward,2021-09-07,78,10.10.10.10
Semi,2021-09-08,55,10.10.10.9
zhangsan,2021-04-08,783,10.10.10.11
zhangsan,2021-04-09,123,10.10.10.11
lisi,2021-05-10,150,10.10.10.12
zhangsan,2021-04-11,456,10.10.10.11
lisi,2021-06-12,234,10.10.10.12
zhangsan,2021-04-13,99,10.10.10.11

2.初始化数据

create table if not exists user_purchase_detail(
name string ,
date string,
amount int,
ip_address string
)
row format delimited fields terminated by ',';

load data local inpath '/opt/data/user_purchase_detail.txt' overwrite into table  user_purchase_detail;

3.指标统计

(1)用户购买明细及上次的购买时间

# SQL:

select name,date,amount,ip_address,
lag(date) over(partition by name order by date) last_date from user_purchase_detail;

# 结果：
name		date		amount	ip_address	last_date
lisi		2021-05-10	150		10.10.10.12	NULL
lisi		2021-06-12	234		10.10.10.12	2021-05-10
Semi			2021-02-03	46		10.10.10.9	NULL
Semi			2021-04-06	235		10.10.10.9	2021-02-03
Semi			2021-09-01	500		10.10.10.9	2021-04-06
Semi			2021-09-05	345		10.10.10.9	2021-09-01
Semi			2021-09-08	55		10.10.10.9	2021-09-05
Edward	2021-09-02	3500	10.10.10.10	NULL
Edward	2021-09-04	578		10.10.10.10	2021-09-02
Edward	2021-09-07	78		10.10.10.10	2021-09-04
zhangsan	2021-04-08	783		10.10.10.11	NULL
zhangsan	2021-04-09	123		10.10.10.11	2021-04-08
zhangsan	2021-04-11	456		10.10.10.11	2021-04-09
zhangsan	2021-04-13	99		10.10.10.11	2021-04-11
Time taken: 6.126 seconds, Fetched: 14 row(s)

(2)用户购买明细及下次的购买时间

#SQL:
select name,date,amount,ip_address,
lead(date) over(partition by name order by date) next_date from user_purchase_detail;

#结果：
name		date		amount	ip_address	next_date
lisi		2021-05-10	150		10.10.10.12	2021-06-12
lisi		2021-06-12	234		10.10.10.12	NULL
Semi			2021-02-03	46		10.10.10.9	2021-04-06
Semi			2021-04-06	235		10.10.10.9	2021-09-01
Semi			2021-09-01	500		10.10.10.9	2021-09-05
Semi			2021-09-05	345		10.10.10.9	2021-09-08
Semi			2021-09-08	55		10.10.10.9	NULL
Edward	2021-09-02	3500	10.10.10.10	2021-09-04
Edward	2021-09-04	578		10.10.10.10	2021-09-07
Edward	2021-09-07	78		10.10.10.10	NULL
zhangsan	2021-04-08	783		10.10.10.11	2021-04-09
zhangsan	2021-04-09	123		10.10.10.11	2021-04-11
zhangsan	2021-04-11	456		10.10.10.11	2021-04-13
zhangsan	2021-04-13	99		10.10.10.11	NULL
Time taken: 8.26 seconds, Fetched: 14 row(s)

(3)用户购买明细及本月第一次购买的时间

# SQL：
select name,date,amount,ip_address,
first_value(date) over (partition by name,substr(date,6,2) order by date) first_purchase from user_purchase_detail;

# 结果：
name		date		amount	ip_address	first_purchase
lisi		2021-05-10	150		10.10.10.12	2021-05-10
lisi		2021-06-12	234		10.10.10.12	2021-06-12
Semi			2021-02-03	46		10.10.10.9	2021-02-03
Semi			2021-04-06	235		10.10.10.9	2021-04-06
Semi			2021-09-01	500		10.10.10.9	2021-09-01
Semi			2021-09-05	345		10.10.10.9	2021-09-01
Semi			2021-09-08	55		10.10.10.9	2021-09-01
Edward	2021-09-02	3500	10.10.10.10	2021-09-02
Edward	2021-09-04	578		10.10.10.10	2021-09-02
Edward	2021-09-07	78		10.10.10.10	2021-09-02
zhangsan	2021-04-08	783		10.10.10.11	2021-04-08
zhangsan	2021-04-09	123		10.10.10.11	2021-04-08
zhangsan	2021-04-11	456		10.10.10.11	2021-04-08
zhangsan	2021-04-13	99		10.10.10.11	2021-04-08
Time taken: 72.55 seconds, Fetched: 14 row(s)

(4)用户购买明细及本月最后一次购买的时间

# SQL:
select name,date,amount,ip_address,
first_value(date) over (partition by name,substr(date,6,2) order by date desc) last_purchase from user_purchase_detail;

# 结果:

name		date		amount	ip_address	last_purchase
lisi		2021-05-10	150		10.10.10.12	2021-05-10
lisi		2021-06-12	234		10.10.10.12	2021-06-12
Semi			2021-02-03	46		10.10.10.9	2021-02-03
Semi			2021-04-06	235		10.10.10.9	2021-04-06
Semi			2021-09-08	55		10.10.10.9	2021-09-08
Semi			2021-09-05	345		10.10.10.9	2021-09-08
Semi			2021-09-01	500		10.10.10.9	2021-09-08
Edward	2021-09-07	78		10.10.10.10	2021-09-07
Edward	2021-09-04	578		10.10.10.10	2021-09-07
Edward	2021-09-02	3500	10.10.10.10	2021-09-07
zhangsan	2021-04-13	99		10.10.10.11	2021-04-13
zhangsan	2021-04-11	456		10.10.10.11	2021-04-13
zhangsan	2021-04-09	123		10.10.10.11	2021-04-13
zhangsan	2021-04-08	783		10.10.10.11	2021-04-13

# last_value是默认到当前行,给一个到最后一行的窗口尺寸即可.
select name,date,amount,ip_address,last_value(date) over (partition by name,month(date) order by date  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  last_purchase from user_purchase_detail;

name		date		amount	ip_address	last_purchase
lisi		2021-05-10	150		10.10.10.12	2021-05-10
lisi		2021-06-12	234		10.10.10.12	2021-06-12
Semi			2021-02-03	46		10.10.10.9	2021-02-03
Semi			2021-04-06	235		10.10.10.9	2021-04-06
Semi			2021-09-01	500		10.10.10.9	2021-09-08
Semi			2021-09-05	345		10.10.10.9	2021-09-08
Semi			2021-09-08	55		10.10.10.9	2021-09-08
Edward	2021-09-02	3500	10.10.10.10	2021-09-07
Edward	2021-09-04	578		10.10.10.10	2021-09-07
Edward	2021-09-07	78		10.10.10.10	2021-09-07
zhangsan	2021-04-08	783		10.10.10.11	2021-04-13
zhangsan	2021-04-09	123		10.10.10.11	2021-04-13
zhangsan	2021-04-11	456		10.10.10.11	2021-04-13
zhangsan	2021-04-13	99		10.10.10.11	2021-04-13
Time taken: 20.816 seconds, Fetched: 14 row(s)

(5)用户购买明细及每月总额

#SQL:
select name,date,amount,ip_address,sum(amount) over(partition by name,substr(date,6,2)) sum_amount from user_purchase_detail;

#结果：
name		date		amount	ip_address	sum_amount
lisi		2021-05-10	150		10.10.10.12	150
lisi		2021-06-12	234		10.10.10.12	234
Semi			2021-02-03	46		10.10.10.9	46
Semi			2021-04-06	235		10.10.10.9	235
Semi			2021-09-01	500		10.10.10.9	900
Semi			2021-09-05	345		10.10.10.9	900
Semi			2021-09-08	55		10.10.10.9	900
Edward	2021-09-07	78		10.10.10.10	4156
Edward	2021-09-04	578		10.10.10.10	4156
Edward	2021-09-02	3500	10.10.10.10	4156
zhangsan	2021-04-13	99		10.10.10.11	1461
zhangsan	2021-04-11	456		10.10.10.11	1461
zhangsan	2021-04-08	783		10.10.10.11	1461
zhangsan	2021-04-09	123		10.10.10.11	1461
Time taken: 4.091 seconds, Fetched: 14 row(s)

(6)用户购买明细及金额按日期累加

#SQL：
select name,date,amount,ip_address,sum(amount) over(partition by name order by date) sum_amount from user_purchase_detail;

#结果：
name		date		amount	ip_address	sum_amount
lisi		2021-05-10	150		10.10.10.12	150
lisi		2021-06-12	234		10.10.10.12	384
Semi			2021-02-03	46		10.10.10.9	46
Semi			2021-04-06	235		10.10.10.9	281
Semi			2021-09-01	500		10.10.10.9	781
Semi			2021-09-05	345		10.10.10.9	1126
Semi			2021-09-08	55		10.10.10.9	1181
Edward	2021-09-02	3500	10.10.10.10	3500
Edward	2021-09-04	578		10.10.10.10	4078
Edward	2021-09-07	78		10.10.10.10	4156
zhangsan	2021-04-08	783		10.10.10.11	783
zhangsan	2021-04-09	123		10.10.10.11	906
zhangsan	2021-04-11	456		10.10.10.11	1362
zhangsan	2021-04-13	99		10.10.10.11	1461
Time taken: 6.109 seconds, Fetched: 14 row(s)

(7)用户购买明细及最近三次的总额

#思路：
// 第一：分组排序 partition by name order by date
// 第二：计算总额
// 		 默认 当前行+上一行+下一行
// 		 如果 上一行为null 则为 当前行+下两行
// 	     如果 下一行为null 则为 当前行+上两行

#SQL：
select name,date,amount,ip_address,
sum(amount+if(lag_amount == 0,lead2_amount,lag_amount)+if(lead_amount == 0,lag2_amount,lead_amount)) total_count
from
(select name,date,amount,ip_address,
lag(amount,1,0) over(partition by name order by date) lag_amount,
lead(amount,1,0) over(partition by name order by date)  lead_amount,
lag(amount,2,0) over(partition by name order by date) lag2_amount,
lead(amount,2,0) over(partition by name order by date)  lead2_amount
from user_purchase_detail) 
a group by name,date,amount,ip_address;

#结果：
name		date		amount	ip_address	total_count
lisi		2021-05-10	150		10.10.10.12	384
lisi		2021-06-12	234		10.10.10.12	384
Semi			2021-02-03	46		10.10.10.9	781
Semi			2021-04-06	235		10.10.10.9	781
Semi			2021-09-01	500		10.10.10.9	1080
Semi			2021-09-05	345		10.10.10.9	900
Semi			2021-09-08	55		10.10.10.9	900
Edward	2021-09-02	3500	10.10.10.10	4156
Edward	2021-09-04	578		10.10.10.10	4156
Edward	2021-09-07	78		10.10.10.10	4156
zhangsan	2021-04-08	783		10.10.10.11	1362
zhangsan	2021-04-09	123		10.10.10.11	1362
zhangsan	2021-04-11	456		10.10.10.11	678
zhangsan	2021-04-13	99		10.10.10.11	678

(8)查询前30%时间的订单信息

#SQL：
select name,date,amount,ip_address from
(select name,date,amount,ip_address,
round(CUME_DIST () over(order by date),2) percent
from user_purchase_detail
) a where percent <=0.3;


#结果：
name		date		amount	ip_address	percent
Semi			2021-02-03	46		10.10.10.9	0.07
Semi			2021-04-06	235		10.10.10.9	0.14
zhangsan	2021-04-08	783		10.10.10.11	0.21
zhangsan	2021-04-09	123		10.10.10.11	0.29
Time taken: 4.073 seconds, Fetched: 4 row(s)
黄土高坡上的独孤前辈
关注
专栏目录
HiveSql的时间函数
someInNeed的博客
09-01 1709
获取当前时间: 1).current_timestamp ------------------ 2019-08-10 00:12:26.606 2).unix_timestamp() -------------------1565367202 3).from_unixtime(unix_timestamp())------------2019-08-10 00:14:10 4).CURRENT_DATE------------------2019-08-10 获取年、月、日、小时、分钟、秒...
【大数据Hive】Hive 窗口函数使用详解
congge_study的博客
07-02 5806
hive窗口函数使用总结
hive窗口函数最全总结
数据挖掘+大数据研发+算法学习
09-02 1万+
hive窗口函数使用
HIVE函数汇总--开窗函数篇
小猿天地
01-04 1万+
详细介绍开窗函数的思路及其用法
Hive中获取当前时间、计算时间差
象在舞的技术专栏
06-05 2万+
1、Hive中获取当前的时间：select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')2、Hive中计算时间差：select datediff('2018-06-04','2018-06-05');
HiveSQL交易综合统计GMV
asd623444055的博客
06-19 708
1
(07)Hive——窗口函数详解
爱吃辣条的博客
02-11 3958
Hive窗口函数详解
Hive窗口函数，定义、demo
05-14
下面是一些使用Hive窗口函数的实例： ```sql -- 计算每组（按照部门分组）内的员工薪水总和 SELECT department, SUM(salary) OVER (PARTITION BY department) as sum_salary FROM employees; -- 对每个部门的员工...
Hive窗口函数
m0_57468722的博客
10-25 278
在SQL中有一类函数叫做聚合函数，例如sum()、avg()、max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据，又要显示聚集后的数据，这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能 Hive 窗口函数 窗口函数最重要的关键字是partition by和order by 具体语法如下：XXX over (partition by xxx order by xxx) 特...
Hive SQL ——窗口函数源码阅读
最新发布
爱吃辣条的博客
08-04 1672
Hive SQL ——窗口函数源码阅读
[Hive举例]-- hive获取今天、昨天、明天和前一个小时、后一个小时的日期
热门推荐
欢迎来到我的博客，一起探索代码里的世界！
09-14 8万+
主题：解决hive获取今天、昨天、明天的日期的问题 注意：由于hive内置函数中，没有提供获取昨天和明天的日期函数,所以需要编写自定义函数去实现 1、获取今天时间(格式可以自定义) 语句： select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') 返回结果： 2016-09-1416:21:59 2、获取昨天、明...
Flink入门第十二课：DataStream api/Flink sql实现每隔5分钟统计最近一小时热门商品小案例
曹利荣的博客
08-25 6141
1、需求&数据 用户行为数据不断写入kafka,程序不断从kafka读取数据，每个五分钟统计最近 一小时浏览次数最多的热门商品top 5。 输入数据： UserBehavior 字段名：userId itemId categoryId behavior timestamp 解释： 用户名 商品id 商品类别id 行为 时间戳 值举例： lily 1715 1464116 pv 1511658000 ..
窗口函数详解及示例
zl_8long的博客
10-31 2780
前言：笔记参考自来源如下 作者：夜希辰 链接：https://www.jianshu.com/p/3f3cf58472ca 来源：简书 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。 一、窗口函数语法 前言：我们在学习hive窗口函数的时候，一定要先了解窗口函数的结构。而不是直接百度sum() over()、row_number() over()、或者count() over()的用法，如果这样做，永远也掌握不到窗口函数的核心。 over()才是窗口函数，而sum、row_number
Hive的Join操作
lihuazaizheli的博客
04-08 1万+
Hive的Join操作
Hive mapreduce的map与reduce个数由什么决定？
lihuazaizheli的博客
07-25 1万+
文章目录1.MapTask的数量决定2.如何来调整MapTask的数量2.1 增加map的数量 : 调小maxsize (要小于blockSize才有效,比如128M)2.2 减少map的数量 : 调大minSize （要大于blockSize才有效，比如250M）2.3生产中一般不调整，但是要知道原理。3. ReduceTask的数量决定3.1 在执行hive shell的时候可以看到下列日志3.2 官网对这三个参数的解释3.3 通过源码分析 hive是如何通过方式一 动态计算reduce的个数的4.如何
kylin（一）介绍 编译 与 单节点测试
lihuazaizheli的博客
06-29 1422
文章目录1. kylin的产生背景1.1 传统数仓会遇到的问题：1.2 kylin核心设计理念的诞生的过程：2.kylin的发展历史3.kylin技术架构3.1 数据源3.2 核心模块4.kylin核心概念4.1 维度/度量：4.2 cube/cuboid4.3 segment5.kylin目录：6.kylin部署架构7.sample_cube体验8.kylin的编译8.1 nodejs安装8.2 bower安装8.3 phantomjs安装8.4 pom.xml文件修改 cdh5.7 ==>
Hive/MySQL/Flink 中对树结构/递归计算的处理
lihuazaizheli的博客
07-25 1358
文章目录一.数据源:二.需要结果:三.hive sql四. 输出结果 一.数据源: id,city,parentId 1,北京市,0 2,山东省,0 3,昌平区,1 4,海淀区,1 5,沙河镇,3 6,马池口镇,3 7,中关村,4 8,上地,4 9,烟台市,2 10,青岛市,2 11,单平区,9 12,芝果区,9 13,即墨区,10 14,城阳,10 注意: 一个id 唯一对应一个ParentId 一个ParentId可以对应多个id 二.需要结果: 一级 二级 三级 0
HiveSQL一本通 - 案例实操
lihuazaizheli的博客
03-21 1165
文章目录0.HiveSQL一本通使用说明6.综合案例练习之基础查询6.1 环境准备创建数据表数据准备加载数据6.2 简单查询练习1.查询姓名中带“山”的学生名单2.查询姓“王”老师的个数3.检索课程编号为“04”且分数小于60的学生的分数信息，结果按分数降序排列4.查询数学成绩不及格的学生信息和其对应的数学学科成绩，按照学号升序排序6.3 分组与汇总练习6.3.1 汇总练习1.查询编号为“02”的课程的总成绩6.3.2 分组练习1.查询各科成绩最高和最低的分，以如下的形式显示：课程号、最高分、最低分2.查询
Hive SQL实战：窗口函数详解与订单分析
本文主要包含了三个具体的案例，分别展示了Hive SQL中的窗口函数(row_number, rank, dense_rank)在数据排序和计数上的应用，以及如何通过SQL查询获取特定条件下的用户信息。 第一个案例聚焦于对学生成绩的排序和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值