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
- 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
The IF syntax is logically equivalent to the CASE WHEN syntax.
CASE WHEN condition THEN true_value [ ELSE false_value ] END
If the condition is true, true_value is returned. Otherwise, null is returned.
- if(condition, true_value,
If the condition is true, true_value is returned. Otherwise, the false_value column is returned.
The COALESCE syntax returns the first non-NULL value from multiple columns.
If value1 equals value2, null is returned. Otherwise, value1 is returned.
The TRY syntax catches some of the underlying exceptions such as division by zero errors, and returns null.