Hive-- case when 和substr

case when 两种用法

总结一下：两种表达方式是等效的

方法一：case when tb1.os = 'android' then 'android'when tb1.os = 'ios' then 'iPhone'else 'PC'end as os,

方法二：

case tb1.os
when 'android' then 'android'
when 'ios' then 'iPhone'
else 'PC'
end as os,

substr

with log as (
SELECT substr(pt, 0, 8) AS pt
, case when get_json_object(data, '$.request.business_prefer.dmp_prefer') is null then 0 else 1 end as dmp
, case when get_json_object(data, '$.request.statistic_user_prefer') is null then 0 else 1 end as stap
, get_json_object(data, '$.response.data.recommend_result[0].strategy_info.fb_combine_ab_test_id') as ab
, 1 as cc
FROM dw.dw_log_reco_app_01_hi
WHERE pt BETWEEN '20210514000000' AND '20210527230000'
AND data LIKE '%reco_rule-c006%')
select pt,ab,sum(dmp) as dmp_count ,sum(stap) as sta_count,sum(cc) as cou from log group by pt,ab