函数 CASE 可以根据条件将一个或多个值计算为一个新的值。一但命中某个 WHEN 中的条件即生成对应 THEN 中的值,而不继续匹配其余的条件。
语法1:
case
when <condition> then <output>
when <condition> then <output>
...
else <otherOutput>
end
语法2:
case <expression>
when <value> then <output>
when <value> then <output>
...
else <otherOutput>
end
- <output> 是条件 <condition> 为真(语法1)或表达式 <expression> 等于 <value>(语法2)时的输出值
- <otherOutput> 是不满足任何条件时的输出值,不填写 ELSE 语句则输出默认值(数据类型与 <output> 保持一致)
本函数所涉及的关键词 CASE、WHEN、THEN、ELSE、END 大小写不敏感。
一般用法
示例:计算 2023 年每月的拟发薪日(15 号)是星期几
uncollect ["2023-1-15","2023-2-15","2023-3-15","2023-4-15","2023-5-15","2023-6-15","2023-7-15","2023-8-15","2023-9-15","2023-10-15","2023-11-15","2023-12-15"] as payday
return CASE dayOfWeek(payday)
when 1 then "Sunday"
when 2 then "Monday"
when 3 then "Tuesday"
when 4 then "Wednesday"
when 5 then "Thursday"
when 6 then "Friday"
when 7 then "Saturday"
END
Sunday
Wednesday
Wednesday
Saturday
Monday
Thursday
Saturday
Tuesday
Friday
Sunday
Wednesday
Friday
示例:计算 2023 年每月的实际发薪日,如果拟发薪日为周末则顺延至下个星期一
uncollect ["2023-1-15","2023-2-15","2023-3-15","2023-4-15","2023-5-15","2023-6-15","2023-7-15","2023-8-15","2023-9-15","2023-10-15","2023-11-15","2023-12-15"] as payday
return CASE dayOfWeek(payday)
when 1 then dateAdd(payday, 1, "day")
when 7 then dateAdd(payday, 2, "day")
else dateAdd(payday, 0, "day")
END
分析:WHEN 中,函数 dateAdd() 对处于周末的拟发薪日 payday 进行偏移并输出为时间类型;ELSE 对应 payday 为工作日的情况,但不能直接输出 payday,需使用 dateAdd() 函数转为时间类型,与 WHEN 保持一致。
2023-01-16 00:00:00
2023-02-15 00:00:00
2023-03-15 00:00:00
2023-04-17 00:00:00
2023-05-15 00:00:00
2023-06-15 00:00:00
2023-07-17 00:00:00
2023-08-15 00:00:00
2023-09-15 00:00:00
2023-10-16 00:00:00
2023-11-15 00:00:00
2023-12-15 00:00:00