| import{_ as n,O as s,P as e,ah as a,aW as o}from"./framework-62ad666a.js";const l={},t=o(`<h2 id="结果集排序" tabindex="-1"><a class="header-anchor" href="#结果集排序" aria-hidden="true">#</a> 结果集排序</h2><h3 id="时间对齐模式下的排序" tabindex="-1"><a class="header-anchor" href="#时间对齐模式下的排序" aria-hidden="true">#</a> 时间对齐模式下的排序</h3><p>IoTDB的查询结果集默认按照时间对齐,可以使用<code>ORDER BY TIME</code>的子句指定时间戳的排列顺序。示例代码如下:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span><span class="token operator">*</span><span class="token operator">*</span> <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>T00:<span class="token number">01</span>:<span class="token number">00</span> <span class="token keyword">order</span> <span class="token keyword">by</span> <span class="token keyword">time</span> <span class="token keyword">desc</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"><pre class="language-text"><code>+-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+ |
| | Time|root.ln.wf02.wt02.hardware|root.ln.wf02.wt02.status|root.ln.wf01.wt01.temperature|root.ln.wf01.wt01.status| |
| +-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+ |
| |2017-11-01T00:01:00.000+08:00| v2| true| 24.36| true| |
| |2017-11-01T00:00:00.000+08:00| v2| true| 25.96| true| |
| |1970-01-01T08:00:00.002+08:00| v2| false| null| null| |
| |1970-01-01T08:00:00.001+08:00| v1| true| null| null| |
| +-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+ |
| Total line number = 4 |
| </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></div><h3 id="设备对齐模式下的排序" tabindex="-1"><a class="header-anchor" href="#设备对齐模式下的排序" aria-hidden="true">#</a> 设备对齐模式下的排序</h3><p>当使用<code>ALIGN BY DEVICE</code>查询对齐模式下的结果集时,可以使用<code>ORDER BY</code>子句对返回的结果集顺序进行规定。</p><p>在设备对齐模式下支持4种排序模式的子句,其中包括两种排序键,<code>DEVICE</code>和<code>TIME</code>,靠前的排序键为主排序键,每种排序键都支持<code>ASC</code>和<code>DESC</code>两种排列顺序。</p><ol><li><p><code>ORDER BY DEVICE</code>: 按照设备名的字典序进行排序,排序方式为字典序排序,在这种情况下,相同名的设备会以组的形式进行展示。</p></li><li><p><code>ORDER BY TIME</code>: 按照时间戳进行排序,此时不同的设备对应的数据点会按照时间戳的优先级被打乱排序。</p></li><li><p><code>ORDER BY DEVICE,TIME</code>: 按照设备名的字典序进行排序,设备名相同的数据点会通过时间戳进行排序。</p></li><li><p><code>ORDER BY TIME,DEVICE</code>: 按照时间戳进行排序,时间戳相同的数据点会通过设备名的字典序进行排序。</p></li></ol><blockquote><p>为了保证结果的可观性,当不使用<code>ORDER BY</code>子句,仅使用<code>ALIGN BY DEVICE</code>时,会为设备视图提供默认的排序方式。其中默认的排序视图为<code>ORDER BY DEVCE,TIME</code>,默认的排序顺序为<code>ASC</code>, 即结果集默认先按照设备名升序排列,在相同设备名内再按照时间戳升序排序。</p></blockquote><p>当主排序键为<code>DEVICE</code>时,结果集的格式与默认情况类似:先按照设备名对结果进行排列,在相同的设备名下内按照时间戳进行排序。示例代码如下:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span><span class="token operator">*</span><span class="token operator">*</span> <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>T00:<span class="token number">01</span>:<span class="token number">00</span> <span class="token keyword">order</span> <span class="token keyword">by</span> device <span class="token keyword">desc</span><span class="token punctuation">,</span><span class="token keyword">time</span> <span class="token keyword">asc</span> align <span class="token keyword">by</span> device<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"><pre class="language-text"><code>+-----------------------------+-----------------+--------+------+-----------+ |
| | Time| Device|hardware|status|temperature| |
| +-----------------------------+-----------------+--------+------+-----------+ |
| |1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null| |
| |1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null| |
| |2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null| |
| |2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null| |
| |2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96| |
| |2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36| |
| +-----------------------------+-----------------+--------+------+-----------+ |
| Total line number = 6 |
| </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></div><p>主排序键为<code>Time</code>时,结果集会先按照时间戳进行排序,在时间戳相等时按照设备名排序。 示例代码如下:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span><span class="token operator">*</span><span class="token operator">*</span> <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>T00:<span class="token number">01</span>:<span class="token number">00</span> <span class="token keyword">order</span> <span class="token keyword">by</span> <span class="token keyword">time</span> <span class="token keyword">asc</span><span class="token punctuation">,</span>device <span class="token keyword">desc</span> align <span class="token keyword">by</span> device<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"><pre class="language-text"><code>+-----------------------------+-----------------+--------+------+-----------+ |
| | Time| Device|hardware|status|temperature| |
| +-----------------------------+-----------------+--------+------+-----------+ |
| |1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null| |
| |1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null| |
| |2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null| |
| |2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96| |
| |2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null| |
| |2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36| |
| +-----------------------------+-----------------+--------+------+-----------+ |
| Total line number = 6 |
| </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></div><p>当没有显式指定时,主排序键默认为<code>Device</code>,排序顺序默认为<code>ASC</code>,示例代码如下:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span><span class="token operator">*</span><span class="token operator">*</span> <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>T00:<span class="token number">01</span>:<span class="token number">00</span> align <span class="token keyword">by</span> device<span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>结果如图所示,可以看出,<code>ORDER BY DEVICE ASC,TIME ASC</code>就是默认情况下的排序方式,由于<code>ASC</code>是默认排序顺序,此处可以省略。</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>+-----------------------------+-----------------+--------+------+-----------+ |
| | Time| Device|hardware|status|temperature| |
| +-----------------------------+-----------------+--------+------+-----------+ |
| |2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96| |
| |2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36| |
| |1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null| |
| |1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null| |
| |2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null| |
| |2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null| |
| +-----------------------------+-----------------+--------+------+-----------+ |
| Total line number = 6 |
| </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></div><p>同样,可以在聚合查询中使用<code>ALIGN BY DEVICE</code>和<code>ORDER BY</code>子句,对聚合后的结果进行排序,示例代码如下所示:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span><span class="token operator">*</span><span class="token operator">*</span> <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">01</span>T00:<span class="token number">00</span>:<span class="token number">00.000</span><span class="token operator">+</span><span class="token number">08</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">01</span>T00:<span class="token number">03</span>:<span class="token number">00.000</span><span class="token operator">+</span><span class="token number">08</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> <span class="token keyword">order</span> <span class="token keyword">by</span> device <span class="token keyword">asc</span><span class="token punctuation">,</span><span class="token keyword">time</span> <span class="token keyword">asc</span> align <span class="token keyword">by</span> device |
| </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"><pre class="language-text"><code>+-----------------------------+-----------------+---------------+-------------+------------------+ |
| | Time| Device|count(hardware)|count(status)|count(temperature)| |
| +-----------------------------+-----------------+---------------+-------------+------------------+ |
| |2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| 1| 1| |
| |2017-11-01T00:02:00.000+08:00|root.ln.wf01.wt01| null| 0| 0| |
| |2017-11-01T00:03:00.000+08:00|root.ln.wf01.wt01| null| 0| 0| |
| |2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| 1| 1| null| |
| |2017-11-01T00:02:00.000+08:00|root.ln.wf02.wt02| 0| 0| null| |
| |2017-11-01T00:03:00.000+08:00|root.ln.wf02.wt02| 0| 0| null| |
| +-----------------------------+-----------------+---------------+-------------+------------------+ |
| Total line number = 6 |
| </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></div>`,27);function r(p,i){return s(),e("div",null,[a(` |
| |
| 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. |
| |
| `),t])}const d=n(l,[["render",r],["__file","Order-By.html.vue"]]);export{d as default}; |