Comparative period functions - Simple Log Service - Alibaba Cloud Documentation Center

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

Simple Log Service (SLS) 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 name	Syntax	Description	SQL support	SPL support
compare function	compare(x, n)	Compares the calculated result for the current time period with the result from n seconds prior.	√	×
compare function	compare(x, n1, n2, n3...)	Compares the calculated result for the current time period with results from n1, n2, n3... seconds prior.	√	×
ts_compare function	ts_compare(x, n)	Compares the result for an aligned time, such as per hour, in the current period with the result from the corresponding time n seconds prior.	√	×
ts_compare function	ts_compare(x, n1, n2, n3...)	Compares the result for an aligned time, such as per hour, in the current period with results from the corresponding times 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 calculated result for the current time period with results from one or more previous periods.

Syntax

Compares the calculated result for the current time period with the result from n seconds prior.
```
compare(x, n)
```
Compares the calculated result for the current time period with results from n1, n2, n3... seconds prior.
```
compare(x, n1, n2, n3...)
```

Parameters

Parameter	Description
x	The value to compare. The value must be of the `double` or `long` type.
n	The time window in seconds. Examples: `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 Time) and execute the following query statement. In the statement, 86400 represents the current time minus 86400 seconds (1 day), and log represents the Logstore name.
- Display results as 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 (e.g., from 14:00:00 to 15:00:00 on December 25, 2020).
    - 3522.0: The PVs for the same hour on the previous day (e.g., from 14:00:00 to 15:00:00 on December 24, 2020).
    - 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, where diff is an alias for the result, and diff [1] obtains the first value from the array.
  - Query and analysis results
    - 3337.0: The PVs for the current hour (e.g., from 14:00:00 to 15:00:00 on December 25, 2020).
    - 3522.0: The PVs for the same hour on the previous day (e.g., from 14:00:00 to 15:00:00 on December 24, 2020).
    - 0.947473026689381: The ratio of PVs between the current hour and the same hour on the previous day.
Example 2: For the current hour, count the requests for each request status and method combination, and compare the result with the count from the previous hour.
Select 1 Hour (Aligned Time) as the time range for query and analysis, and execute the following query and analysis statement. In the statement, 3600 represents the current time minus 3600 seconds (1 hour), and log represents the Logstore name.
- 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: The results include three columns: status, request_method, and _col2. The _col2 column contains the array returned by the compare function in the format [current_period_value, previous_period_value, ratio]. For example, a row for a status code of 501 and a GET request method returns [39.0, 13.0, 3.0]. This indicates a PV count of 39 for the current period, 13 for the previous period, and a period-over-period ratio of 3.0.

Ts_compare function

The ts_compare function compares the calculated result for an aligned time (e.g., per hour) in the current period with results from corresponding times in previous periods.

Important

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

Syntax

Compares the calculated result for an aligned time in the current time period with the result from the corresponding aligned time in a time period n seconds prior.
```
ts_compare(x, n)
```
Compares the calculated result for an aligned time in the current period with results from the corresponding times n1, n2, n3... seconds prior.
```
ts_compare(x, n1, n2, n3...)
```

Parameters

Parameter	Description
x	The value to compare. The value must be of the `double` or `long` type.
n	The time window in seconds. Examples: `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 previous day and two days ago.
Set the time range for query and analysis to Today (Aligned Time), and execute the following query statement. In this statement, 86400 represents the current time minus 86400 seconds (1 day), 172800 represents the current time minus 172800 seconds (2 days), log represents the Logstore name, and date_trunc('hour',__time__ ) represents using the date_trunc function to align the time to the hour.
- Display results as 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 (e.g., from 00:00 to 01:00 on September 22, 2022).
    - 1191.0: The PVs for the same time period on the previous day (e.g., from 00:00 to 01:00 on September 21, 2022).
    - 1253.0: The PVs for the same time period two days ago (e.g., from 00:00 to 01:00 on September 20, 2022).
    - 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 September 21, 2022.
    - 1663603200.0: The UNIX timestamp for 00:00 on September 20, 2022.
    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: Each row in the results table represents one hour of data. time is the hourly timestamp. day1, day2, and day3 are the PV counts for the current day, the previous day, and two days prior, respectively. ratio1 is the ratio between the current day and the previous day, and ratio2 is the ratio between the current day and two days prior. A value close to 1.0 indicates little change.
Example 2: Calculate the hour-over-hour change in page views (PVs) for today.
Set the time range for the query and analysis to Today (relative), and run the following query statement. In this statement, 3600 represents the current time minus 3600 seconds (1 hour), log is the Logstore name, and date_trunc('hour',__time__ ) uses the date_trunc function to align the time 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
```
- The query and analysis results contain two columns: time and data. For example, when time is 2021-01-27 00:00:00.000, data is [1160.0,10034.0,0.11560693641618497,1611673200.0]. When time is 2021-01-27 01:00:00.000, data is [10177.0,1160.0,8.773275862068966,1611676800.0]. When time is 2021-01-27 02:00:00.000, data is [26804.0,10177.0,2.633782057580195,1611680400.0].
  Note
  The timestamps in the results may vary depending on when you run the query.