hive 查询去除空值_将HIVE查询结果中的空值或NULL值替换为特定值

I'm trying to show a default value, "Others", when the query does not return any result for one of the selected columns. I'll show you the example.

This query returns an empty value for os(agent) SO (in the first row):

select country, os(agent) SO, count(*) from clicks_data

where country is not null and os(agent) is not null

group by country, os(agent);

Output:

ZA 4

ZA Android 4

ZA Mac 8

ZA Windows 5

Instead, I would like to get this result:

ZA Others 4

ZA Android 4

ZA Mac 8

ZA Windows 5

My next attempt was this query, but it's not really working, either:

select country, regexp_replace(os(agent),'','Others') SO, count(*) from clicks_data

where country is not null and os(agent) is not null

group by country, os(agent);

This is the result:

ZA Others 4

ZA OthersAOthersnOthersdOthersrOthersoOthersiOthersdOthers 4

ZA OthersMOthersaOtherscOthers 8

ZA OthersWOthersiOthersnOthersdOthersoOtherswOtherssOthers 5

解决方案

Use LENGTH() to check the length of the column value. It returns > 0, if there is some value else return 0 for empty or NULL value.

Also frame the column value in CASE WHEN ... END block

The final query may look like:

SELECT country, CASE WHEN LENGTH(os(agent)) > 0 THEN os(agent) ELSE 'Others' END AS SO, COUNT(*)

FROM clicks_data

WHERE country IS NOT NULL AND os(agent) IS NOT NULL

GROUP BY country, os(agent);

Hope this help you!!!