HIve函数—窗口函数

最新推荐文章于 2024-09-05 00:08:49 发布
最新推荐文章于 2024-09-05 00:08:49 发布
阅读量315 收藏 1
点赞数
分类专栏： Hive散记 文章标签： hive 大数据 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/To_9426464/article/details/113973329
版权

HIve函数—窗口函数

本文记录了hive中窗口函数的使用及一些简单案例

文章目录

提示：以下是本篇文章正文内容，下面案例可供参考

1. 概述

  1. 窗口函数即是对查询的结果集的行数据进行计算，也就是说开的这个窗口大小可能会随着行数据的变化而变化；
  2. 窗口函数需要特殊的关键字OVER子句来指定窗口即触发一个窗口函数；
  3. over() 往往跟在聚合函数后面，即类似开了一个窗口，截取了一部分数据集出来，给前面的聚合函数使用。

说明：窗口函数不同于我们熟悉的普通函数和聚合函数：窗口函数对于每个组返回多行，即组内每一行会对应返回一行值，也就是说对行数据进行处理；而聚合函数是对列中的一系列数据进行处理，对于每个组只返回一行

2. 相关函数说明

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

2. 简单案例

案例需求如下：
（1）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）将cost按照日期进行累加
（4）查询顾客上次的购买时间
（5）查询前20%时间的订单信息

2.1 数据准备

不同用户在不同时间消费的金额(以‘,’为分隔符)
name，orderdate，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

2.3 建表、导入数据

进入hive的客户端
在hive的home目录下的datas目录创建business.txt，准备源数据

[root@Master datas]# vi business.txt

创建hive表business，并指定分隔符 ’,‘

create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

导入数据，并且可以查看下导入的数据

hive (default)> load data local inpath "./datas/business.txt" into table business;
hive (default)> select * from business;

在这里插入图片描述

3. 完成需求

3.1 查询在2017年4月份购买过的顾客及总人数

我们可以先看看如果不用窗口函数，简单聚合，即不加 over()子句的效果
代码如下（示例）：

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

结果：

name	count
jack	1
mart	4

在这里插入图片描述

可以看出此条sql语句求的需求，其实是求2017年4月份购买过的用户及其购买的次数，而不是总人数，这是对用户分组后的每个用户购买记录次数的聚合，而不是我们本次的需求，求总人数，也就是说，我们预期的效果应该是：

当月购买的用户名字		当月购买的总人数
jack				2
mart				2

实现需求，我们加个over()子句看看
代码如下（示例）：

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

结果：

可以看出，加了个over()子句，完成需求，并且，虽然我们没有给count的结果别名，但系统也为我们自动分配了列名 count_window_0

name	count_window_0
mart	2
jack	2

在这里插入图片描述

分析：

  1. 搞清楚需求，需求是让我们求当月购买商品的用户及统计当月购买商品总人数，也就是说有多少个人买了，而不是每个用户购买的总次数，后者好实现，只需分组求和即可，统计每个用户的记录数即可

  2. 当然也会有小伙伴会说，我们需求也是可以分组求和，即对分组后形成的用户那一列求和，思路是对的，而且当时我也是这样想的，如下：
    代码如下（示例）：

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

    在这里插入图片描述
    但你会发现，这会报错，提示我们Invalid table alias or column reference 'group_name': (possible column names are: name, orderdate, cost)，意思是说是无效的表别名或列引用group_name，其实很容易理解，count()聚合函数用法，如下：

    1. COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
    2. COUNT(*) 函数返回表中的记录数
    3. COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

    可以发现聚合函数，一般情况下，统计的是数据集是表business，并且其统计的列(名)在表business中存在的，我们通过group by分组后，如果想要进行统计，那么其关联原表的是列name，而不是group_name，group_name只是在分组后数据取得别名，而表business不存在列group_name，所以这就是为什么提示无效的引用group_name，当然如果刚刚那条sql语句改为如下，是可以通过的，因为这个跟我们一开始的一样，只是加了个别名，统计的仍然是购买的次数，而不是人数
    代码如下（示例）：

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

    在这里插入图片描述

  3. 在概述中说明了何为窗口函数，窗口函数即是对查询的结果集的行数据进行计算，那么在这里可以这样通俗理解，即group by分组后产生得结果集，因为遇到了over()，那么它就会开一个窗口，这个窗口中包含聚合函数count()和查询出来得结果集，所以当count(*)，统计记录数时，是以这个结果集为基准，所以group by分组后，只剩下jack跟mart，所以统计出来，人数为2

哈哈哈，不知道大家有没有get到，也有可能被我绕晕，不明白的可以多看几遍，动手去修改下sql，把能想到的情况一一列出来，对比，例如，以下这种情况：
我们仍然是统计2017年4月份购买过的顾客及当月的总人次，所以那么这里就不用分组了，因为只要分组过滤出来就可以统计了：
代码如下（示例）：

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

结果：

name	count_window_0
mart	5
mart	5
mart	5
mart	5
jack	5

在这里插入图片描述

然后再试试下面这条sql，与上条sql语句区别，仅仅是少了一个over() 子句
代码如下（示例）：

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

在这里插入图片描述
这个报错是很正常的，因为这个正确的写法，就是我们日常写的分组聚合函数语句，就是需要group by

希望没有把你们绕晕~

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

又是一个看不懂需求的情况，每个字明明看得懂，但连在起来就是看不懂系列
先看看原始数据
name，orderdate，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

分析：

  1. 需求让我们达到的效果是什么，比如有哪些用户在1月份购买了东西，把是谁在什么时候花了多少打印出来，然后月购买总额，就是在1月份中这些用户买东西，花了多少打印出来，即预期效果应该是下面这样的

    name	orderdate	cost  sum
jack	2017-01-01	10	  54   
tony	2017-01-04	29    54	此3条为1月份
tony	2017-01-02	15    54

jack	2017-02-03	23	  23	此条为1月份

jack	2017-04-06	48    110	此2条为4月份  
mart	2017-04-08	62    110

neil	2017-05-10	12    30	此2条为5月份
neil	2017-05-17	18	  30

  2. 也就是说，需要分组原则是月份(日期)

    代码如下（示例）：

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

    结果：
    在这里插入图片描述

3.3 将cost按照日期进行累加

分析：即先将日期排序，然后将前几天花了少钱进行相加，例如：

orderdate	cost	sum
01-01		1		1
02-02		2		3
03-03		3		6

代码如下（示例）：

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

结果：
在这里插入图片描述

再修改下需求，即这个累加，是每个用户按照购买日期，cost的累加，这里是以用户为组
以下两条都可以实现，distribute by、partition by都有分区概念，但前者是和sort by连用，后者是和order by连用，不能交叉使用，并且要注意的是：有order by的情况，窗口范围是从第一条数据开始，到本条数据
代码如下（示例）：

select name,orderdate,cost,sum(cost) over(distribute by name sort by orderdate) 
from business;

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

结果：
在这里插入图片描述

3.4 查询顾客上次的购买时间(lag、lead函数)

(lag、lead函数->不写在over()内)
用到 LAG(col,n,[default])函数：往前第n行数据，col -> 需要查看的列，n -> 第n行数据，default -> 如果没有，则取给定的默认值，[default]是可选参数，如果不传，则打印NULL
代码如下（示例）：

select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time
from business;

结果：
在这里插入图片描述在这里插入图片描述

LEAD(col,n,[default])：往后第n行，用法与lag一样
代码如下（示例）：

select name,orderdate,cost, 
lead(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time
from business;

结果：
在这里插入图片描述

3.5 查询前20%时间的订单信息(ntile函数)

用到NTILE(n)函数
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
需求分析：即将时间进行排序，并把排好序的数据取前20%，也就是前1/5，所以用NTILE()函数对数据进行平均等分为5分，并编号1,2,3,4,5，然后取第1份的数据
代码如下（示例）：

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

结果：
在这里插入图片描述

总的数据为：
代码如下（示例）：

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

结果：
在这里插入图片描述

4. 其它函数案例(限定数据集)

  1. 起点 -> 当前行，当前行 -> 末尾，如果有分组，则这个起点、末尾是组内区间的起点和末尾
    CURRENT ROW：当前行 与 UNBOUNDED FOLLOWING：表示到后面的终点，统计每个用户当前行 —> 最后一行的购买金额
    代码如下（示例）：

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

    结果：
    在这里插入图片描述

    UNBOUNDED PRECEDING：从前面的起点 —> 当前行

    代码如下（示例）：

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

    结果：
    在这里插入图片描述

  2. 往前、往后几行(如果有分组，那么累加也是对仅在分组区间内的数据生效)
    统计往前一行 -> 当前行
    代码如下（示例）：

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

    结果：
    在这里插入图片描述

    统计当前行 -> 往后一行

    代码如下（示例）：

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

    结果：
    在这里插入图片描述

5. Rank函数

rank函数也是窗口函数，因为也是需要用到over()子句

  1. 函数说明
    RANK() 排序相同时会重复，总数不会变(1,1,3,4)
    DENSE_RANK() 排序相同时会重复，总数会减少(1,1,2,3)
    ROW_NUMBER() 会根据顺序计算(1,2,3,4)
  2. 数据准备
    唐僧师徒四人各科成绩
    孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
猪八戒	语文	94
猪八戒	数学	56
猪八戒	英语	84
唐僧	语文	64
唐僧	数学	86
唐僧	英语	84
沙悟净	语文	65
沙悟净	数学	85
沙悟净	英语	78
  3. 创表、导入数据
    代码如下（示例）：
    create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";

load data local inpath './datas/score.txt' into table score;
    在这里插入图片描述
  4. 函数应用
    代码如下（示例）：
    select name,
subject,
score,
rank() over(partition by subject order by score desc) rank,
dense_rank() over(partition by subject order by score desc) dense_rank,
row_number() over(partition by subject order by score desc) row_number
from score;
    结果：
    在这里插入图片描述

总结

文章也是仅作知识点的记录，欢迎大家指出错误，如有新的感悟也会一并更新，一起探讨~~~

这事儿就很秃然
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【大数据Hive】Hive 窗口函数使用详解
congge_study的博客
07-02 5741
hive窗口函数使用总结
Hive 窗口函数
hylpeace的博客
07-09 1781
HIVE窗口函数
【hive经典指标，离线数仓指标，ADS层指标分析】最近7日内连续3日下单用户数
qq_40382400的博客
08-03 938
1.ODS获取用户登录信息2.DIM3.DWD4.DWS5.ADS6.SQL。
Hive sql 常见面试题-国庆期间的7日动销率和滞销率
weixin_42435657的博客
03-06 397
动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品 / 已上架总商品数）。只要当天任一店铺有任何商品的销量就输出该天的结果从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天每天每个品类的商品的动销率和滞销率。
Hive 窗口函数详解
雾岛与鲸的博客
09-04 6613
目录1、hive窗口函数2、在日常的开发中常用那些类型函数？3、了解哪些窗口函数，都是什么意思？窗口函数功能over语法1、物理窗口（真实往上下移动多少行rows between）：2、 逻辑窗口(满足条件上下多少行):(金融行业、保险行业、p2p等)4、窗口聚合函数count开窗函数sum开窗函数avg开窗函数min开窗函数max开窗函数5、窗口分析函数first_value开窗函数last_value开窗函数lag开窗函数lead开窗函数cume_dist开窗函数6、窗口排序函数rank开窗函数dens
HIVE窗口函数
ganghaodream的博客
07-30 128
我自己建的一个临时表： select 'A' as name ,1 as num,2 as dd union all select 'A' as name ,2 as num,3 as dd union all select 'A' as name ,3 as num,7 as dd union all select 'B' as name ,4 as num,1 as dd union ...
hive窗口函数
01-09
### Hive窗口函数详解 #### 一、概述 在大数据处理领域，Apache Hive 是一个广泛使用的数据仓库工具，它提供了一种SQL-like的语言——HiveQL，使得用户能够更方便地进行数据汇总、特殊查询和分析。其中，窗口函数...
hive窗口函数.docx
07-08
Hive 窗口函数详解 Hive 窗口函数是 Hive 中的一种强大的分析函数，它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口...
快速学习-Hive函数
01-20
Hive函数的学习是理解和运用Hive的关键部分。熟练掌握内置函数的使用，并学会创建自定义函数，能够使你在数据处理中游刃有余。无论是处理大数据集还是进行复杂的数据分析，Hive的函数库都能提供强大的支持。通过不断...
数据源10min自动断开连接导致查询抛异常(未获取可用连接)
镜水灵动
08-31 300
mysql可以修改连接超时时间，当设置为10min的时候，由于druid配置不当，导致。
Hive数据仓库
weixin_51591328的博客
09-04 115
概述 创建表
hive on spark 合并小文件
xingyuelhb的博客
09-04 198
最近在做用户画像相关的数据开发，在hive中做行转列的过程中，每个标签即tag在写入hive表时，有大量的小文件生成，通过增加参数解决了小文件过多的问题，但是在完成特殊要求--（因后续需要同步ck，ck也有个为解决的问题，超过200M可能会丢失数据，故需要将hive在行转列的时候，合并的小文件在200M一下，方便后续ck数据同步）时，又产生了新的问题，通过hive合并小文件的参数，总是有些tag的数据生成的文件在200M以上；增加此参数，才会在hive on spark模式中进行小文件合并。
Hive服务部署及Datagrip工具使用
qq_45115959的博客
09-04 820
目录Hive服务部署 Hiveserver2服务1）用户说明2）Hiveserver2部署（1）Hadoop端配置（2）Hive端配置3）测试（1）启动Hiveserver2（2）使用命令行客户端beeline进行远程访问（3）使用Datagrip图形化客户端进行远程访问① 创建连接② 配置连接属性③ 界面介绍④ 测试SQL执行⑤ 修改数据库 Metastore服务1）Metastore运行模式2）Metastore部署（1）嵌入式模式（2）独立服务模式3）测试 编写Hive服务启动脚本（了解）1）前台启动
计算机毕业设计Hadoop+PySpark共享单车预测系统 PyHive 共享单车数据分析可视化大屏 共享单车爬虫 共享单车数据仓库 机器学习 深度学习
全网粉丝100W+、全栈领域优质创作者、B站、github、CSDN等社区技术专家、专注于高端精品毕业项目源码实战
09-04 299
计算机毕业设计Hadoop+PySpark共享单车预测系统 PyHive 共享单车数据分析可视化大屏 共享单车爬虫 共享单车数据仓库 机器学习 深度学习
Hive时间窗口函数保姆级教程（最全解析、应用和优化）（持续更新）
最新发布
大模型大数据攻城狮的专栏
09-05 298
时间窗口函数在SQL查询中扮演着特殊而重要的角色，它们被专门设计用于处理时间序列数据相关的查询。这类函数通过设定一个特定的时间窗口，允许用户在这个时间范围内对数据进行各种操作，如聚合、排序或深入分析等。这些操作的目的在于揭示数据随时间推移而展现出的变化趋势或内在特性。在处理时间序列数据时，时间窗口函数的价值无可替代，它们为数据分析和挖掘工作提供了强大的支持。时间序列数据，顾名思义，是按时间顺序排列的数据，这类数据在各个领域都有广泛的应用，如金融市场分析、气候变化研究、销售趋势预测等。
一. 从Hive开始
weixin_45032905的博客
09-04 443
总结，大数据背景下，hdfs看起来想一个无限大的存储空间，mapreduce的思想充分利用所有的cpu和内存。所以hive中的一个核心模块就是metastore， 用来存储结构化的信息，也就是“数据的数据”，其实也是借用了传统文件系统的“metaData”（元数据）的概念。是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的限制（也即是爆炸的虚表没有和原表进行连接）然后hive的执行引擎会将sql进行语法分析，生成语法树，然后生成mapreduce程序，进行数据的计算工作。
运维学习————kafka(1)
m0_73376570的博客
09-04 695
kafka中文文档kafka是由apache软件基金会开发的一个开源流处理框架，由JAVA和scala语言编写。是一个高吞吐量的分布式的发布和订阅消息的一个系统。Kafka用于构建实时的数据管道和流式的app.它可以水平扩展，高可用，速度快，并且已经运行在数千家公司的生产环境。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值