tencent cloud

Feedback

Conditional Expressions

Last updated: 2022-03-03 12:08:34

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

    ExpressionSyntaxDescription
    CASE WHENCASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2] [ELSE result3] ENDClassifies data according to specified conditions.
    IFIF(condition, result1)If `condition` is `true`, returns `result1`. Otherwise, returns `null`.
    IF(condition, result1, result2)If `condition` is `true`, returns `result1`. Otherwise, returns `result2`.
    NULLIFNULLIF(expression1, expression2)Determines whether the values of two expressions are equal. If the values are equal, returns `null`. Otherwise, returns the value of the first expression.
    TRYTRY(expression)Captures exception information to enable the system to continue query and analysis operations.
    COALESCECOALESCE(expression1, expression2...)Gets the first non-null value in multiple 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

    Parameter Description
    condition Conditional expression
    result Return result

    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

    Parameter Description
    condition Conditional expression
    result Return result

    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

    Parameter Description
    expression Any valid scalar expression

    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

    Parameter Description
    expression Expression of any type

    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

    Parameter Description
    expression Any valid scalar expression

    Example

    • Query and analysis statement

      * | select COALESCE(null, 'test')
      
    • Query and analysis result

    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support