Period-over-Period Functions
This section describes period-over-period functions, including their syntax, parameters, and usage examples.
Function List
Function |
Description |
---|---|
Compares the calculation result in the current period with that n seconds ago. |
|
Compares the calculation result in the current period with that n seconds ago. It also returns a millisecond-level timestamp in the result array. |
compare
- The following syntax compares the calculation result in the current period with that n seconds ago:
compare(x, n)
- The following syntax compares the calculation result in the current period with that n1, n2, and n3 seconds ago:
compare(x, n1, n2, n3...)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
x |
Aggregation function result field to be compared. |
Double or long |
Yes |
n? |
Time window (in seconds). For example, 3600 means 1 hour, 86400 means one day, 604800 means one week, and 31622400 means one year. |
long |
Yes |
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
This function compares 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
- The following syntax compares the calculation result in the current period with that n seconds ago:
ts_compare(x, n)
- The following syntax compares the calculation result in the current period with that n1, n2, and n3 seconds ago:
ts_compare(x, n1, n2, n3...)
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 one day, 604800 means one week, and 31622400 means one 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 statement
* | SELECT t_time, ts_compare(PV, 86400) AS data FROM( SELECT to_unixtime(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]
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot