SQL Period-over-Period Functions

Updated on 2025-02-12 GMT+08:00

View PDF

This section describes the basic syntax and examples of period-over-period functions.

compare Function

This function is used to compare the calculation result in the current period with that in a previous period (n seconds ago).

Syntax

This syntax is used to compare the calculation result in the current period with that n seconds ago.
```
compare(x,n)
```
This syntax is used to compare the calculation result in the current period with that n1, n2, and n3 seconds ago.
```
compare(x, n1, n2, n3...)
```

Description

**Table 1** Parameters of the year-on-year function
Parameter	Description
x	Name of the target column. The value is of the double or long type.
n	Time window (in seconds). For example, 3600 means 1 hour, 86400 means 1 day, 604800 means 1 week, and 31622400 means 1 year.

Returned Data Type

JSON array. The format is [Current calculation result,Calculation result n seconds ago,Ratio of the current calculation result to that n seconds ago].

Example

This example calculates the ratio of the number of visits in the current hour to that in the same period of the previous day.

Set the query and analysis time range to 1 hour (full hour), and run the following query and analysis statements: In the preceding command, 86400 indicates the current time minus 86400 seconds (one day).
```
SELECT
compare(PV, 86400)
FROM  (SELECT count(*) AS PV )
```
Check the query and analyze results.
- 5994.0 indicates the number of website visits in the current hour (for example, from 2021-01-02 00:00:00 to 2021-01-02 01:00:00).
- 6000.0 indicates the number of website visits in the same period of the previous day, for example, from 2021-01-01 00:00:00 to 2021-01-01 01:00:00.
- 0.999 indicates the ratio of the number of website visits in the current hour to that in the same period of the previous day.
Figure 1 Query and analysis results

Display query and analysis results in columns.

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

Figure 2 Query and analysis results

ts_compare Function

This function is used to compare the calculation result in the current period with that n seconds ago.

The ts_compare function must be grouped by time column (GROUP BY).

Syntax

This function is used to compare the calculation result in the current period with that n seconds ago.
```
ts_compare(x, n)
```
This function is used to compare the calculation result in the current period with that n1, n2, and n3 seconds ago.
```
ts_compare(x, n1, n2, n3...)
```

Description

**Table 2** Parameters of the period-over-period function
Parameter	Description
x	The value is of the double or long type.
n	Time window (in seconds). For example, 3600 means 1 hour, 86400 means 1 day, 604800 means 1 week, and 31622400 means 1 year.

Returned Data Type

JSON array. The format is [Current calculation result,Calculation result n seconds ago,Ratio of the current calculation result to that n seconds ago,UNIX timestamp n seconds ago].

Example

This example compares the number of website visits in the 3 hours of today with that in the 3 hours of the previous day.

Set the query and analysis time range to 3 hours of the current day and run the following statements: In the preceding command, 86400 indicates that the current time minus 86400 seconds (one day), and date_trunc('hour',__time) indicates that the date_trunc function is used to truncate the input timestamp to the hour.

Query and analysis statements

SELECT
  t_time,
  ts_compare(PV, 86400) AS data
FROM(
    SELECT
      date_trunc('hour', __time) AS t_time,
      count(*) AS PV
    GROUP BY
      t_time
    ORDER BY
      t_time
  )
GROUP BY
  t_time

Query and analysis results

t_time	data
2021-10-26T06:00:00.000Z	[159.0,224.0,0.7098214285714286,1.6351416E9]
2021-10-26T07:00:00.000Z	[100.0,148.0,0.6756756756756757,1.6351452E9]
2021-10-26T08:00:00.000Z	[100.0,100.0,1.0, 1.6016544E9, 1.6351488E9]