| <!-- |
| |
| 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` 子句指定数据缺失情况下的填充模式，允许用户按照特定的方法对任何查询的结果集填充空值，如取前一个不为空的值、线性插值等。 |
| |
| ## 语法定义 |
| |
| **`FILL` 子句的语法定义如下：** |
| |
| ```sql |
| FILL '(' PREVIOUS | LINEAR | constant ')' |
| ``` |
| |
| **注意：** |
| - 在 `Fill` 语句中只能指定一种填充方法，该方法作用于结果集的全部列。 |
| - 空值填充不兼容 0.13 版本及以前的语法（即不支持 `FILL((<data_type>[<fill_method>(, <before_range>, <after_range>)?])+)`） |
| |
| ## 填充方式 |
| |
| **IoTDB 目前支持以下三种空值填充方式：** |
| |
| - `PREVIOUS` 填充：使用该列前一个非空值进行填充。 |
| - `LINEAR` 填充：使用该列前一个非空值和下一个非空值的线性插值进行填充。 |
| - 常量填充：使用指定常量填充。 |
| |
| **各数据类型支持的填充方法如下表所示：** |
| |
| | 数据类型 | 支持的填充方法 | |
| | :------- |:------------------------| |
| | BOOLEAN | `PREVIOUS`、常量 | |
| | INT32 | `PREVIOUS`、`LINEAR`、常量 | |
| | INT64 | `PREVIOUS`、`LINEAR`、常量 | |
| | FLOAT | `PREVIOUS`、`LINEAR`、常量 | |
| | DOUBLE | `PREVIOUS`、`LINEAR`、常量 | |
| | TEXT | `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; |
| ``` |
| |
| 查询结果如下： |
| |
| ``` |
| +-----------------------------+-------------------------------+--------------------------+ |
| | 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` 填充 |
| |
| **对于查询结果集中的空值，使用该列前一个非空值进行填充。** |
| |
| **注意：** 如果结果集的某一列第一个值就为空，则不会填充该值，直到遇到该列第一个非空值为止。 |
| |
| 例如，使用 `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 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` 填充 |
| |
| **对于查询结果集中的空值，使用该列前一个非空值和下一个非空值的线性插值进行填充。** |
| |
| **注意：** |
| - 如果某个值之前的所有值都为空，或者某个值之后的所有值都为空，则不会填充该值。 |
| - 如果某列的数据类型为boolean/text，我们既不会填充它，也不会报错，只是让那一列保持原样。 |
| |
| 例如，使用 `LINEAR` 填充，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 fill(linear); |
| ``` |
| |
| `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` | `BOOLEAN` `TEXT` | |
| | `INT64` | `INT32` `INT64` `FLOAT` `DOUBLE` `TEXT` | |
| | `DOUBLE` | `FLOAT` `DOUBLE` `TEXT` | |
| | `TEXT` | `TEXT` | |
| - 当常量值大于 `INT32` 所能表示的最大值时，对于 `INT32` 类型的列，既不填充该列，也不报错，将该列保持原样。 |
| |
| 例如，使用 `FLOAT` 类型的常量填充，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 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 |
| ``` |
| |
| 再比如，使用 `BOOLEAN` 类型的常量填充，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 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 |
| ``` |
