You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by qi...@apache.org on 2021/12/28 14:15:09 UTC
[iotdb] branch master updated: update fill doc (#4647)
This is an automated email from the ASF dual-hosted git repository.
qiaojialin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new effef77 update fill doc (#4647)
effef77 is described below
commit effef773801e1fd65aaccc085f65ee8e30922d03
Author: CRZbulabula <33...@users.noreply.github.com>
AuthorDate: Tue Dec 28 22:14:23 2021 +0800
update fill doc (#4647)
---
.../DML-Data-Manipulation-Language.md | 492 ++++++++++----------
.../DML-Data-Manipulation-Language.md | 494 +++++++++++----------
2 files changed, 520 insertions(+), 466 deletions(-)
diff --git a/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md b/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
index 1515880..6257de8 100644
--- a/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
+++ b/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
@@ -566,6 +566,199 @@ Total line number = 3
It costs 0.170s
```
+### Fill Null Value
+
+In the actual use of IoTDB, when doing the query operation of timeseries, situations where the value is null at some time points may appear, which will obstruct the further analysis by users. In order to better reflect the degree of data change, users expect missing values to be filled. Therefore, the IoTDB system introduces fill methods.
+
+Fill methods refers to filling empty values according to the user's specified method and effective time range when performing timeseries queries for single or multiple columns. If the queried point's value is not null, the fill function will not work.
+
+#### Fill Methods
+
+IoTDB supports previous, linear, and value fill methods. Table 3-1 lists the data types and supported fill methods.
+
+<center>
+
+**Table 3-1 Data types and the supported fill methods**
+
+|Data Type|Supported Fill Methods|
+|:---|:---|
+|boolean|previous, value|
+|int32|previous, linear, value|
+|int64|previous, linear, value|
+|float|previous, linear, value|
+|double|previous, linear, value|
+|text|previous|
+</center>
+
+> Note: Only one Fill method can be specified in a Fill statement. Null value fill is compatible with version 0.12 and previous syntax (fill((<data_type>[<fill_method>(, <before_range>, <after_range>)?])+)), but the old syntax could not specify multiple fill methods at the same time
+
+#### Single Fill Query
+
+When data for a particular timestamp is null, the null values can be filled using single fill, as described below:
+
+* Previous Function
+
+When the value of the queried timestamp is null, the value of the previous timestamp is used to fill the blank. The formalized previous method is as follows:
+
+```sql
+select <path> from <prefixPath> where time = <T> fill(previous(, <before_range>)?)
+```
+
+Detailed descriptions of all parameters are given in Table 3-2.
+
+<center>
+
+**Table 3-2 Previous fill paramter list**
+
+
+|Parameter name (case insensitive)|Interpretation|
+|:---|:---|
+|path, prefixPath|query path; mandatory field|
+|T|query timestamp (only one can be specified); mandatory field|
+|before\_range|represents the valid time range of the previous method. The previous method works when there are values in the [T-before\_range, T] range. When before\_range is not specified, before\_range takes the default value default\_fill\_interval; -1 represents infinit; optional field|
+</center>
+
+Here we give an example of filling null values using the previous method. The SQL statement is as follows:
+
+```sql
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(previous, 1s)
+```
+which means:
+
+Because the timeseries root.sgcc.wf03.wt01.temperature is null at 2017-11-01T16:37:50.000, the system uses the previous timestamp 2017-11-01T16:37:00.000 (and the timestamp is in the [2017-11-01T16:36:50.000, 2017-11-01T16:37:50.000] time range) for fill and display.
+
+On the [sample data](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), the execution result of this statement is shown below:
+
+```
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| 21.93|
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.016s
+```
+
+It is worth noting that if there is no value in the specified valid time range, the system will not fill the null value, as shown below:
+
+```
+IoTDB> select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(float[previous, 1s])
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| null|
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.004s
+```
+
+* Linear Method
+
+When the value of the queried timestamp is null, the value of the previous and the next timestamp is used to fill the blank. The formalized linear method is as follows:
+
+```sql
+select <path> from <prefixPath> where time = <T> fill(linear(, <before_range>, <after_range>)?)
+```
+Detailed descriptions of all parameters are given in Table 3-3.
+
+<center>
+
+**Table 3-3 Linear fill paramter list**
+
+|Parameter name (case insensitive)|Interpretation|
+|:---|:---|
+|path, prefixPath|query path; mandatory field|
+|T|query timestamp (only one can be specified); mandatory field|
+|before\_range, after\_range|represents the valid time range of the linear method. The previous method works when there are values in the [T-before\_range, T+after\_range] range. When before\_range and after\_range are not explicitly specified, default\_fill\_interval is used. -1 represents infinity; optional field|
+</center>
+
+**Note** if the timeseries has a valid value at query timestamp T, this value will be used as the linear fill value.
+Otherwise, if there is no valid fill value in either range [T - before_range, T] or [T, T + after_range], linear fill method will return null.
+
+Here we give an example of filling null values using the linear method. The SQL statement is as follows:
+
+```sql
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(linear, 1m, 1m)
+```
+which means:
+
+Because the timeseries root.sgcc.wf03.wt01.temperature is null at 2017-11-01T16:37:50.000, the system uses the previous timestamp 2017-11-01T16:37:00.000 (and the timestamp is in the [2017-11-01T16:36:50.000, 2017-11-01T16:37:50.000] time range) and its value 21.927326, the next timestamp 2017-11-01T16:38:00.000 (and the timestamp is in the [2017-11-01T16:37:50.000, 2017-11-01T16:38:50.000] time range) and its value 25.311783 to perform linear fitting calculation: 21.927326 + (25.311783- [...]
+
+On the [sample data](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), the execution result of this statement is shown below:
+
+```
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| 24.746666|
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.017s
+```
+
+* Value Method
+
+When the value of the queried timestamp is null, given fill value is used to fill the blank. The formalized value method is as follows:
+
+```sql
+select <path> from <prefixPath> where time = <T> fill(constant)
+```
+Detailed descriptions of all parameters are given in Table 3-4.
+
+<center>
+
+**Table 3-4 Specific value fill paramter list**
+
+|Parameter name (case insensitive)|Interpretation|
+|:---|:---|
+|path, prefixPath|query path; mandatory field|
+|T|query timestamp (only one can be specified); mandatory field|
+|constant|represents given fill value|
+</center>
+
+**Note** if the timeseries has a valid value at query timestamp T, this value will be used as the specific fill value.
+
+Here we give an example of filling null values using the value method. The SQL statement is as follows:
+
+```sql
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(2.0)
+```
+which means:
+
+Because the timeseries root.sgcc.wf03.wt01.temperature is null at 2017-11-01T16:37:50.000, the system uses given specific value 2.0 to fill
+
+On the [sample data](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), the execution result of this statement is shown below:
+
+```
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| 2.0|
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.007s
+```
+
+When using the ValueFill, note that IoTDB will not fill the query result if the data type is different from the input constant
+
+example:
+
+```sql
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill('test')
+```
+
+result:
+
+```
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| null |
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.007s
+```
+
### Aggregate Query
This section mainly introduces the related examples of aggregate query.
@@ -914,11 +1107,13 @@ It costs 0.004s
```
#### Down-Frequency Aggregate Query with Fill Clause
-All aggregation operators can use a uniform filling method to fill the null values
+IoTDB supports null value filling of original down-frequency aggregate results. Previous, Linear, and Value fill methods can be used in any aggregation operator in a query statement, but only one fill method can be used in a query statement. In addition, the following two points should be paid attention to when using:
+- GroupByFill will not fill the aggregate result of count in any case, because in IoTDB, if there is no data in a time range, the aggregate result of count is 0
+- GroupByFill will classify sum aggregation results. In IoTDB, if a query interval does not contain any data, sum aggregation result is null, and GroupByFill will fill sum. If the sum of a time range happens to be 0, GroupByFill will not fill the value
-In group by fill, sliding step is not supported in group by clause
+The syntax of down-frequency aggregate query is similar to that of single fill query. Simple examples and usage details are listed below:
-Difference Between PREVIOUSUNTILLAST And PREVIOUS:
+##### Difference Between PREVIOUSUNTILLAST And PREVIOUS:
* PREVIOUS will fill any null value as long as there exist value is not null before it.
* PREVIOUSUNTILLAST won't fill the result whose time is after the last time of that time series.
@@ -941,7 +1136,7 @@ Total line number = 6
It costs 0.010s
```
-we will find that in root.ln.wf01.wt01.temperature the first time and value are 2017-11-07T23:49:00 and 23.7 and the last time and value are 2017-11-08T00:00:00 and 21.07 respectively.
+we will find that in root.ln.wf01.wt01.temperature the first time and value are 2017-11-07T23:49:00 and 23.7 and the last time and value are 2017-11-08T00:00:00 and 21.07 respectively.
Then execute SQL statements:
@@ -992,14 +1187,17 @@ which means:
using PREVIOUSUNTILLAST won't fill time after 2017-11-07T23:57.
-In addition, there is no data in the first time interval [2017-11-07T23:50:00, 2017-11-07T23:51:00). The last time interval with data is [2017-11-01T23:49:00, 2017-11-07T23:50:00). The first interval can be filled by setting the forward query parameter beforeRange of PreviousFill as shown in the following example:
+##### Fill The First And Last Null Value
+
+The fill methods of IoTDB can be divided into three categories: PreviousFill, LinearFill and ValueFill. Where PreviousFill needs to know the first not-null value before the null value, LinearFill needs to know the first not-null value before and after the null value to fill. If the first or last value in the result returned by a query statement is null, a sequence of null values may exist at the beginning or the end of the result set, which does not meet GroupByFill's expectations.
+
+In the above example, there is no data in the first time interval [2017-11-07T23:50:00, 2017-11-07T23:51:00). The previous time interval with data is [2017-11-01T23:49:00, 2017-11-07T23:50:00). The first interval can be filled by setting PREVIOUS to fill the forward query parameter before_range as shown in the following example:
```sql
SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (PREVIOUS, 1m);
```
result:
-
```
IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (PREVIOUS, 1m);
+-----------------------------+-----------------------------------------+
@@ -1021,56 +1219,83 @@ It costs 0.005s
explain:
-Because the beforeRange parameter sets the maximum value of forward query at the same time, and there is no data within the time interval [2017-11-07T23:55:00, 2017-11-07T23:57:00), [2017-11-07T23:56:00, 2017-11-07T23:57:00]. If beforeRange is not specified, PreviousFill will use the previous not null value in the query range by default
+In order not to conflict with the original semantics, when before_range and after_range parameters are not set, the null value of GroupByFill is filled with the first/next not-null value of the null value. When setting before_range, after_range parameters, and the timestamp of the null record is set to T. GroupByFill takes the previous/last not-null value in [t-before_range, t+after_range) to complete the fill.
+
+Because there is no data in the time interval [2017-11-07T23:55:00, 2017-11-07T23:57:00), Therefore, although this example fills the data of [2017-11-07T23:50:00, 2017-11-07T23:51:00) by setting before_range, due to the small before_range, [2017-11-07T23:56:00, 2017-11-07T23:57:00) data cannot be filled.
-Time range group aggregation query results can also be filled using VALUE and LINEAR, as shown in the following example:
+Before_range and after_range parameters can also be filled with LINEAR, as shown in the following example:
```sql
-SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (10.0);
SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (LINEAR, 5m, 5m);
```
result：
```
-IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (10.0);
+IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (LINEAR, 5m, 5m);
+-----------------------------+-----------------------------------------+
| Time|last_value(root.ln.wf01.wt01.temperature)|
+-----------------------------+-----------------------------------------+
-|2017-11-07T23:50:00.000+08:00| 10.0|
+|2017-11-07T23:50:00.000+08:00| 22.970001|
|2017-11-07T23:51:00.000+08:00| 22.24|
-|2017-11-07T23:52:00.000+08:00| 10.0|
+|2017-11-07T23:52:00.000+08:00| 23.41|
|2017-11-07T23:53:00.000+08:00| 24.58|
|2017-11-07T23:54:00.000+08:00| 22.52|
-|2017-11-07T23:55:00.000+08:00| 10.0|
-|2017-11-07T23:56:00.000+08:00| 10.0|
+|2017-11-07T23:55:00.000+08:00| 23.143333|
+|2017-11-07T23:56:00.000+08:00| 23.766666|
|2017-11-07T23:57:00.000+08:00| 24.39|
-|2017-11-07T23:58:00.000+08:00| 10.0|
+|2017-11-07T23:58:00.000+08:00| 23.283333|
+-----------------------------+-----------------------------------------+
Total line number = 9
-It costs 0.018s
+It costs 0.008s
+```
-IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (LINEAR, 5m, 5m);
+> Note: Set the initial down-frequency query interval to [start_time, end_time). The query result will keep the same as not setting before_range and after_range parameters. However, the query interval is changed to [start_time - before_range, end_time + after_range). Therefore, the efficiency will be affected when these two parameters are set too large. Please pay attention to them when using.
+
+##### ValueFill
+The ValueFill method parses the input constant value into a string. During fill, the string constant is converted to the corresponding type of data. If the conversion succeeds, the null record will be filled; otherwise, it is not filled. Examples are as follows:
+
+```sql
+SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (20.0)
+SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL ('temperature')
+```
+
+result:
+```
+IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (20.0);
+-----------------------------+-----------------------------------------+
| Time|last_value(root.ln.wf01.wt01.temperature)|
+-----------------------------+-----------------------------------------+
-|2017-11-07T23:50:00.000+08:00| 22.970001|
+|2017-11-07T23:50:00.000+08:00| 20.0|
|2017-11-07T23:51:00.000+08:00| 22.24|
-|2017-11-07T23:52:00.000+08:00| 23.41|
+|2017-11-07T23:52:00.000+08:00| 20.0|
|2017-11-07T23:53:00.000+08:00| 24.58|
|2017-11-07T23:54:00.000+08:00| 22.52|
-|2017-11-07T23:55:00.000+08:00| 23.143333|
-|2017-11-07T23:56:00.000+08:00| 23.766666|
+|2017-11-07T23:55:00.000+08:00| 20.0|
+|2017-11-07T23:56:00.000+08:00| 20.0|
|2017-11-07T23:57:00.000+08:00| 24.39|
-|2017-11-07T23:58:00.000+08:00| 23.283333|
+|2017-11-07T23:58:00.000+08:00| 20.0|
+-----------------------------+-----------------------------------------+
Total line number = 9
-It costs 0.008s
-```
-
-explain：
+It costs 0.007s
-Similar to PREVIOUS, LINEAR has two parameters, beforeRange and afterRange, which can be filled with data outside the query interval
+IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL ('temperature');
++-----------------------------+-----------------------------------------+
+| Time|last_value(root.ln.wf01.wt01.temperature)|
++-----------------------------+-----------------------------------------+
+|2017-11-07T23:50:00.000+08:00| null|
+|2017-11-07T23:51:00.000+08:00| 22.24|
+|2017-11-07T23:52:00.000+08:00| null|
+|2017-11-07T23:53:00.000+08:00| 24.58|
+|2017-11-07T23:54:00.000+08:00| 22.52|
+|2017-11-07T23:55:00.000+08:00| null|
+|2017-11-07T23:56:00.000+08:00| null|
+|2017-11-07T23:57:00.000+08:00| 24.39|
+|2017-11-07T23:58:00.000+08:00| null|
++-----------------------------+-----------------------------------------+
+Total line number = 9
+It costs 0.005s
+```
#### Down-Frequency Aggregate Query with Level Clause
@@ -1223,216 +1448,9 @@ It costs 0.012s
> ```sql
> SELECT avg(s1+1) FROM root.sg.d1; -- The aggregation function has expression parameters.
> SELECT avg(s1) + avg(s2) FROM root.sg.* GROUP BY LEVEL=1; -- Grouped by level
-> SELECT avg(s1) + avg(s2) FROM root.sg.d1 GROUP BY([0, 10000), 1s) FILL(double [previous]); -- Automated fill
+> SELECT avg(s1) + avg(s2) FROM root.sg.d1 GROUP BY([0, 10000), 1s) FILL(previous); -- Automated fill
> ```
-### Automated Fill
-
-In the actual use of IoTDB, when doing the query operation of timeseries, situations where the value is null at some time points may appear, which will obstruct the further analysis by users. In order to better reflect the degree of data change, users expect missing values to be automatically filled. Therefore, the IoTDB system introduces the function of Automated Fill.
-
-Automated fill function refers to filling empty values according to the user's specified method and effective time range when performing timeseries queries for single or multiple columns. If the queried point's value is not null, the fill function will not work.
-
-#### Fill Function
-
-* Previous Function
-
-When the value of the queried timestamp is null, the value of the previous timestamp is used to fill the blank. The formalized previous method is as follows (see Section 7.1.3.6 for detailed syntax):
-
-```sql
-select <path> from <prefixPath> where time = <T> fill(previous(, <before_range>)?)
-```
-
-Detailed descriptions of all parameters are given in Table 3-4.
-
-<center>
-
-**Table 3-4 Previous fill paramter list**
-
-
-|Parameter name (case insensitive)|Interpretation|
-|:---|:---|
-|path, prefixPath|query path; mandatory field|
-|T|query timestamp (only one can be specified); mandatory field|
-|before\_range|represents the valid time range of the previous method. The previous method works when there are values in the [T-before\_range, T] range. When before\_range is not specified, before\_range takes the default value default\_fill\_interval; -1 represents infinit; optional field|
-</center>
-
-Here we give an example of filling null values using the previous method. The SQL statement is as follows:
-
-```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(previous, 1s)
-```
-which means:
-
-Because the timeseries root.sgcc.wf03.wt01.temperature is null at 2017-11-01T16:37:50.000, the system uses the previous timestamp 2017-11-01T16:37:00.000 (and the timestamp is in the [2017-11-01T16:36:50.000, 2017-11-01T16:37:50.000] time range) for fill and display.
-
-On the [sample data](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), the execution result of this statement is shown below:
-
-```
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| 21.93|
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.016s
-```
-
-It is worth noting that if there is no value in the specified valid time range, the system will not fill the null value, as shown below:
-
-```
-IoTDB> select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(float[previous, 1s])
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| null|
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.004s
-```
-
-* Linear Method
-
-When the value of the queried timestamp is null, the value of the previous and the next timestamp is used to fill the blank. The formalized linear method is as follows:
-
-```sql
-select <path> from <prefixPath> where time = <T> fill(linear(, <before_range>, <after_range>)?)
-```
-Detailed descriptions of all parameters are given in Table 3-5.
-
-<center>
-
-**Table 3-5 Linear fill paramter list**
-
-|Parameter name (case insensitive)|Interpretation|
-|:---|:---|
-|path, prefixPath|query path; mandatory field|
-|T|query timestamp (only one can be specified); mandatory field|
-|before\_range, after\_range|represents the valid time range of the linear method. The previous method works when there are values in the [T-before\_range, T+after\_range] range. When before\_range and after\_range are not explicitly specified, default\_fill\_interval is used. -1 represents infinity; optional field|
-</center>
-
-**Note** if the timeseries has a valid value at query timestamp T, this value will be used as the linear fill value.
-Otherwise, if there is no valid fill value in either range [T-before_range，T] or [T, T + after_range], linear fill method will return null.
-
-Here we give an example of filling null values using the linear method. The SQL statement is as follows:
-
-```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(linear, 1m, 1m)
-```
-which means:
-
-Because the timeseries root.sgcc.wf03.wt01.temperature is null at 2017-11-01T16:37:50.000, the system uses the previous timestamp 2017-11-01T16:37:00.000 (and the timestamp is in the [2017-11-01T16:36:50.000, 2017-11-01T16:37:50.000] time range) and its value 21.927326, the next timestamp 2017-11-01T16:38:00.000 (and the timestamp is in the [2017-11-01T16:37:50.000, 2017-11-01T16:38:50.000] time range) and its value 25.311783 to perform linear fitting calculation: 21.927326 + (25.311783- [...]
-
-On the [sample data](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), the execution result of this statement is shown below:
-
-```
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| 24.746666|
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.017s
-```
-
-* Value Method
-
-When the value of the queried timestamp is null, given fill value is used to fill the blank. The formalized value method is as follows:
-
-```sql
-select <path> from <prefixPath> where time = <T> fill(constant)
-```
-Detailed descriptions of all parameters are given in Table 3-6.
-
-<center>
-
-**Table 3-6 Specific value fill paramter list**
-
-|Parameter name (case insensitive)|Interpretation|
-|:---|:---|
-|path, prefixPath|query path; mandatory field|
-|T|query timestamp (only one can be specified); mandatory field|
-|constant|represents given fill value|
-</center>
-
-**Note** if the timeseries has a valid value at query timestamp T, this value will be used as the specific fill value.
-
-Here we give an example of filling null values using the value method. The SQL statement is as follows:
-
-```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(2.0)
-```
-which means:
-
-Because the timeseries root.sgcc.wf03.wt01.temperature is null at 2017-11-01T16:37:50.000, the system uses given specific value 2.0 to fill
-
-On the [sample data](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), the execution result of this statement is shown below:
-
-```
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| 2.0|
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.007s
-```
-
-When using the ValueFill, note that IoTDB will not fill the query result if the data type is different from the input constant
-
-example:
-
-```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill('test')
-```
-
-result:
-
-```
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| null |
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.007s
-```
-
-#### Correspondence between Data Type and Fill Method
-
-Data types and the supported fill methods are shown in Table 3-6.
-
-<center>
-
-**Table 3-6 Data types and the supported fill methods**
-
-|Data Type|Supported Fill Methods|
-|:---|:---|
-|boolean|previous, value|
-|int32|previous, linear, value|
-|int64|previous, linear, value|
-|float|previous, linear, value|
-|double|previous, linear, value|
-|text|previous|
-</center>
-
-When the fill method is not specified, each data type bears its own default fill methods and parameters. The corresponding relationship is shown in Table 3-7.
-
-<center>
-
-**Table 3-7 Default fill methods and parameters for various data types**
-
-|Data Type|Default Fill Methods and Parameters|
-|:---|:---|
-|boolean|previous, 600000|
-|int32|previous, 600000|
-|int64|previous, 600000|
-|float|previous, 600000|
-|double|previous, 600000|
-|text|previous, 600000|
-</center>
-
-> Note: In version 0.7.0, at least one fill method should be specified in the Fill statement.
-
### Last point Query
In scenarios when IoT devices updates data in a fast manner, users are more interested in the most recent point of IoT devices.
@@ -1710,7 +1728,7 @@ It costs 0.016s
It is worth noting that because the current FILL clause can only fill in the missing value of timeseries at a certain time point, that is to say, the execution result of FILL clause is exactly one line, so LIMIT and OFFSET are not expected to be used in combination with FILL clause, otherwise errors will be prompted. For example, executing the following SQL statement:
```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(float[previous, 1m]) limit 10
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(previous, 1m) limit 10
```
The SQL statement will not be executed and the corresponding error prompt is given as follows:
@@ -1810,7 +1828,7 @@ It costs 0.000s
The SQL statement is:
```sql
-select * from root.sgcc.wf03.wt01 where time = 2017-11-01T16:35:00 fill(float[previous, 1m]) slimit 1 soffset 1
+select * from root.sgcc.wf03.wt01 where time = 2017-11-01T16:35:00 fill(previous, 1m) slimit 1 soffset 1
```
which means:
diff --git a/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md b/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
index 8cefc42..76058f8 100644
--- a/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
+++ b/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
@@ -578,6 +578,208 @@ Total line number = 3
It costs 0.170s
```
+### 空值填充
+
+在 IoTDB 的实际使用中，当进行时间序列的查询操作时，可能会出现在某些时间点值为 null 的情况，这会妨碍用户进行进一步的分析。 为了更好地反映数据更改的程度，用户希望可以自动填充缺失值。 因此，IoTDB 系统引入了自动填充功能。
+
+自动填充功能是指对单列或多列执行时间序列查询时，根据用户指定的方法和有效时间范围填充空值。 如果查询点的值不为 null，则填充功能将不起作用。
+
+#### 填充方法
+
+IoTDB 目前支持 previous, linear, value 三种空值填充方式，数据类型和支持的填充方法如表 3-1 所示。
+
+<center>
+
+**表 3-1 数据类型和支持的填充方法**
+
+| 数据类型 | 支持的填充方法 |
+| :------- | :--------------------- |
+| boolean | previous, value |
+| int32 | previous, linear, value |
+| int64 | previous, linear, value |
+| float | previous, linear, value |
+| double | previous, linear, value |
+| text | previous, value |
+
+</center>
+
+> 注意：在 Fill 语句中只能指定一种填充方法。空值填充兼容 0.12 版本及以前的语法（即 fill((<data_type>[<fill_method>(, <before_range>, <after_range>)?])+)），但老的语法也不能同时指定多种填充方式
+
+#### 单点补空值查询
+
+当某一特定时间戳的数据为空时，可以使用单值填充对空值进行填充，详细说明如下：
+
+- Previous 方式
+
+当查询的时间戳值为空时，将使用前一个时间戳的值来填充空白。 形式化的先前方法如下：
+
+```sql
+select <path> from <prefixPath> where time = <T> fill(previous(, <before_range>)?)
+```
+
+表 3-2 给出了所有参数的详细说明。
+
+<center>
+
+**表 3-2 previous 填充参数列表**
+
+| 参数名称（不区分大小写） | 解释 |
+| :----------------------- | :----------------------------------------------------------- |
+| path, prefixPath | 查询路径； 必填项 |
+| T | 查询时间戳（只能指定一个）； 必填项 |
+| before\_range | 表示前一种方法的有效时间范围。 当 [T-before\_range，T] 范围内的值存在时，前一种方法将起作用。 如果未指定 before_range，则 before_range 会使用默认值 default_fill_interval; -1 表示无穷大； 可选字段 |
+
+</center>
+
+在这里，我们举一个使用先前方法填充空值的示例。 SQL 语句如下：
+
+```sql
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(previous, 1m)
+```
+
+意思是：
+
+由于时间根目录 root.sgcc.wf03.wt01.temperature 在 2017-11-01T16：37：50.000 为空，因此系统使用以前的时间戳 2017-11-01T16：37：00.000（且时间戳位于 [2017-11-01T16:36:50.000, 2017-11-01T16:37:50.000] 范围）进行填充和显示。
+
+在 [样例数据中](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), 该语句的执行结果如下所示：
+
+```
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| 21.93|
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.016s
+```
+
+值得注意的是，如果在指定的有效时间范围内没有值，系统将不会填充空值，如下所示：
+
+```
+IoTDB> select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(previous, 1s)
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| null|
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.004s
+```
+
+- Linear 方法
+
+当查询的时间戳值为空时，将使用前一个和下一个时间戳的值来填充空白。 形式化线性方法如下：
+
+```sql
+select <path> from <prefixPath> where time = <T> fill(linear(, <before_range>, <after_range>)?)
+```
+
+表 3-3 中给出了所有参数的详细说明。
+
+<center>
+
+**表 3-3 线性填充参数列表**
+
+| 参数名称（不区分大小写） | 解释 |
+| :-------------------------- | :----------------------------------------------------------- |
+| path, prefixPath | 查询路径； 必填项 |
+| T | 查询时间戳（只能指定一个）； 必填项 |
+| before\_range, after\_range | 表示线性方法的有效时间范围。 当 [T - before\_range，T + after\_range] 范围内的值存在时，前一种方法将起作用。 如果未明确指定 before\_range 和 after\_range，则使用 default\_fill\_interval。 -1 表示无穷大； 可选字段 |
+
+</center>
+
+需要注意的是一旦时间序列在查询时间戳 T 时刻存在有效值，线性填充就会使用这个值作为结果返回。
+除此之外，如果在 [T - before_range, T] 或 [T, T + after_range] 两个范围中任意一个范围内不存在有效填充值，则线性填充返回 null 值。
+
+在这里，我们举一个使用线性方法填充空值的示例。 SQL 语句如下：
+
+```sql
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(linear, 1m, 1m)
+```
+
+意思是：
+
+由于时间根目录 root.sgcc.wf03.wt01.temperature 在 2017-11-01T16:37:50.000 为空，因此系统使用以前的时间戳 2017-11-01T16:37:00.000（且时间戳位于 [2017-11-01T16:36:50.000，2017-11-01T16:37:50.000) 时间范围）及其值 21.927326，下一个时间戳记 2017-11-01T16:38:00.000（且时间戳记位于 (2017-11-01T16:37:50.000, 2017-11-01T16:38:50.000) 时间范围）及其值 25.311783 以执行线性拟合计算：
+
+21.927326 +（25.311783-21.927326）/ 60s * 50s = 24.747707
+
+在 [样例数据](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), 该语句的执行结果如下所示：
+
+```
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| 24.747707|
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.017s
+```
+
+- Value方法
+
+当查询的时间戳值为空时，将使用给定的值来填充空白。 特定值填充方法如下：
+
+```sql
+select <path> from <prefixPath> where time = <T> fill(constant)
+```
+
+表 3-4 中给出了所有参数的详细说明。
+
+<center>
+
+**表 3-4 特定值填充参数列表**
+
+| 参数名称（不区分大小写） | 解释 |
+| :-------------------------- | :----------------------------------------------------------- |
+| path, prefixPath | 查询路径； 必填项 |
+| T | 查询时间戳（只能指定一个）； 必填项 |
+| constant | 给定的填充值；必填项 |
+
+</center>
+
+需要注意的是一旦时间序列在查询时间戳T时刻存在有效值，特定值填充就会使用这个值作为结果返回。
+
+
+在这里，我们举一个使用特定值方法填充空值的示例。 SQL语句如下：
+
+```sql
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(2.0)
+```
+
+意思是：
+
+由于时间根目录root.sgcc.wf03.wt01.temperature在2017-11-01T16:37:50.000为空，因此使用给定的值2.0进行填充：
+
+在 [样例数据](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), 该语句的执行结果如下所示：
+
+```
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| 2.0 |
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.007s
+```
+
+在使用 VALUE 方法填充时需要注意，如果查询结果的数据类型与输入常量值不同，IoTDB 将不进行填充
+
+示例：
+```sql
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill('test')
+```
+
+结果：
+```
++-----------------------------+-------------------------------+
+| Time|root.sgcc.wf03.wt01.temperature|
++-----------------------------+-------------------------------+
+|2017-11-01T16:37:50.000+08:00| null |
++-----------------------------+-------------------------------+
+Total line number = 1
+It costs 0.007s
+```
+
### 聚合查询
本章节主要介绍聚合查询的相关示例，
@@ -927,13 +1129,16 @@ Total line number = 7
It costs 0.004s
```
-#### 时间区间分组聚合查询补空值
+#### 降采样补空值查询
+
+IoTDB 支持对原降采样结果进行空值填充，previous, linear, value 填充方式均可作用于查询语句中的任一聚合算子，但一条查询语句只能使用一种空值填充方式。此外，使用时需要注意以下两点：
-时间区间分组聚合出的各个时间段的结果，支持所有聚合算子使用统一的填充方式补空
+- GroupByFill 在任何情形下都不会填充 count 的聚合结果，因为在 IoTDB 中，若某个查询区间不存在任何数据，count 的聚合结果为 0
+- GroupByFill 会分类处理 sum 的聚合结果，在 IoTDB 中，若某个查询区间不存在任何数据，sum 的聚合结果为 null，将被 GroupByFill 填充；若某个查询区间内 sum 的聚合结果恰好为 0，那么 GroupByFill 不会填充这个值
-不允许设置滑动步长，默认为聚合时间区间，实际为定长采样
+降采样补空值查询语法同单点补空值查询语法相似，下面列出简单的示例和使用细节：
-PREVIOUS 和 PREVIOUSUNTILLAST 的区别
+##### PREVIOUS 和 PREVIOUSUNTILLAST 的区别
* PREVIOUS：只要空值前边有值，就会用其填充空值
* PREVIOUSUNTILLAST：不会填充此序列最新点后的空值
@@ -1007,7 +1212,11 @@ It costs 0.006s
使用 PREVIOUSUNTILLAST 将不会填充 2017-11-07T23:57:00 以后的值。
-此外，第一个时间区间 [2017-11-07T23:50:00, 2017-11-07T23:51:00) 内没有任何数据，上一个有数据的时间区间是 [2017-11-01T23:49:00, 2017-11-07T23:50:00)，可以通过设置 PREVIOUS 填充向前查询参数 beforeRange 来填充第一个区间的数据，示例如下：
+##### 第一个值与最后一个值的填充
+
+IoTDB 的空值填充方式可以分为 PreviousFill, LinearFill, ValueFill 三大类。其中，PreviousFill 需要知道空值前的第一个非空数据，LinearFill 需要知道空值前后的第一个非空数据才能进行填充。假使某条查询语句返回的结果中第一个或最后一个值为空，就可能导致结果集在首尾存在一段连续的空值，不满足 GroupByFill 的业务期望。
+
+在上例中，第一个时间区间 [2017-11-07T23:50:00, 2017-11-07T23:51:00) 内没有任何数据，上一个有数据的时间区间是 [2017-11-01T23:49:00, 2017-11-07T23:50:00)，可以通过设置 PREVIOUS 填充向前查询参数 beforeRange 来填充第一个区间的数据，示例如下：
```sql
SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (PREVIOUS, 1m);
@@ -1035,55 +1244,82 @@ It costs 0.005s
解释：
-因为 beforeRange 参数同时设定了向前查询的最大值，而时间区间 [2017-11-07T23:55:00, 2017-11-07T23:57:00) 内均没有数据，所以不填充 [2017-11-07T23:56:00, 2017-11-07T23:57:00)。在不指定 beforeRange 时，前值填充默认使用查询范围内上一个非空值
+为了不与原有语义冲突，当不设置 before_range, after_range 参数时，GroupByFill 的空值填充取空值的前一个/后一个非空值完成；当设置 before_range, after_range 参数时，设空值所在记录的时间戳为 t，GroupByFill 取 [t-before_range, t+after_range) 内的前一个/后一个非空值完成填充。
+
+因为时间区间 [2017-11-07T23:55:00, 2017-11-07T23:57:00) 内均没有数据，所以本例虽然通过设置 before_range 填充了 [2017-11-07T23:50:00, 2017-11-07T23:51:00) 的数据，但由于 before_range 较小，[2017-11-07T23:56:00, 2017-11-07T23:57:00) 的数据无法填充。
-时间区间分组聚合查询结果也支持使用 VALUE 和 LINEAR 方式进行填充，示例如下：
+before_range, after_range 参数也可辅助 LINEAR 方式进行填充，示例如下：
```sql
-SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (10.0);
SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (LINEAR, 5m, 5m);
```
结果：
```
-IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (10.0);
+IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (LINEAR, 5m, 5m);
+-----------------------------+-----------------------------------------+
| Time|last_value(root.ln.wf01.wt01.temperature)|
+-----------------------------+-----------------------------------------+
-|2017-11-07T23:50:00.000+08:00| 10.0|
+|2017-11-07T23:50:00.000+08:00| 22.970001|
|2017-11-07T23:51:00.000+08:00| 22.24|
-|2017-11-07T23:52:00.000+08:00| 10.0|
+|2017-11-07T23:52:00.000+08:00| 23.41|
|2017-11-07T23:53:00.000+08:00| 24.58|
|2017-11-07T23:54:00.000+08:00| 22.52|
-|2017-11-07T23:55:00.000+08:00| 10.0|
-|2017-11-07T23:56:00.000+08:00| 10.0|
+|2017-11-07T23:55:00.000+08:00| 23.143333|
+|2017-11-07T23:56:00.000+08:00| 23.766666|
|2017-11-07T23:57:00.000+08:00| 24.39|
-|2017-11-07T23:58:00.000+08:00| 10.0|
+|2017-11-07T23:58:00.000+08:00| 23.283333|
+-----------------------------+-----------------------------------------+
Total line number = 9
-It costs 0.018s
+It costs 0.008s
+```
-IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (LINEAR, 5m, 5m);
+> 注意：设原始降采样查询区间为 [start_time, end_time)，在指定 before_range, after_range 参数后，降采样查询结果不变，但查询区间将转变为 [start_time - before_range, end_time + after_range)。因此这两个参数设置较大时会影响效率，使用时需注意。
+
+##### Value 填充
+值填充方式会将输入的常量值解析为字符串，填充时尝试将字符串常量转换为对应类型的数据，若转换成功则进行填充，否则就不填充。举例如下：
+
+```sql
+SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (20.0)
+SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL ('temperature')
+```
+
+结果：
+```
+IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (20.0);
+-----------------------------+-----------------------------------------+
| Time|last_value(root.ln.wf01.wt01.temperature)|
+-----------------------------+-----------------------------------------+
-|2017-11-07T23:50:00.000+08:00| 22.970001|
+|2017-11-07T23:50:00.000+08:00| 20.0|
|2017-11-07T23:51:00.000+08:00| 22.24|
-|2017-11-07T23:52:00.000+08:00| 23.41|
+|2017-11-07T23:52:00.000+08:00| 20.0|
|2017-11-07T23:53:00.000+08:00| 24.58|
|2017-11-07T23:54:00.000+08:00| 22.52|
-|2017-11-07T23:55:00.000+08:00| 23.143333|
-|2017-11-07T23:56:00.000+08:00| 23.766666|
+|2017-11-07T23:55:00.000+08:00| 20.0|
+|2017-11-07T23:56:00.000+08:00| 20.0|
|2017-11-07T23:57:00.000+08:00| 24.39|
-|2017-11-07T23:58:00.000+08:00| 23.283333|
+|2017-11-07T23:58:00.000+08:00| 20.0|
+-----------------------------+-----------------------------------------+
Total line number = 9
-It costs 0.008s
-```
-
-解释：
+It costs 0.007s
-类似 PREVIOUS 方法，LINEAR 方法有 beforeRange 和 afterRange 两个参数，同时设置时可以使用查询区间外的数据进行填充
+IoTDB> SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL ('temperature');
++-----------------------------+-----------------------------------------+
+| Time|last_value(root.ln.wf01.wt01.temperature)|
++-----------------------------+-----------------------------------------+
+|2017-11-07T23:50:00.000+08:00| null|
+|2017-11-07T23:51:00.000+08:00| 22.24|
+|2017-11-07T23:52:00.000+08:00| null|
+|2017-11-07T23:53:00.000+08:00| 24.58|
+|2017-11-07T23:54:00.000+08:00| 22.52|
+|2017-11-07T23:55:00.000+08:00| null|
+|2017-11-07T23:56:00.000+08:00| null|
+|2017-11-07T23:57:00.000+08:00| 24.39|
+|2017-11-07T23:58:00.000+08:00| null|
++-----------------------------+-----------------------------------------+
+Total line number = 9
+It costs 0.005s
+```
#### 时间区间和路径层级分组聚合查询
@@ -1232,209 +1468,9 @@ It costs 0.012s
> ```SQL
> SELECT avg(s1+1) FROM root.sg.d1; -- 聚合函数内部有表达式
> SELECT avg(s1) + avg(s2) FROM root.sg.* GROUP BY LEVEL=1; -- 按层级聚合
-> SELECT avg(s1) + avg(s2) FROM root.sg.d1 GROUP BY([0, 10000), 1s) FILL(double [previous]); -- 空值填充
+> SELECT avg(s1) + avg(s2) FROM root.sg.d1 GROUP BY([0, 10000), 1s) FILL(previous); -- 空值填充
> ```
-### 空值填充
-
-在 IoTDB 的实际使用中，当进行时间序列的查询操作时，可能会出现在某些时间点值为 null 的情况，这会妨碍用户进行进一步的分析。 为了更好地反映数据更改的程度，用户希望可以自动填充缺失值。 因此，IoTDB 系统引入了自动填充功能。
-
-自动填充功能是指对单列或多列执行时间序列查询时，根据用户指定的方法和有效时间范围填充空值。 如果查询点的值不为 null，则填充功能将不起作用。
-
-#### 填充方式
-
-- Previous 方式
-
-当查询的时间戳值为空时，将使用前一个时间戳的值来填充空白。 形式化的先前方法如下（有关详细语法，请参见第 7.1.3.6 节）：
-
-```sql
-select <path> from <prefixPath> where time = <T> fill(previous(, <before_range>)?)
-```
-
-表 3-4 给出了所有参数的详细说明。
-
-<center>
-
-**表 3-4previous 填充参数列表**
-
-| 参数名称（不区分大小写） | 解释 |
-| :----------------------- | :----------------------------------------------------------- |
-| path, prefixPath | 查询路径； 必填项 |
-| T | 查询时间戳（只能指定一个）； 必填项 |
-| before\_range | 表示前一种方法的有效时间范围。 当 [T-before\_range，T] 范围内的值存在时，前一种方法将起作用。 如果未指定 before_range，则 before_range 会使用默认值 default_fill_interval; -1 表示无穷大； 可选字段 |
-
-</center>
-
-在这里，我们举一个使用先前方法填充空值的示例。 SQL 语句如下：
-
-```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(previous, 1m)
-```
-
-意思是：
-
-由于时间根目录 root.sgcc.wf03.wt01.temperature 在 2017-11-01T16：37：50.000 为空，因此系统使用以前的时间戳 2017-11-01T16：37：00.000（且时间戳位于 [2017-11-01T16:36:50.000, 2017-11-01T16:37:50.000] 范围）进行填充和显示。
-
-在 [样例数据中](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), 该语句的执行结果如下所示：
-
-```
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| 21.93|
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.016s
-```
-
-值得注意的是，如果在指定的有效时间范围内没有值，系统将不会填充空值，如下所示：
-
-```
-IoTDB> select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(float[previous, 1s])
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| null|
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.004s
-```
-
-- Linear 方法
-
-当查询的时间戳值为空时，将使用前一个和下一个时间戳的值来填充空白。 形式化线性方法如下：
-
-```sql
-select <path> from <prefixPath> where time = <T> fill(linear(, <before_range>, <after_range>)?)
-```
-
-表 3-5 中给出了所有参数的详细说明。
-
-<center>
-
-**表 3-5 线性填充参数列表**
-
-| 参数名称（不区分大小写） | 解释 |
-| :-------------------------- | :----------------------------------------------------------- |
-| path, prefixPath | 查询路径； 必填项 |
-| T | 查询时间戳（只能指定一个）； 必填项 |
-| before\_range, after\_range | 表示线性方法的有效时间范围。 当 [T-before\_range，T + after\_range] 范围内的值存在时，前一种方法将起作用。 如果未明确指定 before\_range 和 after\_range，则使用 default\_fill\_interval。 -1 表示无穷大； 可选字段 |
-
-</center>
-
-需要注意的是一旦时间序列在查询时间戳 T 时刻存在有效值，线性填充就会使用这个值作为结果返回。
-除此之外，如果在 [T-before_range，T] 或 [T, T + after_range] 两个范围中任意一个范围内不存在有效填充值，则线性填充返回 null 值。
-
-在这里，我们举一个使用线性方法填充空值的示例。 SQL 语句如下：
-
-```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(linear, 1m, 1m)
-```
-
-意思是：
-
-由于时间根目录 root.sgcc.wf03.wt01.temperature 在 2017-11-01T16：37：50.000 为空，因此系统使用以前的时间戳 2017-11-01T16：37：00.000（且时间戳位于 [2017- 11-01T16：36：50.000，2017-11-01T16：37：50.000] 时间范围）及其值 21.927326，下一个时间戳记 2017-11-01T16：38：00.000（且时间戳记位于 [2017-11-11] 01T16：37：50.000、2017-11-01T16：38：50.000] 时间范围）及其值 25.311783 以执行线性拟合计算：
-
-21.927326 +（25.311783-21.927326）/ 60s * 50s = 24.747707
-
-在 [样例数据](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), 该语句的执行结果如下所示：
-
-```
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| 24.746666|
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.017s
-```
-
-- Value方法
-
-当查询的时间戳值为空时，将使用给定的值来填充空白。 特定值填充方法如下：
-
-```sql
-select <path> from <prefixPath> where time = <T> fill(constant)
-```
-
-表3-6中给出了所有参数的详细说明。
-
-<center>
-
-**表3-6特定值填充参数列表**
-
-| 参数名称（不区分大小写） | 解释 |
-| :-------------------------- | :----------------------------------------------------------- |
-| path, prefixPath | 查询路径； 必填项 |
-| T | 查询时间戳（只能指定一个）； 必填项 |
-| constant | 给定的填充值 |
-
-</center>
-
-需要注意的是一旦时间序列在查询时间戳T时刻存在有效值，特定值填充就会使用这个值作为结果返回。
-
-
-在这里，我们举一个使用特定值方法填充空值的示例。 SQL语句如下：
-
-```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(2.0)
-```
-
-意思是：
-
-由于时间根目录root.sgcc.wf03.wt01.temperature在2017-11-01T16:37:50.000为空，因此使用给定的值2.0进行填充：
-
-在 [样例数据](https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt), 该语句的执行结果如下所示：
-
-```
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| 2.0 |
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.007s
-```
-
-在使用 VALUE 方法填充时需要注意，如果查询结果的数据类型与输入常量值不同，IoTDB 将不进行填充
-
-示例：
-```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill('test')
-```
-
-结果：
-```
-+-----------------------------+-------------------------------+
-| Time|root.sgcc.wf03.wt01.temperature|
-+-----------------------------+-------------------------------+
-|2017-11-01T16:37:50.000+08:00| null |
-+-----------------------------+-------------------------------+
-Total line number = 1
-It costs 0.007s
-```
-
-#### 数据类型和填充方法之间的对应关系
-
-数据类型和支持的填充方法如表 3-6 所示。
-
-<center>
-
-**表 3-6 数据类型和支持的填充方法**
-
-| 数据类型 | 支持的填充方法 |
-| :------- | :--------------------- |
-| boolean | previous, value |
-| int32 | previous, linear, value |
-| int64 | previous, linear, value |
-| float | previous, linear, value |
-| double | previous, linear, value |
-| text | previous, value |
-
-</center>
-
-> 注意：应在 Fill 语句中至少指定一种填充方法。
-
### 最新点查询
SQL 语法：
@@ -1708,7 +1744,7 @@ It costs 0.016s
值得注意的是，由于当前的 FILL 子句只能在某个时间点填充时间序列的缺失值，也就是说，FILL 子句的执行结果恰好是一行，因此 LIMIT 和 OFFSET 不会是 与 FILL 子句结合使用，否则将提示错误。 例如，执行以下 SQL 语句：
```sql
-select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(float[previous, 1m]) limit 10
+select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(previous, 1m) limit 10
```
SQL 语句将不会执行，并且相应的错误提示如下：
@@ -1810,7 +1846,7 @@ It costs 0.000s
SQL 语句是：
```sql
-select * from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(float[previous, 1m]) slimit 1 soffset 1
+select * from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(previous, 1m) slimit 1 soffset 1
```
结果如下所示：