与 Hive SQL 合并
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13461424/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COALESCE with Hive SQL
提问by Parsa
Since there is no IFNULL, ISNULL, or NVLfunction supported on Hive, I'm having trouble converting NULL to 0. I tried COALESCE(*column name*, 0)but received the below error message:
由于没有IFNULL，ISNULL或NVL支撑在蜂房功能，遇到麻烦转换NULL为0。我试图COALESCE(*column name*, 0)但收到以下错误消息：
Argument type mismatch 0: The expressions after COALESCE should all have the same type: "bigint" is expected but "int" is found
参数类型不匹配 0：COALESCE 之后的表达式都应该具有相同的类型：需要“bigint”但找到“int”
How to resolve this?
如何解决这个问题？
采纳答案by Parsa
As Lamak pointed out in the comment, COALESCE(column, CAST(0 AS BIGINT))resolves the error.
正如拉马克在评论中指出的那样，COALESCE(column, CAST(0 AS BIGINT))解决了错误。
回答by Ivan Klass
Hive supports bigint literal since 0.8 version. So, additional "L" is enough:
Hive从 0.8 版本开始支持bigint 文字。所以，额外的“L”就足够了：
COALESCE(column, 0L)
回答by kanishka vatsa
回答by Amit_Hora
If customer primary contact medium is email, if email is null then phonenumber, and if phonenumber is also null then address. It would be written using COALESCE as
如果客户的主要联系媒介是电子邮件，如果电子邮件为空，则电话号码，如果电话号码也为空，则地址。它将使用 COALESCE 编写为
coalesce(email,phonenumber,address)
while the same in hive can be achieved by chaining together nvl as
而在 hive 中也可以通过将 nvl 链接在一起来实现
nvl(email,nvl(phonenumber,nvl(address,'n/a')))
回答by staticor
From Language DDL & UDF of Hive
NVL(value, default value)
Returns default value if value is null else returns value
如果值为 null 则返回默认值，否则返回值
回答by Srikant
nvl(value,defaultvalue) as Columnname
will set the missing values to defaultvalue specified
将缺失值设置为指定的默认值
回答by Zorayr
From [Hive Language Manual][1]:
来自 [Hive 语言手册][1]：
COALESCE (T v1, T v2, ...)
合并 (T v1, T v2, ...)
Will return the first value that is not NULL, or NULL if all values's are NULL
将返回第一个不是 NULL 的值，如果所有值都是 NULL，则返回 NULL
