Hive SQL 聚合统计函数
提示
Hive SQL 教程 欢迎使用。提供建议、纠错、催更等加作者微信: gairuo123（备注：sql ）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》 和 《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;
更多内置聚合函数参见。
更新时间：2021-06-17 15:15:59 标签：sql 聚合 hive
