HIVE 3版本中 if 调整

原创

王旭平

修改于 2022-12-05 20:17:15

3661

修改于 2022-12-05 20:17:15

文章被收录于专栏：大数据生态

之前使用的0.10版本的hive，hql查询if语句如下：

SELECT 
IF(split(m.phone_platform,'_')[1] LIKE '4%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'k' ,'4.X',       
IF(split(m.phone_platform,'_')[1] LIKE '5%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'l','5.X',
IF(split(m.phone_platform,'_')[1] LIKE '6%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'm','6.X',
IF(split(m.phone_platform,'_')[1] LIKE '7%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'n','7.X',
IF(split(m.phone_platform,'_')[1] LIKE '8%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'o','8.X',
IF(split(m.phone_platform,'_')[1] LIKE '9%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'p','9.X','Other')))))) AS aid,n.p_id,COUNT(mei) AS sum_mei,pt 
FROM  md_st m 
join ct_p n 
on m.p_code = n.p_code where m.pt >= DATE_SUB('2022-12-11',10)  
GROUP BY 
IF(split(m.phone_platform,'_')[1] LIKE '4%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'k' ,'4.X',       
IF(split(m.phone_platform,'_')[1] LIKE '5%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'l','5.X',
IF(split(m.phone_platform,'_')[1] LIKE '6%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'm','6.X',
IF(split(m.phone_platform,'_')[1] LIKE '7%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'n','7.X',
IF(split(m.phone_platform,'_')[1] LIKE '8%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'o','8.X',
IF(split(m.phone_platform,'_')[1] LIKE '9%' OR LOWER(split(m.phone_platform,'_')[1]) LIKE 'p','9.X','Other')))))),n.p_id,pt;

调整后的

SELECT 
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '4%' ,'4.X',       
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '5%' ,'5.X',
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '6%' ,'6.X',
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '7%' ,'7.X',
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '8%' ,'8.X',
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '9%' ,'9.X','Other')))))) AS aid,n.p_id,COUNT(mei) AS sum_mei,pt 
FROM  md_st m 
join ct_p n 
on m.p_code = n.p_code where m.pt >= DATE_SUB('2022-12-11',10)  
GROUP BY 
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '4%' ,'4.X',       
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '5%' ,'5.X',       
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '6%' ,'6.X',       
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '7%' ,'7.X',       
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '8%' ,'8.X',       
IF(split(m.phone_platform,'_')[size(split(m.phone_platform, '_'))-1] LIKE '9%' ,'9.X','Other')))))),n.p_id,pt;

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

emr