Hive--开窗函数--窗口聚合函数：SUM、AVG、COUNT、MAX、MIN

已于 2025-08-06 13:52:57 修改
阅读量1.7w 收藏 41
点赞数 10
CC 4.0 BY-SA版权
分类专栏： # Hive函数 文章标签： hive
于 2020-11-22 18:23:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_46893497/article/details/109959878
本文介绍窗口聚合函数的基本概念及应用场景，包括SUM、AVG、COUNT等函数如何在不同窗口设置下进行数据聚合运算，如累计求和、指定区间求和等，并通过具体实例展示其在数据处理中的应用。

窗口聚合函数

窗口聚合函数：SUM、AVG、COUNT、MAX、MIN

以SUM为例

总结

  • 1.如果不使用 over()，窗口大小是针对查询产生的所有数据
  • 功能：用于实现数据分区后的聚合
  • 语法：fun_name(col1) over (partition by col2 order by col3)
  • 示例：实现分区内的累加，其他的原理类似
  • 不指定窗口时,不排序默认第一行到最后一行,排序默认第一行到当前行
  • 指定窗口时 --rows between 起始位置 and 结束位置
    • N preceding：往前多少行
    • N following：往后多少行
    • current row：当前行
    • unbounded：起点或者终点，没有边界
    • unbounded preceding 表示从前面的起点
    • unbounded following：表示到后面的终点
  • 注意
    • 分组列表，组内按排列顺序求累加和, 每组的相同level（排序字段）分享同一累加值。
    • 如果level（排序字段）有相同，需要注意是否是你想要的结果，是否需要考虑指定窗口位置

实现分区内起始到当前行的pv累加，指定排序默认窗口：取分区第一行开始到当前行的和

--创建文件：vim /export/datas/window.txt
cookie1,2018-04-10,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie2,2018-04-13,3
cookie2,2018-04-14,2
cookie2,2018-04-15,4
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4
cookie2,2018-04-10,1
cookie2,2018-04-11,5
cookie2,2018-04-12,7
--创建表
create database db_function;
use db_function;
create table hanjiaxiaozhi_f1(
cookieid string,
daystr string,
pv int
) row format delimited fields terminated by ',';
--加载数据
load data local inpath '/export/datas/window.txt' into table hanjiaxiaozhi_f1;
set hive.exec.mode.local.auto=true;
--实现分区内起始到当前行的pv累加，默认窗口：取分区第一行开始到当前行的和
select 
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid order by daystr) as pv1 
from 
  hanjiaxiaozhi_f1;
  
+-----------+-------------+-----+------+--+
| cookieid  |   daystr    | pv  | pv3  |
+-----------+-------------+-----+------+--+
| cookie1   | 2018-04-10  | 1   | 1    |
| cookie1   | 2018-04-11  | 5   | 6    |
| cookie1   | 2018-04-12  | 7   | 13   |
| cookie1   | 2018-04-13  | 3   | 16   |
| cookie1   | 2018-04-14  | 2   | 18   |
| cookie1   | 2018-04-15  | 4   | 22   |
| cookie1   | 2018-04-16  | 4   | 26   |
| cookie2   | 2018-04-10  | 1   | 1    |
| cookie2   | 2018-04-11  | 5   | 6    |
| cookie2   | 2018-04-12  | 7   | 13   |
| cookie2   | 2018-04-13  | 3   | 16   |
| cookie2   | 2018-04-14  | 2   | 18   |
| cookie2   | 2018-04-15  | 4   | 22   |
+-----------+-------------+-----+------+--+

实现分区内起始到当前行的pv累加，指定排序默认窗口：取分区第一行开始到当前行的和，每组的相同level分享同一累加值。

select id,score,
sum(score) over(patition by id order by score) cumm_scores  
from GRADE
idscorecumm_scores
19898
1100198
295190
295190

实现分区内所有pv的累加，不指定排序默认窗口：从第一行到最后一行

select 
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid ) as pv2
from 
  hanjiaxiaozhi_f1;
  
+-----------+-------------+-----+------+--+
| cookieid  |   daystr    | pv  | pv2  |
+-----------+-------------+-----+------+--+
| cookie1   | 2018-04-16  | 4   | 26   |
| cookie1   | 2018-04-15  | 4   | 26   |
| cookie1   | 2018-04-13  | 3   | 26   |
| cookie1   | 2018-04-12  | 7   | 26   |
| cookie1   | 2018-04-11  | 5   | 26   |
| cookie1   | 2018-04-14  | 2   | 26   |
| cookie1   | 2018-04-10  | 1   | 26   |
| cookie2   | 2018-04-12  | 7   | 22   |
| cookie2   | 2018-04-11  | 5   | 22   |
| cookie2   | 2018-04-10  | 1   | 22   |
| cookie2   | 2018-04-14  | 2   | 22   |
| cookie2   | 2018-04-13  | 3   | 22   |
| cookie2   | 2018-04-15  | 4   | 22   |
+-----------+-------------+-----+------+--+

手动指定窗口的大小：分区

rows between 起始位置 and 结束位置
指定窗口的关键字：rows between 前 and 后 
 - N preceding：往前多少行
- N following：往后多少行
- current row：当前行
- unbounded：起点或者终点，没有边界
- unbounded preceding 表示从前面的起点
- unbounded following：表示到后面的终点
rows between unbounded preceding and current row
--实现分区内从第一行到当前行的pv累加
--手动指定窗口的大小：分区
rows between 起始位置 and 结束位置
rows between unbounded preceding and current row

实现分区内起始到当前行的pv累加,手动指定窗口大小从第一行到当前行

select 
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid order by daystr rows between unbounded preceding and current row) as pv3
from 
  hanjiaxiaozhi_f1;
  
  
+-----------+-------------+-----+------+--+
| cookieid  |   daystr    | pv  | pv3  |
+-----------+-------------+-----+------+--+
| cookie1   | 2018-04-10  | 1   | 1    |
| cookie1   | 2018-04-11  | 5   | 6    |
| cookie1   | 2018-04-12  | 7   | 13   |
| cookie1   | 2018-04-13  | 3   | 16   |
| cookie1   | 2018-04-14  | 2   | 18   |
| cookie1   | 2018-04-15  | 4   | 22   |
| cookie1   | 2018-04-16  | 4   | 26 
| cookie2   | 2018-04-10  | 1   | 1    |
| cookie2   | 2018-04-11  | 5   | 6    |
| cookie2   | 2018-04-12  | 7   | 13   |
| cookie2   | 2018-04-13  | 3   | 16   |
| cookie2   | 2018-04-14  | 2   | 18   |
| cookie2   | 2018-04-15  | 4   | 22   |
+-----------+-------------+-----+------+--+

实现分区内指定前N行到当前行的pv累加

select 
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid order by daystr rows between 3 preceding and current row) as pv4
from 
  hanjiaxiaozhi_f1;
  
+-----------+-------------+-----+------+--+
| cookieid  |   daystr    | pv  | pv4  |
+-----------+-------------+-----+------+--+
| cookie1   | 2018-04-10  | 1   | 1    |
| cookie1   | 2018-04-11  | 5   | 6    |
| cookie1   | 2018-04-12  | 7   | 13   |
| cookie1   | 2018-04-13  | 3   | 16   |
| cookie1   | 2018-04-14  | 2   | 17   |
| cookie1   | 2018-04-15  | 4   | 16   |
| cookie1   | 2018-04-16  | 4   | 13   |
| cookie2   | 2018-04-10  | 1   | 1    |
| cookie2   | 2018-04-11  | 5   | 6    |
| cookie2   | 2018-04-12  | 7   | 13   |
| cookie2   | 2018-04-13  | 3   | 16   |
| cookie2   | 2018-04-14  | 2   | 17   |
| cookie2   | 2018-04-15  | 4   | 16   |
+-----------+-------------+-----+------+--+

实现分区内指定前N行到后N行的pv累加

select 
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid order by daystr rows between 3 preceding and 1 following) as pv5
from 
  hanjiaxiaozhi_f1;
  
+-----------+-------------+-----+------+--+
| cookieid  |   daystr    | pv  | pv5  |
+-----------+-------------+-----+------+--+
| cookie1   | 2018-04-10  | 1   | 6    |
| cookie1   | 2018-04-11  | 5   | 13   |
| cookie1   | 2018-04-12  | 7   | 16   |
| cookie1   | 2018-04-13  | 3   | 18   |
| cookie1   | 2018-04-14  | 2   | 21   |
| cookie1   | 2018-04-15  | 4   | 20   |
| cookie1   | 2018-04-16  | 4   | 13   |
| cookie2   | 2018-04-10  | 1   | 6    |
| cookie2   | 2018-04-11  | 5   | 13   |
| cookie2   | 2018-04-12  | 7   | 16   |
| cookie2   | 2018-04-13  | 3   | 18   |
| cookie2   | 2018-04-14  | 2   | 21   |
| cookie2   | 2018-04-15  | 4   | 16   |
+-----------+-------------+-----+------+--+

实现分区内指定当前行到后N行的pv累加

select 
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid order by daystr rows between current row and unbounded following) as pv6
from 
  hanjiaxiaozhi_f1;
  
+-----------+-------------+-----+------+--+
| cookieid  |   daystr    | pv  | pv6  |
+-----------+-------------+-----+------+--+
| cookie1   | 2018-04-10  | 1   | 26   |
| cookie1   | 2018-04-11  | 5   | 25   |
| cookie1   | 2018-04-12  | 7   | 20   |
| cookie1   | 2018-04-13  | 3   | 13   |
| cookie1   | 2018-04-14  | 2   | 10   |
| cookie1   | 2018-04-15  | 4   | 8    |
| cookie1   | 2018-04-16  | 4   | 4    |
| cookie2   | 2018-04-10  | 1   | 22   |
| cookie2   | 2018-04-11  | 5   | 21   |
| cookie2   | 2018-04-12  | 7   | 16   |
| cookie2   | 2018-04-13  | 3   | 9    |
| cookie2   | 2018-04-14  | 2   | 6    |
| cookie2   | 2018-04-15  | 4   | 4    |
+-----------+-------------+-----+------+--+

注意：窗口函数不支持distinct

  • 会报错Error in query: Distinct window functions are not supported: 。。。。
  • 解决：size( collect_set(xxx)over( ) )
Hive聚合函数详细讲解
yang1015661763的博客
01-17 569
Hive中的聚合函数用于在数据上进行计算并返回单个值，这些值通常是基于一组行或列的汇总。
开窗函数sum，avg，min，max用法
samll_DATA的博客
08-11 1453
sum开窗函数 数据准备 hive> select * from TestData; OK cookie1 2021-04-10 1 cookie1 2021-04-11 5 cookie1 2021-04-12 7 cookie1 2021-04-13 3 cookie1 2021-04-14 2 cookie1 2021-04-15 4 cookie1 2021-04-16 4 结果 SELECT cookieid, crea
Mysql_窗口函数_OVER()的分组参数PARTITION BY
feizuiku0116的博客
08-21 1万+
一.PARTITION BY与GROUP BY区别 group by是分组函数，partition by是分析函数 在执行顺序上：from > where > group by > having > order by，而partition by应用在以上关键字之后，可以简单理解为就是在执行完select之后，在所得结果集之上进行partition by分组 partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似exc
Hive的窗口函数
最新发布
保持热爱。
07-24 905
窗口函数是Hive处理复杂分析需求的核心工具，通过ORDER BY和窗口框架的组合，可实现排名分析、趋势计算、时间序列处理等高级功能。实际应用中，需根据业务场景选择合适的窗口函数类型（排名、聚合、分析），并注意窗口框架的边界定义，以确保计算结果符合预期。
hive开窗函数
小白学编程
01-31 4485
1、什么是开窗函数？ 开窗就是在查询的结果上多出一个列，特征就是带有over(),分为聚合开窗和排序开窗 2、常用的开窗函数 a、排序开窗 row_number() over(...) --从1开始生成序列 举例：select *,ROW_NUMBER() OVER (partition by id order by extract_time desc 同类型的有：rank() over(...)...
Hive——开窗函数
LINBE_blazers的博客
09-23 291
平常我们使用 hive或者 mysql时，一般聚合函数用的比较多。但对于某些偏分析的需求，group by可能很费力，子查询很多，这个时候就需要使用窗口分析函数了~ 注：hive、oracle提供开窗函数，mysql不提供 参考文章：https://blog.csdn.net/Abysscarry/article/details/81408265...
【Hive】窗口函数（开窗函数部分）
康师傅没有眼泪
11-24 5480
我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.目前，我用的多的就是 row_number() , sum(), 解决topN 问题，分区排序，累积计数。hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于OLAP分析（在线分析处理）。
hive基本聚合函数
kwame211的博客
02-20 2921
数据聚合是按照特定条件将数据整合并表达出来，以总结出更多的组信息。Hive包含内建的一些基本聚合函数，如MAX, MIN, AVG等等，同时也通过GROUPING SETS, ROLLUP, CUBE等函数支持更高级的聚合。Hive基本内建聚合函数通常与GROUP BY连用，默认情况下是对整个表进行操作。在使用GROUP BY时，除聚合函数外其他已选择列必须包含在GROUP BY子句中。 下表...
【Hive---12】窗口函数『 窗口函数与group by 区别 | 窗口聚合函数 | 窗口排名函数 | 窗口分析函数』
渐渐的忘记，赶不上明天（读研ing，2024年毕业）
12-15 3411
1. 概述 1.1 窗口函数的partition by与group by 的分组有什么区别？ 1.2 窗口函数的order by + order by 的排序有区别吗？ 2. 语法 2.1 哪些函数可以开窗变为窗口函数？ 2.2 语法 ---- 关于order by 的一个坑 关于窗口函数是否可以嵌套 3. 窗口聚合函数 3.1 sum()窗口函数的一个注意点 3.2 例子 4. 窗口排名函数 4.1 求TopN 4.2 求几分之几 5 窗口分析函数 6. 关于窗口函数的一些思考
Hive-函数-窗口函数
坚持自己的梦想
08-05 1447
Hive中常用的窗口函数（也称为开窗函数）丰富多样，这些函数能够在进行数据分析时提供强大的支持，特别适用于需要对分组数据进行复杂计算和排序的场景。
大数据-Hive开窗函数
MsSpark的博客
12-20 3588
Hive开窗函数结构： xx() over(partition by xx order by xx) Hive开窗函数，大致有以下两类： 1. 排序开窗函数 row_number()：组内排名后获取行号 rank()：组内排名（连续） dense_rank()：组内排名（跳跃） first_value() last_value() lag(col, n, default)：用于统计窗口内往上取第n个值 lead(col, n, default)：用于统计窗口内往下取第n个值 cume_dist(col)：计
Hive---分组聚合函数，窗口函数
Hi_this_is_ID的博客
10-21 3977
一、分组聚合函数（group by） 用到的表是t_access: 有group by 那个group by 会在select、update、alter等等之前运行 1、把数据中的日期整合并显示数目，寻找最大的ip select dt,count(*),max(ip) as cnt from school.t_access group by dt; 结果图： 2、查询在数据...
【Hive---11】用户定义函数 『 单列函数UDF | 聚合函数 UDAF | 表生成函数 UDTF | 自定义函数』
渐渐的忘记，赶不上明天（读研ing，2024年毕业）
12-14 350
用户定义函数（UDF：User-Defined Functions） 1. 单列函数 (UDF - 一进一出) 2. 聚合函数 (UDAF - 多进一出) 2.1 基础聚合函数 2.2 高级聚合函数 2.2 增强聚合函数 2.2.1 grouping sets() 2.2.2 with cube() 与 with rollup() 3 表生成函数 (UDTF - 一进多出) 3.1 explode()炸裂函数 3.2 posexplode()炸裂函数 3.2 parse_url_tuple()函数 3.3
Hive 的开窗函数有哪些？
My_wife_QBL的博客
08-01 1196
开窗函数是一类特殊的 SQL 函数，它能够在查询结果的某个窗口内进行计算。与传统的聚合函数不同，开窗函数不会将结果集缩减到单一的行，而是保留所有行，同时在每一行上进行计算。开窗函数通常用于分析、排名及计算移动平均等场景。：开窗函数的名称。argument：函数的参数。：定义窗口的分区。ORDER BY：定义窗口内的排序。Hive 的开窗函数为数据分析提供了强大的功能，使得用户能够在不聚合数据的情况下对数据进行细致的分析。通过使用各种开窗函数（如RANK()SUM()LEAD()和LAG()
开窗函数之聚合、取特定值、排名
只是晓云闲的博客
06-25 1515
一、聚合开窗函数 sum(score) over(partition by name ) 二、ORDER BY 三、排名开窗函数ROW_NUMBER、DENSE_RANK、RANK
Hive聚合运算
qianchun22的博客
09-19 1287
Hive聚合运算Hive聚合运算GROUP BYHAVING基础聚合高级聚合 Hive聚合运算 GROUP BY group by用于分组 Hive基本内置聚合函数与group by一起使用 如果没有指定group by子句，则默认聚合整个表 除聚合函数外，所选的其他列也必须包含在group by中 group by支持使用case when或表达式 支持按位置编号分组：set hive.groupby.orderby.position.alias=true; 案例： #执行失败(原因：除聚合函数外，所
HiveSQL题——聚合函数(sum/count/max/min/avg)
爱吃辣条的博客
01-30 2628
HiveSQL题——聚合函数(sum/count/max/min/avg)
Hive聚合函数及采样函数详解
热门推荐
yhao2014的专栏
06-03 2万+
本文主要使用实例对Hive内建的一些聚合函数、分析函数以及采样函数进行比较详细的讲解。
开窗函数
dhv98472的博客
06-02 176
·格式 函数名(列) OVER(选项) ·作用 一般的聚集函数返回值是单值，而开窗函数返回值是多值，可能是一列的形式 ·示例 select S#,AVG(score) 平均成绩,(RANK() over (order by AVG(score))) rank from sc group by s# 结果： 转载于:https://www.c...
Hive函数完全手册：仓库存储技巧
1. **聚合函数**：这些函数用于对一组值执行计算，并返回单个值。 - `COUNT`：计算某个列的行数。 - `SUM`：计算某个列的总和。 - `AVG`：计算某个列的平均值。 - `MIN` 和 `MAX`：找出列中的最小值和最大值。 ...
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值