All Products
Search
Document Center

Simple Log Service:Comparison operators

Last Updated:Jun 05, 2024

Comparison operators are used to compare parameter values. The values that are of the following data types can be compared: double, bigint, varchar, timestamp, and date. This topic describes the syntax of comparison operators. This topic also provides examples on how to use the comparison operators.

The following table describes the comparison operators that are supported by Simple Log Service.

Important If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.

Operator

Syntax

Description

Supported in SQL

Supported in SPL

Relational operators

x < y

If x is less than y, true is returned.

x > y

If x is greater than y, true is returned.

x <= y

If x is less than or equal to y, true is returned.

x >= y

If x is greater than or equal to y, true is returned.

x = y

If x is equal to y, true is returned.

x <> y

If x is not equal to y, true is returned.

x != y

If x is not equal to y, true is returned.

ALL operator

x relational operator ALL(subquery)

If x meets all conditions, true is returned.

×

ANY operator

x relational operator ANY(subquery)

If x meets one of the conditions, true is returned.

×

BETWEEN operator

x BETWEEN y AND z

If x is between y and z, true is returned.

DISTINCT operator

x IS DISTINCT FROM y

If x is not equal to y, true is returned.

×

x IS NOT DISTINCT FROM y

If x is equal to y, true is returned.

×

LIKE operator

x LIKE pattern [escape 'escape_character']

Matches a specified character pattern in a string. The string is case-sensitive.

SOME operator

x relational operator SOME(subquery)

If x meets one of the conditions, true is returned.

×

GREATEST operator

GREATEST(x, y...)

Obtains the greater value of x and y.

×

LEAST operator

LEAST(x, y...)

Obtains the smaller value of x and y.

×

NULL operator

x IS NULL

If x is null, true is returned.

x IS NOT NULL

If x is not null, true is returned.

Relational operators

Relational operators compare x and y. If the condition is met, true is returned.

Syntax

Syntax

Description

x < y

x is less than y.

x > y

x is greater than y.

x <= y

x is less than or equal to y.

x >= y

x is greater than or equal to y.

x = y

x is equal to y.

x <> y

x is not equal to y.

x != y

x is not equal to y.

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

y

The value of this parameter is of a data type that supports comparison.

Return value type

The Boolean type.

Examples

  • Example 1: Query logs from the previous day.

    • Query statement

      * |
      SELECT
        *
      FROM  log
      WHERE
        __time__ < to_unixtime(current_date)
        AND __time__ > to_unixtime(date_add('day', -1, current_date))
    • Query and analysis resultscurrent_date

  • Example 2: E-commerce Company A uses the mobile and client_ip fields in access logs to find the customers whose phone numbers are from a different place than the IP addresses used to access the website of the company.

    • Sample field

      mobile:1881111****
      client_ip:192.168.2.0
    • Query statement

      * |
      SELECT
        mobile,
        client_ip,
        count(*) AS PV
      WHERE
        mobile_city(mobile) != ip_to_city(client_ip)
        AND ip_to_city(client_ip) != ''
      GROUP BY
        client_ip,
        mobile
      ORDER BY
        PV DESC
    • Query and analysis resultsmobile_city

ALL operator

The ALL operator determines whether x meets all conditions. If all conditions are met, true is returned.

Syntax

x relational operator ALL(subquery)

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

Relational operator

The value of this parameter is a relational operator. Valid values: < > <= >= = <> !=

Important

The ALL operator must follow the relational operator. Relational operators: < > <= >= = <> !=

subquery

The value of this parameter is an SQL subquery.

Return value type

The Boolean type.

Examples

Check whether each request related to instance i-01 is responded with the status code 200.

  • Sample field

    instance_id:i-01
    status:200
  • Query statement

    * | select 200 = ALL(select status where instance_id='i-01')
  • Query and analysis resultsall

ANY operator

The ANY operator determines whether x meets one of the conditions. If one of the conditions is met, true is returned.

Syntax

x relational operator ANY(subquery)

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

Relational operator

The value of this parameter is a relational operator. Valid values: < > <= >= = <> !=

Important

The ANY operator must follow the relational operator. Relational operators: < > <= >= = <> !=

subquery

The value of this parameter is an SQL subquery.

Return value type

The Boolean type.

Examples

Check whether any request related to instance i-01 is responded with the status code 200.

  • Sample field

    instance_id:i-01
    status:200
  • Query statement

    * | SELECT 200 = ANY(SELECT status WHERE instance_id='i-01')
  • Query and analysis resultsany

BETWEEN operator

The BETWEEN operator determines whether x is between y and z. If the condition is met, true is returned. y and z specify a closed interval.

Syntax

x BETWEEN y AND z

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

y

The value of this parameter is of a data type that supports comparison.

z

The value of this parameter is of a data type that supports comparison.

Important
  • The data types of x, y, and z must be the same.

  • If the value of x, y, or z contains null, null is returned.

Return value type

The Boolean type.

Examples

  • Example 1: Determine whether the value of the status field is within the [200,299] range.

    • Query statement

      * | SELECT status BETWEEN 200 AND 299
    • Query and analysis resultsBETWEEN

  • Example 2: Determine the number of logs whose value of the status field is not within the [200,299] range.

    • Query statement

      * | SELECT count(*) AS count FROM log WHERE status NOT BETWEEN 200 AND 299
    • Query and analysis resultsbetween

DISTINCT operator

The DISTINCT operator determines whether x is equal to y.

Syntax

  • IS DISTINCT FROM: If x is not equal to y, true is returned.

    x IS DISTINCT FROM y
  • IS NOT DISTINCT FROM: If x is equal to y, true is returned.

    x IS NOT DISTINCT FROM y

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

y

The value of this parameter is of a data type that supports comparison.

Unlike the = and <> operators, the DISTINCT operator can be used to perform comparison on null.

x

y

x = y

x <> y

x IS DISTINCT FROM y

x IS NOT DISTINCT FROM y

1

1

true

false

false

true

1

2

false

true

true

false

1

null

null

null

true

false

null

null

null

null

false

true

Return value type

The Boolean type.

Examples

Compare 0 against null.

  • Query statement

    * | select 0 IS DISTINCT FROM null
  • Query and analysis resultsdistinct

LIKE operator

The LIKE operator matches a specified character pattern in a string. The string is case-sensitive.

Syntax

x LIKE pattern [escape 'escape_character']

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

pattern

The value of this parameter is the character pattern, which can contain strings or wildcard characters. The following wildcard characters are supported:

  • Percent sign (%): indicates an arbitrary number of characters.

  • Underscore (_): indicates a single character.

escape_character

The value of this parameter is a character expression that is used to escape the wildcard characters in the character pattern.

Note

The LIKE operator is used to query logs based on exact match. For more information, see How do I query logs by using exact match?

Return value type

The Boolean type.

Examples

SQL

  • Example 1: Query the logs whose value of the request_uri field ends with file-8 or file-6.

    • Sample field

      request_uri:/request/path-2/file-6
    • Query statement

      *|SELECT * WHERE request_uri LIKE '%file-8' OR request_uri LIKE '%file-6'
    • Query and analysis resultsOR

  • Example 2: Check whether the value of the request_uri field ends with file-6.

    • Sample field

      request_uri:/request/path-2/file-6
    • Query statement

      * | SELECT request_uri LIKE '%file-6'
    • Query and analysis resultslike

SPL

  • Example 1: Query the logs whose value of the request_uri field ends with file-8 or file-6.

    • Sample field

request_uri:/request/path-2/file-6
  • SPL statement

*|WHERE request_uri LIKE '%file-8' OR request_uri LIKE '%file-6'
  • SPL results

image.png

  • Example 2: Check whether the value of the request_uri field ends with file-6.

    • Sample field

request_uri:/request/path-2/file-6
  • SPL statement

* | extend a = request_uri LIKE '%file-6'
  • SPL results

image.png

SOME operator

The SOME operator determines whether x meets one of the conditions. If one of the conditions is met, true is returned.

Syntax

x relational operator SOME(subquery)

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

Relational operator

The value of this parameter is a relational operator. Valid values: < > <= >= = <> !=

Important

The SOME operator must follow the relational operator. Relational operators: < > <= >= = <> !=

subquery

The value of this parameter is an SQL subquery.

Return value type

The Boolean type.

Examples

Check whether any request related to instance i-01 is processed for less than 20s.

  • Sample field

    instance_id:i-01
    request_time:16
  • Query statement

    * | SELECT 20 > SOME(SELECT request_time WHERE instance_id='i-01')
  • Query and analysis resultsany

GREATEST operator

The GREATEST operator obtains the greater value of x and y.

Note

The GREATEST operator is used for horizontal comparison, and the max function is used for vertical comparison.

Syntax

GREATEST(x, y...)

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

y

The value of this parameter is of a data type that supports comparison.

Return value type

The double type.

Examples

Compare the values of the request_time and status fields in the same log to obtain the greater value.

  • Sample field

    request_time:38
    status:200
  • Query statement

    * |  SELECT GREATEST(request_time,status)
  • Query and analysis resultsgreatest

LEAST operator

The LEAST operator obtains the smaller value of x and y.

Note

The LEAST operator is used for horizontal comparison, and the min function is used for vertical comparison.

Syntax

LEAST(x, y...)

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

y

The value of this parameter is of a data type that supports comparison.

Return value type

The double type.

Examples

Compare the values of the request_time and status fields in the same log to obtain the smaller value.

  • Sample field

    request_time:77
    status:200
  • Query statement

    * |  SELECT LEAST(request_time,status)
  • Query and analysis resultsleast

NULL operator

The NULL operator determines whether x is null.

Syntax

  • IS NULL: If x is null, true is returned.

    x IS NULL
  • IS NOT NULL: If x is not null, true is returned.

    x IS NOT NULL

Parameters

Parameter

Description

x

The value of this parameter is of a data type that supports comparison.

Return value type

The Boolean type.

Examples

  • Example1: Determine whether the value of the status field is null.

    • Query statement

      * | select status IS NULL
    • Query and analysis resultsis null

  • Example 2: Determine the number of logs whose status field is not empty.

    • Query statement

      * | SELECT count(*) AS count FROM log WHERE status IS NOT NULL
    • Query and analysis resultsis not null