分析函数hive计算均值_Hive第六天——Hive函数（开窗函数之累计统计）
本文部分参考自：https://blog.csdn.net/qq_23897391/article/details/100558433
Hive第六天——Hive函数
自己的话：千里之行，始于足下。
每天都要保持前进，我势必要有强劲的实力，再跟明天的自己问好。
开窗函数：累计统计
这类函数叫法很多，包括分析函数、窗口函数、开窗函数、分析窗口函数，其实说的都是一类函数
一、开窗函数简介
开窗函数用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合)，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
二、开窗函数语法
1.开窗函数的语法为：
over(partition by 列名1,列名2 …… order by 列名3,列名4 …… [desc])
括号中的两个关键词partition by 和order by 可以只出现一个。
partition by 和order by 后面的列名可以根据需求设定任意数量个列名。
order by后面可以选择是否跟desc，加上为倒序排序(从大到小)，不加则默认为从小到大排序。
over() 前面是一个函数。
2.分段解析：
sum(columns) over (partition by col1 order by col2 rows between n/unbounded preceding and m following/current row )
如果不指定rows between，默认为从起点到当前行；
如果不指定order by，则将分组内所有值累加；
关键是理解rows between含义，也叫做window子句：
preceding:往前
following：往后
current rows：当前行
unbounded：起点
unbounded preceding：表示从前面的起点
unbounded following：表示到后面的终点
三、开窗函数分类
在Hive中开窗函数按功能主要分为以下四类：
组内累计统计
组内排序
组内层次查询
组内偏移关联
四、组内累计统计开窗函数
Hive中提供了很多分析函数，用于完成负责的统计分析。先看看基础的sum,avg,min,max，用于实现分组内所有和连续累计的统计。
数据准备：
新建test.txt文件，输入如下的三列数据，以空格分隔。第一列是月份，第二列代表商铺名称，第三列代表该商铺该月营业额(万元)。
[root@hadoop ~]# vim test.txt
2019-01 a 10
2019-02 a 20
2019-03 a 30
2019-01 b 10
2019-02 b 20
2019-03 b 30
在hive中新建表temp_test9，将test文件中的数据插入，查看数据。
hive >CREATE TABLE temp_test9 (
>month STRING comment '月份',
>shop STRING comment '商铺名称',
>money STRING comment '营业额(万元)')
>row format delimited
>fields terminated BY ' ';
load data local inpath '/root/test.txt' into table temp_test9;
select * from temp_test9;
temp_test9.monthtemp_test9.shoptemp_test9.money
2019-01a10
2019-02a20
2019-03a30
2019-01b10
2019-02b20
2019-03b30
1.累计求和 sum(xx) over
(1)求商店a每个月从1月累计到该月的总营业额，即：
1月的数据是1月的营业额
2月的数据是1月+2月的营业额
3月的数据是1月+2月+3月的营业额
hive >SELECT month,SUM(MONEY) OVER
>(ORDER BY month --按照月份进行排序，然后默认从起点行到当前行做累计求和) AS money_leiji
>FROM temp_test9
>WHERE shop = 'a'; --开窗函数不用写group by
结果：
monthmoney_leiji
2019-0110.0
2019-0230.0
2019-0360.0
(2)同时求出商店a、b每个月从1月累计到该月的总营业额
hive >SELECT shop,month,SUM(MONEY)
>OVER (PARTITION BY shop ORDER BY month --先按照shop进行分组，然后每个组内再按照月份进行排序，最后默认从起点行到当前行做累计求和)
>AS money_leiji
>FROM temp_test9;
结果：
shopmonthmoney_leiji
a2019-0110.0
a2019-0230.0
a2019-0360.0
b2019-0110.0
b2019-0230.0
b2019-0360.0
1、累计统计，遇到重复行时不累加
sum(count(*)) over(order by count(*) desc )
2、累计统计，遇到重复行时也累加
sum(count(*)) over(order by count(*) desc rows between UNBOUNDED PRECEDING and current row )
2.累计求平均值 avg(xx) over
其他的开窗函数和求和的累计逻辑都是相同的。
举例：
同时求出商店a、b每个月从1月累计到该月的平均营业额
hive >SELECT shop,month,AVG(MONEY) OVER
>(PARTITION BY shop ORDER BY month --先按照shop进行分组，然后每个组内再按照月份进行排序，最后默认从起点行到当前行做累计求均值)
>AS money_leiji
>FROM temp_test9;
结果：
shopmonthmoney_leiji
a2019-0110.0
a2019-0215.0
a2019-0320.0
b2019-0110.0
b2019-0215.0
b2019-0320.0
3.累计求最大值 max(xx) over
举例：
同时求出商店a、b每个月从1月累计到该月的营业额最大值
hive >SELECT shop,month,MAX(MONEY)
>OVER (PARTITION BY shop ORDER BY
>month --先按照shop进行分组，然后每个组内再按照月份进行排序，最后默认从起点行到当前行做累计求最大值)
>AS money_leiji_max
>FROM temp_test9;
结果：
shopmonthmoney_leiji_max
a2019-0110
a2019-0220
a2019-0330
b2019-0110
b2019-0220
b2019-0330
4.累计求最小值 min(xx) over
举例：
同时求出商店a、b每个月从1月累计到该月的营业额最小值
hive >SELECT shop,month,MIN(MONEY)
>OVER (PARTITION BY shop ORDER BY month --先按照shop进行分组，然后每个组内再按照月份进行排序，最后默认从起点行到当前行做累计求最小值)
>AS money_leiji_min
>FROM temp_test9;
结果：
shopmonthmoney_leiji_min
a 2019-0110
a 2019-0210
a 2019-0310
b 2019-0110
b 2019-0210
b 2019-0310