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)