我有一个查询,它返回一列STATUS,它可以有两个不同的值SUCCESS和FAILED。因此,有时它只返回SUCCESS或FAILURE或SUCCESS和FAILURE两者。我的要求是为snowflake编写一个SQL查询,当值仅为SUCCESS时返回1,当值为FAILURE或同时为SUCCEESS和FAILURE时返回0。
我想你想要conditional aggregation
。如果你喜欢的话,可以用case
重写iff
。
场景1
with cte(col)as
(select 'SUCCESS' union all
select 'FAILURE')
select min(iff(col='SUCCESS',1,0)) as status
from cte;
场景2
with cte(col)as
(select 'SUCCESS' union all
select 'SUCCESS')
select min(iff(col='SUCCESS',1,0)) as status
from cte
您可以使用CASE语句,
select CASE WHEN COLUMN1 = 'SUCESS' THEN 1
ELSE 0 END ret_value from
values
('SUCESS'), ('FAILURE'), ('SUCCESS and FAILURE');