Interval-Valued Comparison and Periodicity-Valued Comparison Functions
Download
포커스 모드
폰트 크기
This document introduces the basic syntax and examples of interval-valued comparison and periodicity-valued comparison functions.
Function | Syntax | Description |
compare(x,n) | Compare the calculation result of the current time period with the calculation result of a time period n seconds before: | |
| compare(x,n1,n2,n3...) | Compare the calculation result of the current time period with the calculation results of time periods n1, n2, and n3 seconds before: |
| compare(x,n,time) | Compares the change trend of x over time in the current time period with that in the time period n seconds ago, where time is the time column corresponding to the change trend of x. |
| compare(x,n1,n2,n3...,time) | Compares the change trend of x over time in the current time period with those in the time periods n1 seconds, n2 seconds, and n3 seconds ago, where time is the time column corresponding to the change trend of x. |
compare
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
Compare the calculation result of the current time period with the calculation result of a time period n seconds before:
compare (x, n)
Compare the calculation result of the current time period with the calculation results of time periods n1, n2, and n3 seconds before:
compare (x, n1, n2, n3...)
Compare the change trend of x over time in the current time period with that in the time period n seconds ago, where time is the time column corresponding to the change trend of x.
compare(x,n,time)
Compare the change trend of x over time in the current time period with those in the time periods n1 seconds, n2 seconds, and n3 seconds ago, where time is the time column corresponding to the change trend of x.
compare(x,n1,n2,n3...,time)
Parameter description
Parameter | Description |
x | The parameter value is of the double or long type. |
n | Time window. Unit: seconds. Example: 3600 (1 hour), 86400 (1 day), 604800 (1 week), or 31622400 (1 year). |
time | This parameter is required if the change trend of x over time is compared. Time is the time column corresponding to the change trend of x and must be of the timestamp type. |
Return value type
JSON array in the following format: [the current calculation result, the calculation result n seconds before, the ratio of the current calculation result to the calculation result of n seconds before].
Example
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 for query and analysis to 1 hour and execute the following query and analysis statement, where
86400 indicates the current time minus 86400 seconds (1 day).Query and analysis statement
* | SELECT compare(PV, 86400) FROM (SELECT count(*) AS PV)
Query and analysis result
[1860,1656,1.1231884057971016]
1860: indicates the PVs of the current 1 hour.
1656: indicates the PVs of the same time period the day before.
1.1231884057971016: indicates the ratio of the PVs of the current hour to the PVs of the same time period the day before.
To display the query and analysis result in multiple columns, execute the following query and analysis statement:
* |SELECT compare[1] AS today, compare[2] AS yesterday, compare[3] AS ratioFROM (SELECT compare(PV, 86400) AS compareFROM (SELECT COUNT(*) AS PV))
Example 2. Calculate the trend of the PVs of every 5 minutes today to the trend of the PVs of the same time period the day before.
Set the time range for query and analysis to Today and execute the following query statement.
86400 indicates the current time minus 86400 seconds (1 day), and current_date indicates the date of the current day.Query and analysis statement
* |SELECTcompare[1] AS today, compare[2] AS yesterday, timeFROM(SELECTcompare(PV, 86400, time) AS compare, timeFROM(SELECTcount(*) AS PV, histogram(__TIMESTAMP__, INTERVAL 5 MINUTE) AS timeGROUP BYtime))
Query and analysis result
피드백