All Products
Search
Document Center

Simple Log Service:Comparative period functions

Last Updated:Jan 26, 2024

This topic describes the syntax of comparative period functions. This topic also provides examples on how to use the functions.

The following table describes the comparative period functions that are supported by Simple Log Service.

Important If you want to use strings in analytic statements, you must enclose the strings in single quotation marks (''). Strings that are not enclosed or strings that are 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.

Function

Syntax

Description

Supported in SQL

Supported in SPL

compare function

compare(x, n)

Compares the calculation result of the current time period with the calculation result of a time period n seconds before.

×

compare(x, n1, n2, n3...)

Compares the calculation result of the current time period with the calculation results of multiple time periods n1, n2, and n3 seconds before.

×

ts_compare function

ts_compare(x, n)

Compares the calculation result of the current time period with the calculation result of a time period n seconds before.

×

ts_compare(x, n1, n2, n3...)

Compares the calculation result of the current time period with the calculation results of multiple time periods n1, n2, and n3 seconds before.

×

Important
  • When you use the compare function, the time based on which data is compared must be the same.

    For example, you can use the compare function to compare the data of the current hour with the data of the same time period on the previous day. However, you cannot use the compare function to compare the data of the current hour with the data of the previous hour.

  • The query and analysis results of the ts_compare function must be grouped by the time column by using the GROUP BY clause.

compare function

The compare function compares the calculation result of the current time period with the calculation result of a time period n seconds before.

Syntax

  • To compare the calculation result of the current time period with the calculation result of a time period n seconds before, use the following syntax:

    compare(x, n)
  • To compare the calculation result of the current time period with the calculation results of multiple time periods n1, n2, and n3 seconds before, use the following syntax:

    compare(x, n1, n2, n3...)

Parameters

Parameter

Description

x

The value of this parameter is of the double or long type.

n

The time window. Unit: seconds. Examples: 3600, 86400, 604800, and 31622400. The values indicate 1 hour, 1 day, 1 week, and 1 year.

Return value type

The array type. The return value is in the following format: [The calculation result of the current time period, The calculation result of a time period n seconds before, The ratio of the calculation result of the current time period to the calculation result of a time period n seconds before].

Examples

  • Example 1: Calculate the ratio of the page views (PVs) of the current hour to the PVs of the same time period on the previous day.

    Set the time range to 1 Hour(Time Frame) and execute the following query statement. 86400 specifies the current time minus 86,400 seconds, which is equivalent to one day. log specifies the name of the Logstore that is used.

    • To display the query and analysis results in an array, execute the following query statement:

      • Query statement (Debug)

        * |
        SELECT
          compare(PV, 86400)
        FROM  (
            SELECT
              count(*) AS PV
            FROM      log
          )
      • Query and analysis resultsPV比

        • 3337.0 indicates the PVs of the current hour. Example: PVs from 14:00:00 to 15:00:00 on December 25, 2020.

        • 3522.0 indicates the PVs of the same time period on the previous day. Example: PVs from 14:00:00 to 15:00:00 on December 24, 2020.

        • 0.947473026689381 indicates the ratio of the PVs of the current hour to the PVs of the same time period on the previous day.

    • To display the query and analysis results in multiple columns, execute the following query statement:

      • Query statement (Debug)

        * |
        SELECT
          diff [1] AS today,
          diff [2] AS yesterday,
          diff [3] AS ratio
        FROM  (
            SELECT
              compare(PV, 86400) AS diff
            FROM      (
                SELECT
                  count(*) AS PV
                FROM          log
              )
          )

        The result of the compare function is an array. In the preceding query statement, diff specifies the alias for the result, and diff [1] specifies the first value in the array.

      • Query and analysis results同比结果

        • 3337.0 indicates the PVs of the current hour. Example: PVs from 14:00:00 to 15:00:00 on December 25, 2020.

        • 3522.0 indicates the PVs of the same time period on the previous day. Example: PVs from 14:00:00 to 15:00:00 on December 24, 2020.

        • 0.947473026689381 indicates the ratio of the PVs of the current hour to the PVs of the same time period on the previous day.

  • Example 2: Query the numbers of request methods by request status of the current hour, and then compare the numbers of the current hour with the numbers of the same time period on the previous day.

    Set the time range to 1 Hour(Time Frame) and execute the following query statement. 3600 specifies the current time minus 3,600 seconds, which is equivalent to 1 hour. log specifies the name of the Logstore that is used.

    • Query statement (Debug)

      * |
      SELECT
        status,
        request_method,
        compare(PV, 3600)
      FROM  (
          SELECT
            status,
            request_method,
            count(*) AS PV
          FROM      log
          GROUP BY
            status,
            request_method
        )
      GROUP BY
        status,
        request_method
    • Query and analysis results同比和环比函数

ts_compare function

The ts_compare function compares the calculation result of the current time period with the calculation result of a time period n seconds before.

Important

The query and analysis results of the ts_compare function must be grouped by the time column by using the GROUP BY clause.

Syntax

  • To compare the calculation result of the current time period with the calculation result of a time period n seconds before, use the following syntax:

    ts_compare(x, n)
  • To compare the calculation result of the current time period with the calculation results of multiple time periods n1, n2, and n3 seconds before, use the following syntax:

    ts_compare(x, n1, n2, n3...)

Parameters

Parameter

Description

x

The value of this parameter is of the double or long type.

n

The time window. Unit: seconds. Examples: 3600, 86400, 604800, and 31622400. The values indicate 1 hour, 1 day, 1 week, and 1 year.

Return value type

The array type. The return value is in the following format: [The calculation result of the current time period, The calculation result of a time period n seconds before, The ratio of the calculation result of the current time period to the calculation result of a time period n seconds before, The UNIX timestamp of a time period n seconds before].

Examples

  • Example 1: Calculate the ratio of the PVs of every hour today to the PVs of the same time period on the previous day and two days before.

    Set the time range to Today(Time Frame) and execute the following query statement. 86400 specifies the current time minus 86,400 seconds, which is equivalent to one day. 172800 specifies the current time minus 172,800 seconds, which is equivalent to two days. log specifies the name of the Logstore that is used. date_trunc('hour',__time__ ) specifies that the return value is truncated by hour.

    • To display the query and analysis results in an array, execute the following query statement:

      • Query statement (Debug)

        * |
        SELECT
          time,
          ts_compare(PV, 86400, 172800) as diff
        FROM  (
            SELECT
              count(*) as PV,
              date_trunc('hour', __time__) AS time
            FROM      log
            GROUP BY
              time
          )
        GROUP BY
          time
        ORDER BY
          time
      • Query and analysis results每分钟PV

        • 1174.0 indicates the PVs of the current time period. Example: PVs from 00:00 to 01:00 on September 22, 2022.

        • 1191.0 indicates the PVs of the same time period on the previous day. Example: PVs from 00:00 to 01:00 on September 21, 2022.

        • 1253.0 indicates the PVs of the same time period two days before. Example: PVs from 00:00 to 01:00 on September 20, 2022.

        • 0.9857262804366079 indicates the ratio of the PVs of the current time period to the PVs of the same time period on the previous day.

        • 0.936951316839585 indicates the ratio of the PVs of the current time period to the PVs of the same period two days before.

        • 1663689600.0 indicates the UNIX timestamp of 00:00 on September 21, 2022.

        • 1663603200.0 indicates the UNIX timestamp of 00:00 on September 20, 2022.

        Note

        The time returned in the query and analysis results varies based on the actual scenario.

    • To display the query and analysis results in multiple columns, execute the following query statement:

      • Query statement (Debug)

        * |
        SELECT
          time,
          diff [1] AS day1,
          diff [2] AS day2,
          diff [3] AS day3,
          diff [4] AS ratio1,
          diff [5] AS ratio2
        FROM  (
            SELECT
              time,
              ts_compare(PV, 86400, 172800) AS diff
            FROM      (
                SELECT
                  count(*) as PV,
                  date_trunc('hour', __time__) AS time
                FROM          log
                GROUP BY
                  time
              )
            GROUP BY
              time
            ORDER BY
              time
          )
      • Query and analysis results同比

  • Example 2: Calculate the ratios of the PVs of every hour to the PVs of the previous hour on the current day.

    Set the time range to Today(Relative) and execute the following query statement. 3600 specifies the current time minus 3,600 seconds, which is equivalent to 1 hour. log specifies the name of the Logstore that is used. date_trunc('hour',__time__ ) specifies that the date_trunc function is used to truncate a time value by hour.

    • Query statement (Debug)

      * |
      SELECT
        time,
        ts_compare(PV, 3600) AS data
      FROM(
          SELECT
            date_trunc('hour', __time__) AS time,
            count(*) AS PV
          FROM      log
          GROUP BY
            time
          ORDER BY
            time
        )
      GROUP BY
        time
    • Query and analysis resultsts_compare

      Note

      The time returned in the query and analysis results varies based on the actual scenario.