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.
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.
- Query statement
- 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.
- Query statement
- To display the query and analysis result in the form of a JSON array, execute the
following query statement:
- 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.
- Query statement
- 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
- Query statement
- To display the query and analysis result in the form of a JSON array, execute the
following query statement:
- 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
- Query statement
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.
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