HiveSQL的CASE-WHEN的使用

HiveSQL的CASE-WHEN的使用

case when 条件 then 条件为真时的值 else 条件为假时的值 end

SELECT 
DATE(DATE_SUB(A.CREATE_TIME, -CAST(B.CYCLE AS INT))),
DATE(DATE_SUB(A.CREATE_TIME, -CAST(B.CYCLE AS INT))),
FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd'),

CASE WHEN 
DATE(DATE_SUB(A.CREATE_TIME, -CAST(B.CYCLE AS INT))) <= FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd') 
THEN DATE(DATE_SUB(A.CREATE_TIME, -CAST(B.CYCLE AS INT))) 
ELSE NULL
END AS TIME_EXIT 

FROM ODS.S02_ACT_WEIXINVC A 
INNER JOIN ODS.S02_ACT_WYH_PRODUCT B 
ON A.PRODUCT_ID = B.ID 
AND A.PRODUCT_ID IN 
(1881560122394597, 1881560122394596, 1881560122394598)  ;

如果只是简单地判断是否为空，赋个默认值用 nvl 函数：

select nvl(cast(mem.mb_last_login_time as String),'1900-01-01 00:00:00')
FROM ods.s02_member mem where mid = 1816945285831115;