学习笔记 - HIVE 3版本中 if 调整 - V2CE

之前使用的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;

学习笔记 – HIVE 3版本中 if 调整