hive对有null值的列进行avg,sum,count等聚合性操作结果测试

hive对有null值的列进行avg,sum,count等聚合性操作结果测试

数值、null、字符串等联合测试

with tmp as
(
select NULL as col1
 union all
select 'NULL' as col1
 union all
select null as col1
 union all
select 3 as col1
 union all
select 2 as col1
 union all
select '2' as col1
)
select avg(col1) avg_numm, sum(col1) sum_num, count(1) cnt, count(col1) cnt_col1
from tmp;

查询结果：无影响，且会自动过滤null、字符串的影响操作

+---------------------+----------+------+-----------+--+
|      avg_numm       | sum_num  | cnt  | cnt_col1  |
+---------------------+----------+------+-----------+--+
| 2.3333333333333335  | 7.0      | 6    | 4         |
+---------------------+----------+------+-----------+--+

参考：https://blog.csdn.net/lz6363/article/details/91041435