Docs

Torch Log Service

SQL function

Year-on-year and period-on-period functions

This article introduces the syntax of year-over-year and month-over-month functions supported by TLS, as well as usage examples for common scenarios.

Function list

Notice

In TLS analysis statements (SQL statements), strings must be enclosed in single quotes (''), while identifiers that are unquoted or enclosed in double quotes ("") are treated as field names or column names. For example, 'time' represents a string, while time or "time" represents a field name or column name.

The comparison time points in the COMPARE function must be the same. For example, it is possible to compare the data for the current one-hour period with the data for the same period yesterday, but it is not possible to compare the data for the current one-hour period with the data for the previous one-hour period.

The TS_COMPARE function must group by the time column using GROUP BY.

Function name	Syntax	Description
COMPARE function	COMPARE(KEY, n)	Compare the calculation results within the current time range with those from n seconds ago.
COMPARE function		COMPARE(KEY, n1, n2, n3...)	Compare the calculation results within the current time range with the calculation results within the time ranges n1, n2, and n3 seconds earlier.
TS_COMPARE function	TS_COMPARE(KEY, n)	Compare the calculation results within the current time range with those within the time range n seconds ago.
TS_COMPARE function		TS_COMPARE(KEY, n1, n2, n3...)	Compare the calculation results within the current time range with the calculation results within the time ranges n1, n2, and n3 seconds ago.

COMPARE function

The COMPARE function is used to compare the calculation results within the current time range with the calculation results within the time range n seconds earlier.

Function syntax

Syntax format

Compare the calculation results within the current time range with those from the time range n seconds earlier.

COMPARE(KEY, n)

Compare the calculation results within the current time range with the calculation results from the time ranges n1, n2, and n3 seconds earlier.

COMPARE(KEY, n1, n2, n3...)

Parameters description

Parameter
Description
KEY
Fields and expressions whose values are of Double type or Long type.
n
Time window (unit: seconds). For example, 86400 (1 day).

Return value descriptions

The return value is of array type, in the format [current calculation result, calculation result from n seconds ago, ratio of the current calculation result to the calculation result from n seconds ago].

Function examples

Scenarios

Calculate the ratio of order volume between the current hour and the same hour yesterday.

Display results as an array

Statements for query and analysis

Select the time range for query and analysis as 1 hour (on the hour), and execute the following query and analysis statements.

|SELECT
COMPARE(PV, 86400)
FROM (
SELECT COUNT(*) AS PV
)

Query and analysis results

1631.0 represents the order volume for the current one-hour period (for example, 2023-09-27 10:00:00 to 2023-09-27 11:00:00).

1983.0 represents the order volume for the same time period yesterday (for example, 2023-09-26 10:00:00 to 2023-09-26 11:00:00).

0.8224911749873929 represents the ratio of the order volume in the current hour to the order volume in the same time period yesterday.

Display results in columns

Query and analysis statements

Select the time range for query and analysis as 1 hour (on-the-hour), and execute the following query and analysis statements. diff is an alias for the result calculated by the COMPARE function (in array format), and diff [1] means to get the first value in the array.

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

Query and analysis results

1757 represents the order volume for the current one-hour period (for example, 2023-09-27 12:00:00 to 2023-09-27 13:00:00).

1783 represents the order volume for the same time period yesterday (for example, 2023-09-26 12:00:00~2023-09-26 13:00:00).

0.9854178351093662 indicates the ratio of the number of orders in the current hour to the number of orders in the same hour yesterday.

TS_COMPARE function

The TS_COMPARE function is used to compare the calculation results within the current time range with the calculation results within the time range n seconds earlier.

Function syntax

Syntax format

Compare the calculation results within the current time range with those within the time range n seconds ago.

TS_COMPARE(KEY, n)

Compare the calculation results within the current time range with the calculation results within the time ranges ending n1, n2, and n3 seconds ago.

TS_COMPARE(KEY, n1, n2, n3...)

Parameters description

Parameter
Description
x
Fields and expressions whose values are of Double type or Long type.
n
Time window (unit: seconds). For example, 86400 (1 day).

Return value description

The return value is an Array. The format is [current calculation result, calculation result from n seconds ago, ratio of the current calculation result to the calculation result from n seconds ago, UNIX timestamp from n seconds ago].

Function examples

Scenarios

Calculate, for each hour today, the ratio of today's order volume for that hour to the order volume at the same hour yesterday, and the ratio to the order volume at the same hour the day before yesterday.

Display results as an array

Queries and analytical statements

Select the time range for query and analysis as today (on the hour), and execute the following query and analysis statements.

*|SELECT
time,
TS_COMPARE(PV, 86400, 172800) AS diff
FROM (
SELECT
COUNT(*) AS PV,
DATE_TRUNC('hour', __time__) AS time
GROUP BY time
)
GROUP BY time ORDER BY time

Query and analysis results

375.0 represents the order volume for the current time period (for example, 2023-09-26 03:00~04:00).

347.0 represents the order volume for the same time period yesterday (for example, 2023-09-25 03:00~04:00).

258.0 represents the order volume for the same time period on the day before yesterday (for example, 2023-09-24 03:00~04:00).

1.080691642651297 represents the ratio of the order volume of the current time period to that of the same time period yesterday.

1.4534883720930232 represents the ratio of order volume for the current time period to that of the same time period on the day before yesterday.

Display results in columns

Statements for query and analysis

|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
GROUP BY time
)
GROUP BY time
ORDER BY time
)

Query and analysis results

Last updated: 2026.03.26 18:11:06

Parameter	Description
KEY	Fields and expressions whose values are of Double type or Long type.
n	Time window (unit: seconds). For example, 86400 (1 day).