This topic describes the syntax of interval-valued comparison and periodicity-valued comparison functions. This topic also provides examples on how to use the functions.

The following table describes the interval-valued comparison and periodicity-valued comparison functions that are supported by Log Service.

Notice 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.
Function Syntax Description
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 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.
Notice The query and analysis results of the ts_compare function must be grouped by the time column by using the GROUP BY clause.
ts_compare(x,n1,n2,n3...) Compares the calculation result of the current time period with the calculation results of time periods n1, n2, and n3 seconds before.
Notice 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 is used to compare 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 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. Example: 3600 (1 hour), 86400 (1 day), 604800 (one week), or 31622400 (one year).

Return value type

The returned result is a JSON array in the following format: [the current value, the value before n seconds, the ratio of the current value to the value of n seconds before, the UNIX timestamp before n seconds].

Examples

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

    Set the time range to 1 Hour(Time Frame) and execute the following query statement. 86400 indicates the current time minus 86400 seconds (one day). log indicates the Logstore name.

    • To display the query and analysis result in the form of a JSON array, execute the following query statement:
      • Query statement
        * |
        SELECT
          compare(PV, 86400)
        FROM  (
            SELECT
              count(*) AS PV
            FROM      log
          )
      • Query and analysis resultPV ratio
        • 3337.0 indicates the PVs of the current 1 hour, for example, Dec 25, 2020, 14:00:00 ~ Dec 25, 2020, 15:00:00.
        • 3522.0 indicates the PVs of the same time period the day before, for example, Dec 24, 2020, 14:00:00 ~ Dec 24, 2020, 15:00:00.
        • 0.947473026689381 indicates the ratio of the PVs of the current hour to the PVs of the same time period the day before.
    • To display the query and analysis result in multiple columns, execute the following query statement:
      • Query statement
        * |
        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
              )
          )
      • Query and analysis resultResult of an interval-valued comparison
        • 3337.0 indicates the PVs of the current 1 hour, for example, Dec 25, 2020, 14:00:00 ~ Dec 25, 2020, 15:00:00.
        • 3522.0 indicates the PVs of the same time period the day before, for example, Dec 24, 2020, 14:00:00 ~ Dec 24, 2020, 15:00:00.
        • 0.947473026689381 indicates the ratio of the PVs of the current hour to the PVs of the same time period the day before.
  • Example 2: Calculate the ratio of the PVs of every hour today to the PVs of the same time period the day before and two days before.

    Set the time range to Today(Time Frame) and execute the following query statement. 86400 indicates the current time minus 86400 seconds (one day). 172800 indicates the current time minus 172800 seconds (two days). log indicates the Logstore name. date_format(from_unixtime(__time__), '%H:00') indicates the returned time format.

    • To display the query and analysis result in the form of a JSON array, execute the following query statement:
      • Query statement
        * |
        SELECT
          time,
          compare(PV, 86400, 172800) as diff
        FROM  (
            SELECT
              count(*) as PV,
              date_format(from_unixtime(__time__), '%H:00') as time
            FROM      log
            GROUP BY
              time
          )
        GROUP BY
          time
        ORDER BY
          time
      • Query and analysis resultPVs per minute
        • 1176.0 indicates the PVs of the current time period, for example, Dec 25, 2020, 00:00 ~ Dec 25, 2020, 01:00.
        • 1180 indicates the PVs of the same time period the day before, for example, Dec 24, 2020, 00:00 ~ Dec 24, 2020, 01:00.
        • 1167.0 indicates the PVs of the same time period two days before, for example, Dec 23, 2020, 00:00:00 ~ Dec 23, 2020, 01:00:00.
        • 0.9966101694915255 indicates the ratio of the PVs of the current time period to the PVs of the same time period the day before.
        • 1.0077120822622108 indicates the ratio of the PVs of the current period to the PVs of the same period two days before.
    • To display the query and analysis result in multiple columns, execute the following query statement:
      • Query statement
        * |
        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,
              compare(PV, 86400, 172800) as diff
            FROM      (
                SELECT
                  count(*) as PV,
                  date_format(from_unixtime(__time__), '%H:00') as time
                FROM          log
                GROUP BY
                  time
              )
            GROUP BY
              time
            ORDER BY
              time
          )
      • Query and analysis resultInterval-valued comparison
  • Example 3: Calculate the ratio of the PVs of December to the PVs of November in the same year.

    Set the time range to This Month(Time Frame) and execute the following query statement. 2592000 indicates the current time minus 2592000 seconds (one month). log indicates the Logstore name. date_trunc('month', __time__) indicates that the date_trunc function is used to truncate a time by month.

    • Query statement
      * |
      SELECT
        time,
        compare(PV, 2592000) AS diff
      FROM  (
          SELECT
            count(*) AS PV,
            date_trunc('month', __time__) AS time
          FROM      log
          GROUP BY
            time
        )
      GROUP BY
        time
      ORDER BY
        time
    • Query and analysis resultPV ratio of a periodicity-valued comparison

ts_compare function

The ts_compare function is used to compare the calculation result of the current time period with the calculation result of a time period n seconds before.

Notice 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 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. Example: 3600 (1 hour), 86400 (1 day), 604800 (one week), or 31622400 (one year).

Return value type

The returned result is a JSON array in the following format: [the current value, the value before n seconds, the ratio of the current value to the value n seconds before, the UNIX timestamp before n seconds].

Examples

Calculate the ratio of the PVs of every hour today to the PVs of the previous hour.

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

  • Query statement
    * |
    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 resultts_compare