The CASE WHEN syntax is used to classify continuous data. For example, you can use the CASE WHEN syntax to extract information from http_user_agent and classify the information into two types: Android and iOS.
SELECT
CASE
WHEN http_user_agent like '%android%' then 'android'
WHEN http_user_agent like '%ios%' then 'ios'
ELSE 'unknown' END
as http_user_agent,
count(1) as pv
group by http_user_agent
Examples
- Calculate the proportion of requests with status code 200 to all requests:
* | SELECT sum( CASE WHEN status =200 then 1 ELSE 0 end ) *1.0 / count(1) as status_200_percentage
- Calculate the distribution of latencies:
* | SELECT ` CASE WHEN latency < 10 then 's10' WHEN latency < 100 then 's100' WHEN latency < 1000 then 's1000' WHEN latency < 10000 then 's10000' else 's_large' end as latency_slot, count(1) as pv group by latency_slot
IF syntax
The IF syntax is logically equivalent to the CASE WHEN syntax.
CASE
WHEN condition THEN true_value
[ ELSE false_value ]
END
- if(condition,
true_value)
If the condition is true, true_value is returned. Otherwise, null is returned.
- if(condition, true_value,
false_value)
If the condition is true, true_value is returned. Otherwise, the false_value column is returned.
COALESCE syntax
The COALESCE syntax returns the first non-NULL value from multiple columns.
coalesce(value1, value2[,...])
NULLIF syntax
If value1 equals value2, null is returned. Otherwise, value1 is returned.
nullif(value1, value2)
TRY syntax
The TRY syntax catches some of the underlying exceptions such as division by zero errors, and returns null.
try(expression)