Hive内嵌模式缺点 hive case when 嵌套

1. 用法一览

if（条件表达式，结果1，结果2）相当于三目运算符，当条件表达式的结果为true时返回结果1，false时返回结果2。

注意： if后面的表达式类型可以不一样

-- 此表达式结果返回： 'hello' 「string类型」
select 
	if(100 > 50 , 'hello',-100) as res 
from dual ;



-- 此表达式结果返回： -100  「bigint类型」
select 
	if(100 > 500 , 'hello', -100) as res 
from dual ;

简单case when ，类似于单层if

case when condition1 = ture then expression1 
         when condition2 = true then  expression2
             ...
    end

 

嵌套case when ，类似于if 嵌套， 特别当符合条件比较多而且较为复杂时，相当有用

case when condition1 = ture 
      then    
       case when condition condition2 = true 
         then expression2
         ...
       end 
   end

 

2.项目实战

       现有2个集合：商品及其核心码集合A 和 商品的尺码集合B，需要求出每款商品的核心码及其周边的2个尺码【B-1,B,B+1】、4个尺码【B-2,B-1,B,B+1,B+2】集合，倘若核心码附近的尺码不足则反向取离核心码最近的尺码补全，最后的结果以数组格式表示。

       样例数据如下：

         某个款的尺码集合为[046,048,050,052,054,056,058] ，

               如果此款的核心码为050，那么其周边2个尺码与其组成的集合应该为[048,050,052] ；

               如果此款的核心码为 046， 那么其周边2个尺码与其组成的集合应该为[046,048,050] ；

               如果此款的核心码为 058， 那么其周边2个尺码与其组成的集合应该为[054,056,058] ；

         取核心码附近4个尺码的样例数据与此类似，不再赘述。

 

「if应用」取核心码附近2个尺码代码如下：

-- 取核心码附近2个尺码
select 
	 t3.spu_code
	,t3.core_code
	,t3.size_code
	,t3.if_core_code	-- 是否核心码

    -- 当前置位尺码为空「核心码左靠边」时，后置位必然不为空，此时取后置2位的尺码
    -- 当前置位尺码不为空时，若后置位为空「核心右靠边」，那么此时核心码的前置2位必然不为空，此时取前置2位，否则取后置位即可
	,split(if(previois_code is null , 
	  	        concat(next_2_code,',',core_code,',',next_code),
		        concat(previois_code,',',core_code,',',nvl(next_code,previois_2_code))
	    ),',') as code_size_array
from(
  select 
	 t1.str_id
  	,t1.spu_code
	,t1.core_code
	,t2.size_code
	,if(t1.core_code = nvl(t2.size_code,0) , 1 , 0 )  as if_core_code	-- 是否核心码
  	-- 取每个尺码前后各2个尺码备用
	,lag(t2.size_code,2) over(partition by t1.str_id,t1.spu_code order by  t2.size_code + 0 asc  )	as 	previois_2_code
	,lag(t2.size_code,1) over(partition by t1.str_id,t1.spu_code order by  t2.size_code + 0 asc  )	as 	previois_code
	,lead(t2.size_code,1) over(partition by t1.str_id,t1.spu_code order by t2.size_code + 0 asc  ) as next_code
	,lead(t2.size_code,2) over(partition by t1.str_id,t1.spu_code order by t2.size_code + 0 asc  ) as next_2_code
	-- ,if(previois_code is null , 
  from( 
	select
	   str_id
	  ,spu_code		-- 款号
	  ,core_code 		-- 核心码
	from tmp_ads_bkd_core_code_0020_006 
	where ds = 20190824 
	and spu_code = '2192K56908'
  ) t1 left outer join(
	select 
	 goods_style_code as spu_code 		-- 款号
	,goods_size_code as size_code 		-- 尺码
	from dim_xgjnvn_sku_goods 
	where ds = 20190824 
	and goods_style_code = '2192K56908'
  ) t2 
  on t1.spu_code = t2.spu_code
) t3 
where t3.if_core_code = 1 
;

 

   「case when 嵌套应用」 取核心码附近4个尺码的核心代码如下：

-- 取核心码附近4个尺码
select 
     t3.spu_code
    ,t3.core_code
    ,t3.size_code
    ,t3.if_core_code    -- 是否核心码
    ,split(case when previois_2_code is not null 
            then case when next_2_code is not null  -- 当B-2和B+2均不为空时， 取 B-2,B-1,B,B+1,B+2
                        then concat(previois_2_code,',',previois_code,',',core_code,',',next_code,',',next_2_code)
                    
                      when next_2_code is null -- 当B-2不为空，但是B+2为空时，需要判断B+1的值    
                        then case when next_code is not null    -- 当B-2不为空，但是B+2为空，B+1 不为空时，取 B-2,B-1,B.B+1,B-3                        
                                    then concat(previois_2_code,',',previois_code,',',core_code,',',next_code,',',previois_3_code)
                                  when next_code is null    -- 当B-2不为空，但是B+2与B+1皆为空时 ， 取 B-2,B-1,B,B-3,B-4                        
                                    then concat(previois_2_code,',',previois_code,',',core_code,',',previois_3_code,',',previois_4_code)
                             end 
                 end 
         when previois_2_code is null 
            then case when previois_code is not null    -- 当B-2为空，B-1不为空时，取 B+3,B-1,B,B+1,B+2                    
                        then concat(next_3_code,',',previois_code,',',core_code,',',next_code,',',next_2_code)
                      when previois_code is null    -- 当 B-2为空，B-1也为空时， 取 B+4,B+3,B,B+1,B+2
                        then concat(next_4_code,',',next_3_code,',',core_code,',',next_code,',',next_2_code)
                 end 
    end,',') as code_size_array     
from(
  select 
     t1.str_id
    ,t1.spu_code
    ,t1.core_code
    ,t2.size_code
    ,if(t1.core_code = nvl(t2.size_code,0) , 1 , 0 )  as if_core_code   -- 是否核心码
    -- 取每个尺码前后各4个尺码备用
    ,lag(t2.size_code,4) over(partition by t1.str_id,t1.spu_code order by  t2.size_code + 0 asc  )  as  previois_4_code
    ,lag(t2.size_code,3) over(partition by t1.str_id,t1.spu_code order by  t2.size_code + 0 asc  )  as  previois_3_code
    ,lag(t2.size_code,2) over(partition by t1.str_id,t1.spu_code order by  t2.size_code + 0 asc  )  as  previois_2_code
    ,lag(t2.size_code,1) over(partition by t1.str_id,t1.spu_code order by  t2.size_code + 0 asc  )  as  previois_code
    ,lead(t2.size_code,1) over(partition by t1.str_id,t1.spu_code order by t2.size_code + 0 asc  ) as next_code
    ,lead(t2.size_code,2) over(partition by t1.str_id,t1.spu_code order by t2.size_code + 0 asc  ) as next_2_code
    ,lead(t2.size_code,3) over(partition by t1.str_id,t1.spu_code order by t2.size_code + 0 asc  ) as next_3_code
    ,lead(t2.size_code,4) over(partition by t1.str_id,t1.spu_code order by t2.size_code + 0 asc  ) as next_4_code
    -- ,if(previois_code is null , 
  from( 
    select
       str_id
      ,spu_code     -- 款号
      ,core_code        -- 核心码
    from tmp_ads_bkd_core_code_0020_006 
    where ds = 20190824 
    and spu_code = '2192K56908'
  ) t1 left outer join(
    select 
     goods_style_code as spu_code       -- 款号
    ,goods_size_code as size_code       -- 尺码
    from dim_xgjnvn_sku_goods 
    where ds = 20190824 
    and goods_style_code = '2192K56908'
  ) t2 
  on t1.spu_code = t2.spu_code
) t3 
where t3.if_core_code = 1 
 ;

以上逻辑稍显复杂，附上流程图，如下：

 

3. 个人想法

    嵌套的case when 比起联合条件下的case when「when conditionA = true and conditionB = true  and ...」 来代码量可能要大一些，可是好早在逻辑够清楚，各个分支的控制条件都一目了然，方便调试，快速定位到问题所在，如果算上调试、测试时间，总的来说会更好一些。

   让程序中的判断不是非常复杂时，建议用if语句替代case when ，可以减少代码量。

   欢迎交流！！！