将空值替换为0(hadoop配置单元)

axzmvihb 于 2021-05-27 发布在 Hadoop

关注(0)|答案(1)|浏览(454)

在执行左连接之后，我得到了许多空值。如何在同一查询中的某些列中用0替换这些空值？

select 
    m1.*, t2.Apple, t3.Berry, t4.Cherry
from
    table1 as t1
    left join table2 as t2 on t1.id = t2.id
    left join table3 as t3 on t1.id = t3.id
    left join table3 as t4 on t1.id = t4.id
;

示例输出

ID  Apple     Berry    Cheery
1   1         NULL     1
2   1         NULL     NULL
3   NULL      1        NULL
4   NULL      NULL     NULL

sql hadoop Hive left-join sql-null

来源：https://stackoverflow.com/questions/63891981/sql-hive-replace-null-values-with-0-hadoop-hive

1条答案

按热度按时间

e3bfsja21#

你可以用 coalesce() 替换 null 价值观 0 学生：

select 
    t1.*, 
    coalesce(t2.Apple,  0) as apple, 
    coalesce(t3.Berry,  0) as berry, 
    coalesce(t4.Cherry, 0) as cherry
from
    table1 as t1
    left join table2 as t2 on t1.id = t2.id
    left join table3 as t3 on t1.id = t3.id
    left join table4 as t4 on t1.id = t4.id
;

请注意，这假设所有3个fruit列都是数字数据类型。
旁注：我修复了原始查询中表别名的一些拼写错误。

赞(0）回复(0）举报 2021-05-27

我来回答

将空值替换为0(hadoop配置单元)

1条答案

相关问题

热门标签

最新问答