tencent cloud

This document introduces the basic syntax and examples of conditional expressions.

CASE WHEN

The CASE WHEN expression is used to classify data.

Syntax

CASE WHEN condition1 THEN result1
    [WHEN condition2 THEN result2]
    [ELSE result3]
END

Parameter description

Example

• Example 1. Extract browser information from the http_user_agent field, classify the information into the Chrome, Safari, and unknown types, and calculate the PVs of the three types.

  • Query and analysis statement

    * |
    SELECT
    CASE
    WHEN http_user_agent like '%Chrome%' then 'Chrome'
    WHEN http_user_agent like '%Safari%' then 'Safari'
    ELSE 'unknown'
    END AS http_user_agent,
    count(*) AS pv
    GROUP BY
    http_user_agent
    

  • Query and analysis result

• Example 2. Get the statistics on the distribution of different request times.

  • Query and analysis statement

    * |
    SELECT
    CASE
    WHEN request_time < 0.001 then 't0.001'
    WHEN request_time < 0.01 then 't0.01'
    WHEN request_time < 0.1 then 't0.1'
    WHEN request_time < 1 then 't1'
    ELSE 'overtime'
    END AS request_time,
    count(*) AS pv
    GROUP BY
    request_time
    

  • Query and analysis result

IF

The IF expression is used to classify data. It is similar to the CASE WHEN expression.

Syntax

• If condition is true, return result1. Otherwise, return null.

  IF(condition, result1)
  

• If condition is true, return result1. Otherwise, return result2.

  IF(condition, result1, result2)
  

Parameter description

Example

Calculate the proportion of requests with status code 200 to all requests.

• Query and analysis statement

  * |
  SELECT
  sum(IF(status = 200, 1, 0)) * 1.0 / count(*) AS status_200_percentag
  

• Query and analysis result

NULLIF

The NULLIF expression is used to determine whether the values of two expressions are equal. If the values are equal, return null. Otherwise, return the value of the first expression.

Syntax

NULLIF(expression1, expression2)

Parameter description

Example

Determine whether the values of the server_addr and http_host fields are the same. If the values are different, return the value of the server_addr.

• Query and analysis statement

  * | SELECT NULLIF(server_addr,http_host)
  
  

• Query and analysis result

TRY

The TRY expression is used to capture exception information to enable the system to continue query and analysis operations.

Syntax

TRY(expression)

Parameter description

Example

When an exception occurs during the regexp_extract function execution, the TRY expression captures the exception information, continues the query and analysis operation, and returns the query and analysis result.

• Query and analysis statement

  * | 
  SELECT
  TRY(regexp_extract(uri, '.*\/(index.*)', 1)) 
  AS file, count(*) 
  AS count 
  GROUP BY 
  file
  

• Query and analysis result

COALESCE

The COALESCE expression is used to get the first non-null value in multiple expressions.

Syntax

COALESCE(expression1, expression2...)

Parameter description

Example

• Query and analysis statement

  * | select COALESCE(null, 'test')
  

• Query and analysis result