Hive中的CASE语句 [英] CASE statements in Hive

问题描述

好的，我有一个下面的代码来标记tabl中带有二进制标志的month_cd最高的记录：

 选择t1。 month_cd，t2.max_month_cd 
，CASE WHEN t2.max_month_cd！= null then 0 else 1 end test_1 
，CASE WHEN t2.max_month_cd = null then 0 else 1 end test_2 
 from source t1 
左连接（
）选择
 MAX（month_cd）作为max_month_cd 
从源
）t2 
 on t1.month_cd = t2.max_month_cd; 
  

看起来很直截了当，但返回的结果是：

  month_cd max_month_cd test_1 test_2 
 201610 null 1 1 
 201611 201611 1 1 
  

对我来说意义不大，似乎太明显，不会成为执行引擎中的错误。我缺少什么？

解决方案

有关于NULL的所有概念。由于Null不是任何数据域的成员，因此它不被视为值，而是指示缺少价值的标记（或占位符）。因此，与Null进行比较永远不会导致True或False，但总是会出现在第三个逻辑结果Unknown中。 NULL不是什么，没有对象。所以，没有什么不能等于NULL或其他东西。在SQL中有用于测试空值的 IS NULL 和 IS NOT NULL 条件。在您的CASE中，逻辑表达式的结果是未知的，因此ELSE值被赋值。

更正后的版本：

  CASE WHEN t2.max_month_cd IS NOT NULL then 0 else 1 end test_1 
，CASE WHEN t2.max_month_cd IS null then 0 else 1 end test_2 
  

请参阅以下内容以供参考： https://en.wikipedia.org/wiki/Null_（SQL）＃Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29

Ok, i have a following code to mark records that have highest month_cd in tabl with binary flag:

Select t1.month_cd, t2.max_month_cd
  ,CASE WHEN t2.max_month_cd != null then 0 else 1 end test_1
  ,CASE WHEN t2.max_month_cd = null then 0 else 1 end test_2
from source t1
Left join (
  Select 
    MAX(month_cd) as max_month_cd 
  From source 
) t2 
on t1.month_cd = t2.max_month_cd;

It seems straight forward to me, but result it return is:

month_cd  max_month_cd  test_1  test_2
201610    null          1       1
201611    201611        1       1

Makes zero sense to me, and seems to be way too obvious to be a bug in execution engine. What am i missing?

解决方案

There is all about NULL concept. Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown. NULL is nothing, absence of object. So, nothing can NOT be equal to NULL or something else. In SQL there are IS NULL and IS NOT NULL conditions to be used for test for null values. In your CASE the result of logical expression is unknown, so ELSE value is assigned.

Corrected version:

CASE WHEN t2.max_month_cd IS NOT null then 0 else 1 end test_1
,CASE WHEN t2.max_month_cd IS null then 0 else 1 end test_2

See this for reference: https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29

这篇关于Hive中的CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！