Log Service supports CASE WHEN syntax to classify the continuous data. For example, extract the 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

Example

  • The ratio of requests with 200 as the computing status code to the total number of requests:
    * | SELECT 
 sum(
 CASE 
 WHEN status =200 then 1
 ELSE 0 end
 ) *1.0 / count(1) as status_200_percentage
  • Make statistics of the distribution of different latency intervals
    * | 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 condition is true, the column true_value is returned, otherwise null.

  • if(condition, true_value, false_value)

    If condition is true, the column true_value is returned, otherwise the column false_value is returned.

Coalesce syntax

Coalesce returns the first non-null value for multiple columns.

Coalesce (value1, value2 [,...])

NULLIF syntax

If value1 and value2 are equal, null is returned, otherwise value1 is returned.

nullif(value1, value2)

TRY syntax

The try syntax can catch some of the underlying exceptions, such as the 0 error, to return a null value. 

try(expression)