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

总结

• avg、sum、count、max、min
  • 具体列会过滤掉null值（_{如sum(column_1)}）
    • 多列联合若有任何一列为NULL，也会过滤
  • 常量不过滤（_如sum(1)）
  • count比较特殊，如果全是null值结果会是0

1. 普通的count、sum、avg、max、min会不会过滤呢

• 在hive中，我们经常会遇到对某列进行count、sum、avg、max、min等操作计算记录数、求和、求平均值等，但这列经常会出现有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
    , max(col1) max_col1
    , min(col1) min_col1
from tmp;

avg_numm	sum_num	    cnt  cnt_col1	max_col1	min_col1	
832.5	    1665	    3	    2	     999	     666	

• 从结果中很容易得出结论：avg、sum、count、max、min都会过滤掉null值
• 但是需要注意的是count(1)的结果是3

2. 两列(一列为null)联合去重会不会过滤呢

• 使用count distinct计算两列联合去重时，若有任何一列为NULL，那么count计数时就会略过这一条。
  • 如：count(distinct a,b) 若a或者b中有一个是null，那么这一行就不会参与计数
    平台：hive、mysql都是如此

id	tools	name
1	hive	a
2	hive	b
3	hive	NULL
4	NULL	m

select count(distinct tools,name) from test;
--2

• 所以在存储数据时一定要注意NULL值的处理，如将字符串类型写成‘UNKNOWN’等，

3. count比较特殊，如果全是null值结果会是0

select name
    , avg(id) avg_numm
	, sum(id) sum_num
	, count(1) cnt
	, count(id) cnt_id
    , max(id) max_id
    , min(id) min_id 
from (  select 1 as id ,'张三' as name
    union all select 2 as id ,'张三' as name
    union all select null as id ,'张三' as name
    union all select null as id ,'李四' as name
)a
group by name
order by name 
;

name	avg_numm	sum_num	cnt	cnt_id	max_id	min_id
张三	1.5	3	3	2	2	1
李四	NULL	NULL	1	0	NULL	NULL