Interval-valued comparison and periodicity-valued comparison functions - Index and Query| Alibaba Cloud Documentation Center

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 function	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 function	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 result
    - 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 result
    - 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 result
    - 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 result
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 result

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 result