| <!-- |
| |
| Licensed to the Apache Software Foundation (ASF) under one |
| or more contributor license agreements. See the NOTICE file |
| distributed with this work for additional information |
| regarding copyright ownership. The ASF licenses this file |
| to you under the Apache License, Version 2.0 (the |
| "License"); you may not use this file except in compliance |
| with the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, |
| software distributed under the License is distributed on an |
| "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| KIND, either express or implied. See the License for the |
| specific language governing permissions and limitations |
| under the License. |
| |
| --> |
| |
| |
| # 查询补空值 |
| |
| 当执行一些查询时，结果集的某行某列可能没有数据，则此位置结果为空，但这种空值不利于进行数据可视化展示和分析，需要对空值进行填补。 |
| |
| 查询补空值允许用户按照特定的方法对任何查询的结果填充空值，如取前一个不为空的值，或线性插值。补空值之后的查询结果能更好地反映数据分布，有利于用户进行数据分析。 |
| |
| 在 IoTDB 中，用户可以使用 FILL 子句指定数据缺失的情况下的填充模式。如果查询点不为空，则填充功能将不起作用。 |
| |
| ## 填充方法 |
| |
| IoTDB 目前支持 `previous` , `linear`, `value` 三种空值填充方式，数据类型和支持的填充方法如下表所示： |
| |
| | 数据类型 | 支持的填充方法 | |
| | :------- |:------------------------| |
| | 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>)?])+)） |
| |
| ### Previous 填充 |
| |
| 当查询的时间戳下数据为空时，将使用前一个时间戳的值来填充空白。 语法定义如下： |
| |
| |
| ```sql |
| fill(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 |
| ``` |
| 如果我们不使用任何填充方式，原始的查询结果如下： |
| |
| ``` |
| +-----------------------------+-------------------------------+--------------------------+ |
| | Time|root.sgcc.wf03.wt01.temperature|root.sgcc.wf03.wt01.status| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:37:00.000+08:00| 21.93| true| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:38:00.000+08:00| null| false| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:39:00.000+08:00| 22.23| null| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:40:00.000+08:00| 23.43| null| |
| +-----------------------------+-------------------------------+--------------------------+ |
| Total line number = 4 |
| ``` |
| |
| 如果我们使用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) |
| ``` |
| |
| previous填充后的结果如下： |
| |
| ``` |
| +-----------------------------+-------------------------------+--------------------------+ |
| | Time|root.sgcc.wf03.wt01.temperature|root.sgcc.wf03.wt01.status| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:37:00.000+08:00| 21.93| true| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:38:00.000+08:00| 21.93| false| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:39:00.000+08:00| 22.23| false| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:40:00.000+08:00| 23.43| false| |
| +-----------------------------+-------------------------------+--------------------------+ |
| Total line number = 4 |
| ``` |
| |
| > 注意：如果结果集的某一列第一个值就位空，那我们不会填充这一列，直到遇到这一列第一个不为空的值为止。 |
| |
| |
| ### Linear 填充 |
| |
| 当查询的时间戳下数据为空时，将使用前一个和下一个时间戳的值来填充空白。 语法定义如下： |
| |
| ```sql |
| 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) |
| ``` |
| |
| 线性填充后的结果如下： |
| |
| ``` |
| +-----------------------------+-------------------------------+--------------------------+ |
| | Time|root.sgcc.wf03.wt01.temperature|root.sgcc.wf03.wt01.status| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:37:00.000+08:00| 21.93| true| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:38:00.000+08:00| 22.08| false| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:39:00.000+08:00| 22.23| null| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:40:00.000+08:00| 23.43| null| |
| +-----------------------------+-------------------------------+--------------------------+ |
| Total line number = 4 |
| ``` |
| |
| > 注意：如果某个值之前的所有值都为空，或者某个值之后的所有值都为空，我们不会填充该值。 |
| > 注意：如果某列的数据类型为boolean/text，我们既不会填充它，也不会报错，只是让那一列保持原样。 |
| |
| ### Value 填充 |
| |
| 当查询的时间戳下数据为空时，将使用给定的值来填充空白。语法定义如下： |
| |
| ```sql |
| select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(2.0) |
| ``` |
| |
| 使用float类型的常量填充后的结果如下: |
| |
| ``` |
| +-----------------------------+-------------------------------+--------------------------+ |
| | Time|root.sgcc.wf03.wt01.temperature|root.sgcc.wf03.wt01.status| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:37:00.000+08:00| 21.93| true| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:38:00.000+08:00| 2.0| false| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:39:00.000+08:00| 22.23| null| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:40:00.000+08:00| 23.43| null| |
| +-----------------------------+-------------------------------+--------------------------+ |
| Total line number = 4 |
| ``` |
| |
| ```sql |
| select temperature from root.sgcc.wf03.wt01 where time = 2017-11-01T16:37:50.000 fill(true) |
| ``` |
| |
| 使用boolean类型的常量填充后的结果如下: |
| |
| ``` |
| +-----------------------------+-------------------------------+--------------------------+ |
| | Time|root.sgcc.wf03.wt01.temperature|root.sgcc.wf03.wt01.status| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:37:00.000+08:00| 21.93| true| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:38:00.000+08:00| null| false| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |2017-11-01T16:39:00.000+08:00| 22.23| true| |
| +-----------------------------+-------------------------------+--------------------------+ |
| |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> | | |
