hive对有null值的列进行avg,sum,count等操作时会不会过滤null值

时间:2021-07-02 20:10:40

在hive中,我们经常会遇到对某列进行count、sum、avg等操作计算记录数、求和、求平均值等,但这列经常会出现有null值的情况,那这些操作会不会过滤掉null能呢?

下面我们简单测试下:

with tmp as
(
select null as col1
union all
select 666 as col1
union all
select 999 as col1
)
select avg(col1) avg_numm, sum(col1) sum_num, count(1) cnt, count(col1) cnt_col1
from tmp

832.5 1665 3 2
1
2
3
4
5
6
7
8
9
10
11
12
从结果中很容易得出结论:avg、sum、count都会过滤掉null值
---------------------