hive踩坑记录：count(distinct col1,col2) 遇见某列中有null值，结果不准

count(distinct col1,col2) 遇见某列中中有null值，结果不准

SELECT count(DISTINCT col1,col2)

from

(SELECT 2 as col1,1 as col2

union all

SELECT null as col1,2 as col2

union all

SELECT null as col1,3 as col2

union all

SELECT null as col1,4 as col2

)as tmp

count的结果为1

 

count(distinct col1,col2) 遇见某列中中有null值，结果不准

SELECT count(DISTINCT col1,col2)

from

(SELECT null as col1,1 as col2

union all

SELECT null as col1,2 as col2

union all

SELECT null as col1,3 as col2

union all

SELECT null as col1,4 as col2

)as tmp

count的结果为一

 

 

 

原因:本以为是将所有的null全部归为一行实际上上，实际上某列为null时整个就不算了

解决方案：将null值用个统一字符串替换