Google Git
Sign in
apache / iotdb-website / 75d2a7733301c2f3abe03a30fe5266762f120133 / . / assets / Featured-Functions.html-Cc_jS9rs.js
blob: 230651a8e879054bcf66da38b863f209e59652b0 [file] [log] [blame]
import{_ as e,c as p,b as a,d as i,e as n,a as o,f as c,r,o as t}from"./app-BWhNjvfs.js";const d={};function u(v,s){const l=r("RouteLink");return t(),p("div",null,[s[3]||(s[3]=a('<h1 id="特色函数" tabindex="-1"><a class="header-anchor" href="#特色函数"><span>特色函数</span></a></h1><h2 id="_1-时序特色函数" tabindex="-1"><a class="header-anchor" href="#_1-时序特色函数"><span>1. 时序特色函数</span></a></h2><h3 id="_1-1-降采样函数" tabindex="-1"><a class="header-anchor" href="#_1-1-降采样函数"><span>1.1 降采样函数</span></a></h3><h4 id="_1-1-1-date-bin-函数" tabindex="-1"><a class="header-anchor" href="#_1-1-1-date-bin-函数"><span>1.1.1 <code>date_bin</code> 函数</span></a></h4><h5 id="功能描述" tabindex="-1"><a class="header-anchor" href="#功能描述"><span>功能描述：</span></a></h5><p><code>date_bin</code> 是一个标量函数，用于将时间戳规整到指定的时间区间起点，并结合 <code>GROUP BY</code> 子句实现降采样。</p><ul><li>部分区间结果为空：只会对满足条件的数据进行时间戳规整，不会填充缺失的时间区间。</li><li>全部区间结果为空：：满足条件的整个查询范围内没有数据时，降采样返回空结果集</li></ul><h5 id="使用示例" tabindex="-1"><a class="header-anchor" href="#使用示例"><span>使用示例：</span></a></h5><h6 id="示例数据" tabindex="-1"><a class="header-anchor" href="#示例数据"><span>示例数据</span></a></h6>',9)),i("p",null,[s[1]||(s[1]=n("在")),o(l,{to:"/zh/UserGuide/latest-Table/Reference/Sample-Data.html"},{default:c(()=>s[0]||(s[0]=[n("示例数据页面")])),_:1}),s[2]||(s[2]=n("中，包含了用于构建表结构和插入数据的SQL语句，下载并在IoTDB CLI中执行这些语句，即可将数据导入IoTDB，您可以使用这些数据来测试和执行示例中的SQL语句，并获得相应的结果。"))]),s[4]||(s[4]=a(`<p>示例 1：获取设备** <strong><code>100</code></strong> **某个时间范围的每小时平均温度</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#ABB2BF;"> date_bin(1h, </span><span style="color:#C678DD;">time</span><span style="color:#ABB2BF;">) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> hour_time, </span><span style="color:#56B6C2;">avg</span><span style="color:#ABB2BF;">(temperature) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> avg_temp</span></span>
<span class="line"><span style="color:#C678DD;">FROM</span><span style="color:#ABB2BF;"> table1</span></span>
<span class="line"><span style="color:#C678DD;">WHERE</span><span style="color:#ABB2BF;"> (</span><span style="color:#C678DD;">time</span><span style="color:#56B6C2;"> &gt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">27</span><span style="color:#D19A66;"> 00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#C678DD;"> AND</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &lt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">30</span><span style="color:#D19A66;"> 00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">)</span></span>
<span class="line"><span style="color:#C678DD;"> AND</span><span style="color:#ABB2BF;"> device_id </span><span style="color:#56B6C2;">=</span><span style="color:#98C379;"> &#39;100&#39;</span></span>
<span class="line"><span style="color:#C678DD;">GROUP BY</span><span style="color:#D19A66;"> 1</span><span style="color:#ABB2BF;">;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+-----------------------------+--------+</span></span>
<span class="line"><span>| hour_time|avg_temp|</span></span>
<span class="line"><span>+-----------------------------+--------+</span></span>
<span class="line"><span>|2024-11-29T11:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-29T18:00:00.000+08:00| 90.0|</span></span>
<span class="line"><span>|2024-11-28T08:00:00.000+08:00| 85.0|</span></span>
<span class="line"><span>|2024-11-28T09:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T10:00:00.000+08:00| 85.0|</span></span>
<span class="line"><span>|2024-11-28T11:00:00.000+08:00| 88.0|</span></span>
<span class="line"><span>+-----------------------------+--------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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>示例 2：获取每个设备某个时间范围的每小时平均温度</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#ABB2BF;"> date_bin(1h, </span><span style="color:#C678DD;">time</span><span style="color:#ABB2BF;">) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> hour_time, device_id, </span><span style="color:#56B6C2;">avg</span><span style="color:#ABB2BF;">(temperature) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> avg_temp</span></span>
<span class="line"><span style="color:#C678DD;">FROM</span><span style="color:#ABB2BF;"> table1</span></span>
<span class="line"><span style="color:#C678DD;">WHERE</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &gt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">27</span><span style="color:#D19A66;"> 00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#C678DD;"> AND</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &lt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">30</span><span style="color:#D19A66;"> 00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span></span>
<span class="line"><span style="color:#C678DD;">GROUP BY</span><span style="color:#D19A66;"> 1</span><span style="color:#ABB2BF;">, device_id;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+-----------------------------+---------+--------+</span></span>
<span class="line"><span>| hour_time|device_id|avg_temp|</span></span>
<span class="line"><span>+-----------------------------+---------+--------+</span></span>
<span class="line"><span>|2024-11-29T11:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>|2024-11-29T18:00:00.000+08:00| 100| 90.0|</span></span>
<span class="line"><span>|2024-11-28T08:00:00.000+08:00| 100| 85.0|</span></span>
<span class="line"><span>|2024-11-28T09:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>|2024-11-28T10:00:00.000+08:00| 100| 85.0|</span></span>
<span class="line"><span>|2024-11-28T11:00:00.000+08:00| 100| 88.0|</span></span>
<span class="line"><span>|2024-11-29T10:00:00.000+08:00| 101| 85.0|</span></span>
<span class="line"><span>|2024-11-27T16:00:00.000+08:00| 101| 85.0|</span></span>
<span class="line"><span>+-----------------------------+---------+--------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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>示例 3：获取所有设备某个时间范围的每小时平均温度</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#ABB2BF;"> date_bin(1h, </span><span style="color:#C678DD;">time</span><span style="color:#ABB2BF;">) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> hour_time, </span><span style="color:#56B6C2;">avg</span><span style="color:#ABB2BF;">(temperature) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> avg_temp</span></span>
<span class="line"><span style="color:#C678DD;"> FROM</span><span style="color:#ABB2BF;"> table1</span></span>
<span class="line"><span style="color:#C678DD;"> WHERE</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &gt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">27</span><span style="color:#D19A66;"> 00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#C678DD;"> AND</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &lt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">30</span><span style="color:#D19A66;"> 00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span></span>
<span class="line"><span style="color:#C678DD;"> group by</span><span style="color:#D19A66;"> 1</span><span style="color:#ABB2BF;">;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+-----------------------------+--------+</span></span>
<span class="line"><span>| hour_time|avg_temp|</span></span>
<span class="line"><span>+-----------------------------+--------+</span></span>
<span class="line"><span>|2024-11-29T10:00:00.000+08:00| 85.0|</span></span>
<span class="line"><span>|2024-11-27T16:00:00.000+08:00| 85.0|</span></span>
<span class="line"><span>|2024-11-29T11:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-29T18:00:00.000+08:00| 90.0|</span></span>
<span class="line"><span>|2024-11-28T08:00:00.000+08:00| 85.0|</span></span>
<span class="line"><span>|2024-11-28T09:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T10:00:00.000+08:00| 85.0|</span></span>
<span class="line"><span>|2024-11-28T11:00:00.000+08:00| 88.0|</span></span>
<span class="line"><span>+-----------------------------+--------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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><h4 id="_1-1-2-date-bin-gapfill-函数" tabindex="-1"><a class="header-anchor" href="#_1-1-2-date-bin-gapfill-函数"><span>1.1.2 <code>date_bin_gapfill</code> 函数</span></a></h4><h5 id="功能描述-1" tabindex="-1"><a class="header-anchor" href="#功能描述-1"><span>功能描述</span></a></h5><p><code>date_bin_gapfill</code> 是 <code>date_bin</code> 的扩展，能够填充缺失的时间区间，从而返回完整的时间序列。</p><ul><li>部分区间结果为空：对满足条件的数据进行时间戳规整，并填充缺失的时间区间。</li><li>全部区间结果为空：：整个查询范围内没有数据时，<code>date_bin_gapfill</code>会返回空结果集</li></ul><h5 id="功能限制" tabindex="-1"><a class="header-anchor" href="#功能限制"><span>功能限制</span></a></h5><ul><li><strong><code>date_bin_gapfill</code></strong> <strong>必须与</strong> <strong><code>GROUP BY</code></strong> <strong>子句搭配使用</strong>，如果用在其他子句中，不会报错，但不会执行 gapfill 功能，效果与使用 <code>date_bin</code> 相同。</li><li><strong>每个</strong> <strong><code>GROUP BY</code></strong> <strong>子句中只能使用一个</strong> <strong><code>date_bin_gapfill</code></strong>。如果出现多个 <code>date_bin_gapfill</code>，会报错：multiple date_bin_gapfill calls not allowed</li><li><strong><code>date_bin_gapfill</code></strong> <strong>的执行顺序</strong>：GAPFILL 功能发生在 <code>HAVING</code> 子句执行之后，<code>FILL</code> 子句执行之前。</li><li><strong>使用</strong> <strong><code>date_bin_gapfill</code></strong> <strong>时，****<code>WHERE</code></strong> <strong>子句中的时间过滤条件必须是以下形式之一：</strong><ul><li><code>time &gt;= XXX AND time &lt;= XXX</code></li><li><code>time &gt; XXX AND time &lt; XXX</code></li><li><code>time BETWEEN XXX AND XXX</code></li></ul></li><li><strong>使用</strong> <strong><code>date_bin_gapfill</code></strong> <strong>时，如果出现其他时间过滤条件</strong>，会报错。时间过滤条件与其他值过滤条件只能通过 <code>AND</code> 连接。</li><li><strong>如果不能从 where 子句中推断出 startTime 和 endTime，则报错</strong>：could not infer startTime or endTime from WHERE clause。</li></ul><h5 id="使用示例-1" tabindex="-1"><a class="header-anchor" href="#使用示例-1"><span>使用示例</span></a></h5><p>示例 1：填充缺失时间区间</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#ABB2BF;"> date_bin_gapfill(1h, </span><span style="color:#C678DD;">time</span><span style="color:#ABB2BF;">) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> hour_time, </span><span style="color:#56B6C2;">avg</span><span style="color:#ABB2BF;">(temperature) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> avg_temp</span></span>
<span class="line"><span style="color:#C678DD;">FROM</span><span style="color:#ABB2BF;"> table1</span></span>
<span class="line"><span style="color:#C678DD;">WHERE</span><span style="color:#ABB2BF;"> (</span><span style="color:#C678DD;">time</span><span style="color:#56B6C2;"> &gt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">28</span><span style="color:#D19A66;"> 07</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#C678DD;"> AND</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &lt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">28</span><span style="color:#D19A66;"> 16</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">)</span></span>
<span class="line"><span style="color:#C678DD;"> AND</span><span style="color:#ABB2BF;"> device_id </span><span style="color:#56B6C2;">=</span><span style="color:#98C379;"> &#39;100&#39;</span></span>
<span class="line"><span style="color:#C678DD;">GROUP BY</span><span style="color:#D19A66;"> 1</span><span style="color:#ABB2BF;">;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+-----------------------------+--------+</span></span>
<span class="line"><span>| hour_time|avg_temp|</span></span>
<span class="line"><span>+-----------------------------+--------+</span></span>
<span class="line"><span>|2024-11-28T07:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T08:00:00.000+08:00| 85.0|</span></span>
<span class="line"><span>|2024-11-28T09:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T10:00:00.000+08:00| 85.0|</span></span>
<span class="line"><span>|2024-11-28T11:00:00.000+08:00| 88.0|</span></span>
<span class="line"><span>|2024-11-28T12:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T13:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T14:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T15:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T16:00:00.000+08:00| null|</span></span>
<span class="line"><span>+-----------------------------+--------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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>示例 2：结合设备分组填充缺失时间区间</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#ABB2BF;"> date_bin_gapfill(1h, </span><span style="color:#C678DD;">time</span><span style="color:#ABB2BF;">) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> hour_time, device_id, </span><span style="color:#56B6C2;">avg</span><span style="color:#ABB2BF;">(temperature) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> avg_temp</span></span>
<span class="line"><span style="color:#C678DD;">FROM</span><span style="color:#ABB2BF;"> table1</span></span>
<span class="line"><span style="color:#C678DD;">WHERE</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &gt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">28</span><span style="color:#D19A66;"> 07</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#C678DD;"> AND</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &lt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">28</span><span style="color:#D19A66;"> 16</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span></span>
<span class="line"><span style="color:#C678DD;">GROUP BY</span><span style="color:#D19A66;"> 1</span><span style="color:#ABB2BF;">, device_id;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+-----------------------------+---------+--------+</span></span>
<span class="line"><span>| hour_time|device_id|avg_temp|</span></span>
<span class="line"><span>+-----------------------------+---------+--------+</span></span>
<span class="line"><span>|2024-11-28T07:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>|2024-11-28T08:00:00.000+08:00| 100| 85.0|</span></span>
<span class="line"><span>|2024-11-28T09:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>|2024-11-28T10:00:00.000+08:00| 100| 85.0|</span></span>
<span class="line"><span>|2024-11-28T11:00:00.000+08:00| 100| 88.0|</span></span>
<span class="line"><span>|2024-11-28T12:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>|2024-11-28T13:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>|2024-11-28T14:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>|2024-11-28T15:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>|2024-11-28T16:00:00.000+08:00| 100| null|</span></span>
<span class="line"><span>+-----------------------------+---------+--------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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>示例 3：查询范围内没有数据返回空结果集</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#ABB2BF;"> date_bin_gapfill(1h, </span><span style="color:#C678DD;">time</span><span style="color:#ABB2BF;">) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> hour_time, device_id, </span><span style="color:#56B6C2;">avg</span><span style="color:#ABB2BF;">(temperature) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> avg_temp</span></span>
<span class="line"><span style="color:#C678DD;">FROM</span><span style="color:#ABB2BF;"> table1</span></span>
<span class="line"><span style="color:#C678DD;">WHERE</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &gt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">27</span><span style="color:#D19A66;"> 09</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#C678DD;"> AND</span><span style="color:#C678DD;"> time</span><span style="color:#56B6C2;"> &lt;=</span><span style="color:#D19A66;"> 2024</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">11</span><span style="color:#ABB2BF;">-</span><span style="color:#D19A66;">27</span><span style="color:#D19A66;"> 14</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span><span style="color:#ABB2BF;">:</span><span style="color:#D19A66;">00</span></span>
<span class="line"><span style="color:#C678DD;">GROUP BY</span><span style="color:#D19A66;"> 1</span><span style="color:#ABB2BF;">, device_id;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+---------+---------+--------+</span></span>
<span class="line"><span>|hour_time|device_id|avg_temp|</span></span>
<span class="line"><span>+---------+---------+--------+</span></span>
<span class="line"><span>+---------+---------+--------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="_1-2-diff函数" tabindex="-1"><a class="header-anchor" href="#_1-2-diff函数"><span>1.2 DIFF函数</span></a></h3><h5 id="功能概述" tabindex="-1"><a class="header-anchor" href="#功能概述"><span>功能概述</span></a></h5><p><code>DIFF</code> 函数用于计算当前行与上一行的差值。对于第一行，由于没有前一行数据，因此永远返回 <code>NULL</code>。</p><h5 id="函数定义" tabindex="-1"><a class="header-anchor" href="#函数定义"><span>函数定义</span></a></h5><div class="language- line-numbers-mode" data-highlighter="shiki" data-ext="" data-title="" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>DIFF(numberic[, boolean]) -&gt; Double</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><div class="line-number"></div></div></div><h5 id="参数说明" tabindex="-1"><a class="header-anchor" href="#参数说明"><span>参数说明</span></a></h5><ul><li><p>第一个参数：数值类型</p><ul><li><strong>类型</strong>：必须是数值类型（<code>INT32</code>、<code>INT64</code>、<code>FLOAT</code>、<code>DOUBLE</code>）</li><li><strong>作用</strong>：指定要计算差值的列。</li></ul></li><li><p>第二个参数：布尔类型（可选）</p><ul><li><strong>类型</strong>：布尔类型（<code>true</code> 或 <code>false</code>）。</li><li><strong>默认值</strong>：<code>true</code>。</li><li><strong>作用</strong>： <ul><li><strong><code>true</code></strong>：忽略 <code>NULL</code> 值，向前找到第一个非 <code>NULL</code> 值进行差值计算。如果前面没有非 <code>NULL</code> 值，则返回 <code>NULL</code>。</li><li><strong><code>false</code></strong>：不忽略 <code>NULL</code> 值，如果前一行为 <code>NULL</code>，则差值结果为 <code>NULL</code>。</li></ul></li></ul></li></ul><h5 id="注意事项" tabindex="-1"><a class="header-anchor" href="#注意事项"><span>注意事项</span></a></h5><ul><li>在树模型中，第二个参数需要指定为 <code>&#39;ignoreNull&#39;=&#39;true&#39;</code> 或 <code>&#39;ignoreNull&#39;=&#39;false&#39;</code>，但在表模型中，只需指定为 <code>true</code> 或 <code>false</code>。</li><li>如果用户写成 <code>&#39;ignoreNull&#39;=&#39;true&#39;</code> 或 <code>&#39;ignoreNull&#39;=&#39;false&#39;</code>，表模型会将其视为对两个字符串常量进行等号比较，返回布尔值，但结果总是 <code>false</code>，等价于指定第二个参数为 <code>false</code>。</li></ul><h5 id="使用示例-2" tabindex="-1"><a class="header-anchor" href="#使用示例-2"><span>使用示例</span></a></h5><p>示例 1：忽略 <code>NULL</code> 值</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#C678DD;"> time</span><span style="color:#ABB2BF;">, DIFF(temperature) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> diff_temp</span></span>
<span class="line"><span style="color:#C678DD;">FROM</span><span style="color:#ABB2BF;"> table1</span></span>
<span class="line"><span style="color:#C678DD;">WHERE</span><span style="color:#ABB2BF;"> device_id </span><span style="color:#56B6C2;">=</span><span style="color:#98C379;"> &#39;100&#39;</span><span style="color:#ABB2BF;">;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>结果：</p><div class="language-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+-----------------------------+---------+</span></span>
<span class="line"><span>| time|diff_temp|</span></span>
<span class="line"><span>+-----------------------------+---------+</span></span>
<span class="line"><span>|2024-11-29T11:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-29T18:30:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T08:00:00.000+08:00| -5.0|</span></span>
<span class="line"><span>|2024-11-28T09:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T10:00:00.000+08:00| 0.0|</span></span>
<span class="line"><span>|2024-11-28T11:00:00.000+08:00| 3.0|</span></span>
<span class="line"><span>|2024-11-26T13:37:00.000+08:00| 2.0|</span></span>
<span class="line"><span>|2024-11-26T13:38:00.000+08:00| 0.0|</span></span>
<span class="line"><span>+-----------------------------+---------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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>示例 2：不忽略 <code>NULL</code> 值</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#C678DD;"> time</span><span style="color:#ABB2BF;">, DIFF(temperature, false) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> diff_temp</span></span>
<span class="line"><span style="color:#C678DD;">FROM</span><span style="color:#ABB2BF;"> table1</span></span>
<span class="line"><span style="color:#C678DD;">WHERE</span><span style="color:#ABB2BF;"> device_id </span><span style="color:#56B6C2;">=</span><span style="color:#98C379;"> &#39;100&#39;</span><span style="color:#ABB2BF;">;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>结果：</p><div class="language-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+-----------------------------+---------+</span></span>
<span class="line"><span>| time|diff_temp|</span></span>
<span class="line"><span>+-----------------------------+---------+</span></span>
<span class="line"><span>|2024-11-29T11:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-29T18:30:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T08:00:00.000+08:00| -5.0|</span></span>
<span class="line"><span>|2024-11-28T09:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T10:00:00.000+08:00| null|</span></span>
<span class="line"><span>|2024-11-28T11:00:00.000+08:00| 3.0|</span></span>
<span class="line"><span>|2024-11-26T13:37:00.000+08:00| 2.0|</span></span>
<span class="line"><span>|2024-11-26T13:38:00.000+08:00| 0.0|</span></span>
<span class="line"><span>+-----------------------------+---------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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>示例 3：完整示例</p><div class="language-sql line-numbers-mode" data-highlighter="shiki" data-ext="sql" data-title="sql" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span style="color:#C678DD;">SELECT</span><span style="color:#C678DD;"> time</span><span style="color:#ABB2BF;">, temperature, </span></span>
<span class="line"><span style="color:#ABB2BF;"> DIFF(temperature) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> diff_temp_1,</span></span>
<span class="line"><span style="color:#ABB2BF;"> DIFF(temperature, false) </span><span style="color:#C678DD;">AS</span><span style="color:#ABB2BF;"> diff_temp_2</span></span>
<span class="line"><span style="color:#C678DD;">FROM</span><span style="color:#ABB2BF;"> table1 </span></span>
<span class="line"><span style="color:#C678DD;">WHERE</span><span style="color:#ABB2BF;"> device_id </span><span style="color:#56B6C2;">=</span><span style="color:#98C379;"> &#39;100&#39;</span><span style="color:#ABB2BF;">;</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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-plain line-numbers-mode" data-highlighter="shiki" data-ext="plain" data-title="plain" style="background-color:#282c34;color:#abb2bf;"><pre class="shiki one-dark-pro vp-code"><code><span class="line"><span>+-----------------------------+-----------+-----------+-----------+</span></span>
<span class="line"><span>| time|temperature|diff_temp_1|diff_temp_2|</span></span>
<span class="line"><span>+-----------------------------+-----------+-----------+-----------+</span></span>
<span class="line"><span>|2024-11-29T11:00:00.000+08:00| null| null| null|</span></span>
<span class="line"><span>|2024-11-29T18:30:00.000+08:00| 90.0| null| null|</span></span>
<span class="line"><span>|2024-11-28T08:00:00.000+08:00| 85.0| -5.0| -5.0|</span></span>
<span class="line"><span>|2024-11-28T09:00:00.000+08:00| null| null| null|</span></span>
<span class="line"><span>|2024-11-28T10:00:00.000+08:00| 85.0| 0.0| null|</span></span>
<span class="line"><span>|2024-11-28T11:00:00.000+08:00| 88.0| 3.0| 3.0|</span></span>
<span class="line"><span>|2024-11-26T13:37:00.000+08:00| 90.0| 2.0| 2.0|</span></span>
<span class="line"><span>|2024-11-26T13:38:00.000+08:00| 90.0| 0.0| 0.0|</span></span>
<span class="line"><span>+-----------------------------+-----------+-----------+-----------+</span></span></code></pre><div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0;"><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>`,53))])}const y=e(d,[["render",u],["__file","Featured-Functions.html.vue"]]),m=JSON.parse('{"path":"/zh/UserGuide/latest-Table/SQL-Manual/Featured-Functions.html","title":"特色函数","lang":"zh-CN","frontmatter":{"description":"特色函数 1. 时序特色函数 1.1 降采样函数 1.1.1 date_bin 函数 功能描述： date_bin 是一个标量函数，用于将时间戳规整到指定的时间区间起点，并结合 GROUP BY 子句实现降采样。 部分区间结果为空：只会对满足条件的数据进行时间戳规整，不会填充缺失的时间区间。 全部区间结果为空：：满足条件的整个查询范围内没有数据时，降采...","head":[["link",{"rel":"alternate","hreflang":"en-us","href":"https://iotdb.apache.org/UserGuide/latest-Table/SQL-Manual/Featured-Functions.html"}],["meta",{"property":"og:url","content":"https://iotdb.apache.org/zh/UserGuide/latest-Table/SQL-Manual/Featured-Functions.html"}],["meta",{"property":"og:site_name","content":"IoTDB Website"}],["meta",{"property":"og:title","content":"特色函数"}],["meta",{"property":"og:description","content":"特色函数 1. 时序特色函数 1.1 降采样函数 1.1.1 date_bin 函数 功能描述： date_bin 是一个标量函数，用于将时间戳规整到指定的时间区间起点，并结合 GROUP BY 子句实现降采样。 部分区间结果为空：只会对满足条件的数据进行时间戳规整，不会填充缺失的时间区间。 全部区间结果为空：：满足条件的整个查询范围内没有数据时，降采..."}],["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":"2025-03-11T09:16:06.000Z"}],["meta",{"property":"article:modified_time","content":"2025-03-11T09:16:06.000Z"}],["script",{"type":"application/ld+json"},"{\\"@context\\":\\"https://schema.org\\",\\"@type\\":\\"Article\\",\\"headline\\":\\"特色函数\\",\\"image\\":[\\"\\"],\\"dateModified\\":\\"2025-03-11T09:16:06.000Z\\",\\"author\\":[]}"]]},"headers":[{"level":2,"title":"1. 时序特色函数","slug":"_1-时序特色函数","link":"#_1-时序特色函数","children":[{"level":3,"title":"1.1 降采样函数","slug":"_1-1-降采样函数","link":"#_1-1-降采样函数","children":[]},{"level":3,"title":"1.2 DIFF函数","slug":"_1-2-diff函数","link":"#_1-2-diff函数","children":[]}]}],"git":{"createdTime":1739452628000,"updatedTime":1741684566000,"contributors":[{"name":"W1y1r","username":"W1y1r","email":"150988475+W1y1r@users.noreply.github.com","commits":1,"url":"https://github.com/W1y1r"},{"name":"leto-b","username":"leto-b","email":"bingqian.bai@timecho.com","commits":2,"url":"https://github.com/leto-b"},{"name":"xiangmy21","username":"xiangmy21","email":"90919434+xiangmy21@users.noreply.github.com","commits":1,"url":"https://github.com/xiangmy21"}]},"readingTime":{"minutes":6.35,"words":1906},"filePathRelative":"zh/UserGuide/latest-Table/SQL-Manual/Featured-Functions.md","localizedDate":"2025年2月13日","autoDesc":true}');export{y as comp,m as data};