tencent cloud

Conditional Expressions
Download PDF
Last updated: 2024-01-22 10:52:48
Conditional Expressions
Last updated: 2024-01-22 10:52:48
Download PDF
This document introduces the basic syntax and examples of conditional expressions.
Expression
Syntax
Description
CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2] [ELSE result3] END
Classifies data according to specified conditions.
IF(condition, result1)
If `condition` is `true`, returns `result1`. Otherwise, returns `null`.
IF(condition, result1, result2)
If `condition` is `true`, returns `result1`. Otherwise, returns `result2`.
NULLIF(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.
TRY(expression)
Captures exception information to enable the system to continue query and analysis operations.
COALESCE(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.
* |
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

Example 2

Get the statistics on the distribution of different request times.
* |
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

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.

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

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.
* | SELECT NULLIF(server_addr,http_host)

TR

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.
* | 
SELECT
TRY(regexp_extract(uri, './(index.)', 1)) 
  AS file, count(*) 
  AS count 
GROUP BY 
file

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

* | select COALESCE(null, 'test')

Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback