case
The case
expression goes through conditions and returns a value when the
first condition is met (like an if-else statement).
The case
expression must contain at least one when...then... expression.
Once a when
condition is true, it will stop reading and return the
result specified in the then
clause. If no conditions are true, it
returns the value in the else
clause. If there is no
else
part and no conditions are true, it returns NULL. The return
type of all conditions in a case
expression must be the same.
The case...when...
expression can be used in distributed queries.
Syntax
case
when condition_1 then expression_1
[when condition_2 then expression_2]
...
[when condition_n then expression_n]
[else expression_end]
end
Starting from version 2.00.10, the when and then clauses of a CASE expression no longer need to be written on a single line.
Examples
t = table(`st0`st1`st2`st4`st5`st6 as sym, 80 200 150 220 130 190 as vol)
select sym, case
when vol < 100 then -1
when vol < 200 then 0
else 1
end as flag
from t
sym | flag |
---|---|
st0 | -1 |
st1 | 1 |
st2 | 0 |
st4 | 1 |
st5 | 0 |
st6 | 0 |
t = table(2022.01.01 2022.01.01 2022.01.01 2022.01.01 2022.01.01 2022.01.01 as date, `a`a`b`b`a`b as id, 300 290 302 296 304 320 as val)
select date, case when t.id == `a then val end as `GroupA, case when t.id == `b then val end as `GroupB from t
date | GroupA | GroupB |
---|---|---|
2022.01.01 | 300 | |
2022.01.01 | 290 | |
2022.01.01 | 302 | |
2022.01.01 | 296 | |
2022.01.01 | 304 | |
2022.01.01 | 320 |
select sum(val) as total from t group by case when t.val < 300 then 0 else 1 end as flag
flag | total |
---|---|
1 | 1,226 |
0 | 586 |
Use analytic functions in a case
expression.
t = table([2023.06M,2023.07M,2023.08M,2023.07M,2023.07M,2023.06M,2023.08M,2023.06M,2023.08M] as month, ["A","A","B","B","C","C","A","B","C"] as flag, [1200,1500,1200,1300,1400,1400,1400,1000,1300] as val)
t
month |
flag |
val |
---|---|---|
2023.06M | A | 1,200 |
2023.07M | A | 1,500 |
2023.08M | B | 1,200 |
2023.07M | B | 1,300 |
2023.07M | C | 1,400 |
2023.06M | C | 1,400 |
2023.08M | A | 1,400 |
2023.06M | B | 1,000 |
2023.08M | C | 1,300 |
For flag "A", calculate the value deviation from monthly average. For other flags, calculate the standard deviation of value for the month.
select month,flag,(case when flag = "A"
then (val - avg(val)over(partition by month))
else std(val)over(partition by month) end) as value
from t
month |
flag |
value |
---|---|---|
2023.06M | A | 0 |
2023.07M | A | 100 |
2023.08M | B | 100 |
2023.07M | B | 100 |
2023.07M | C | 100 |
2023.06M | C | 200 |
2023.08M | A | 100 |
2023.06M | B | 200 |
2023.08M | C | 100 |