Perform period-over-period comparison with compare and ts_compare functions - Simple Log Service

This topic describes the syntax of comparative period functions and provides examples.

Log Service supports the following comparative period functions.

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	SQL	SPL
compare function	compare(x, n)	Compares the result in the current time period with that from a period n seconds prior.	√	×
compare function	compare(x, n1, n2, n3...)	Compares the result in the current time period with results from the periods n1, n2, n3... seconds prior.	√	×
ts_compare function	ts_compare(x, n)	Compares the result for an aligned time (e.g., per hour) in the current time period with that of the corresponding time in a period n seconds prior.	√	×
ts_compare function	ts_compare(x, n1, n2, n3...)	Compares the result for an aligned time (e.g., per hour) in the current time period with results from the corresponding times in the periods n1, n2, n3... seconds prior.	√	×

Important

The ts_compare function requires a GROUP BY clause with a single time column.
The compare and ts_compare functions cannot be nested.

compare function

The compare function compares the result from the current time period with results from one or more previous periods.

Syntax

Compares the current result with the result from a single previous time period.
```
compare(x, n)
```
Compares the current result with the results from multiple previous time periods.
```
compare(x, n1, n2, n3...)
```

Parameters

Parameter	Description
x	The value to compare, which must be a `double` or `long`.
n	The time window in seconds. For example: 3600 (1 hour), 86400 (1 day), 604800 (1 week), or 31622400 (1 year).

Return value

Returns an array. For a single time offset n, the format is [current_result, previous_result, ratio]. When multiple time offsets (n1, n2, ...) are specified, the format is [current_result, previous_result_1, previous_result_2, ..., ratio_1, ratio_2, ...].

Examples

Example 1: Calculate the ratio of page views (PVs) between the current hour and the same hour on the previous day.

Set the time range for query and analysis to 1 Hour (aligned) and run the following query statement. In the statement, 86400 specifies a time offset of 86,400 seconds (1 day), and log is the name of the Logstore.
- Display results in an array
  - Query statement (Debug)
```
* |
SELECT
  compare(PV, 86400)
FROM  (
    SELECT
      count(*) AS PV
    FROM      log
  )
```
  - Query and analysis results
    - 3337.0: The PVs for the current hour, for example, from 14:00:00 to 15:00:00 on 2020-12-25.
    - 3522.0: The PVs for the same hour on the previous day, for example, from 14:00:00 to 15:00:00 on 2020-12-24.
    - 0.947473026689381: The ratio of PVs between the current hour and the same hour on the previous day.
- Display results in separate columns
  - 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 compare function returns an array. In this query, diff is the alias for the result array, and diff[1] extracts the first element from the array.
  - Query and analysis results
    - 3337.0: The PVs for the current hour, for example, from 14:00:00 to 15:00:00 on 2020-12-25.
    - 3522.0: The PVs for the same hour on the previous day, for example, from 14:00:00 to 15:00:00 on 2020-12-24.
    - 0.947473026689381: The ratio of PVs between the current hour and the same hour on the previous day.
Example 2: Count requests by status and method for the current hour, and compare these counts with those from the previous hour.

Set the time range for query and analysis to 1 Hour (aligned) and run the following query statement. In the statement, 3600 specifies a time offset of 3,600 seconds (1 hour), and log is the name of the Logstore.
- 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 results for an aligned time (e.g., hourly) in the current period with results from the same aligned time in one or more previous periods.

Important

The ts_compare function requires a GROUP BY clause with a single time column.

Syntax

Compares the result for an aligned time with the result from the corresponding time in a single previous time period.
```
ts_compare(x, n)
```
Compares the result for an aligned time with the results from the corresponding times in multiple previous time periods.
```
ts_compare(x, n1, n2, n3...)
```

Parameters

Parameter	Description
x	The value to compare, which must be a `double` or `long`.
n	The time window in seconds. For example: 3600 (1 hour), 86400 (1 day), 604800 (1 week), or 31622400 (1 year).

Return value

Returns an array. For a single time offset n, the format is [current_result, previous_result, ratio, previous_UNIX_timestamp]. When multiple time offsets (n1, n2, ...) are specified, the format is [current_result, previous_result_1, previous_result_2, ..., ratio_1, ratio_2, ..., previous_UNIX_timestamp_1, previous_UNIX_timestamp_2, ...].

Examples

Example 1: Compare today's hourly page views (PVs) with those from the same hour on the two preceding days.

Set the time range for query and analysis to Today (aligned) and run the following query statement. In the statement, 86400 specifies an offset of one day, 172800 specifies an offset of two days, log is the name of the Logstore, and date_trunc('hour',__time__) aligns timestamps to the hour.
- Display results in an array
  - 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
    - 1174.0: The PVs for the current time period, for example, 00:00 to 01:00 on 2022-09-22.
    - 1191.0: The PVs for the same time period on the previous day, for example, 00:00 to 01:00 on 2022-09-21.
    - 1253.0: The PVs for the same time period two days ago, for example, 00:00 to 01:00 on 2022-09-20.
    - 0.9857262804366079: The ratio of PVs between the current hour and the same hour on the previous day.
    - 0.936951316839585: The ratio of PVs between the current hour and the same hour two days ago.
    - 1663689600.0: The UNIX timestamp for 00:00 on 2022-09-21.
    - 1663603200.0: The UNIX timestamp for 00:00 on 2022-09-20.
    Note
    The timestamps in the results may vary depending on when you run the query.
- Display results in separate columns
  - 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 hour-over-hour change in page views for today.

Set the time range for query and analysis to Today (Relative) and run the following query statement. In the statement, 3600 specifies an offset of 3,600 seconds (1 hour), log is the name of the Logstore, and date_trunc('hour',__time__ ) aligns timestamps to the 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 results
  
  Note
  The timestamps in the results may vary depending on when you run the query.