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