Hive分析函数之SUM，AVG，MIN和MAX OVER(PARTITION BY xxx order by xxx，用于求一段时间内截至到每天的累计访问次数、平均访问次数、最小访问次数、最大访问次

淡淡的烟草味﹌ 2023-06-23 03:54 8阅读 0赞

**        Hive分析函数之SUM，AVG，MIN和MAX OVER(PARTITION BY xxx order by xxx，用于求一段时间内截至到每天的累计访问次数、平均访问次数、最小访问次数、最大访问次数**

Hive提供了很多分析函数，用于统计分析，比如SUM OVER PARTITION BY ORDER BY 是 实现分组累计的计算方法。

本文先介绍SUM、AVG、MIN、MAX这四个函数。

### 一、创建数据文件data.txt，内容如下： ###

> P0887,2016-02-10,1  
> P0887,2016-02-11,3  
> P0887,2016-02-12,1  
> P0887,2016-02-13,9  
> P0887,2016-02-14,3  
> P0887,2016-02-15,12  
> P0889,2016-02-16,2  
> P0889,2016-02-14,3  
> P0889,2016-02-15,10  
> P0889,2016-02-16,6  
> P0890,2016-02-14,1  
> P0890,2016-02-15,19  
> P0890,2016-02-16,30

### 二、创建hive表并加载数据： ###

> CREATE TABLE \`yyz\_func\`(  
>   \`polno\` string COMMENT 'polno',   
>   \`createtime\` string COMMENT 'createtime',   
>   \`pnum\` int COMMENT 'pnum')  
> COMMENT 'yyz\_func'  
> ROW FORMAT DELIMITED   
>   FIELDS TERMINATED BY ','   
> STORED AS INPUTFORMAT   
>   'org.apache.hadoop.mapred.TextInputFormat'   
> OUTPUTFORMAT   
>   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> 
>   
> load data local inpath '/home/workdir/yyz/data/data' into table yyz\_func;

### 三、为了便于查看结果，set相关配置，显示列名： ###

> spark-hive> select \* from yyz\_func;  
> 19/12/17 11:18:42 INFO SparkHiveShell: current SQL: select \* from yyz\_func  
> P0887    2016-02-10    1  
> P0887    2016-02-11    3  
> P0887    2016-02-12    1  
> P0887    2016-02-13    9  
> P0887    2016-02-14    3  
> P0887    2016-02-15    12  
> P0889    2016-02-16    2  
> P0889    2016-02-14    3  
> P0889    2016-02-15    10  
> P0889    2016-02-16    6  
> P0890    2016-02-14    1  
> P0890    2016-02-15    19  
> P0890    2016-02-16    30  
> Time taken: 1.874 s

公司集群CLI命令行测试，发现Hive命令行输出不显示列名，或者显示的列名带表名，可读性很差哇。

1）、**显示列名办法：进入cli后 set hive.cli.print.header=true;**  
在某些版本（hive cli只输入上面命令即会出现表名+列名，在spark-hive中只会出现列名，不会出现表名），为解决显示列名以后，又出现表名+列名的显示方式，可读性也不好。  
**显示列名不显示表名的办法：set  hive.resultset.use.unique.column.names=false;**  
2）、在cli中set配置属性只是当次有效，如果想永久配置的话，将上述命令配置到hive/conf下的配置文件中，或者配置到hiverc文件里，因为每次CLI启动时，在提示符出现之前，Hive会自动在HOME目录下查找名为.hiverc的文件，而且执行这个文件中的所有命令。非常适合做初始化，所以有些关于hive的初始化设置可以配置到家目录下的.hiverc文件里

> spark-hive> set hive.cli.print.header=true;  
> 19/12/17 11:19:34 INFO SparkHiveShell: current SQL: set hive.cli.print.header=true  
> Time taken: 4.205 s  
> spark-hive> select \* from yyz\_func;          
> 19/12/17 11:19:40 INFO SparkHiveShell: current SQL: select \* from yyz\_func  
> polno    createtime    pnum  
> P0887    2016-02-10    1  
> P0887    2016-02-11    3  
> P0887    2016-02-12    1  
> P0887    2016-02-13    9  
> P0887    2016-02-14    3  
> P0887    2016-02-15    12  
> P0889    2016-02-16    2  
> P0889    2016-02-14    3  
> P0889    2016-02-15    10  
> P0889    2016-02-16    6  
> P0890    2016-02-14    1  
> P0890    2016-02-15    19  
> P0890    2016-02-16    30  
> Time taken: 4.09 s

### 四、sum(xxx)over(partition by yyy order by zzz)简单示例 ###

1、 select polno,createtime,sum(pnum)over(order by pnum) from yyz\_func;     --按照 v2排序，累计n+n-1+....+1

> hive> select polno,createtime,sum(pnum)over(order by pnum) from yyz\_func;  
> Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 4.72 sec   HDFS Read: 0 HDFS Write: 0 SUCCESS  
> Total MapReduce CPU Time Spent: 4 seconds 720 msec  
> OK  
> polno createtime  sum\_window\_0  
> P0887 2016-02-10  3  
> P0887 2016-02-12  3  
> P0890 2016-02-14  3  
> P0889 2016-02-16  5  
> P0889 2016-02-14  14  
> P0887 2016-02-14  14  
> P0887 2016-02-11  14  
> P0889 2016-02-16  20  
> P0887 2016-02-13  29  
> P0889 2016-02-15  39  
> P0887 2016-02-15  51  
> P0890 2016-02-15  70  
> P0890 2016-02-16  100  
> Time taken: 65.532 seconds, Fetched: 13 row(s)

2、select polno,createtime,sum(pnum)over(partition by polno order by pnum) from yyz\_func;   --先分组，组内在进行 1 中的操作

>   
> hive> select polno,createtime,sum(pnum)over(partition by polno order by pnum) from yyz\_func;  
> Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 4.9 sec   HDFS Read: 0 HDFS Write: 0 SUCCESS  
> Total MapReduce CPU Time Spent: 4 seconds 900 msec  
> OK  
> polno createtime  sum\_window\_0  
> P0887 2016-02-10  2  
> P0887 2016-02-12  2  
> P0887 2016-02-11  8  
> P0887 2016-02-14  8  
> P0887 2016-02-13  17  
> P0887 2016-02-15  29  
> P0889 2016-02-16  2  
> P0889 2016-02-14  5  
> P0889 2016-02-16  11  
> P0889 2016-02-15  21  
> P0890 2016-02-14  1  
> P0890 2016-02-15  20  
> P0890 2016-02-16  50  
> Time taken: 56.717 seconds, Fetched: 13 row(s)

3、select polno,createtime,sum(pnum)over(partition by polno order by polno) from yyz\_func;   ---稳定排序

> hive> select polno,createtime,sum(pnum)over(partition by polno order by polno) from yyz\_func;  
> Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 3.72 sec   HDFS Read: 0 HDFS Write: 0 SUCCESS  
> Total MapReduce CPU Time Spent: 3 seconds 720 msec  
> OK  
> polno createtime  sum\_window\_0  
> P0887 2016-02-10  29  
> P0887 2016-02-15  29  
> P0887 2016-02-14  29  
> P0887 2016-02-13  29  
> P0887 2016-02-12  29  
> P0887 2016-02-11  29  
> P0889 2016-02-16  21  
> P0889 2016-02-16  21  
> P0889 2016-02-15  21  
> P0889 2016-02-14  21  
> P0890 2016-02-14  50  
> P0890 2016-02-15  50  
> P0890 2016-02-16  50  
> Time taken: 71.428 seconds, Fetched: 13 row(s)

4、select polno,createtime,sum(pnum)over(partition by polno) from yyz\_func;   --相同key的进行回填处理

> hive> select polno,createtime,sum(pnum)over(partition by polno) from yyz\_func;  
> Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 4.47 sec   HDFS Read: 0 HDFS Write: 0 SUCCESS  
> Total MapReduce CPU Time Spent: 4 seconds 470 msec  
> OK  
> polno createtime  sum\_window\_0  
> P0887 2016-02-10  29  
> P0887 2016-02-15  29  
> P0887 2016-02-14  29  
> P0887 2016-02-13  29  
> P0887 2016-02-12  29  
> P0887 2016-02-11  29  
> P0889 2016-02-16  21  
> P0889 2016-02-16  21  
> P0889 2016-02-15  21  
> P0889 2016-02-14  21  
> P0890 2016-02-14  50  
> P0890 2016-02-15  50  
> P0890 2016-02-16  50  
> Time taken: 126.898 seconds, Fetched: 13 row(s)

5、 select distinct polno,createtime,total\_sum from (select polno,createtime,sum(pnum) over(partition by polno) as total\_sum from yyz\_func)a  --取一条

> hive> select distinct polno,createtime,total\_sum from (select polno,createtime,sum(pnum) over(partition by polno) as total\_sum from yyz\_func)a;  
> Total MapReduce CPU Time Spent: 10 seconds 160 msec  
> OK  
> polno createtime  total\_sum  
> P0887 2016-02-10  29  
> P0887 2016-02-11  29  
> P0887 2016-02-12  29  
> P0887 2016-02-13  29  
> P0887 2016-02-14  29  
> P0887 2016-02-15  29  
> P0889 2016-02-14  21  
> P0889 2016-02-15  21  
> P0889 2016-02-16  21  
> P0890 2016-02-14  50  
> P0890 2016-02-15  50  
> P0890 2016-02-16  50  
> Time taken: 110.093 seconds, Fetched: 12 row(s)

6、当然也可以逆序累加，只需order by desc 即可

总结区别：group by 和partition by的区别  
1）group 单纯分组  
2）partition 也能分组，但还具备累计的功能

### 五、sum函数示例，适用于一段求历史数据中累计到每一天的访问量： ###

SELECT polno,  
createtime,  
pnum,  
SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime) AS pnum1, --默认为从起点到当前行  
SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pnum2,  --从起点到当前行  
SUM(pnum) OVER(PARTITION BY polno) ASpnum3, --分组内所有行  
SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pnum4,  --当前行+往前3行(当前行的值+前面三行的值)  
SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pnum5,  --当前行+往前3行+往后1行  
SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pnum6 ---当前行+往后所有行  
FROM yyz\_func;

具体例子和结果：

> SELECT polno,  
> createtime,  
> pnum,  
> SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime) AS pnum1,  
> SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pnum2,  
> SUM(pnum) OVER(PARTITION BY polno) AS pnum3,  
> SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pnum4,  
> SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pnum5,  
> SUM(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pnum6  
> FROM yyz\_func;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70][]

注释：

1)、如果不指定ROWS BETWEEN,默认为从起点到当前行;

2)、如果不指定ORDER BY，则将分组内所有值累加;

理解ROWS BETWEEN含义,也叫做WINDOW子句：  
PRECEDING：往前  
FOLLOWING：往后  
CURRENT ROW：当前行  
UNBOUNDED：起点，UNBOUNDED PRECEDING表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点  
其他AVG，MIN，MAX，和SUM用法一样。

### 六、avg函数示例，适用于求一段历史数据中累计到每一天的平均访问量： ###

SELECT polno,  
createtime,  
pnum,  
AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime) AS pnum1, --默认为从起点到当前行  
AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pnum2, --从起点到当前行  
AVG(pnum) OVER(PARTITION BY polno) AS pnum3, --分组内所有行  
AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pnum4, --当前行+往前3行(当前行的值+前面三行的值)  
AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pnum5, --当前行+往前3行+往后1行  
AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pnum6 --当前行+往后所有行  
FROM yyz\_func;

> select polno,createtime,pnum,cast(pnum1 as decimal(10,2)),cast(pnum2 as decimal(10,2)),cast(pnum3 as decimal(10,2)),cast(pnum4 as decimal(10,2)),cast(pnum5 as decimal(10,2)),cast(pnum6 as decimal(10,2))  
> from  
> (  
>     SELECT polno,  
>     createtime,  
>     pnum,  
>     AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime) AS pnum1,  
>     AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pnum2,  
>     AVG(pnum) OVER(PARTITION BY polno) AS pnum3,  
>     AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pnum4,  
>     AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pnum5,  
>     AVG(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pnum6  
>     FROM yyz\_func  
> )aa

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 1][]

ps，hive中保留小数位数的方法，参照 [https://blog.csdn.net/helloxiaozhe/article/details/103578666][https_blog.csdn.net_helloxiaozhe_article_details_103578666]

### 七、max函数示例，适用于求一段历史数据中最大的单日访问量 ###

> SELECT polno,  
> createtime,  
> pnum,  
> max(pnum) OVER(PARTITION BY polno ORDER BY createtime) AS pnum1,  
> max(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pnum2,  
> max(pnum) OVER(PARTITION BY polno) AS pnum3,  
> max(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pnum4,  
> max(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pnum5,  
> max(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pnum6  
> FROM yyz\_func;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 2][]

### 八、min函数示例，适用于求一段历史数据中最小的单日访问量 ###

> SELECT polno,  
> createtime,  
> pnum,  
> min(pnum) OVER(PARTITION BY polno ORDER BY createtime) AS pnum1,  
> min(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pnum2,  
> min(pnum) OVER(PARTITION BY polno) AS pnum3,  
> min(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pnum4,  
> min(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pnum5,  
> min(pnum) OVER(PARTITION BY polno ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pnum6  
> FROM yyz\_func;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 3][]

参考：https://blog.csdn.net/jiangshouzhuang/article/details/51057093

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191217123622790.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191217130912324.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ==,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_helloxiaozhe_article_details_103578666]: https://blog.csdn.net/helloxiaozhe/article/details/103578666
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191217131133723.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191217131237771.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ==,size_16,color_FFFFFF,t_70

发表评论取消回复

表情：

评论列表（有 0 条评论，8人围观）

还没有评论，来说两句吧...

相关阅读

相关海量日志提取访问次数最多的ＩＰ

方法: 计数法假设一天之内某个IP访问百度的次数不超过40亿次,则访问次数可以用unsigned表示.用数组统计出每个IP地址出现的次数, 即可得到访问次数最大的IP地址.

以你之姓@/ 2024年02月19日 18:52/ 0 赞/ 105 阅读

相关根据IP限制指定时间内访问接口的次数

在网上看见有人问一个问题：想限制一下某个接口在一分钟之内只能被同一个ip请求指定次数。方法比较多，这里就用Redis做一个简单的限制。大致逻辑：把请求的ip作为ke

柔光的暖阳◎/ 2024年01月20日 08:04/ 0 赞/ 95 阅读

相关 laravel限定路由访问次数

简介有时候我们可能会需要限定路由访问次数这样的需求。在`laravel`中又一个内置中间件可以帮我们实现这个需求 throttle:3,1 第一个参数，

- 日理万妓/ 2023年10月06日 16:04/ 0 赞/ 61 阅读

相关 Hive分析函数之SUM，AVG，MIN和MAX OVER(PARTITION BY xxx order by xxx，用于求一段时间内截至到每天的累计访问次数、平均访问次数、最小访问次数、最大访问次

Hive分析函数之SUM，AVG，MIN和MAX OVER(PARTITION BY xxx order by xxx，用于求一段时间内截至到每天的累计访问次数

淡淡的烟草味﹌/ 2023年06月23日 03:54/ 0 赞/ 9 阅读

相关 spring boot 限制接口访问次数

首先自定义一个元注解 import java.lang.annotation.Documented; import java.lang.annotation.

╰+攻爆jí腚メ/ 2022年10月01日 06:41/ 0 赞/ 383 阅读

相关 LeetCode（数据库）- 每次访问的交易次数

题目链接：[点击打开链接][Link 1] 题目大意：略。解题思路：注意解决方案(1)中，生成序数表可以借助于窗口函数 ROW\_NUMBER() OVER()；本题中如

秒速五厘米/ 2022年09月11日 05:24/ 0 赞/ 225 阅读

相关 spring实现访问次数控制

根据IP去限制用户单位时间的访问次数防刷. @Retention(RetentionPolicy.RUNTIME) @Target(ElementType

╰半夏微凉°/ 2022年07月15日 14:40/ 0 赞/ 265 阅读

相关访问频率以及次数控制

前言 1、在调用别人接口的时候，给我提示了下请求太过频繁，所以我添加了频率控制 2、在别人访问我们系统的时候，我们会对他访问次数进行限制 1、频率控制

末蓝、/ 2022年04月23日 00:56/ 0 赞/ 210 阅读

相关 Hadoop按日期统计访问次数

MapReduce编程主要组件 InputFormat类：分割成多个splits和每行怎么解析。 Mapper类：对输入的每对<key,value

深碍√TFBOYSˉ_/ 2022年02月21日 00:05/ 0 赞/ 570 阅读

相关限制访问次数例子和Ajax的some

-- 限制访问次数作业解答 -- -- urls.py 里-- from django.conf.urls import url from

冷不防/ 2022年01月12日 11:19/ 0 赞/ 241 阅读