Hive中NULLIF的替换是什么? [英] What is the replacement of NULLIF in Hive?

问题描述

我想知道Hive中NULLIF的替代品是什么?我正在使用COALESCE，但不能满足我的要求.我的查询语句类似于:

I would like to know what is the replacement of NULLIF in Hive? I am using COALESCE but its not serving my requirement. My query statement is something like :

COALESCE(A,B,C) AS D

COALESCE将返回第一个NOT NULL值.但是我的A/B/C包含空白值，因此COALESCE并未将该值分配给D，因为它认为空白为NOT NULL.但是我希望将正确的值分配给D.

COALESCE will return first NOT NULL value. But my A/B/C contain blank values so COALESCE is not assigning that value to D as it is considering blank as NOT NULL. But I want the correct value to be get assign to D.

在SQL中，我本可以使用COALESCE(NULLIF(A,'')......)，所以它也会检查空白.我尝试了CASE，但无法正常工作.

In SQL I could have use COALESCE(NULLIF(A,'')......) so it will check for blank as well. I tried CASE but it's not working.

推荐答案

只需使用case:

select (case when A is null or A = '' then . . . end)

这是标准的SQL，因此可以在Hive和其他地方使用.

This is standard SQL, so it will work in Hive and elsewhere.

针对您的特定问题:

select (case when A is not null and A <> '' then A
             when B is not null and B <> '' then B
             else C
        end)

您实际上可以将其缩短为:

You can actually shorten this to:

select (case when A <> '' then A
             when B <> '' then B
             else C
        end)

因为NULL值比较失败.我会使用此版本，但是经常学习SQL的人更喜欢使用not null比较的更明确的版本.

because NULL values fail comparisons. I would use this version but often people learning SQL prefer the more explicit version with the not null comparison.

这篇关于Hive中NULLIF的替换是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！