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 ,可以减少代码量。
欢迎交流!!!