Period-over-Period Functions
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
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 same period of 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 )
- Query and analysis results.
Figure 1 Query and analysis 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.
- Query and analysis results displayed 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.
![](https://support.huaweicloud.com/intl/en-us/usermanual-lts/public_sys-resources/note_3.0-en-us.png)
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
Parameter |
Description |
---|---|
x |
The value is of the double or long type. |
n |
Time window (in seconds). For example, 3600 (1 hour), 86400 (1 day), 604800 (1 week), and 31622400 (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]
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