配置单元中的sql嵌套if语句

ia2d9nvy  于 2021-06-27  发布在  Hive
关注(0)|答案(2)|浏览(371)

我想把if语句复制到我的配置单元代码中。基本上,我在2014-2018年为一个相应的客户消费,我试图了解客户停止与我们消费的最后一年,以及以美元为单位的损失是基于上一年的消费。我不能做一个case语句,因为每个if语句都有几个变量,但我愿意接受建议!

If (FY_2014 > 0, (If(FY_2015 = 0, FY_2014,0)), 0) as '2014to2015'
    If (FY_2015 > 0, (If(FY_2016 = 0, FY_2015,0)), 0) as '2015to2016'
        If (FY_2016 > 0, (If(FY_2017 = 0, FY_2016,0)), 0) as '2016to2017'
            If (FY_2017 > 0, (If(FY_2018 = 0, FY_2017,0)), 0) as'2017to2018'

提前感谢你们的帮助!

r6vfmomb

r6vfmomb1#

我认为在更少的条件下,这也能起到同样的作用。

case when FY_2014 > 0 AND FY_2015 = 0 then FY_2014 else 0 end as "2014to2015",
case when FY_2015 > 0 AND FY_2016 = 0 then FY_2015 else 0 end as "2015to2016",
case when FY_2016 > 0 AND FY_2017 = 0 then FY_2016 else 0 end as "2016to2017",
case when FY_2017 > 0 AND FY_2018 = 0 then FY_2017 else 0 end as "2017to2018"
vxqlmq5t

vxqlmq5t2#

一个嵌套的案子怎么样?

case when FY_2014 > 0 then case when FY_2015 = 0 then FY_2014 else 0 end else 0 end as "2014to2015",
   case when FY_2015 > 0 then case when FY_2016 = 0 then FY_2015 else 0 end else 0 end as "2015to2016",
   case when FY_2016 > 0 then case when FY_2017 = 0 then FY_2016 else 0 end else 0 end as "2016to2017",
   case when FY_2017 > 0 then case when FY_2018 = 0 then FY_2017 else 0 end else 0 end as "2017to2018"

相关问题