[Hive系列08] 聚合函数详解

数据分析师的FIRE人生 2021-06-22

2398

点击上方「数据分析师的FIRE人生」→

点击右上角「...」→设为星标⭐

大家好，我是风影楼，一名互联网公司的数据分析师。之前我曾在CSDN编写了Hive系列的付费博客专栏，目前收获了25W次的访问，不过由于CSDN的付费设置无法取消，所以我决定在公众号重新免费分享一遍。这一篇主要介绍Hive中的聚合函数。

1、语法

聚合函数对一组值执行计算，并返回单个值，也被称为组函数。聚合函数经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。group by与聚合函数一起使用时，必须将所有select语句中的非聚合函数字段全部group by，否则会报错。语法如下：

select 列名
      ,aggregate_function(列名) as num
from 表名
group by 列名;
复制

当group by后的字段不全（没有包括所有select后的非聚合函数字段）时报错如下：

select platform
      ,user_id
      ,sum(use_cnt) as num
from app.t_od_use_cnt
group by platform;


failed: semanticexception [error 10025]: line 2:7 expression not in group by key 'user_id'
复制

举例：

表t_od_use_cnt中记录了某一天各平台访问app的用户id，以及每个用户当天的启动次数和使用时长。

platform	user_id	use_cnt	use_time
android	1	10	50
android	1	10	50
android	2	20	100
android	3	50	300
iphone	4	10	50

2、count(*)

功能：返回检索到的行总数，包括包含null值的行

举例：

查询每个平台有多少条记录
select platform
      ,count(*) as num
from app.t_od_use_cnt
group by platform;
 
运行结果如下：
platform num
android  4
iphone 1
复制

3、count(col)

功能：返回为其提供的列为非 null 的行数

举例：

查询每个平台有多少条记录
select platform
      ,count(user_id) as num
from app.t_od_use_cnt
group by platform;
 
运行结果如下：
platform num
android  4
iphone 1
复制

4、count(distinct col)

功能：返回所提供的列唯一且非null的行数

举例：

查询每个平台有多少不同的用户
select platform
      ,count(distinct user_id) as num
from app.t_od_use_cnt
group by platform;


运行结果如下：
platform num
android  3
iphone 1
复制

4、sum(col) sum(distinct col)

功能：返回组中元素的总和或组中列的不同值的总和。

举例：

查询每个平台的用户共使用了多少次
select platform
      ,sum(use_cnt) as num
from app.t_od_use_cnt
group by platform;
 
运行结果如下：
platform num
android 70
iphone 10
复制

5、avg(col) avg(distinct col)

功能：返回组中元素的平均值或组中列的不同值的平均值。

举例：

查询每个平台下的用户人均使用了多少次
select platform
      ,user_id      
      ,sum(use_cnt) as num
from app.t_od_use_cnt
group by platform
      ,user_id
      
运行结果如下：
android 001 20
android 002 20
android 003 50
iphone 004 10




select platform
      ,avg(use_cnt) as num
from (
select platform
            ,user_id
            ,sum(use_cnt) as num
from app.t_od_use_cnt
group by platform
            ,user_id
      ) a
group by platform;


运行结果如下：
platform num
android 30
iphone 10
复制

6、min(col)

功能：返回组中列的最小值。

举例：

查询每个平台下的使用最少的次数
select platform
      ,min(use_cnt) as num
from app.t_od_use_cnt
group by platform;
 
运行结果如下： 
platform num
android 10
iphone 10
复制

7、max(col)

功能：返回组中列的最大值。

举例：

查询每个平台下的使用最多的次数
select platform
      ,max(use_cnt) as num
from app.t_od_use_cnt
group by platform;
 
运行结果如下： 
platform num
android 50
iphone 10
复制

8、percentile(BIGINT col, p)

功能：求col列构成的数据集中准确的第p百分位数，p必须介于0和1之间，col字段只支持整数，返回一个DOUBLE类型的分位数值。当p为0.5时，返回值为中位数，当p为0.25时，返回第一四分位数，其他分位数依此类推。

分位数（quantile），亦称分位点，是指将有限的数集中的所有值，从小到大排序后分为几个等份的数值点。常用的有中位数（即二分位数）、四分位数、十分位数等。

计算有限个数的数据的二分位数的方法是：把所有的同类数据按照大小的顺序排列。如果数据的个数是奇数，则中间那个数据就是这群数据的中位数；如果数据的个数是偶数，则中间那两个数据的算术平均值就是这群数据的中位数。

四分位数（quartile）是统计学中分位数的一种，即把所有数值由小到大排列并分成四等份，处于三个分割点位置的数值就是四分位数。

第一四分位数(q1)，又称“较小四分位数”，等于该样本中所有数值由小到大排列后第25%的数字；

第二四分位数(q2)，又称“中位数”，等于该样本中所有数值由小到大排列后第50%的数字；

第三四分位数(q3)，又称“较大四分位数”，等于该样本中所有数值由小到大排列后第75%的数字。

举例：

使用如下表格中的数据：

select * from temp_test4;


运行结果如下：
shop1  1
shop1  3
shop1  2
shop1  4
shop1  6
shop1  5
shop1  7
shop1  9
shop1  8
shop1  10
shop2  1
shop2  2
shop2  5
shop2  3
shop2  6
shop2  9
shop2  7
shop2  8
shop2  4
shop2  10
shop2  11
复制

计算shop1和shop2的成本中位数
SELECT shop
      ,percentile(chengben, 0.5)
FROM temp_test4
GROUP BY shop;


运行结果如下：
shop  _c1
shop1  5.5
shop2  6.0


shop1有10个数据，从小到大排序后中间的两个数为5和6，所以中位数为5.5
复制

9、percentile(BIGINT col, array(p1 [, p2]…))

功能：和上文语法类似，但第二个参数为数组格式，可以输入多个百分位数。返回值类型也为array<double>（由DOUBLE类型的分位数组成的数组），返回的数组中为与第二个参数中对应的百分位数。

举例：

计算shop1和shop2的成本第一四分位数，中位数，第三四分位数
SELECT shop
      ,percentile(chengben, array(0.25,0.5,0.75))
FROM temp_test4
GROUP BY shop;


运行结果如下：
shop  _c1
shop1  [3.25,5.5,7.75]
shop2  [3.5,6.0,8.5]
复制

10、percentile_approx(DOUBLE col, p [, B])

功能：求近似的第p个百分位数，p必须介于0和1之间，返回值类型为double，col字段支持浮点类型。参数B控制近似精度，B越大，结果的准确度越高，参数B如果不填则使用默认值10000。

官网说数据集的去重值个数小于第三个参数时，会返回精确的分位数值，但是实测发现返回的依然是近似值，只是如果数据集的去重值个数小于第三个参数时，近似值会最接近值真实分位数值。如果数据集的去重值个数大于第三个参数，近似值则会更加的不精确。

应用场景：当数据量很大时，上一个精确的分位数值可能无法运行成功或耗时很长，可以考虑使用近似分位数函数代替。

举例：

计算中位数的近似值，第三个参数填任意一个大于数据集去重的个数的值
SELECT shop
      ,percentile_approx(chengben, 0.5, 11)
FROM temp_test4
GROUP BY shop;


运行结果如下：
shop  _c1
shop1  5.0
shop2  5.5


数据集去重的个数小于10000时，可以不填第三个参数，结果和填一个大于数据集去重的个数的值是一样的
SELECT shop
      ,percentile_approx(chengben, 0.5)
FROM temp_test4
GROUP BY shop;


运行结果如下：
shop  _c1
shop1  5.0
shop2  5.5


当第三个参数小于数据集去重的个数小于时，会返回更加不精准的近似值
SELECT shop
      ,percentile_approx(chengben, 0.5 ,5)
FROM temp_test4
GROUP BY shop;


运行结果如下：
shop  _c1
shop1  4.5
shop2  5.0
复制

11、percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])

功能：和上文语法类似，但第二个参数为数组格式，可以输入多个百分位数。返回值类型也为array<double>（由DOUBLE类型的分位数组成的数组），返回的数组中为与第二个参数中对应的百分位数，第三个参数B的用法和上面的用法一致。

举例：

SELECT shop
      ,percentile_approx(chengben, array(0.25,0.5,0.75),11)
FROM temp_test4
GROUP BY shop;


运行结果如下：
shop  _c1
shop1  [2.5,5.0,7.5]
shop2  [2.75,5.5,8.25]
复制

12、corr(col1, col2)

功能：col1和col2分别写入两个列名，最后会返回两个数字列的皮尔逊相关系数（DOUBLE类型）。另此函数为聚合函数，可以配合group by对多个组同时求两列的相关系数。

定义：

相关系数(Correlation coefficient)是最早由统计学家卡尔·皮尔逊设计的统计指标，是研究变量之间线性相关程度的量，一般用字母 r 表示。

由于研究对象的不同，相关系数有多种定义方式，较为常用的是皮尔逊相关系数，也叫简单相关系数或线性相关系数。

用数学公式表示，皮尔森相关系数等于两个变量的协方差除于两个变量的标准差。

性质：

相关系数定量地刻画了 X 和 Y的相关程度。r的取值在-1与+1之间。

若r>0，表明两个变量是正相关，即一个变量的值越大，另一个变量的值也会越大；

若r<0，表明两个变量是负相关，即一个变量的值越大另一个变量的值反而会越小。

r 的绝对值越大表明相关性越强，要注意的是这里并不存在因果关系。

若r=0，表明两个变量间不是线性相关，但有可能是其他方式的相关（比如曲线方式）。

一般相关系数的绝对值与相关程度对应关系如下：

0.8-1.0 极强相关

0.6-0.8 强相关

0.4-0.6 中等程度相关

0.2-0.4 弱相关

0.0-0.2 极弱相关或无相关

适用范围：

当两个变量的标准差都不为零时，相关系数才有定义，皮尔逊相关系数适用于以下三种情况：

两个变量之间是线性关系，都是连续数据。
两个变量的总体是正态分布，或接近正态的单峰分布。
两个变量的观测值是成对的，每对观测值之间相互独立。

缺点：

相关系数有一个明显的缺点，即它接近于1的程度与数据组数n相关，这容易给人一种假象。因为，当n较小时（<30），相关系数的波动较大，对有些样本相关系数的绝对值易接近于1，特别是当n=2时，相关系数的绝对值总为1。而当n较大时（>100），相关系数的绝对值容易偏小。

因此在样本容量n较小时，我们仅凭相关系数较大就判定变量x与y之间有密切的线性关系是不妥当的。所以在SPSS、R等统计软件中，在计算相关系数的时，会对相关系数做显著性假设检验。显著性假设检验是为了说明：得到的结果是不是偶然因素导致的（具有统计学意义）；相关系数回答的问题是相关程度强弱。

假如说我得到”P<0.05，相关系数 R=0.279”，意味着二者之间确实（P<0.05）存在相关关系，而相关性为0.279，通俗的说就是有95%的把握说明二者有弱相关关系。

而如果“P>0.05，相关系数R=0.799”，则意味着二者之间相关性很强（R=0.799），而这个高相关的结果可能是偶然因素导致的，即不具有统计学意义，通俗的说就是没有大于95%的把握说明二者有弱相关关系。

需要指出的是，当样本量很小时（<10），一般都无法通过显著性检验，而且相关系数很可能偏大，且随n的变化波动很大。而当样本量很大时（>100），一般都能通过显著性检验，但相关系数很可能偏小，得到一个显著弱相关的无效结果。所以推荐计算两个变量的简单相关系数时，使用30-40个样本。

举例：

select * from temp_test3;


运行结果如下：
temp_test3.xiaoshoue  temp_test3.chengben
20  10
23  12
26  14
29  16
32  18
35  20
38  22
41  24
44  26
47  28
50  30
53  32
56  34
59  23
62  38
65  40
68  42
71  44
74  29
77  48
80  50
83  52
86  54
89  64
92  58
95  60
98  62
101  64
104  66
107  68
计算该公司销售额与成本的相关系数。因为样本数为30，所以避免了相关系数的缺点，假定能通过显著性假设检验。


hive (app)> select corr(xiaoshoue,chengben) as corr from temp_test3;


运行结果如下：
corr
0.9733370471164661
复制

所以该公司销售额与成本的相关系数为0.9733370471164661，说明二者为强正相关。

13、collect_set(col)

功能：将该列中的所有元素去重后以数组的形式返回，可用于列转行。

举例：

select * from temp_test5;


运行结果如下：
temp_test5.shop  temp_test5.shangpin_num
a  1
a  2
a  3
a  3
b  4
b  5
b  6
b  6


select shop
      ,collect_set(shangpin_num)
from temp_test5
group by shop;


运行结果如下：
shop  _c1
a  [1,2,3]
b  [4,5,6]
复制

14、collect_list(col)

功能：将该列中的所有元素以数组的形式返回，与collect_set的唯一区别是不会进行去重

举例：

select shop
      ,collect_list(shangpin_num)
from temp_test5
group by shop;


运行结果如下：
shop  _c1
a  [1,2,3,3]
b  [4,5,6,6
复制

点个在看你最好看

数据库

文章转载自数据分析师的FIRE人生，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

[Hive系列08] 聚合函数详解

评论

相关阅读