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 1.30.22/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 |