Google Git
Sign in
apache / iotdb-website / 58cad7010f370b058faa76922f1e0850e2372f4d / . / assets / Fill-Null-Value.html-CHcvFTYo.js
blob: 0741341683d8463a8120e82ea828db804fd7df1f [file] [log] [blame]
import{_ as l,r as p,o as i,c as o,b as s,d as n,a as t,e as a}from"./app-Bx8hKGcu.js";const r={},c=a(`<h2 id="查询补空值" tabindex="-1"><a class="header-anchor" href="#查询补空值"><span>查询补空值</span></a></h2><p>当对序列进行分段聚合时，某一段时间可能不存在数据，则此段数据的聚合结果为空，但这种空值不利于进行数据可视化展示和分析，需要对空值进行填补。</p><p>查询补空值允许用户按照特定的方法对查询结果填充空值，如取前一个不为空的值，或线性插值。补空值之后的查询结果能更好地反映数据分布，有利于用户进行数据分析。</p><p>在 IoTDB 中，用户可以使用 FILL 子句指定某一时间点或一时间窗口数据缺失的情况下的填充模式。如果查询点空，则填充功能将不起作用。</p><h3 id="填充方法" tabindex="-1"><a class="header-anchor" href="#填充方法"><span>填充方法</span></a></h3><p>IoTDB 目前支持 <code>previous</code> , <code>linear</code>, <code>value</code> 三种空值填充方式，数据类型和支持的填充方法如下表所示：</p><table><thead><tr><th style="text-align:left;">数据类型</th><th style="text-align:left;">支持的填充方法</th></tr></thead><tbody><tr><td style="text-align:left;">boolean</td><td style="text-align:left;">previous, value</td></tr><tr><td style="text-align:left;">int32</td><td style="text-align:left;">previous, linear, value</td></tr><tr><td style="text-align:left;">int64</td><td style="text-align:left;">previous, linear, value</td></tr><tr><td style="text-align:left;">float</td><td style="text-align:left;">previous, linear, value</td></tr><tr><td style="text-align:left;">double</td><td style="text-align:left;">previous, linear, value</td></tr><tr><td style="text-align:left;">text</td><td style="text-align:left;">previous, value</td></tr></tbody></table><blockquote><p>注意：在 Fill 语句中只能指定一种填充方法。空值填充兼容 0.12 版本及以前的语法（即 fill((&lt;data_type&gt;[&lt;fill_method&gt;(, &lt;before_range&gt;, &lt;after_range&gt;)?])+)），但老的语法也不能同时指定多种填充方式。</p></blockquote><h3 id="单点补空值" tabindex="-1"><a class="header-anchor" href="#单点补空值"><span>单点补空值</span></a></h3><p>当某一特定时间戳的数据为空时，可以使用单值填充对空值进行填充，详细说明如下：</p><h4 id="previous-填充" tabindex="-1"><a class="header-anchor" href="#previous-填充"><span>Previous 填充</span></a></h4><p>当查询的时间戳下数据为空时，将使用前一个时间戳的值来填充空白。 语法定义如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">&lt;</span>path<span class="token operator">&gt;</span> <span class="token keyword">from</span> <span class="token operator">&lt;</span>prefixPath<span class="token operator">&gt;</span> <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">=</span> <span class="token operator">&lt;</span>T<span class="token operator">&gt;</span> fill<span class="token punctuation">(</span>previous<span class="token punctuation">(</span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>before_range<span class="token operator">&gt;</span><span class="token punctuation">)</span>?<span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>下表给出了所有参数的详细说明：</p><table><thead><tr><th style="text-align:left;">参数名称（不区分大小写）</th><th style="text-align:left;">解释</th></tr></thead><tbody><tr><td style="text-align:left;">path, prefixPath</td><td style="text-align:left;">查询路径； 必填项</td></tr><tr><td style="text-align:left;">T</td><td style="text-align:left;">查询时间戳（只能指定一个）； 必填项</td></tr><tr><td style="text-align:left;">before_range</td><td style="text-align:left;">表示前一种方法的有效时间范围。 当 [T-before_range，T] 范围内的值存在时，前一种方法将起作用。 如果未指定 before_range，则 before_range 会使用默认值 default_fill_interval; -1 表示无穷大； 可选字段</td></tr></tbody></table><p>在这里，我们举一个使用 Previous 方法填充空值的示例。 SQL 语句如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> temperature <span class="token keyword">from</span> root<span class="token punctuation">.</span>sgcc<span class="token punctuation">.</span>wf03<span class="token punctuation">.</span>wt01 <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">=</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">01</span>T16:<span class="token number">37</span>:<span class="token number">50.000</span> fill<span class="token punctuation">(</span>previous<span class="token punctuation">,</span> <span class="token number">1</span>m<span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>含义：</p><p>由于时间根目录 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] 范围）进行填充和显示。</p>`,19),u={href:"https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt",target:"_blank",rel:"noopener noreferrer"},d=a(`<div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-------------------------------+
| Time|root.sgcc.wf03.wt01.temperature|
+-----------------------------+-------------------------------+
|2017-11-01T16:37:50.000+08:00| 21.93|
+-----------------------------+-------------------------------+
Total line number = 1
It costs 0.016s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>值得注意的是，如果在指定的有效时间范围内没有值，系统将不会填充空值，如下所示：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; 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
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="linear-填充" tabindex="-1"><a class="header-anchor" href="#linear-填充"><span>Linear 填充</span></a></h4><p>当查询的时间戳下数据为空时，将使用前一个和下一个时间戳的值来填充空白。 语法定义如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">&lt;</span>path<span class="token operator">&gt;</span> <span class="token keyword">from</span> <span class="token operator">&lt;</span>prefixPath<span class="token operator">&gt;</span> <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">=</span> <span class="token operator">&lt;</span>T<span class="token operator">&gt;</span> fill<span class="token punctuation">(</span>linear<span class="token punctuation">(</span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>before_range<span class="token operator">&gt;</span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>after_range<span class="token operator">&gt;</span><span class="token punctuation">)</span>?<span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>下表给出了所有参数的详细说明：</p><table><thead><tr><th style="text-align:left;">参数名称（不区分大小写）</th><th style="text-align:left;">解释</th></tr></thead><tbody><tr><td style="text-align:left;">path, prefixPath</td><td style="text-align:left;">查询路径； 必填项</td></tr><tr><td style="text-align:left;">T</td><td style="text-align:left;">查询时间戳（只能指定一个）； 必填项</td></tr><tr><td style="text-align:left;">before_range, after_range</td><td style="text-align:left;">表示线性方法的有效时间范围。 当 [T - before_range，T + after_range] 范围内的值存在时，前一种方法将起作用。 如果未明确指定 before_range 和 after_range，则使用 default_fill_interval。 -1 表示无穷大； 可选字段</td></tr></tbody></table><p>需要注意的是一旦时间序列在查询时间戳 T 时刻存在有效值，线性填充就会使用这个值作为结果返回。<br> 除此之外，如果在 [T - before_range, T] 或 [T, T + after_range] 两个范围中任意一个范围内不存在有效填充值，则线性填充返回 null 值。</p><p>在这里，我们举一个使用线性方法填充空值的示例。 SQL 语句如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> temperature <span class="token keyword">from</span> root<span class="token punctuation">.</span>sgcc<span class="token punctuation">.</span>wf03<span class="token punctuation">.</span>wt01 <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">=</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">01</span>T16:<span class="token number">37</span>:<span class="token number">50.000</span> fill<span class="token punctuation">(</span>linear<span class="token punctuation">,</span> <span class="token number">1</span>m<span class="token punctuation">,</span> <span class="token number">1</span>m<span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>含义：</p><p>由于时间根目录 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 以执行线性拟合计算：</p><p>21.927326 +（25.311783-21.927326）/ 60s * 50s = 24.747707</p>`,14),m={href:"https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt",target:"_blank",rel:"noopener noreferrer"},v=a(`<div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-------------------------------+
| Time|root.sgcc.wf03.wt01.temperature|
+-----------------------------+-------------------------------+
|2017-11-01T16:37:50.000+08:00| 24.747707|
+-----------------------------+-------------------------------+
Total line number = 1
It costs 0.017s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="value-填充" tabindex="-1"><a class="header-anchor" href="#value-填充"><span>Value 填充</span></a></h4><p>当查询的时间戳下数据为空时，将使用给定的值来填充空白。语法定义如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">&lt;</span>path<span class="token operator">&gt;</span> <span class="token keyword">from</span> <span class="token operator">&lt;</span>prefixPath<span class="token operator">&gt;</span> <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">=</span> <span class="token operator">&lt;</span>T<span class="token operator">&gt;</span> fill<span class="token punctuation">(</span>constant<span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>下表给出了所有参数的详细说明：</p><table><thead><tr><th style="text-align:left;">参数名称（不区分大小写）</th><th style="text-align:left;">解释</th></tr></thead><tbody><tr><td style="text-align:left;">path, prefixPath</td><td style="text-align:left;">查询路径； 必填项</td></tr><tr><td style="text-align:left;">T</td><td style="text-align:left;">查询时间戳（只能指定一个）； 必填项</td></tr><tr><td style="text-align:left;">constant</td><td style="text-align:left;">给定的填充值；必填项</td></tr></tbody></table><p>需要注意的是一旦时间序列在查询时间戳T时刻存在有效值，特定值填充就会使用这个值作为结果返回。</p><p>在这里，我们举一个使用特定值方法填充空值的示例。 SQL语句如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> temperature <span class="token keyword">from</span> root<span class="token punctuation">.</span>sgcc<span class="token punctuation">.</span>wf03<span class="token punctuation">.</span>wt01 <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">=</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">01</span>T16:<span class="token number">37</span>:<span class="token number">50.000</span> fill<span class="token punctuation">(</span><span class="token number">2.0</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>含义：</p><p>由于时间序列 root.sgcc.wf03.wt01.temperature 在 2017-11-01T16:37:50.000 为空，因此使用给定的值 2.0 进行填充：</p>`,11),k={href:"https://github.com/thulab/iotdb/files/4438687/OtherMaterial-Sample.Data.txt",target:"_blank",rel:"noopener noreferrer"},b=a(`<div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-------------------------------+
| Time|root.sgcc.wf03.wt01.temperature|
+-----------------------------+-------------------------------+
|2017-11-01T16:37:50.000+08:00| 2.0 |
+-----------------------------+-------------------------------+
Total line number = 1
It costs 0.007s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>在使用 VALUE 方法填充时需要注意，如果查询结果的数据类型与输入常量值不同，IoTDB 将不进行填充</p><p>示例：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> temperature <span class="token keyword">from</span> root<span class="token punctuation">.</span>sgcc<span class="token punctuation">.</span>wf03<span class="token punctuation">.</span>wt01 <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">=</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">01</span>T16:<span class="token number">37</span>:<span class="token number">50.000</span> fill<span class="token punctuation">(</span><span class="token string">&#39;test&#39;</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>结果：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-------------------------------+
| Time|root.sgcc.wf03.wt01.temperature|
+-----------------------------+-------------------------------+
|2017-11-01T16:37:50.000+08:00| null |
+-----------------------------+-------------------------------+
Total line number = 1
It costs 0.007s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="降采样补空值" tabindex="-1"><a class="header-anchor" href="#降采样补空值"><span>降采样补空值</span></a></h3><p>IoTDB 支持对原降采样结果进行空值填充，<code>previous</code>、<code>linear</code>、<code>value</code> 填充方式均可作用于查询语句中的任一聚合算子，但一条查询语句只能使用一种空值填充方式。此外，使用时需要注意以下两点：</p><ul><li>在任何情形下都不会填充 <code>count</code> 的聚合结果，因为对于不存在任数据的查询区间，<code>count</code> 的结果为 0。</li><li>分类处理 sum 的聚合结果：若某个查询区间不存在任何数据，sum 的聚合结果为 null，将被 GroupByFill 填充；若某个查询区间内 sum 的聚合结果恰好为 0，那么 GroupByFill 不会填充这个值。</li></ul><p>降采样补空值查询语法同单点补空值查询语法相似，下面列出简单的示例和使用细节：</p><h4 id="previous-和-previousuntillast-的区别" tabindex="-1"><a class="header-anchor" href="#previous-和-previousuntillast-的区别"><span>PREVIOUS 和 PREVIOUSUNTILLAST 的区别</span></a></h4><ul><li>PREVIOUS：只要空值前边有值，就会用其填充空值。</li><li>PREVIOUSUNTILLAST：不会填充此序列最新点后的空值。</li></ul><p>首先检查一下 root.ln.wf01.wt01.temperature 在时间 2017-11-07T23:49:00 以后的值：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; SELECT temperature FROM root.ln.wf01.wt01 where time &gt;= 2017-11-07T23:49:00
+-----------------------------+-----------------------------+
| Time|root.ln.wf01.wt01.temperature|
+-----------------------------+-----------------------------+
|2017-11-07T23:49:00.000+08:00| 23.7|
|2017-11-07T23:51:00.000+08:00| 22.24|
|2017-11-07T23:53:00.000+08:00| 24.58|
|2017-11-07T23:54:00.000+08:00| 22.52|
|2017-11-07T23:57:00.000+08:00| 24.39|
|2017-11-08T00:00:00.000+08:00| 21.07|
+-----------------------------+-----------------------------+
Total line number = 6
It costs 0.010s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>root.ln.wf01.wt01.temperature 最早时间和值是 2017-11-07T23:49:00 和 23.7；最后时间和值是 2017-11-08T00:00:00 和 21.07</p><p>SQL 示例：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_value<span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span>wf01<span class="token punctuation">.</span>wt01 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">50</span>:<span class="token number">00</span><span class="token punctuation">,</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">59</span>:<span class="token number">00</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span>m<span class="token punctuation">)</span> FILL <span class="token punctuation">(</span>PREVIOUSUNTILLAST<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> last_value<span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span>wf01<span class="token punctuation">.</span>wt01 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">50</span>:<span class="token number">00</span><span class="token punctuation">,</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">59</span>:<span class="token number">00</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span>m<span class="token punctuation">)</span> FILL <span class="token punctuation">(</span>PREVIOUS<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p>结果：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (PREVIOUSUNTILLAST);
+-----------------------------+-----------------------------------------+
| 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| 22.24|
|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| 22.52|
|2017-11-07T23:56:00.000+08:00| 22.52|
|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.007s
IoTDB&gt; SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (PREVIOUS);
+-----------------------------+-----------------------------------------+
| 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| 22.24|
|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| 22.52|
|2017-11-07T23:56:00.000+08:00| 22.52|
|2017-11-07T23:57:00.000+08:00| 24.39|
|2017-11-07T23:58:00.000+08:00| 24.39|
+-----------------------------+-----------------------------------------+
Total line number = 9
It costs 0.006s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>解释：</p><p>使用 PREVIOUSUNTILLAST 将不会填充 2017-11-07T23:57:00 以后的值。</p><h4 id="第一个值与最后一个值的填充" tabindex="-1"><a class="header-anchor" href="#第一个值与最后一个值的填充"><span>第一个值与最后一个值的填充</span></a></h4><p>IoTDB 的空值填充方式可以分为 PreviousFill, LinearFill, ValueFill 三大类。其中，PreviousFill 需要知道空值前的第一个非空数据，LinearFill 需要知道空值前后的第一个非空数据才能进行填充。假使某条查询语句返回的结果中第一个或最后一个值为空，就可能导致结果集在首尾存在一段连续的空值，不满足 GroupByFill 的业务期望。</p><p>在上例中，第一个时间区间 [2017-11-07T23:50:00, 2017-11-07T23:51:00) 内没有任何数据，上一个有数据的时间区间是 [2017-11-01T23:49:00, 2017-11-07T23:50:00)，可以通过设置 PREVIOUS 填充向前查询参数 beforeRange 来填充第一个区间的数据，示例如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_value<span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span>wf01<span class="token punctuation">.</span>wt01 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">50</span>:<span class="token number">00</span><span class="token punctuation">,</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">59</span>:<span class="token number">00</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span>m<span class="token punctuation">)</span> FILL <span class="token punctuation">(</span>PREVIOUS<span class="token punctuation">,</span> <span class="token number">1</span>m<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>结果：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; 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);
+-----------------------------+-----------------------------------------+
| Time|last_value(root.ln.wf01.wt01.temperature)|
+-----------------------------+-----------------------------------------+
|2017-11-07T23:50:00.000+08:00| 23.7|
|2017-11-07T23:51:00.000+08:00| 22.24|
|2017-11-07T23:52:00.000+08:00| 22.24|
|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| 22.52|
|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| 24.39|
+-----------------------------+-----------------------------------------+
Total line number = 9
It costs 0.005s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>解释：</p><p>为了不与原有语义冲突，当不设置 before_range, after_range 参数时，GroupByFill 的空值填充取空值的前一个/后一个非空值完成；当设置 before_range, after_range 参数时，设空值所在记录的时间戳为 t，GroupByFill 取 [t-before_range, t+after_range) 内的前一个/后一个非空值完成填充。</p><p>因为时间区间 [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) 的数据无法填充。</p><p>before_range, after_range 参数也可辅助 LINEAR 方式进行填充，示例如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_value<span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span>wf01<span class="token punctuation">.</span>wt01 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">50</span>:<span class="token number">00</span><span class="token punctuation">,</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">59</span>:<span class="token number">00</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span>m<span class="token punctuation">)</span> FILL <span class="token punctuation">(</span>LINEAR<span class="token punctuation">,</span> <span class="token number">5</span>m<span class="token punctuation">,</span> <span class="token number">5</span>m<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>结果：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; 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| 22.970001|
|2017-11-07T23:51:00.000+08:00| 22.24|
|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| 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| 23.283333|
+-----------------------------+-----------------------------------------+
Total line number = 9
It costs 0.008s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>注意：设原始降采样查询区间为 [start_time, end_time)，在指定 before_range, after_range 参数后，降采样查询结果不变，但查询区间将转变为 [start_time - before_range, end_time + after_range)。因此这两个参数设置较大时会影响效率，使用时需注意。</p></blockquote><h4 id="value-填充-1" tabindex="-1"><a class="header-anchor" href="#value-填充-1"><span>Value 填充</span></a></h4><p>值填充方式会将输入的常量值解析为字符串，填充时尝试将字符串常量转换为对应类型的数据，若转换成功则进行填充，否则就不填充。举例如下：</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_value<span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span>wf01<span class="token punctuation">.</span>wt01 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">50</span>:<span class="token number">00</span><span class="token punctuation">,</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">59</span>:<span class="token number">00</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span>m<span class="token punctuation">)</span> FILL <span class="token punctuation">(</span><span class="token number">20.0</span><span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> last_value<span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span>wf01<span class="token punctuation">.</span>wt01 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">50</span>:<span class="token number">00</span><span class="token punctuation">,</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">07</span>T23:<span class="token number">59</span>:<span class="token number">00</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span>m<span class="token punctuation">)</span> FILL <span class="token punctuation">(</span><span class="token string">&#39;temperature&#39;</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p>结果：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; 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| 20.0|
|2017-11-07T23:51:00.000+08:00| 22.24|
|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| 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| 20.0|
+-----------------------------+-----------------------------------------+
Total line number = 9
It costs 0.007s
IoTDB&gt; SELECT last_value(temperature) FROM root.ln.wf01.wt01 GROUP BY([2017-11-07T23:50:00, 2017-11-07T23:59:00),1m) FILL (&#39;temperature&#39;);
+-----------------------------+-----------------------------------------+
| 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
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div>`,40);function g(T,h){const e=p("ExternalLinkIcon");return i(),o("div",null,[c,s("p",null,[n("在 "),s("a",u,[n("样例数据"),t(e)]),n(" 中, 该语句的执行结果如下所示：")]),d,s("p",null,[n("在 "),s("a",m,[n("样例数据"),t(e)]),n(" 中, 该语句的执行结果如下所示：")]),v,s("p",null,[n("在 "),s("a",k,[n("样例数据"),t(e)]),n(" 中, 该语句的执行结果如下所示：")]),b])}const w=l(r,[["render",g],["__file","Fill-Null-Value.html.vue"]]),x=JSON.parse('{"path":"/zh/UserGuide/V0.13.x/Query-Data/Fill-Null-Value.html","title":"","lang":"zh-CN","frontmatter":{"description":"查询补空值 当对序列进行分段聚合时，某一段时间可能不存在数据，则此段数据的聚合结果为空，但这种空值不利于进行数据可视化展示和分析，需要对空值进行填补。 查询补空值允许用户按照特定的方法对查询结果填充空值，如取前一个不为空的值，或线性插值。补空值之后的查询结果能更好地反映数据分布，有利于用户进行数据分析。 在 IoTDB 中，用户可以使用 FILL 子句...","head":[["link",{"rel":"alternate","hreflang":"en-us","href":"https://iotdb.apache.org/UserGuide/V0.13.x/Query-Data/Fill-Null-Value.html"}],["meta",{"property":"og:url","content":"https://iotdb.apache.org/zh/UserGuide/V0.13.x/Query-Data/Fill-Null-Value.html"}],["meta",{"property":"og:site_name","content":"IoTDB Website"}],["meta",{"property":"og:description","content":"查询补空值 当对序列进行分段聚合时，某一段时间可能不存在数据，则此段数据的聚合结果为空，但这种空值不利于进行数据可视化展示和分析，需要对空值进行填补。 查询补空值允许用户按照特定的方法对查询结果填充空值，如取前一个不为空的值，或线性插值。补空值之后的查询结果能更好地反映数据分布，有利于用户进行数据分析。 在 IoTDB 中，用户可以使用 FILL 子句..."}],["meta",{"property":"og:type","content":"article"}],["meta",{"property":"og:locale","content":"zh-CN"}],["meta",{"property":"og:locale:alternate","content":"en-US"}],["meta",{"property":"og:updated_time","content":"2023-07-10T03:11:17.000Z"}],["meta",{"property":"article:modified_time","content":"2023-07-10T03:11:17.000Z"}],["script",{"type":"application/ld+json"},"{\\"@context\\":\\"https://schema.org\\",\\"@type\\":\\"Article\\",\\"headline\\":\\"\\",\\"image\\":[\\"\\"],\\"dateModified\\":\\"2023-07-10T03:11:17.000Z\\",\\"author\\":[]}"]]},"headers":[{"level":2,"title":"查询补空值","slug":"查询补空值","link":"#查询补空值","children":[{"level":3,"title":"填充方法","slug":"填充方法","link":"#填充方法","children":[]},{"level":3,"title":"单点补空值","slug":"单点补空值","link":"#单点补空值","children":[]},{"level":3,"title":"降采样补空值","slug":"降采样补空值","link":"#降采样补空值","children":[]}]}],"git":{"createdTime":1688958677000,"updatedTime":1688958677000,"contributors":[{"name":"CritasWang","email":"critas@outlook.com","commits":1}]},"readingTime":{"minutes":10.96,"words":3288},"filePathRelative":"zh/UserGuide/V0.13.x/Query-Data/Fill-Null-Value.md","localizedDate":"2023年7月10日","autoDesc":true}');export{w as comp,x as data};