Hive SQL 聚合统计函数

更新时间：2021-06-17 15:15:59 标签：sql 聚合 hive

提示

Hive SQL 教程编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》。作者开办 Python 数据分析培训，详情 Python 数据分析培训。

Hive 提供了常用的聚合、集合统计函数，也提供了自己特有的计算函数。基本的聚合函数可以将查询出来的数据做常见的聚合计算和处理。以下这些都是一进一出函数。

本文例子中使用的数据是筛选指定字段中的数据内容。

计数 COUNT

查询结果总共有多少条，有三种形式：

count(*) 返回检索到的行总数，包括包含NULL值的行
count(col) 返回为其提供的列为非 NULL 的行数
count(DISTINCT col[, col]) 返回所提供的列唯一且非NULL的行数

例如：

SELECT COUNT(*)
FROM Students;
'''
COUNT(*)|
--------+
       9|
'''

也可以和 GROUP BY 的子句配合，表示分组后的各组数量。

SELECT gender,
       count(*) AS Qty
FROM Students
GROUP BY gender
'''
gender|Qty|
------+---+
女     |  3|
男     |  6|
'''

需要注意的是：

count(1) 与 count(*) 得到的结果一致，包含 null 值
count(字段) 不计算 null 值
count(null) 结果恒为 0
count 中不能使用算术比较运算符（如=，<或<>）

去重计数 count(distinct)

我时候我们需要不重复值的总数，如一个名单中有小组信息，那我们想知道这个名单中共有几个小组，就需要对小组进行去重后再进行计数：

SELECT count(DISTINCT class) AS Qty
FROM Students
'''
Qty|
---+
  3|
'''

如果再考虑性别，性别和小组组合后去重后计数：

SELECT count(DISTINCT team, gender) AS Qty
FROM Students

但是，对于千万上亿的大量数据来说，上边的方法容易造成「数据倾斜」，所谓「数据倾斜」就是指数据的分散度不够，导致大量的数据集中到了一台或者几台机器上计算，造成执行缓慢、卡死等现象。

解决「数据倾斜」可以使用「group by」如：

SELECT class,
         gender,
         count(*) AS Qty
FROM Students
GROUP BY class,
         gender
'''
class|gender|Qty|
-----+------+---+
    1|女     |  2|
    1|男     |  2|
    2|女     |  1|
    2|男     |  2|
    3|男     |  2|
'''

最大最小值 Max Min

max(col) 和 min(col) 可以取出结果中的最大值和最小值。

SELECT max(math) AS math
FROM Students
'''
math|
----+
  88|
'''

SELECT class,
       max(math) AS math
FROM Students
GROUP BY class;
'''
class|math|
-----+----+
    1|  78|
    2|  88|
    3|  78|
'''

最小值：

SELECT min(math) AS math
FROM Students;

SELECT class,
       min(math) AS math
FROM Students
GROUP BY class;

总数 Sum

sum(col) 返回组中元素的总和或组中列的不同值的总和。

SELECT sum(math) AS total
FROM Students;
'''
total|
-----+
  661|
'''

SELECT class,
       sum(math) AS total
FROM Students
GROUP BY class;
'''
class|total|
-----+-----+
    1|  276|
    2|  230|
    3|  155|
'''

sum(DISTINCT col) 可以让去重后的值相加：

SELECT sum(DISTINCT age) AS total FROM Students;
SELECT team, sum(DISTINCT age) AS total FROM Students GROUP BY team;

平均值 Avg

avg(col)返回组中元素的平均值或组中列的不同值的平均值。

SELECT avg(math) AS total
FROM Students;

SELECT team,
       avg(math) AS total
FROM Students
GROUP BY team;

avg(DISTINCT col) 可以让去重后的值平均：

SELECT avg(DISTINCT age) AS total
FROM Students;

SELECT team,
       avg(DISTINCT age) AS total
FROM Students
GROUP BY team;

collect_list(col)

返回具有重复项的对象列表（array），就是说不会对聚合后的内容去重，可以理解成 python 的中的 list 类型转换。

SELECT team,
       collect_list(name) AS name_list
FROM Students
GROUP BY team;

collect_set(col)

返回去除了重复元素的一组对象（array），对聚合后的内容去重，可以理解成 python 的中的 set 类型转换，集合类型中的元素是不能重复的。

SELECT team,
       collect_set(name) AS name_list
FROM Students
GROUP BY team;

更多内置聚合函数参见。

<< 常用聚合统计函数 Hive SQL 教程 Hive SQL 的操作及运算 >>