This is an automated email from the ASF dual-hosted git repository. hui pushed a commit to branch lmh/docUpdate in repository https://gitbox.apache.org/repos/asf/iotdb.git
commit 0c250dfb6e3631d273d8b052ace525de8f1c3f9d Author: Minghui Liu <[email protected]> AuthorDate: Wed Nov 2 16:31:54 2022 +0800 update Fill-Null-Value --- docs/UserGuide/Query-Data/Fill-Null-Value.md | 116 ++++++++++---------- docs/zh/UserGuide/Query-Data/Fill-Null-Value.md | 134 +++++++++++++----------- 2 files changed, 137 insertions(+), 113 deletions(-) diff --git a/docs/UserGuide/Query-Data/Fill-Null-Value.md b/docs/UserGuide/Query-Data/Fill-Null-Value.md index 088501ee73..32e619edc7 100644 --- a/docs/UserGuide/Query-Data/Fill-Null-Value.md +++ b/docs/UserGuide/Query-Data/Fill-Null-Value.md @@ -21,15 +21,33 @@ # Fill Null Value +## Introduction + When executing some queries, there may be no data for some columns in some rows, and data in these locations will be null, but this kind of null value is not conducive to data visualization and analysis, and the null value needs to be filled. -Fill null value allows the user to fill any query result with null values according to a specific method, such as taking the previous value that is not null, or linear interpolation. The query result after filling the null value can better reflect the data distribution, which is beneficial for users to perform data analysis. +In IoTDB, users can use the FILL clause to specify the fill mode when data is missing. Fill null value allows the user to fill any query result with null values according to a specific method, such as taking the previous value that is not null, or linear interpolation. The query result after filling the null value can better reflect the data distribution, which is beneficial for users to perform data analysis. + +## Syntax Definition -In IoTDB, users can use the FILL clause to specify the fill mode when data is missing. If the queried point's value is not null, the fill function will not work. +**The following is the syntax definition of the `FILL` clause:** + +```sql +FILL '(' PREVIOUS | LINEAR | constant ')' +``` + +**Note:** +- We can specify only one fill method in the `FILL` clause, and this method applies to all columns of the result set. +- Null value fill is not compatible with version 0.13 and previous syntax (`FILL((<data_type>[<fill_method>(, <before_range>, <after_range>)?])+)`) is not supported anymore. ## Fill Methods -IoTDB supports previous, linear, and value fill methods. Following table lists the data types and supported fill methods. +**IoTDB supports the following three fill methods:** + +- `PREVIOUS`: Fill with the previous non-null value of the column. +- `LINEAR`: Fill the column with a linear interpolation of the previous non-null value and the next non-null value of the column. +- Constant: Fill with the specified constant. + +**Following table lists the data types and supported fill methods.** | Data Type | Supported Fill Methods | | :-------- |:------------------------| @@ -39,25 +57,18 @@ IoTDB supports previous, linear, and value fill methods. Following table lists t | float | previous, linear, value | | double | previous, linear, value | | text | previous, value | -| </center> | | - -> Note: Only one Fill method can be specified in a Fill statement. Null value fill is not compatible with version 0.13 and previous syntax (fill((<data_type>[<fill_method>(, <before_range>, <after_range>)?])+)) is not supported anymore. +**Note:** For columns whose data type does not support specifying the fill method, we neither fill it nor throw exception, just keep it as it is. -### Previous Fill +**For examples:** -When the value is null, the value of the previous timestamp is used to fill the blank. The formalized previous method is as follows: +If we don't use any fill methods: ```sql -fill(previous) +select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000; ``` -Here we give an example of filling null values using the previous method. The SQL statement is as follows: - -```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 -``` -if we don't use any fill methods, the original result will be like: +the original result will be like: ``` +-----------------------------+-------------------------------+--------------------------+ @@ -74,12 +85,19 @@ if we don't use any fill methods, the original result will be like: Total line number = 4 ``` -if we use previous fill, sql will be like: +### `PREVIOUS` Fill + +**For null values in the query result set, fill with the previous non-null value of the column.** + +**Note:** If the first value of this column is null, we will keep first value as null and won't fill it until we meet first non-null value + +For example, with `PREVIOUS` fill, the SQL is as follows: + ```sql -select temperature from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous) +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 filled result will be like: +result will be like: ``` +-----------------------------+-------------------------------+--------------------------+ @@ -96,23 +114,23 @@ previous filled result will be like: Total line number = 4 ``` -> Note: If the first value of this column is null, we will keep first value as null and won't fill it until we meet first non-null value +### `LINEAR` Fill -### 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.** -When the value in 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 -fill(linear) -``` +**Note:** +- If all the values before current value are null or all the values after current value are null, we will keep current value as null and won't fill it. +- If the column's data type is boolean/text, we neither fill it nor throw exception, just keep it as it is. Here we give an example of filling null values using the linear method. The SQL statement is as follows: +For example, with `LINEAR` fill, the SQL is as follows: + ```sql -select temperature from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(linear) +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); ``` -linear filled result will be like: +result will be like: ``` +-----------------------------+-------------------------------+--------------------------+ @@ -129,25 +147,28 @@ linear filled result will be like: Total line number = 4 ``` +### Constant Fill -> Note: If all the values before current value are null or all the values after current value are null, we will keep current value as null and won't fill it. -> Note: If the column's data type is boolean/text, we neither fill it nor throw exception, just keep it as it is. +**For null values in the query result set, fill with the specified constant.** -### Value Fill +**Note:** +- When using the ValueFill, IoTDB neither fill the query result if the data type is different from the input constant nor throw exception, just keep it as it is. -When the value in the queried timestamp is null, given fill value is used to fill the blank. The formalized value method is as follows: + | Constant Value Data Type | Support Data Type | + |:-------------------------|:----------------------------------| + | `BOOLEAN` | `BOOLEAN` `TEXT` | + | `INT64` | `INT32` `INT64` `FLOAT` `DOUBLE` `TEXT` | + | `DOUBLE` | `FLOAT` `DOUBLE` `TEXT` | + | `TEXT` | `TEXT` | +- If constant value is larger than Integer.MAX_VALUE, IoTDB neither fill the query result if the data type is int32 nor throw exception, just keep it as it is. -```sql -fill(constant) -``` - -Here we give an example of filling null values using the value method. The SQL statement is as follows: +For example, with `FLOAT` constant fill, the SQL is as follows: ```sql -select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(2.0) +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(2.0); ``` -float constant filled result will be like: +result will be like: ``` +-----------------------------+-------------------------------+--------------------------+ @@ -164,11 +185,13 @@ float constant filled result will be like: Total line number = 4 ``` +For example, with `BOOLEAN` constant fill, the SQL is as follows: + ```sql -select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(true) +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(true); ``` -boolean constant filled result will be like: +result will be like: ``` +-----------------------------+-------------------------------+--------------------------+ @@ -184,16 +207,3 @@ boolean constant filled result will be like: +-----------------------------+-------------------------------+--------------------------+ Total line number = 4 ``` - -> Note: When using the ValueFill, IoTDB neither fill the query result if the data type is different from the input constant nor throw exception, just keep it as it is. -> Note: If constant value is larger than Integer.MAX_VALUE, IoTDB neither fill the query result if the data type is int32 nor throw exception, just keep it as it is. - -#### Constant Fill Type Consistency - -| Constant Value Data Type | Support Data Type | -|:-------------------------|:----------------------------------| -| boolean | boolean, text | -| int64 | int32, int64, float, double, text | -| double | float, double, text | -| text | text | -| </center> | | \ No newline at end of file diff --git a/docs/zh/UserGuide/Query-Data/Fill-Null-Value.md b/docs/zh/UserGuide/Query-Data/Fill-Null-Value.md index 6ee476972d..92bc517fda 100644 --- a/docs/zh/UserGuide/Query-Data/Fill-Null-Value.md +++ b/docs/zh/UserGuide/Query-Data/Fill-Null-Value.md @@ -19,45 +19,56 @@ --> +# 空值填充 -# 查询补空值 +## 功能介绍 -当执行一些查询时，结果集的某行某列可能没有数据，则此位置结果为空，但这种空值不利于进行数据可视化展示和分析，需要对空值进行填补。 +当执行一些数据查询时，结果集的某行某列可能没有数据，则此位置结果为空，但这种空值不利于进行数据可视化展示和分析，需要对空值进行填充。 -查询补空值允许用户按照特定的方法对任何查询的结果填充空值，如取前一个不为空的值，或线性插值。补空值之后的查询结果能更好地反映数据分布，有利于用户进行数据分析。 +在 IoTDB 中，用户可以使用 `FILL` 子句指定数据缺失情况下的填充模式，允许用户按照特定的方法对任何查询的结果集填充空值，如取前一个不为空的值、线性插值等。 -在 IoTDB 中，用户可以使用 FILL 子句指定数据缺失的情况下的填充模式。如果查询点不为空，则填充功能将不起作用。 +## 语法定义 -## 填充方法 +**`FILL` 子句的语法定义如下：** -IoTDB 目前支持 `previous` , `linear`, `value` 三种空值填充方式，数据类型和支持的填充方法如下表所示： +```sql +FILL '(' PREVIOUS | LINEAR | constant ')' +``` -| 数据类型 | 支持的填充方法 | -| :------- |:------------------------| -| boolean | previous, value | -| int32 | previous, linear, value | -| int64 | previous, linear, value | -| float | previous, linear, value | -| double | previous, linear, value | -| text | previous, value | +**注意：** +- 在 `Fill` 语句中只能指定一种填充方法，该方法作用于结果集的全部列。 +- 空值填充不兼容 0.13 版本及以前的语法（即不支持 `FILL((<data_type>[<fill_method>(, <before_range>, <after_range>)?])+)`） -> 注意：在 Fill 语句中只能指定一种填充方法。空值填充不兼容 0.13 版本及以前的语法（即 fill((<data_type>[<fill_method>(, <before_range>, <after_range>)?])+)） +## 填充方式 -### Previous 填充 +**IoTDB 目前支持以下三种空值填充方式：** -当查询的时间戳下数据为空时，将使用前一个时间戳的值来填充空白。 语法定义如下： +- `PREVIOUS` 填充：使用该列前一个非空值进行填充。 +- `LINEAR` 填充：使用该列前一个非空值和下一个非空值的线性插值进行填充。 +- 常量填充：使用指定常量填充。 +**各数据类型支持的填充方法如下表所示：** -```sql -fill(previous) -``` +| 数据类型 | 支持的填充方法 | +| :------- |:------------------------| +| BOOLEAN | `PREVIOUS`、常量 | +| INT32 | `PREVIOUS`、`LINEAR`、常量 | +| INT64 | `PREVIOUS`、`LINEAR`、常量 | +| FLOAT | `PREVIOUS`、`LINEAR`、常量 | +| DOUBLE | `PREVIOUS`、`LINEAR`、常量 | +| TEXT | `PREVIOUS`、常量 | + +**注意：** 对于数据类型不支持指定填充方法的列，既不会填充它，也不会报错，只是让那一列保持原样。 -在这里，我们举一个使用 Previous 方法填充空值的示例。 SQL 语句如下： +**下面通过举例进一步说明。** + +如果我们不使用任何填充方式： ```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 +select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000; ``` -如果我们不使用任何填充方式，原始的查询结果如下： + +查询结果如下： ``` +-----------------------------+-------------------------------+--------------------------+ @@ -74,12 +85,19 @@ select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16: Total line number = 4 ``` -如果我们使用previous填充， SQL 语句如下： +### `PREVIOUS` 填充 + +**对于查询结果集中的空值，使用该列前一个非空值进行填充。** + +**注意：** 如果结果集的某一列第一个值就为空，则不会填充该值，直到遇到该列第一个非空值为止。 + +例如，使用 `PREVIOUS` 填充，SQL 语句如下： + ```sql -select temperature from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous) +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填充后的结果如下： +`PREVIOUS` 填充后的结果如下： ``` +-----------------------------+-------------------------------+--------------------------+ @@ -96,24 +114,21 @@ previous填充后的结果如下： Total line number = 4 ``` -> 注意：如果结果集的某一列第一个值就位空，那我们不会填充这一列，直到遇到这一列第一个不为空的值为止。 +### `LINEAR` 填充 +**对于查询结果集中的空值，使用该列前一个非空值和下一个非空值的线性插值进行填充。** -### Linear 填充 +**注意：** +- 如果某个值之前的所有值都为空，或者某个值之后的所有值都为空，则不会填充该值。 +- 如果某列的数据类型为boolean/text，我们既不会填充它，也不会报错，只是让那一列保持原样。 -当查询的时间戳下数据为空时，将使用前一个和下一个时间戳的值来填充空白。 语法定义如下： +例如，使用 `LINEAR` 填充，SQL 语句如下： ```sql -fill(linear) +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); ``` - -在这里，我们举一个使用线性方法填充空值的示例。 SQL 语句如下： - -```sql -select temperature from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(linear) -``` - -线性填充后的结果如下： + +`LINEAR` 填充后的结果如下： ``` +-----------------------------+-------------------------------+--------------------------+ @@ -130,18 +145,28 @@ select temperature from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.00 Total line number = 4 ``` -> 注意：如果某个值之前的所有值都为空，或者某个值之后的所有值都为空，我们不会填充该值。 -> 注意：如果某列的数据类型为boolean/text，我们既不会填充它，也不会报错，只是让那一列保持原样。 +### 常量填充 + +**对于查询结果集中的空值，使用指定常量填充。** -### Value 填充 +**注意：** +- 如果某列数据类型与常量类型不兼容，既不填充该列，也不报错，将该列保持原样。对于常量兼容的数据类型，如下表所示： -当查询的时间戳下数据为空时，将使用给定的值来填充空白。语法定义如下： + | 常量类型 | 能够填充的序列数据类型 | + |:------ |:------------------ | + | `BOOLEAN` | `BOOLEAN` `TEXT` | + | `INT64` | `INT32` `INT64` `FLOAT` `DOUBLE` `TEXT` | + | `DOUBLE` | `FLOAT` `DOUBLE` `TEXT` | + | `TEXT` | `TEXT` | +- 当常量值大于 `INT32` 所能表示的最大值时，对于 `INT32` 类型的列，既不填充该列，也不报错，将该列保持原样。 + +例如，使用 `FLOAT` 类型的常量填充，SQL 语句如下： ```sql -select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(2.0) +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(2.0); ``` -使用float类型的常量填充后的结果如下: +`FLOAT` 类型的常量填充后的结果如下： ``` +-----------------------------+-------------------------------+--------------------------+ @@ -158,11 +183,13 @@ select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 Total line number = 4 ``` +再比如，使用 `BOOLEAN` 类型的常量填充，SQL 语句如下： + ```sql -select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(true) +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(true); ``` -使用boolean类型的常量填充后的结果如下: +`BOOLEAN` 类型的常量填充后的结果如下： ``` +-----------------------------+-------------------------------+--------------------------+ @@ -177,17 +204,4 @@ select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 |2017-11-01T16:40:00.000+08:00| 23.43| true| +-----------------------------+-------------------------------+--------------------------+ Total line number = 4 -``` - -> 注意：当我们使用常量填充时，如果某列数据类型与常量类型不兼容，我们既不填充该列，也不报错，将该列保持原样。 -> 注意：当常量值大于int32所能表示的最大值时，对于int32类型的列，我们既不填充该列，也不报错，将该列保持原样。 - -#### 常量类型兼容性 - -| 常量类型 | 能够填充的数据类型 | -|:----------|:----------------------------------| -| boolean | boolean, text | -| int64 | int32, int64, float, double, text | -| double | float, double, text | -| text | text | -| </center> | | +``` \ No newline at end of file
