This is an automated email from the ASF dual-hosted git repository. jackietien pushed a commit to branch ty/filltimeout in repository https://gitbox.apache.org/repos/asf/iotdb-docs.git
commit e5d296077a8411e49f40338090ac3f80ba817ac0 Author: JackieTien97 <[email protected]> AuthorDate: Fri Mar 1 15:34:00 2024 +0800 Add docs for fill timeout --- src/UserGuide/Master/SQL-Manual/SQL-Manual.md | 9 +- src/UserGuide/Master/stage/Query-Data/Fill.md | 124 ++++++++++++++++++++++ src/UserGuide/latest/SQL-Manual/SQL-Manual.md | 9 +- src/UserGuide/latest/stage/Query-Data/Fill.md | 125 +++++++++++++++++++++++ src/zh/UserGuide/Master/SQL-Manual/SQL-Manual.md | 10 +- src/zh/UserGuide/Master/stage/Query-Data/Fill.md | 124 ++++++++++++++++++++++ src/zh/UserGuide/latest/SQL-Manual/SQL-Manual.md | 10 +- src/zh/UserGuide/latest/stage/Query-Data/Fill.md | 124 ++++++++++++++++++++++ 8 files changed, 525 insertions(+), 10 deletions(-) diff --git a/src/UserGuide/Master/SQL-Manual/SQL-Manual.md b/src/UserGuide/Master/SQL-Manual/SQL-Manual.md index deb1170..8b35906 100644 --- a/src/UserGuide/Master/SQL-Manual/SQL-Manual.md +++ b/src/UserGuide/Master/SQL-Manual/SQL-Manual.md @@ -538,7 +538,7 @@ SELECT [LAST] selectExpr [, selectExpr] ... }] [HAVING havingCondition] [ORDER BY sortKey {ASC | DESC}] - [FILL ({PREVIOUS | LINEAR | constant})] + [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)] [SLIMIT seriesLimit] [SOFFSET seriesOffset] [LIMIT rowLimit] [OFFSET rowOffset] [ALIGN BY {TIME | DEVICE}] @@ -792,6 +792,11 @@ IoTDB > select count(d1.s1) from root.** group by ([1,3),1ms), level=1 having su IoTDB > select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous); ``` +#### `PREVIOUS` FILL and specify the fill timeout threshold +```sql +select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous, 2m); +``` + #### `LINEAR` Fill ```sql @@ -1450,7 +1455,7 @@ BEGIN [WHERE CLAUSE] [GROUP BY(<group_by_interval>[, <sliding_step>]) [, level = <level>]] [HAVING CLAUSE] - [FILL {PREVIOUS | LINEAR | constant}] + [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)] [LIMIT rowLimit OFFSET rowOffset] [ALIGN BY DEVICE] END diff --git a/src/UserGuide/Master/stage/Query-Data/Fill.md b/src/UserGuide/Master/stage/Query-Data/Fill.md index 32e619e..73c23bc 100644 --- a/src/UserGuide/Master/stage/Query-Data/Fill.md +++ b/src/UserGuide/Master/stage/Query-Data/Fill.md @@ -114,6 +114,130 @@ result will be like: Total line number = 4 ``` +**While using `FILL(PREVIOUS)`, you can specify a time interval. If the interval between the timestamp of the current null value and the timestamp of the previous non-null value exceeds the specified time interval, no filling will be performed.** + +> 1. In the case of FILL(LINEAR) and FILL(CONSTANT), if the second parameter is specified, an exception will be thrown +> 2. The interval parameter only supports integers + +For example, the raw data looks like this: + +```sql +select s1 from root.db.d1 +``` +``` ++-----------------------------+-------------+ +| Time|root.db.d1.s1| ++-----------------------------+-------------+ +|2023-11-08T16:41:50.008+08:00| 1.0| ++-----------------------------+-------------+ +|2023-11-08T16:46:50.011+08:00| 2.0| ++-----------------------------+-------------+ +|2023-11-08T16:48:50.011+08:00| 3.0| ++-----------------------------+-------------+ +``` + +We want to group the data by 1 min time interval: + +```sql +select avg(s1) + from root.db.d1 + group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| null| ++-----------------------------+------------------+ +``` + +After grouping, we want to fill the null value: + +```sql +select avg(s1) + from root.db.d1 + group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) + FILL(PREVIOUS); +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| 3.0| ++-----------------------------+------------------+ +``` + +we also don't want the null value to be filled if it keeps null for 2 min. + +```sql +select avg(s1) +from root.db.d1 +group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) + FILL(PREVIOUS, 2m); +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| 3.0| ++-----------------------------+------------------+ +``` + + ### `LINEAR` Fill **For null values in the query result set, fill the column with a linear interpolation of the previous non-null value and the next non-null value of the column.** diff --git a/src/UserGuide/latest/SQL-Manual/SQL-Manual.md b/src/UserGuide/latest/SQL-Manual/SQL-Manual.md index deb1170..8b35906 100644 --- a/src/UserGuide/latest/SQL-Manual/SQL-Manual.md +++ b/src/UserGuide/latest/SQL-Manual/SQL-Manual.md @@ -538,7 +538,7 @@ SELECT [LAST] selectExpr [, selectExpr] ... }] [HAVING havingCondition] [ORDER BY sortKey {ASC | DESC}] - [FILL ({PREVIOUS | LINEAR | constant})] + [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)] [SLIMIT seriesLimit] [SOFFSET seriesOffset] [LIMIT rowLimit] [OFFSET rowOffset] [ALIGN BY {TIME | DEVICE}] @@ -792,6 +792,11 @@ IoTDB > select count(d1.s1) from root.** group by ([1,3),1ms), level=1 having su IoTDB > select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous); ``` +#### `PREVIOUS` FILL and specify the fill timeout threshold +```sql +select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous, 2m); +``` + #### `LINEAR` Fill ```sql @@ -1450,7 +1455,7 @@ BEGIN [WHERE CLAUSE] [GROUP BY(<group_by_interval>[, <sliding_step>]) [, level = <level>]] [HAVING CLAUSE] - [FILL {PREVIOUS | LINEAR | constant}] + [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)] [LIMIT rowLimit OFFSET rowOffset] [ALIGN BY DEVICE] END diff --git a/src/UserGuide/latest/stage/Query-Data/Fill.md b/src/UserGuide/latest/stage/Query-Data/Fill.md index 32e619e..97eeed0 100644 --- a/src/UserGuide/latest/stage/Query-Data/Fill.md +++ b/src/UserGuide/latest/stage/Query-Data/Fill.md @@ -114,6 +114,131 @@ result will be like: Total line number = 4 ``` + +**While using `FILL(PREVIOUS)`, you can specify a time interval. If the interval between the timestamp of the current null value and the timestamp of the previous non-null value exceeds the specified time interval, no filling will be performed.** + +> 1. In the case of FILL(LINEAR) and FILL(CONSTANT), if the second parameter is specified, an exception will be thrown +> 2. The interval parameter only supports integers + +For example, the raw data looks like this: + +```sql +select s1 from root.db.d1 +``` +``` ++-----------------------------+-------------+ +| Time|root.db.d1.s1| ++-----------------------------+-------------+ +|2023-11-08T16:41:50.008+08:00| 1.0| ++-----------------------------+-------------+ +|2023-11-08T16:46:50.011+08:00| 2.0| ++-----------------------------+-------------+ +|2023-11-08T16:48:50.011+08:00| 3.0| ++-----------------------------+-------------+ +``` + +We want to group the data by 1 min time interval: + +```sql +select avg(s1) + from root.db.d1 + group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| null| ++-----------------------------+------------------+ +``` + +After grouping, we want to fill the null value: + +```sql +select avg(s1) + from root.db.d1 + group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) + FILL(PREVIOUS); +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| 3.0| ++-----------------------------+------------------+ +``` + +we also don't want the null value to be filled if it keeps null for 2 min. + +```sql +select avg(s1) +from root.db.d1 +group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) + FILL(PREVIOUS, 2m); +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| 3.0| ++-----------------------------+------------------+ +``` + + ### `LINEAR` Fill **For null values in the query result set, fill the column with a linear interpolation of the previous non-null value and the next non-null value of the column.** diff --git a/src/zh/UserGuide/Master/SQL-Manual/SQL-Manual.md b/src/zh/UserGuide/Master/SQL-Manual/SQL-Manual.md index 4c0fbfd..b156d4f 100644 --- a/src/zh/UserGuide/Master/SQL-Manual/SQL-Manual.md +++ b/src/zh/UserGuide/Master/SQL-Manual/SQL-Manual.md @@ -812,12 +812,16 @@ SQL 示例： ``` ### 6、结果集补空值 ```sql -FILL '(' PREVIOUS | LINEAR | constant ')' +FILL '(' PREVIOUS | LINEAR | constant (, interval=DURATION_LITERAL)? ')' ``` #### `PREVIOUS` 填充 ```sql select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous); ``` +#### `PREVIOUS` 填充并指定填充超时阈值 +```sql +select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous, 2m); +``` #### `LINEAR` 填充 ```sql select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(linear); @@ -914,7 +918,7 @@ selectIntoStatement [GROUP BY groupByTimeClause, groupByLevelClause] - [FILL {PREVIOUS | LINEAR | constant}] + [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)] [LIMIT rowLimit OFFSET rowOffset] @@ -1620,7 +1624,7 @@ BEGIN [WHERE CLAUSE] [GROUP BY(<group_by_interval>[, <sliding_step>]) [, level = <level>]] [HAVING CLAUSE] - [FILL {PREVIOUS | LINEAR | constant}] + [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)] [LIMIT rowLimit OFFSET rowOffset] [ALIGN BY DEVICE] END diff --git a/src/zh/UserGuide/Master/stage/Query-Data/Fill.md b/src/zh/UserGuide/Master/stage/Query-Data/Fill.md index 8641162..8c4f28c 100644 --- a/src/zh/UserGuide/Master/stage/Query-Data/Fill.md +++ b/src/zh/UserGuide/Master/stage/Query-Data/Fill.md @@ -114,6 +114,130 @@ select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16: Total line number = 4 ``` +**在前值填充时，能够支持指定一个时间间隔，如果当前null值的时间戳与前一个非null值的时间戳的间隔，超过指定的时间间隔，则不进行填充。** + +> 1. 在线性填充和常量填充的情况下，如果指定了第二个参数，会抛出异常 +> 2. 时间超时参数仅支持整数 + +例如，原始数据如下所示： + +```sql +select s1 from root.db.d1 +``` +``` ++-----------------------------+-------------+ +| Time|root.db.d1.s1| ++-----------------------------+-------------+ +|2023-11-08T16:41:50.008+08:00| 1.0| ++-----------------------------+-------------+ +|2023-11-08T16:46:50.011+08:00| 2.0| ++-----------------------------+-------------+ +|2023-11-08T16:48:50.011+08:00| 3.0| ++-----------------------------+-------------+ +``` + +根据时间分组，每1分钟求一个平均值 + +```sql +select avg(s1) + from root.db.d1 + group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| null| ++-----------------------------+------------------+ +``` + +根据时间分组并用前值填充 + +```sql +select avg(s1) + from root.db.d1 + group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) + FILL(PREVIOUS); +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| 3.0| ++-----------------------------+------------------+ +``` + +根据时间分组并用前值填充，并指定超过2分钟的就不填充 + +```sql +select avg(s1) +from root.db.d1 +group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) + FILL(PREVIOUS, 2m); +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| 3.0| ++-----------------------------+------------------+ +``` + + ### `LINEAR` 填充 **对于查询结果集中的空值，使用该列前一个非空值和下一个非空值的线性插值进行填充。** diff --git a/src/zh/UserGuide/latest/SQL-Manual/SQL-Manual.md b/src/zh/UserGuide/latest/SQL-Manual/SQL-Manual.md index 6d2848a..8dacffa 100644 --- a/src/zh/UserGuide/latest/SQL-Manual/SQL-Manual.md +++ b/src/zh/UserGuide/latest/SQL-Manual/SQL-Manual.md @@ -812,12 +812,16 @@ SQL 示例： ``` ### 6、结果集补空值 ```sql -FILL '(' PREVIOUS | LINEAR | constant ')' +FILL '(' PREVIOUS | LINEAR | constant (, interval=DURATION_LITERAL)? ')' ``` #### `PREVIOUS` 填充 ```sql select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous); ``` +#### `PREVIOUS` 填充并指定填充超时阈值 +```sql +select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous, 2m); +``` #### `LINEAR` 填充 ```sql select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(linear); @@ -914,7 +918,7 @@ selectIntoStatement [GROUP BY groupByTimeClause, groupByLevelClause] - [FILL {PREVIOUS | LINEAR | constant}] + [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)] [LIMIT rowLimit OFFSET rowOffset] @@ -1619,7 +1623,7 @@ BEGIN [WHERE CLAUSE] [GROUP BY(<group_by_interval>[, <sliding_step>]) [, level = <level>]] [HAVING CLAUSE] - [FILL {PREVIOUS | LINEAR | constant}] + [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)] [LIMIT rowLimit OFFSET rowOffset] [ALIGN BY DEVICE] END diff --git a/src/zh/UserGuide/latest/stage/Query-Data/Fill.md b/src/zh/UserGuide/latest/stage/Query-Data/Fill.md index 8641162..8c4f28c 100644 --- a/src/zh/UserGuide/latest/stage/Query-Data/Fill.md +++ b/src/zh/UserGuide/latest/stage/Query-Data/Fill.md @@ -114,6 +114,130 @@ select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16: Total line number = 4 ``` +**在前值填充时，能够支持指定一个时间间隔，如果当前null值的时间戳与前一个非null值的时间戳的间隔，超过指定的时间间隔，则不进行填充。** + +> 1. 在线性填充和常量填充的情况下，如果指定了第二个参数，会抛出异常 +> 2. 时间超时参数仅支持整数 + +例如，原始数据如下所示： + +```sql +select s1 from root.db.d1 +``` +``` ++-----------------------------+-------------+ +| Time|root.db.d1.s1| ++-----------------------------+-------------+ +|2023-11-08T16:41:50.008+08:00| 1.0| ++-----------------------------+-------------+ +|2023-11-08T16:46:50.011+08:00| 2.0| ++-----------------------------+-------------+ +|2023-11-08T16:48:50.011+08:00| 3.0| ++-----------------------------+-------------+ +``` + +根据时间分组，每1分钟求一个平均值 + +```sql +select avg(s1) + from root.db.d1 + group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| null| ++-----------------------------+------------------+ +``` + +根据时间分组并用前值填充 + +```sql +select avg(s1) + from root.db.d1 + group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) + FILL(PREVIOUS); +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| 3.0| ++-----------------------------+------------------+ +``` + +根据时间分组并用前值填充，并指定超过2分钟的就不填充 + +```sql +select avg(s1) +from root.db.d1 +group by([2023-11-08T16:40:00.008+08:00, 2023-11-08T16:50:00.008+08:00), 1m) + FILL(PREVIOUS, 2m); +``` +``` ++-----------------------------+------------------+ +| Time|avg(root.db.d1.s1)| ++-----------------------------+------------------+ +|2023-11-08T16:40:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:41:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:42:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:43:00.008+08:00| 1.0| ++-----------------------------+------------------+ +|2023-11-08T16:44:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:45:00.008+08:00| null| ++-----------------------------+------------------+ +|2023-11-08T16:46:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:47:00.008+08:00| 2.0| ++-----------------------------+------------------+ +|2023-11-08T16:48:00.008+08:00| 3.0| ++-----------------------------+------------------+ +|2023-11-08T16:49:00.008+08:00| 3.0| ++-----------------------------+------------------+ +``` + + ### `LINEAR` 填充 **对于查询结果集中的空值，使用该列前一个非空值和下一个非空值的线性插值进行填充。**
