import{_ as r,r as l,o as c,c as i,b as s,d as n,a,w as o,e as t}from"./app-Bx8hKGcu.js";const u={},d=t(`<h1 id="select-expression" tabindex="-1"><a class="header-anchor" href="#select-expression"><span>Select Expression</span></a></h1><h2 id="syntax-definition" tabindex="-1"><a class="header-anchor" href="#syntax-definition"><span>Syntax Definition</span></a></h2><p>A selection expression (<code>selectExpr</code>) is a component of a SELECT clause, each <code>selectExpr</code> corresponds to a column in the query result set, and its syntax is defined as follows:</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code>selectClause
    : <span class="token keyword">SELECT</span> resultColumn <span class="token punctuation">(</span><span class="token string">&#39;,&#39;</span> resultColumn<span class="token punctuation">)</span><span class="token operator">*</span>
    <span class="token punctuation">;</span>

resultColumn
    : selectExpr <span class="token punctuation">(</span><span class="token keyword">AS</span> alias<span class="token punctuation">)</span>?
    <span class="token punctuation">;</span>

selectExpr
    : <span class="token string">&#39;(&#39;</span> selectExpr <span class="token string">&#39;)&#39;</span>
    <span class="token operator">|</span> <span class="token string">&#39;-&#39;</span> selectExpr
    <span class="token operator">|</span> selectExpr <span class="token punctuation">(</span><span class="token string">&#39;*&#39;</span> <span class="token operator">|</span> <span class="token string">&#39;/&#39;</span> <span class="token operator">|</span> <span class="token string">&#39;%&#39;</span><span class="token punctuation">)</span> selectExpr
    <span class="token operator">|</span> selectExpr <span class="token punctuation">(</span><span class="token string">&#39;+&#39;</span> <span class="token operator">|</span> <span class="token string">&#39;-&#39;</span><span class="token punctuation">)</span> selectExpr
    <span class="token operator">|</span> functionName <span class="token string">&#39;(&#39;</span> selectExpr <span class="token punctuation">(</span><span class="token string">&#39;,&#39;</span> selectExpr<span class="token punctuation">)</span><span class="token operator">*</span> functionAttribute<span class="token operator">*</span> <span class="token string">&#39;)&#39;</span>
    <span class="token operator">|</span> timeSeriesSuffixPath
    <span class="token operator">|</span> number
    <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 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>From this syntax definition, <code>selectExpr</code> can contain:</p>`,5),k=s("li",null,"suffix of time series path",-1),m=s("li",null,"Time series generation function",-1),b=s("li",null,[n("expressions "),s("ul",null,[s("li",null,"Arithmetic operation expressions"),s("li",null,"Time series generating nested expressions"),s("li",null,"Aggregate query nested expressions")])],-1),v=s("li",null,"Numeric constants (could be used in expressions only)",-1),h=t(`<h2 id="arithmetic-query" tabindex="-1"><a class="header-anchor" href="#arithmetic-query"><span>Arithmetic Query</span></a></h2><blockquote><p>Please note that Aligned Timeseries has not been supported in Arithmetic Query yet. An error message is expected if you use Arithmetic Query with Aligned Timeseries selected.</p></blockquote><h3 id="operators" tabindex="-1"><a class="header-anchor" href="#operators"><span>Operators</span></a></h3><h4 id="unary-arithmetic-operators" tabindex="-1"><a class="header-anchor" href="#unary-arithmetic-operators"><span>Unary Arithmetic Operators</span></a></h4><p>Supported operators: <code>+</code>, <code>-</code></p><p>Supported input data types: <code>INT32</code>, <code>INT64</code>, <code>FLOAT</code> and <code>DOUBLE</code></p><p>Output data type: consistent with the input data type</p><h4 id="binary-arithmetic-operators" tabindex="-1"><a class="header-anchor" href="#binary-arithmetic-operators"><span>Binary Arithmetic Operators</span></a></h4><p>Supported operators: <code>+</code>, <code>-</code>, <code>*</code>, <code>/</code>, <code>%</code></p><p>Supported input data types: <code>INT32</code>, <code>INT64</code>, <code>FLOAT</code> and <code>DOUBLE</code></p><p>Output data type: <code>DOUBLE</code></p><p>Note: Only when the left operand and the right operand under a certain timestamp are not <code>null</code>, the binary arithmetic operation will have an output value.</p><h3 id="example" tabindex="-1"><a class="header-anchor" href="#example"><span>Example</span></a></h3><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> s1<span class="token punctuation">,</span> <span class="token operator">-</span> s1<span class="token punctuation">,</span> s2<span class="token punctuation">,</span> <span class="token operator">+</span> s2<span class="token punctuation">,</span> s1 <span class="token operator">+</span> s2<span class="token punctuation">,</span> s1 <span class="token operator">-</span> s2<span class="token punctuation">,</span> s1 <span class="token operator">*</span> s2<span class="token punctuation">,</span> s1 <span class="token operator">/</span> s2<span class="token punctuation">,</span> s1 <span class="token operator">%</span> s2 <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d1
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-------------+--------------+-------------+-------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
|                         Time|root.sg.d1.s1|-root.sg.d1.s1|root.sg.d1.s2|root.sg.d1.s2|root.sg.d1.s1 + root.sg.d1.s2|root.sg.d1.s1 - root.sg.d1.s2|root.sg.d1.s1 * root.sg.d1.s2|root.sg.d1.s1 / root.sg.d1.s2|root.sg.d1.s1 % root.sg.d1.s2|
+-----------------------------+-------------+--------------+-------------+-------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
|1970-01-01T08:00:00.001+08:00|          1.0|          -1.0|          1.0|          1.0|                          2.0|                          0.0|                          1.0|                          1.0|                          0.0|
|1970-01-01T08:00:00.002+08:00|          2.0|          -2.0|          2.0|          2.0|                          4.0|                          0.0|                          4.0|                          1.0|                          0.0|
|1970-01-01T08:00:00.003+08:00|          3.0|          -3.0|          3.0|          3.0|                          6.0|                          0.0|                          9.0|                          1.0|                          0.0|
|1970-01-01T08:00:00.004+08:00|          4.0|          -4.0|          4.0|          4.0|                          8.0|                          0.0|                         16.0|                          1.0|                          0.0|
|1970-01-01T08:00:00.005+08:00|          5.0|          -5.0|          5.0|          5.0|                         10.0|                          0.0|                         25.0|                          1.0|                          0.0|
+-----------------------------+-------------+--------------+-------------+-------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
Total line number = 5
It costs 0.014s
</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><h2 id="time-series-generating-functions" tabindex="-1"><a class="header-anchor" href="#time-series-generating-functions"><span>Time Series Generating Functions</span></a></h2><p>The time series generating function takes several time series as input and outputs one time series. Unlike the aggregation function, the result set of the time series generating function has a timestamp column.</p><p>All time series generating functions can accept * as input.</p><p>IoTDB supports hybrid queries of time series generating function queries and raw data queries.</p><blockquote><p>Please note that Aligned Timeseries has not been supported in queries with hybrid functions yet. An error message is expected if you use hybrid functions with Aligned Timeseries selected in a query statement.</p></blockquote><h3 id="mathematical-functions" tabindex="-1"><a class="header-anchor" href="#mathematical-functions"><span>Mathematical Functions</span></a></h3><p>Currently, IoTDB supports the following mathematical functions. The behavior of these mathematical functions is consistent with the behavior of these functions in the Java Math standard library.</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Output Series Data Type</th><th>Corresponding Implementation in the Java Standard Library</th></tr></thead><tbody><tr><td>SIN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#sin(double)</td></tr><tr><td>COS</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#cos(double)</td></tr><tr><td>TAN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#tan(double)</td></tr><tr><td>ASIN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#asin(double)</td></tr><tr><td>ACOS</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#acos(double)</td></tr><tr><td>ATAN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#atan(double)</td></tr><tr><td>SINH</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#sinh(double)</td></tr><tr><td>COSH</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#cosh(double)</td></tr><tr><td>TANH</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#tanh(double)</td></tr><tr><td>DEGREES</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#toDegrees(double)</td></tr><tr><td>RADIANS</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#toRadians(double)</td></tr><tr><td>ABS</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Same type as the input series</td><td>Math#abs(int) / Math#abs(long) /Math#abs(float) /Math#abs(double)</td></tr><tr><td>SIGN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#signum(double)</td></tr><tr><td>CEIL</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#ceil(double)</td></tr><tr><td>FLOOR</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#floor(double)</td></tr><tr><td>ROUND</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#rint(double)</td></tr><tr><td>EXP</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#exp(double)</td></tr><tr><td>LN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#log(double)</td></tr><tr><td>LOG10</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#log10(double)</td></tr><tr><td>SQRT</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Math#sqrt(double)</td></tr></tbody></table><p>Example:</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> s1<span class="token punctuation">,</span> sin<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> cos<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> tan<span class="token punctuation">(</span>s1<span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">limit</span> <span class="token number">5</span> <span class="token keyword">offset</span> <span class="token number">1000</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-------------------+-------------------+--------------------+-------------------+
|                         Time|     root.sg1.d1.s1|sin(root.sg1.d1.s1)| cos(root.sg1.d1.s1)|tan(root.sg1.d1.s1)|
+-----------------------------+-------------------+-------------------+--------------------+-------------------+
|2020-12-10T17:11:49.037+08:00|7360723084922759782| 0.8133527237573284|  0.5817708713544664| 1.3980636773094157|
|2020-12-10T17:11:49.038+08:00|4377791063319964531|-0.8938962705202537|  0.4482738644511651| -1.994085181866842|
|2020-12-10T17:11:49.039+08:00|7972485567734642915| 0.9627757585308978|-0.27030138509681073|-3.5618602479083545|
|2020-12-10T17:11:49.040+08:00|2508858212791964081|-0.6073417341629443| -0.7944406950452296| 0.7644897069734913|
|2020-12-10T17:11:49.041+08:00|2817297431185141819|-0.8419358900502509| -0.5395775727782725| 1.5603611649667768|
+-----------------------------+-------------------+-------------------+--------------------+-------------------+
Total line number = 5
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></div><h3 id="string-processing-functions" tabindex="-1"><a class="header-anchor" href="#string-processing-functions"><span>String Processing Functions</span></a></h3><p>Currently, IoTDB supports the following string processing functions:</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Description</th></tr></thead><tbody><tr><td>STRING_CONTAINS</td><td>TEXT</td><td><code>s</code>: the sequence to search for</td><td>BOOLEAN</td><td>Determine whether <code>s</code> is in the string</td></tr><tr><td>STRING_MATCHES</td><td>TEXT</td><td><code>regex</code>: the regular expression to which the string is to be matched</td><td>BOOLEAN</td><td>Determine whether the string can be matched by <code>regex</code></td></tr></tbody></table><p>Example：</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> s1<span class="token punctuation">,</span> string_contains<span class="token punctuation">(</span>s1<span class="token punctuation">,</span> <span class="token string">&#39;s&#39;</span><span class="token operator">=</span><span class="token string">&#39;warn&#39;</span><span class="token punctuation">)</span><span class="token punctuation">,</span> string_matches<span class="token punctuation">(</span>s1<span class="token punctuation">,</span> <span class="token string">&#39;regex&#39;</span><span class="token operator">=</span><span class="token string">&#39;[^\\\\s]+37229&#39;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d4<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+--------------+-------------------------------------------+------------------------------------------------------+
|                         Time|root.sg1.d4.s1|string_contains(root.sg1.d4.s1, &quot;s&quot;=&quot;warn&quot;)|string_matches(root.sg1.d4.s1, &quot;regex&quot;=&quot;[^\\\\s]+37229&quot;)|
+-----------------------------+--------------+-------------------------------------------+------------------------------------------------------+
|1970-01-01T08:00:00.001+08:00|    warn:-8721|                                       true|                                                 false|
|1970-01-01T08:00:00.002+08:00|  error:-37229|                                      false|                                                  true|
|1970-01-01T08:00:00.003+08:00|     warn:1731|                                       true|                                                 false|
+-----------------------------+--------------+-------------------------------------------+------------------------------------------------------+
Total line number = 3
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 class="line-number"></div><div class="line-number"></div></div></div><h3 id="selector-functions" tabindex="-1"><a class="header-anchor" href="#selector-functions"><span>Selector Functions</span></a></h3><p>Currently, IoTDB supports the following selector functions:</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Description</th></tr></thead><tbody><tr><td>TOP_K</td><td>INT32 / INT64 / FLOAT / DOUBLE / TEXT</td><td><code>k</code>: the maximum number of selected data points, must be greater than 0 and less than or equal to 1000</td><td>Same type as the input series</td><td>Returns <code>k</code> data points with the largest values in a time series.</td></tr><tr><td>BOTTOM_K</td><td>INT32 / INT64 / FLOAT / DOUBLE / TEXT</td><td><code>k</code>: the maximum number of selected data points, must be greater than 0 and less than or equal to 1000</td><td>Same type as the input series</td><td>Returns <code>k</code> data points with the smallest values in a time series.</td></tr></tbody></table><p>Example：</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> s1<span class="token punctuation">,</span> top_k<span class="token punctuation">(</span>s1<span class="token punctuation">,</span> <span class="token string">&#39;k&#39;</span><span class="token operator">=</span><span class="token string">&#39;2&#39;</span><span class="token punctuation">)</span><span class="token punctuation">,</span> bottom_k<span class="token punctuation">(</span>s1<span class="token punctuation">,</span> <span class="token string">&#39;k&#39;</span><span class="token operator">=</span><span class="token string">&#39;2&#39;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d2 <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">&gt;</span> <span class="token number">2020</span><span class="token operator">-</span><span class="token number">12</span><span class="token operator">-</span><span class="token number">10</span>T20:<span class="token number">36</span>:<span class="token number">15.530</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+--------------------+------------------------------+---------------------------------+
|                         Time|      root.sg1.d2.s1|top_k(root.sg1.d2.s1, &quot;k&quot;=&quot;2&quot;)|bottom_k(root.sg1.d2.s1, &quot;k&quot;=&quot;2&quot;)|
+-----------------------------+--------------------+------------------------------+---------------------------------+
|2020-12-10T20:36:15.531+08:00| 1531604122307244742|           1531604122307244742|                             null|
|2020-12-10T20:36:15.532+08:00|-7426070874923281101|                          null|                             null|
|2020-12-10T20:36:15.533+08:00|-7162825364312197604|          -7162825364312197604|                             null|
|2020-12-10T20:36:15.534+08:00|-8581625725655917595|                          null|             -8581625725655917595|
|2020-12-10T20:36:15.535+08:00|-7667364751255535391|                          null|             -7667364751255535391|
+-----------------------------+--------------------+------------------------------+---------------------------------+
Total line number = 5
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></div><h3 id="variation-trend-calculation-functions" tabindex="-1"><a class="header-anchor" href="#variation-trend-calculation-functions"><span>Variation Trend Calculation Functions</span></a></h3><p>Currently, IoTDB supports the following variation trend calculation functions:</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Output Series Data Type</th><th>Description</th></tr></thead><tbody><tr><td>TIME_DIFFERENCE</td><td>INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT</td><td>INT64</td><td>Calculates the difference between the time stamp of a data point and the time stamp of the previous data point. There is no corresponding output for the first data point.</td></tr><tr><td>DIFFERENCE</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Same type as the input series</td><td>Calculates the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point.</td></tr><tr><td>NON_NEGATIVE_DIFFERENCE</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Same type as the input series</td><td>Calculates the absolute value of the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point.</td></tr><tr><td>DERIVATIVE</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Calculates the rate of change of a data point compared to the previous data point, the result is equals to DIFFERENCE / TIME_DIFFERENCE. There is no corresponding output for the first data point.</td></tr><tr><td>NON_NEGATIVE_DERIVATIVE</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>Calculates the absolute value of the rate of change of a data point compared to the previous data point, the result is equals to NON_NEGATIVE_DIFFERENCE / TIME_DIFFERENCE. There is no corresponding output for the first data point.</td></tr></tbody></table><p>Example:</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> s1<span class="token punctuation">,</span> time_difference<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> difference<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> non_negative_difference<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> derivative<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> non_negative_derivative<span class="token punctuation">(</span>s1<span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">limit</span> <span class="token number">5</span> <span class="token keyword">offset</span> <span class="token number">1000</span><span class="token punctuation">;</span> 
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
|                         Time|     root.sg1.d1.s1|time_difference(root.sg1.d1.s1)|difference(root.sg1.d1.s1)|non_negative_difference(root.sg1.d1.s1)|derivative(root.sg1.d1.s1)|non_negative_derivative(root.sg1.d1.s1)|
+-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
|2020-12-10T17:11:49.037+08:00|7360723084922759782|                              1|      -8431715764844238876|                    8431715764844238876|    -8.4317157648442388E18|                  8.4317157648442388E18|
|2020-12-10T17:11:49.038+08:00|4377791063319964531|                              1|      -2982932021602795251|                    2982932021602795251|     -2.982932021602795E18|                   2.982932021602795E18|
|2020-12-10T17:11:49.039+08:00|7972485567734642915|                              1|       3594694504414678384|                    3594694504414678384|     3.5946945044146785E18|                  3.5946945044146785E18|
|2020-12-10T17:11:49.040+08:00|2508858212791964081|                              1|      -5463627354942678834|                    5463627354942678834|     -5.463627354942679E18|                   5.463627354942679E18|
|2020-12-10T17:11:49.041+08:00|2817297431185141819|                              1|        308439218393177738|                     308439218393177738|     3.0843921839317773E17|                  3.0843921839317773E17|
+-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
Total line number = 5
It costs 0.014s
</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><h3 id="constant-timeseries-generating-functions" tabindex="-1"><a class="header-anchor" href="#constant-timeseries-generating-functions"><span>Constant Timeseries Generating Functions</span></a></h3><p>The constant timeseries generating function is used to generate a timeseries in which the values of all data points are the same.</p><p>The constant timeseries generating function accepts one or more timeseries inputs, and the timestamp set of the output data points is the union of the timestamp sets of the input timeseries.</p><p>Currently, IoTDB supports the following constant timeseries generating functions:</p><table><thead><tr><th>Function Name</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Description</th></tr></thead><tbody><tr><td>CONST</td><td><code>value</code>: the value of the output data point <br><code>type</code>: the type of the output data point, it can only be INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT</td><td>Determined by the required attribute <code>type</code></td><td>Output the user-specified constant timeseries according to the attributes <code>value</code> and <code>type</code>.</td></tr><tr><td>PI</td><td>None</td><td>DOUBLE</td><td>Data point value: a <code>double</code> value of <code>π</code>, the ratio of the circumference of a circle to its diameter, which is equals to <code>Math.PI</code> in the <em>Java Standard Library</em>.</td></tr><tr><td>E</td><td>None</td><td>DOUBLE</td><td>Data point value: a <code>double</code> value of <code>e</code>, the base of the natural logarithms, which is equals to <code>Math.E</code> in the <em>Java Standard Library</em>.</td></tr></tbody></table><p>Example:</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> s1<span class="token punctuation">,</span> s2<span class="token punctuation">,</span> const<span class="token punctuation">(</span>s1<span class="token punctuation">,</span> <span class="token string">&#39;value&#39;</span><span class="token operator">=</span><span class="token string">&#39;1024&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;INT64&#39;</span><span class="token punctuation">)</span><span class="token punctuation">,</span> pi<span class="token punctuation">(</span>s2<span class="token punctuation">)</span><span class="token punctuation">,</span> e<span class="token punctuation">(</span>s1<span class="token punctuation">,</span> s2<span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1<span class="token punctuation">;</span> 
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>select s1, s2, const(s1, &#39;value&#39;=&#39;1024&#39;, &#39;type&#39;=&#39;INT64&#39;), pi(s2), e(s1, s2) from root.sg1.d1; 
+-----------------------------+--------------+--------------+-----------------------------------------------------+------------------+---------------------------------+
|                         Time|root.sg1.d1.s1|root.sg1.d1.s2|const(root.sg1.d1.s1, &quot;value&quot;=&quot;1024&quot;, &quot;type&quot;=&quot;INT64&quot;)|pi(root.sg1.d1.s2)|e(root.sg1.d1.s1, root.sg1.d1.s2)|
+-----------------------------+--------------+--------------+-----------------------------------------------------+------------------+---------------------------------+
|1970-01-01T08:00:00.000+08:00|           0.0|           0.0|                                                 1024| 3.141592653589793|                2.718281828459045|
|1970-01-01T08:00:00.001+08:00|           1.0|          null|                                                 1024|              null|                2.718281828459045|
|1970-01-01T08:00:00.002+08:00|           2.0|          null|                                                 1024|              null|                2.718281828459045|
|1970-01-01T08:00:00.003+08:00|          null|           3.0|                                                 null| 3.141592653589793|                2.718281828459045|
|1970-01-01T08:00:00.004+08:00|          null|           4.0|                                                 null| 3.141592653589793|                2.718281828459045|
+-----------------------------+--------------+--------------+-----------------------------------------------------+------------------+---------------------------------+
Total line number = 5
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></div><h3 id="data-type-conversion-function" tabindex="-1"><a class="header-anchor" href="#data-type-conversion-function"><span>Data Type Conversion Function</span></a></h3><p>The IoTDB currently supports 6 data types, including INT32, INT64 ,FLOAT, DOUBLE, BOOLEAN, TEXT. When we query or evaluate data, we may need to convert data types, such as TEXT to INT32, or improve the accuracy of the data, such as FLOAT to DOUBLE. Therefore, IoTDB supports the use of cast functions to convert data types.</p><table><thead><tr><th>Function Name</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Series Data Type Description</th></tr></thead><tbody><tr><td>CAST</td><td><code>type</code>: the type of the output data point, it can only be INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT</td><td>Determined by the required attribute <code>type</code></td><td>Converts data to the type specified by the <code>type</code> argument.</td></tr></tbody></table><h4 id="notes" tabindex="-1"><a class="header-anchor" href="#notes"><span>Notes</span></a></h4><ol><li>The value of type BOOLEAN is <code>true</code>, when data is converted to BOOLEAN if INT32 and INT64 are not 0, FLOAT and DOUBLE are not 0.0, TEXT is not empty string or &quot;false&quot;, otherwise <code>false</code>.</li></ol><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; show timeseries root.sg.d1.*;
+-------------+-----+-------------+--------+--------+-----------+----+----------+
|   timeseries|alias|storage group|dataType|encoding|compression|tags|attributes|
+-------------+-----+-------------+--------+--------+-----------+----+----------+
|root.sg.d1.s3| null|      root.sg|   FLOAT|     RLE|     SNAPPY|null|      null|
|root.sg.d1.s4| null|      root.sg|  DOUBLE|     RLE|     SNAPPY|null|      null|
|root.sg.d1.s5| null|      root.sg|    TEXT|   PLAIN|     SNAPPY|null|      null|
|root.sg.d1.s6| null|      root.sg| BOOLEAN|     RLE|     SNAPPY|null|      null|
|root.sg.d1.s1| null|      root.sg|   INT32|     RLE|     SNAPPY|null|      null|
|root.sg.d1.s2| null|      root.sg|   INT64|     RLE|     SNAPPY|null|      null|
+-------------+-----+-------------+--------+--------+-----------+----+----------+
Total line number = 6
It costs 0.006s
IoTDB&gt; select * from root.sg.d1;
+-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
|                         Time|root.sg.d1.s3|root.sg.d1.s4|root.sg.d1.s5|root.sg.d1.s6|root.sg.d1.s1|root.sg.d1.s2|
+-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
|1970-01-01T08:00:00.001+08:00|          1.1|          1.1|         test|        false|            1|            1|
|1970-01-01T08:00:00.002+08:00|         -2.2|         -2.2|        false|         true|           -2|           -2|
|1970-01-01T08:00:00.003+08:00|          0.0|          0.0|         true|         true|            0|            0|
+-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
Total line number = 3
It costs 0.009s
 
IoTDB&gt; select cast(s1, &#39;type&#39;=&#39;BOOLEAN&#39;), cast(s2, &#39;type&#39;=&#39;BOOLEAN&#39;), cast(s3, &#39;type&#39;=&#39;BOOLEAN&#39;), cast(s4, &#39;type&#39;=&#39;BOOLEAN&#39;), cast(s5, &#39;type&#39;=&#39;BOOLEAN&#39;) from root.sg.d1;
+-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
|                         Time|cast(root.sg.d1.s1, &quot;type&quot;=&quot;BOOLEAN&quot;)|cast(root.sg.d1.s2, &quot;type&quot;=&quot;BOOLEAN&quot;)|cast(root.sg.d1.s3, &quot;type&quot;=&quot;BOOLEAN&quot;)|cast(root.sg.d1.s4, &quot;type&quot;=&quot;BOOLEAN&quot;)|cast(root.sg.d1.s5, &quot;type&quot;=&quot;BOOLEAN&quot;)|
+-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
|1970-01-01T08:00:00.001+08:00|                                 true|                                 true|                                 true|                                 true|                                 true|
|1970-01-01T08:00:00.002+08:00|                                 true|                                 true|                                 true|                                 true|                                false|
|1970-01-01T08:00:00.003+08:00|                                false|                                false|                                false|                                false|                                 true|
+-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
Total line number = 3
It costs 0.012s
</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 class="line-number"></div></div></div><ol start="2"><li>The value of type INT32, INT64, FLOAT, DOUBLE are 1 or 1.0 and TEXT is &quot;true&quot;, when BOOLEAN data is true, otherwise 0, 0.0 or &quot;false&quot;.</li></ol><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; select cast(s6, &#39;type&#39;=&#39;INT32&#39;), cast(s6, &#39;type&#39;=&#39;INT64&#39;), cast(s6, &#39;type&#39;=&#39;FLOAT&#39;), cast(s6, &#39;type&#39;=&#39;DOUBLE&#39;), cast(s6, &#39;type&#39;=&#39;TEXT&#39;) from root.sg.d1;
+-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
|                         Time|cast(root.sg.d1.s6, &quot;type&quot;=&quot;INT32&quot;)|cast(root.sg.d1.s6, &quot;type&quot;=&quot;INT64&quot;)|cast(root.sg.d1.s6, &quot;type&quot;=&quot;FLOAT&quot;)|cast(root.sg.d1.s6, &quot;type&quot;=&quot;DOUBLE&quot;)|cast(root.sg.d1.s6, &quot;type&quot;=&quot;TEXT&quot;)|
+-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
|1970-01-01T08:00:00.001+08:00|                                  0|                                  0|                                0.0|                                 0.0|                             false|
|1970-01-01T08:00:00.002+08:00|                                  1|                                  1|                                1.0|                                 1.0|                              true|
|1970-01-01T08:00:00.003+08:00|                                  1|                                  1|                                1.0|                                 1.0|                              true|
+-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
Total line number = 3
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 class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><ol start="3"><li>When TEXT is converted to INT32, INT64, or FLOAT, the TEXT is first converted to DOUBLE and then to the corresponding type, which may cause loss of precision. It will skip directly if the data can not be converted.</li></ol><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; select cast(s5, &#39;type&#39;=&#39;INT32&#39;), cast(s5, &#39;type&#39;=&#39;INT64&#39;), cast(s5, &#39;type&#39;=&#39;FLOAT&#39;) from root.sg.d1;
+----+-----------------------------------+-----------------------------------+-----------------------------------+
|Time|cast(root.sg.d1.s5, &quot;type&quot;=&quot;INT32&quot;)|cast(root.sg.d1.s5, &quot;type&quot;=&quot;INT64&quot;)|cast(root.sg.d1.s5, &quot;type&quot;=&quot;FLOAT&quot;)|
+----+-----------------------------------+-----------------------------------+-----------------------------------+
+----+-----------------------------------+-----------------------------------+-----------------------------------+
Empty set.
It costs 0.009s
</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="example-1" tabindex="-1"><a class="header-anchor" href="#example-1"><span>Example</span></a></h4><p>Example data:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; select text from root.test;
+-----------------------------+--------------+
|                         Time|root.test.text|
+-----------------------------+--------------+
|1970-01-01T08:00:00.001+08:00|           1.1|
|1970-01-01T08:00:00.002+08:00|             1|
|1970-01-01T08:00:00.003+08:00|   hello world|
|1970-01-01T08:00:00.004+08:00|         false|
+-----------------------------+--------------+
</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><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> cast<span class="token punctuation">(</span><span class="token keyword">text</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;BOOLEAN&#39;</span><span class="token punctuation">)</span><span class="token punctuation">,</span> cast<span class="token punctuation">(</span><span class="token keyword">text</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;INT32&#39;</span><span class="token punctuation">)</span><span class="token punctuation">,</span> cast<span class="token punctuation">(</span><span class="token keyword">text</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;INT64&#39;</span><span class="token punctuation">)</span><span class="token punctuation">,</span> cast<span class="token punctuation">(</span><span class="token keyword">text</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;FLOAT&#39;</span><span class="token punctuation">)</span><span class="token punctuation">,</span> cast<span class="token punctuation">(</span><span class="token keyword">text</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;DOUBLE&#39;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
|                         Time|cast(root.test.text, &quot;type&quot;=&quot;BOOLEAN&quot;)|cast(root.test.text, &quot;type&quot;=&quot;INT32&quot;)|cast(root.test.text, &quot;type&quot;=&quot;INT64&quot;)|cast(root.test.text, &quot;type&quot;=&quot;FLOAT&quot;)|cast(root.test.text, &quot;type&quot;=&quot;DOUBLE&quot;)|
+-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
|1970-01-01T08:00:00.001+08:00|                                  true|                                   1|                                   1|                                 1.1|                                  1.1|
|1970-01-01T08:00:00.002+08:00|                                  true|                                   1|                                   1|                                 1.0|                                  1.0|
|1970-01-01T08:00:00.003+08:00|                                  true|                                null|                                null|                                null|                                 null|
|1970-01-01T08:00:00.004+08:00|                                 false|                                null|                                null|                                null|                                 null|
+-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
Total line number = 4
It costs 0.078s
</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></div><h3 id="condition-functions" tabindex="-1"><a class="header-anchor" href="#condition-functions"><span>Condition Functions</span></a></h3><p>Condition functions are used to check whether timeseries data points satisfy some specific condition.</p><p>They return BOOLEANs.</p><p>Currently, IoTDB supports the following condition functions:</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Series Data Type Description</th></tr></thead><tbody><tr><td>ON_OFF</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td><code>threshold</code>: a double type variate</td><td>BOOLEAN</td><td>Return <code>ts_value &gt;= threshold</code>.</td></tr><tr><td>IN_RANGR</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td><code>lower</code>: DOUBLE type<br><code>upper</code>: DOUBLE type</td><td>BOOLEAN</td><td>Return <code>ts_value &gt;= lower &amp;&amp; value &lt;= upper</code>.</td></tr></tbody></table><p>Example Data:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; select ts from root.test;
+-----------------------------+------------+
|                         Time|root.test.ts|
+-----------------------------+------------+
|1970-01-01T08:00:00.001+08:00|           1|
|1970-01-01T08:00:00.002+08:00|           2|
|1970-01-01T08:00:00.003+08:00|           3|
|1970-01-01T08:00:00.004+08:00|           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><h4 id="example-1-1" tabindex="-1"><a class="header-anchor" href="#example-1-1"><span>Example 1</span></a></h4><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> ts<span class="token punctuation">,</span> on_off<span class="token punctuation">(</span>ts<span class="token punctuation">,</span> <span class="token string">&#39;threshold&#39;</span><span class="token operator">=</span><span class="token string">&#39;2&#39;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Output:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; select ts, on_off(ts, &#39;threshold&#39;=&#39;2&#39;) from root.test;
+-----------------------------+------------+-------------------------------------+
|                         Time|root.test.ts|on_off(root.test.ts, &quot;threshold&quot;=&quot;2&quot;)|
+-----------------------------+------------+-------------------------------------+
|1970-01-01T08:00:00.001+08:00|           1|                                false|
|1970-01-01T08:00:00.002+08:00|           2|                                 true|
|1970-01-01T08:00:00.003+08:00|           3|                                 true|
|1970-01-01T08:00:00.004+08:00|           4|                                 true|
+-----------------------------+------------+-------------------------------------+
</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><h4 id="example-2" tabindex="-1"><a class="header-anchor" href="#example-2"><span>Example 2</span></a></h4><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> ts<span class="token punctuation">,</span> in_range<span class="token punctuation">(</span>ts<span class="token punctuation">,</span> <span class="token string">&#39;lower&#39;</span><span class="token operator">=</span><span class="token string">&#39;2&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;upper&#39;</span><span class="token operator">=</span><span class="token string">&#39;3.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Output:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; select ts, in_range(ts,&#39;lower&#39;=&#39;2&#39;, &#39;upper&#39;=&#39;3.1&#39;) from root.test;
+-----------------------------+------------+--------------------------------------------------+
|                         Time|root.test.ts|in_range(root.test.ts, &quot;lower&quot;=&quot;2&quot;, &quot;upper&quot;=&quot;3.1&quot;)|
+-----------------------------+------------+--------------------------------------------------+
|1970-01-01T08:00:00.001+08:00|           1|                                             false|
|1970-01-01T08:00:00.002+08:00|           2|                                              true|
|1970-01-01T08:00:00.003+08:00|           3|                                              true|
|1970-01-01T08:00:00.004+08:00|           4|                                             false|
+-----------------------------+------------+--------------------------------------------------+
</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="continuous-interval-functions" tabindex="-1"><a class="header-anchor" href="#continuous-interval-functions"><span>Continuous Interval Functions</span></a></h3><p>The continuous interval functions are used to query all continuous intervals that meet specified conditions.<br> They can be divided into two categories according to return value:</p><ol><li>Returns the start timestamp and time span of the continuous interval that meets the conditions (a time span of 0 means that only the start time point meets the conditions)</li><li>Returns the start timestamp of the continuous interval that meets the condition and the number of points in the interval (a number of 1 means that only the start time point meets the conditions)</li></ol><table><thead><tr><th>Function Name</th><th>Input TSDatatype</th><th>Parameters</th><th>Output TSDatatype</th><th>Function Description</th></tr></thead><tbody><tr><td>ZERO_DURATION</td><td>INT32/ INT64/ FLOAT/ DOUBLE/ BOOLEAN</td><td><code>min</code>:Optional with default value <code>0L</code><br><code>max</code>:Optional with default value <code>Long.MAX_VALUE</code></td><td>Long</td><td>Return intervals&#39; start times and duration times in which the value is always 0(false), and the duration time <code>t</code> satisfy <code>t &gt;= min &amp;&amp; t &lt;= max</code>. The unit of <code>t</code> is ms</td></tr><tr><td>NON_ZERO_DURATION</td><td>INT32/ INT64/ FLOAT/ DOUBLE/ BOOLEAN</td><td><code>min</code>:Optional with default value <code>0L</code><br><code>max</code>:Optional with default value <code>Long.MAX_VALUE</code></td><td>Long</td><td>Return intervals&#39; start times and duration times in which the value is always not 0, and the duration time <code>t</code> satisfy <code>t &gt;= min &amp;&amp; t &lt;= max</code>. The unit of <code>t</code> is ms</td></tr><tr><td>ZERO_COUNT</td><td>INT32/ INT64/ FLOAT/ DOUBLE/ BOOLEAN</td><td><code>min</code>:Optional with default value <code>1L</code><br><code>max</code>:Optional with default value <code>Long.MAX_VALUE</code></td><td>Long</td><td>Return intervals&#39; start times and the number of data points in the interval in which the value is always 0(false). Data points number <code>n</code> satisfy <code>n &gt;= min &amp;&amp; n &lt;= max</code></td></tr><tr><td>NON_ZERO_COUNT</td><td>INT32/ INT64/ FLOAT/ DOUBLE/ BOOLEAN</td><td><code>min</code>:Optional with default value <code>1L</code><br><code>max</code>:Optional with default value <code>Long.MAX_VALUE</code></td><td>Long</td><td>Return intervals&#39; start times and the number of data points in the interval in which the value is always not 0(false). Data points number <code>n</code> satisfy <code>n &gt;= min &amp;&amp; n &lt;= max</code></td></tr></tbody></table><h4 id="example-3" tabindex="-1"><a class="header-anchor" href="#example-3"><span>Example</span></a></h4><p>Example data:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB&gt; select s1,s2,s3,s4,s5 from root.sg.d2;
+-----------------------------+-------------+-------------+-------------+-------------+-------------+
|                         Time|root.sg.d2.s1|root.sg.d2.s2|root.sg.d2.s3|root.sg.d2.s4|root.sg.d2.s5|
+-----------------------------+-------------+-------------+-------------+-------------+-------------+
|1970-01-01T08:00:00.000+08:00|            0|            0|          0.0|          0.0|        false|
|1970-01-01T08:00:00.001+08:00|            1|            1|          1.0|          1.0|         true|
|1970-01-01T08:00:00.002+08:00|            1|            1|          1.0|          1.0|         true|
|1970-01-01T08:00:00.003+08:00|            0|            0|          0.0|          0.0|        false|
|1970-01-01T08:00:00.004+08:00|            1|            1|          1.0|          1.0|         true|
|1970-01-01T08:00:00.005+08:00|            0|            0|          0.0|          0.0|        false|
|1970-01-01T08:00:00.006+08:00|            0|            0|          0.0|          0.0|        false|
|1970-01-01T08:00:00.007+08:00|            1|            1|          1.0|          1.0|         true|
+-----------------------------+-------------+-------------+-------------+-------------+-------------+
</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>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> s1<span class="token punctuation">,</span> zero_count<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> non_zero_count<span class="token punctuation">(</span>s2<span class="token punctuation">)</span><span class="token punctuation">,</span> zero_duration<span class="token punctuation">(</span>s3<span class="token punctuation">)</span><span class="token punctuation">,</span> non_zero_duration<span class="token punctuation">(</span>s4<span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d2<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-------------+-------------------------+-----------------------------+----------------------------+--------------------------------+
|                         Time|root.sg.d2.s1|zero_count(root.sg.d2.s1)|non_zero_count(root.sg.d2.s2)|zero_duration(root.sg.d2.s3)|non_zero_duration(root.sg.d2.s4)|
+-----------------------------+-------------+-------------------------+-----------------------------+----------------------------+--------------------------------+
|1970-01-01T08:00:00.000+08:00|            0|                        1|                         null|                           0|                            null|
|1970-01-01T08:00:00.001+08:00|            1|                     null|                            2|                        null|                               1|
|1970-01-01T08:00:00.002+08:00|            1|                     null|                         null|                        null|                            null|
|1970-01-01T08:00:00.003+08:00|            0|                        1|                         null|                           0|                            null|
|1970-01-01T08:00:00.004+08:00|            1|                     null|                            1|                        null|                               0|
|1970-01-01T08:00:00.005+08:00|            0|                        2|                         null|                           1|                            null|
|1970-01-01T08:00:00.006+08:00|            0|                     null|                         null|                        null|                            null|
|1970-01-01T08:00:00.007+08:00|            1|                     null|                            1|                        null|                               0|
+-----------------------------+-------------+-------------------------+-----------------------------+----------------------------+--------------------------------+
</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></div><h3 id="equal-size-bucket-sample-function" tabindex="-1"><a class="header-anchor" href="#equal-size-bucket-sample-function"><span>Equal Size Bucket Sample Function</span></a></h3><p>This function samples the input sequence in equal size buckets. That is, according to the downsampling ratio and downsampling method given by the user, the input sequence is equally divided into several buckets according to a fixed number of points, and sampled by the given sampling method within each bucket.</p><ul><li><code>proportion</code>: sample ratio, the value range is <code>(0, 1]</code>.</li><li>four sampling methods: <ul><li><code>equal_size_bucket_random_sample</code></li><li><code>equal_size_bucket_agg_sample</code></li><li><code>equal_size_bucket_m4_sample</code></li><li><code>equal_size_bucket_outlier_sample</code></li></ul></li></ul><h4 id="equal-size-bucket-random-sample" tabindex="-1"><a class="header-anchor" href="#equal-size-bucket-random-sample"><span>Equal Size Bucket Random Sample</span></a></h4><p>Random sampling is performed on the equally divided buckets.</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Series Data Type Description</th></tr></thead><tbody><tr><td>EQUAL_SIZE_BUCKET_RANDOM_SAMPLE</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td><code>proportion</code> The value range is <code>(0, 1]</code>, the default is <code>0.1</code></td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Returns a random sample of equal buckets that matches the sampling ratio</td></tr></tbody></table><h5 id="example-4" tabindex="-1"><a class="header-anchor" href="#example-4"><span>Example</span></a></h5><p>Example data: <code>root.ln.wf01.wt01.temperature</code> has a total of <code>100</code> ordered data from <code>0.0-99.0</code>.</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code>IoTDB<span class="token operator">&gt;</span> <span class="token keyword">select</span> temperature <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 punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------+</span>
<span class="token operator">|</span>                         <span class="token keyword">Time</span><span class="token operator">|</span>root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span>wf01<span class="token punctuation">.</span>wt01<span class="token punctuation">.</span>temperature<span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------+</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<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 operator">|</span>                          <span class="token number">0.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.001</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">1.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.002</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">2.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.003</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">3.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.004</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">4.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.005</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">5.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.006</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">6.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.007</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">7.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.008</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">8.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.009</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">9.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.010</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">10.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.011</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">11.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.012</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">12.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token operator">|</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token operator">|</span>            
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.089</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">89.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.090</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">90.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.091</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">91.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.092</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">92.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.093</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">93.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.094</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">94.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.095</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">95.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.096</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">96.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.097</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">97.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.098</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">98.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.099</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">99.0</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------+</span>
</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></div><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> equal_size_bucket_random_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span><span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> random_sample <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 punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token operator">+</span><span class="token comment">-----------------------------+-------------+</span>
<span class="token operator">|</span>                         <span class="token keyword">Time</span><span class="token operator">|</span>random_sample<span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-------------+</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.007</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>          <span class="token number">7.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.014</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">14.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.020</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">20.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.035</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">35.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.047</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">47.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.059</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">59.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.063</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">63.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.079</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">79.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.086</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">86.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.096</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>         <span class="token number">96.0</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-------------+</span>
Total line number <span class="token operator">=</span> <span class="token number">10</span>
It costs <span class="token number">0.024</span>s
</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><h4 id="equal-size-bucket-aggregation-sample" tabindex="-1"><a class="header-anchor" href="#equal-size-bucket-aggregation-sample"><span>Equal Size Bucket Aggregation Sample</span></a></h4><p>The input sequence is sampled by the aggregation sampling method, and the user needs to provide an additional aggregation function parameter, namely</p><ul><li><code>type</code>: Aggregate type, which can be <code>avg</code> or <code>max</code> or <code>min</code> or <code>sum</code> or <code>extreme</code> or <code>variance</code>. By default, <code>avg</code> is used. <code>extreme</code> represents the value with the largest absolute value in the equal bucket. <code>variance</code> represents the variance in the sampling equal buckets.</li></ul><p>The timestamp of the sampling output of each bucket is the timestamp of the first point of the bucket.</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Series Data Type Description</th></tr></thead><tbody><tr><td>EQUAL_SIZE_BUCKET_AGG_SAMPLE</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td><code>proportion</code> The value range is <code>(0, 1]</code>, the default is <code>0.1</code><br><code>type</code>: The value types are <code>avg</code>, <code>max</code>, <code>min</code>, <code>sum</code>, <code>extreme</code>, <code>variance</code>, the default is <code>avg</code></td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Returns equal bucket aggregation samples that match the sampling ratio</td></tr></tbody></table><h5 id="example-5" tabindex="-1"><a class="header-anchor" href="#example-5"><span>Example</span></a></h5><p>Example data: <code>root.ln.wf01.wt01.temperature</code> has a total of <code>100</code> ordered data from <code>0.0-99.0</code>, and the test data is randomly sampled in equal buckets.</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> equal_size_bucket_agg_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;avg&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> agg_avg<span class="token punctuation">,</span> equal_size_bucket_agg_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;max&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> agg_max<span class="token punctuation">,</span> equal_size_bucket_agg_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span><span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;min&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> agg_min<span class="token punctuation">,</span> equal_size_bucket_agg_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;sum&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> agg_sum<span class="token punctuation">,</span> equal_size_bucket_agg_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;extreme&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> agg_extreme<span class="token punctuation">,</span> equal_size_bucket_agg_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;variance&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> agg_variance <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 punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token operator">+</span><span class="token comment">-----------------------------+-----------------+-------+-------+-------+-----------+------------+</span>
<span class="token operator">|</span>                         <span class="token keyword">Time</span><span class="token operator">|</span>          agg_avg<span class="token operator">|</span>agg_max<span class="token operator">|</span>agg_min<span class="token operator">|</span>agg_sum<span class="token operator">|</span>agg_extreme<span class="token operator">|</span>agg_variance<span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------+-------+-------+-------+-----------+------------+</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<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 operator">|</span>              <span class="token number">4.5</span><span class="token operator">|</span>    <span class="token number">9.0</span><span class="token operator">|</span>    <span class="token number">0.0</span><span class="token operator">|</span>   <span class="token number">45.0</span><span class="token operator">|</span>        <span class="token number">9.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.010</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">14.5</span><span class="token operator">|</span>   <span class="token number">19.0</span><span class="token operator">|</span>   <span class="token number">10.0</span><span class="token operator">|</span>  <span class="token number">145.0</span><span class="token operator">|</span>       <span class="token number">19.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.020</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">24.5</span><span class="token operator">|</span>   <span class="token number">29.0</span><span class="token operator">|</span>   <span class="token number">20.0</span><span class="token operator">|</span>  <span class="token number">245.0</span><span class="token operator">|</span>       <span class="token number">29.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.030</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">34.5</span><span class="token operator">|</span>   <span class="token number">39.0</span><span class="token operator">|</span>   <span class="token number">30.0</span><span class="token operator">|</span>  <span class="token number">345.0</span><span class="token operator">|</span>       <span class="token number">39.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.040</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">44.5</span><span class="token operator">|</span>   <span class="token number">49.0</span><span class="token operator">|</span>   <span class="token number">40.0</span><span class="token operator">|</span>  <span class="token number">445.0</span><span class="token operator">|</span>       <span class="token number">49.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.050</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">54.5</span><span class="token operator">|</span>   <span class="token number">59.0</span><span class="token operator">|</span>   <span class="token number">50.0</span><span class="token operator">|</span>  <span class="token number">545.0</span><span class="token operator">|</span>       <span class="token number">59.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.060</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">64.5</span><span class="token operator">|</span>   <span class="token number">69.0</span><span class="token operator">|</span>   <span class="token number">60.0</span><span class="token operator">|</span>  <span class="token number">645.0</span><span class="token operator">|</span>       <span class="token number">69.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.070</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span><span class="token number">74.50000000000001</span><span class="token operator">|</span>   <span class="token number">79.0</span><span class="token operator">|</span>   <span class="token number">70.0</span><span class="token operator">|</span>  <span class="token number">745.0</span><span class="token operator">|</span>       <span class="token number">79.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.080</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">84.5</span><span class="token operator">|</span>   <span class="token number">89.0</span><span class="token operator">|</span>   <span class="token number">80.0</span><span class="token operator">|</span>  <span class="token number">845.0</span><span class="token operator">|</span>       <span class="token number">89.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.090</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">94.5</span><span class="token operator">|</span>   <span class="token number">99.0</span><span class="token operator">|</span>   <span class="token number">90.0</span><span class="token operator">|</span>  <span class="token number">945.0</span><span class="token operator">|</span>       <span class="token number">99.0</span><span class="token operator">|</span>        <span class="token number">8.25</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------+-------+-------+-------+-----------+------------+</span>
Total line number <span class="token operator">=</span> <span class="token number">10</span>
It costs <span class="token number">0.044</span>s
</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><h4 id="equal-size-bucket-m4-sample" tabindex="-1"><a class="header-anchor" href="#equal-size-bucket-m4-sample"><span>Equal Size Bucket M4 Sample</span></a></h4><p>The input sequence is sampled using the M4 sampling method. That is to sample the head, tail, min and max values for each bucket.</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Series Data Type Description</th></tr></thead><tbody><tr><td>EQUAL_SIZE_BUCKET_M4_SAMPLE</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td><code>proportion</code> The value range is <code>(0, 1]</code>, the default is <code>0.1</code></td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Returns equal bucket M4 samples that match the sampling ratio</td></tr></tbody></table><h5 id="example-6" tabindex="-1"><a class="header-anchor" href="#example-6"><span>Example</span></a></h5><p>Example data: <code>root.ln.wf01.wt01.temperature</code> has a total of <code>100</code> ordered data from <code>0.0-99.0</code>, and the test data is randomly sampled in equal buckets.</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> equal_size_bucket_m4_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> M4_sample <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 punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+---------+
|                         Time|M4_sample|
+-----------------------------+---------+
|1970-01-01T08:00:00.000+08:00|      0.0|
|1970-01-01T08:00:00.001+08:00|      1.0|
|1970-01-01T08:00:00.038+08:00|     38.0|
|1970-01-01T08:00:00.039+08:00|     39.0|
|1970-01-01T08:00:00.040+08:00|     40.0|
|1970-01-01T08:00:00.041+08:00|     41.0|
|1970-01-01T08:00:00.078+08:00|     78.0|
|1970-01-01T08:00:00.079+08:00|     79.0|
|1970-01-01T08:00:00.080+08:00|     80.0|
|1970-01-01T08:00:00.081+08:00|     81.0|
|1970-01-01T08:00:00.098+08:00|     98.0|
|1970-01-01T08:00:00.099+08:00|     99.0|
+-----------------------------+---------+
Total line number = 12
It costs 0.065s
</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></div><h4 id="equal-size-bucket-outlier-sample" tabindex="-1"><a class="header-anchor" href="#equal-size-bucket-outlier-sample"><span>Equal Size Bucket Outlier Sample</span></a></h4><p>This function samples the input sequence with equal number of bucket outliers, that is, according to the downsampling ratio given by the user and the number of samples in the bucket, the input sequence is divided into several buckets according to a fixed number of points. Sampling by the given outlier sampling method within each bucket.</p><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Required Attributes</th><th>Output Series Data Type</th><th>Series Data Type Description</th></tr></thead><tbody><tr><td>EQUAL_SIZE_BUCKET_OUTLIER_SAMPLE</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>The value range of <code>proportion</code> is <code>(0, 1]</code>, the default is <code>0.1</code><br> The value of <code>type</code> is <code>avg</code> or <code>stendis</code> or <code>cos</code> or <code>prenextdis</code>, the default is <code>avg</code> <br>The value of <code>number</code> should be greater than 0, the default is <code>3</code></td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Returns outlier samples in equal buckets that match the sampling ratio and the number of samples in the bucket</td></tr></tbody></table><p>Parameter Description</p><ul><li><code>proportion</code>: sampling ratio</li><li><code>number</code>: the number of samples in each bucket, default <code>3</code></li><li><code>type</code>: outlier sampling method, the value is <ul><li><code>avg</code>: Take the average of the data points in the bucket, and find the <code>top number</code> farthest from the average according to the sampling ratio</li><li><code>stendis</code>: Take the vertical distance between each data point in the bucket and the first and last data points of the bucket to form a straight line, and according to the sampling ratio, find the <code>top number</code> with the largest distance</li><li><code>cos</code>: Set a data point in the bucket as b, the data point on the left of b as a, and the data point on the right of b as c, then take the cosine value of the angle between the ab and bc vectors. The larger the angle, the more likely it is an outlier. Find the <code>top number</code> with the smallest cos value</li><li><code>prenextdis</code>: Let a data point in the bucket be b, the data point to the left of b is a, and the data point to the right of b is c, then take the sum of the lengths of ab and bc as the yardstick, the larger the sum, the more likely it is to be an outlier, and find the <code>top number</code> with the largest sum value</li></ul></li></ul><h5 id="example-7" tabindex="-1"><a class="header-anchor" href="#example-7"><span>Example</span></a></h5><p>Example data: <code>root.ln.wf01.wt01.temperature</code> has a total of <code>100</code> ordered data from <code>0.0-99.0</code>. Among them, in order to add outliers, we make the number modulo 5 equal to 0 increment by 100.</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code>IoTDB<span class="token operator">&gt;</span> <span class="token keyword">select</span> temperature <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 punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------+</span>
<span class="token operator">|</span>                         <span class="token keyword">Time</span><span class="token operator">|</span>root<span class="token punctuation">.</span>ln<span class="token punctuation">.</span>wf01<span class="token punctuation">.</span>wt01<span class="token punctuation">.</span>temperature<span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------+</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<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 operator">|</span>                          <span class="token number">0.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.001</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">1.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.002</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">2.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.003</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">3.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.004</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">4.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.005</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                        <span class="token number">105.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.006</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">6.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.007</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">7.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.008</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">8.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.009</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                          <span class="token number">9.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.010</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">10.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.011</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">11.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.012</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">12.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.013</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">13.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.014</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">14.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.015</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                        <span class="token number">115.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.016</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">16.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token operator">|</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.092</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">92.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.093</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">93.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.094</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">94.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.095</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                        <span class="token number">195.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.096</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">96.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.097</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">97.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.098</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">98.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.099</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                         <span class="token number">99.0</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------+</span>
</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></div><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> equal_size_bucket_outlier_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;avg&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;number&#39;</span><span class="token operator">=</span><span class="token string">&#39;2&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> outlier_avg_sample<span class="token punctuation">,</span> equal_size_bucket_outlier_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;stendis&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;number&#39;</span><span class="token operator">=</span><span class="token string">&#39;2&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> outlier_stendis_sample<span class="token punctuation">,</span> equal_size_bucket_outlier_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;cos&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;number&#39;</span><span class="token operator">=</span><span class="token string">&#39;2&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> outlier_cos_sample<span class="token punctuation">,</span> equal_size_bucket_outlier_sample<span class="token punctuation">(</span>temperature<span class="token punctuation">,</span> <span class="token string">&#39;proportion&#39;</span><span class="token operator">=</span><span class="token string">&#39;0.1&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;type&#39;</span><span class="token operator">=</span><span class="token string">&#39;prenextdis&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;number&#39;</span><span class="token operator">=</span><span class="token string">&#39;2&#39;</span><span class="token punctuation">)</span> <span class="token keyword">as</span> outlier_prenextdis_sample <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 punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result:</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token operator">+</span><span class="token comment">-----------------------------+------------------+----------------------+------------------+-------------------------+</span>
<span class="token operator">|</span>                         <span class="token keyword">Time</span><span class="token operator">|</span>outlier_avg_sample<span class="token operator">|</span>outlier_stendis_sample<span class="token operator">|</span>outlier_cos_sample<span class="token operator">|</span>outlier_prenextdis_sample<span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+------------------+----------------------+------------------+-------------------------+</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.005</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">105.0</span><span class="token operator">|</span>                 <span class="token number">105.0</span><span class="token operator">|</span>             <span class="token number">105.0</span><span class="token operator">|</span>                    <span class="token number">105.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.015</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">115.0</span><span class="token operator">|</span>                 <span class="token number">115.0</span><span class="token operator">|</span>             <span class="token number">115.0</span><span class="token operator">|</span>                    <span class="token number">115.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.025</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">125.0</span><span class="token operator">|</span>                 <span class="token number">125.0</span><span class="token operator">|</span>             <span class="token number">125.0</span><span class="token operator">|</span>                    <span class="token number">125.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.035</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">135.0</span><span class="token operator">|</span>                 <span class="token number">135.0</span><span class="token operator">|</span>             <span class="token number">135.0</span><span class="token operator">|</span>                    <span class="token number">135.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.045</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">145.0</span><span class="token operator">|</span>                 <span class="token number">145.0</span><span class="token operator">|</span>             <span class="token number">145.0</span><span class="token operator">|</span>                    <span class="token number">145.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.055</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">155.0</span><span class="token operator">|</span>                 <span class="token number">155.0</span><span class="token operator">|</span>             <span class="token number">155.0</span><span class="token operator">|</span>                    <span class="token number">155.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.065</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">165.0</span><span class="token operator">|</span>                 <span class="token number">165.0</span><span class="token operator">|</span>             <span class="token number">165.0</span><span class="token operator">|</span>                    <span class="token number">165.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.075</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">175.0</span><span class="token operator">|</span>                 <span class="token number">175.0</span><span class="token operator">|</span>             <span class="token number">175.0</span><span class="token operator">|</span>                    <span class="token number">175.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.085</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">185.0</span><span class="token operator">|</span>                 <span class="token number">185.0</span><span class="token operator">|</span>             <span class="token number">185.0</span><span class="token operator">|</span>                    <span class="token number">185.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.095</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>             <span class="token number">195.0</span><span class="token operator">|</span>                 <span class="token number">195.0</span><span class="token operator">|</span>             <span class="token number">195.0</span><span class="token operator">|</span>                    <span class="token number">195.0</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+------------------+----------------------+------------------+-------------------------+</span>
Total line number <span class="token operator">=</span> <span class="token number">10</span>
It costs <span class="token number">0.041</span>s
</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><h3 id="m4-function" tabindex="-1"><a class="header-anchor" href="#m4-function"><span>M4 Function</span></a></h3><p>M4 is used to sample the <code>first, last, bottom, top</code> points for each sliding window:</p><ul><li>the first point is the point with the <strong>m</strong>inimal time;</li><li>the last point is the point with the <strong>m</strong>aximal time;</li><li>the bottom point is the point with the <strong>m</strong>inimal value (if there are multiple such points, M4 returns one of them);</li><li>the top point is the point with the <strong>m</strong>aximal value (if there are multiple such points, M4 returns one of them).</li></ul><img src="https://alioss.timecho.com/docs/img/github/198178733-a0919d17-0663-4672-9c4f-1efad6f463c2.png" alt="image" style="zoom:50%;"><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Attributes</th><th>Output Series Data Type</th><th>Series Data Type Description</th></tr></thead><tbody><tr><td>M4</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Different attributes used by the size window and the time window. The size window uses attributes <code>windowSize</code> and <code>slidingStep</code>. The time window uses attributes <code>timeInterval</code>, <code>slidingStep</code>, <code>displayWindowBegin</code>, and <code>displayWindowEnd</code>. More details see below.</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>Returns the <code>first, last, bottom, top</code> points in each sliding window. M4 sorts and deduplicates the aggregated points within the window before outputting them.</td></tr></tbody></table><h4 id="attributes" tabindex="-1"><a class="header-anchor" href="#attributes"><span>Attributes</span></a></h4><p><strong>(1) Attributes for the size window:</strong></p><ul><li><code>windowSize</code>: The number of points in a window. Int data type. <strong>Required</strong>.</li><li><code>slidingStep</code>: Slide a window by the number of points. Int data type. Optional. If not set, default to the same as <code>windowSize</code>.</li></ul><img src="https://alioss.timecho.com/docs/img/github/198181449-00d563c8-7bce-4ecd-a031-ec120ca42c3f.png" alt="image" style="zoom:50%;"><p><strong>(2) Attributes for the time window:</strong></p><ul><li><code>timeInterval</code>: The time interval length of a window. Long data type. <strong>Required</strong>.</li><li><code>slidingStep</code>: Slide a window by the time length. Long data type. Optional. If not set, default to the same as <code>timeInterval</code>.</li><li><code>displayWindowBegin</code>: The starting position of the window (included). Long data type. Optional. If not set, default to Long.MIN_VALUE, meaning using the time of the first data point of the input time series as the starting position of the window.</li><li><code>displayWindowEnd</code>: End time limit (excluded, essentially playing the same role as <code>WHERE time &lt; displayWindowEnd</code>). Long data type. Optional. If not set, default to Long.MAX_VALUE, meaning there is no additional end time limit other than the end of the input time series itself.</li></ul><img src="https://alioss.timecho.com/docs/img/github/198183015-93b56644-3330-4acf-ae9e-d718a02b5f4c.png" alt="groupBy window" style="zoom:67%;"><h4 id="examples" tabindex="-1"><a class="header-anchor" href="#examples"><span>Examples</span></a></h4><p>Input series:</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token operator">+</span><span class="token comment">-----------------------------+------------------+</span>
<span class="token operator">|</span>                         <span class="token keyword">Time</span><span class="token operator">|</span>root<span class="token punctuation">.</span>vehicle<span class="token punctuation">.</span>d1<span class="token punctuation">.</span>s1<span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+------------------+</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.001</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>               <span class="token number">5.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.002</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">15.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.005</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">10.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.008</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>               <span class="token number">8.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.010</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">30.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.020</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">20.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.025</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>               <span class="token number">8.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.027</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">20.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.030</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">40.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.033</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>               <span class="token number">9.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.035</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">10.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.040</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">20.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.045</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">30.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.052</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>               <span class="token number">8.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.054</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>              <span class="token number">18.0</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+------------------+</span>
</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></div><p>SQL for query1:</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> M4<span class="token punctuation">(</span>s1<span class="token punctuation">,</span><span class="token string">&#39;timeInterval&#39;</span><span class="token operator">=</span><span class="token string">&#39;25&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;displayWindowBegin&#39;</span><span class="token operator">=</span><span class="token string">&#39;0&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;displayWindowEnd&#39;</span><span class="token operator">=</span><span class="token string">&#39;100&#39;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>vehicle<span class="token punctuation">.</span>d1
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Output1:</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------------------------------------------------------------------------+</span>
<span class="token operator">|</span>                         <span class="token keyword">Time</span><span class="token operator">|</span>M4<span class="token punctuation">(</span>root<span class="token punctuation">.</span>vehicle<span class="token punctuation">.</span>d1<span class="token punctuation">.</span>s1<span class="token punctuation">,</span> <span class="token string">&quot;timeInterval&quot;</span><span class="token operator">=</span><span class="token string">&quot;25&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;displayWindowBegin&quot;</span><span class="token operator">=</span><span class="token string">&quot;0&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;displayWindowEnd&quot;</span><span class="token operator">=</span><span class="token string">&quot;100&quot;</span><span class="token punctuation">)</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------------------------------------------------------------------------+</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.001</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                                                                            <span class="token number">5.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.010</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                                                                           <span class="token number">30.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.020</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                                                                           <span class="token number">20.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.025</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                                                                            <span class="token number">8.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.030</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                                                                           <span class="token number">40.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.045</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                                                                           <span class="token number">30.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.052</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                                                                            <span class="token number">8.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.054</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                                                                           <span class="token number">18.0</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------------------------------------------------------------------------+</span>
Total line number <span class="token operator">=</span> <span class="token number">8</span>
</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>SQL for query2:</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> M4<span class="token punctuation">(</span>s1<span class="token punctuation">,</span><span class="token string">&#39;windowSize&#39;</span><span class="token operator">=</span><span class="token string">&#39;10&#39;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>vehicle<span class="token punctuation">.</span>d1
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Output2:</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------------------+</span>
<span class="token operator">|</span>                         <span class="token keyword">Time</span><span class="token operator">|</span>M4<span class="token punctuation">(</span>root<span class="token punctuation">.</span>vehicle<span class="token punctuation">.</span>d1<span class="token punctuation">.</span>s1<span class="token punctuation">,</span> <span class="token string">&quot;windowSize&quot;</span><span class="token operator">=</span><span class="token string">&quot;10&quot;</span><span class="token punctuation">)</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------------------+</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.001</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                      <span class="token number">5.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.030</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                     <span class="token number">40.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.033</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                      <span class="token number">9.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.035</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                     <span class="token number">10.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.045</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                     <span class="token number">30.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.052</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                      <span class="token number">8.0</span><span class="token operator">|</span>
<span class="token operator">|</span><span class="token number">1970</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T08:<span class="token number">00</span>:<span class="token number">00.054</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span>                                     <span class="token number">18.0</span><span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------------------+-----------------------------------------+</span>
Total line number <span class="token operator">=</span> <span class="token number">7</span>
</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></div><h4 id="suggested-use-cases" tabindex="-1"><a class="header-anchor" href="#suggested-use-cases"><span>Suggested Use Cases</span></a></h4><p><strong>(1) Use Case: Extreme-point-preserving downsampling</strong></p><p>As M4 aggregation selects the <code>first, last, bottom, top</code> points for each window, M4 usually preserves extreme points and thus patterns better than other downsampling methods such as Piecewise Aggregate Approximation (PAA). Therefore, if you want to downsample the time series while preserving extreme points, you may give M4 a try.</p><p><strong>(2) Use case: Error-free two-color line chart visualization of large-scale time series through M4 downsampling</strong></p>`,175),g={href:"http://www.vldb.org/pvldb/vol7/p797-jugel.pdf",target:"_blank",rel:"noopener noreferrer"},T=t("<p>Given a chart of <code>w*h</code> pixels, suppose that the visualization time range of the time series is <code>[tqs,tqe)</code> and (tqe-tqs) is divisible by w, the points that fall within the <code>i</code>-th time span <code>Ii=[tqs+(tqe-tqs)/w*(i-1),tqs+(tqe-tqs)/w*i)</code> will be drawn on the <code>i</code>-th pixel column, i=1,2,...,w. Therefore, from a visualization-driven perspective, use the sql: <code>&quot;select M4(s1,&#39;timeInterval&#39;=&#39;(tqe-tqs)/w&#39;,&#39;displayWindowBegin&#39;=&#39;tqs&#39;,&#39;displayWindowEnd&#39;=&#39;tqe&#39;) from root.vehicle.d1&quot;</code> to sample the <code>first, last, bottom, top</code> points for each time span. The resulting downsampled time series has no more than <code>4*w</code> points, a big reduction compared to the original large-scale time series. Meanwhile, the two-color line chart drawn from the reduced data is identical that to that drawn from the original data (pixel-level consistency).</p>",1),f={href:"https://grafana.com/docs/grafana/latest/dashboards/variables/add-template-variables/#global-variables",target:"_blank",rel:"noopener noreferrer"},y=s("code",null,"$__interval_ms",-1),x=t(`<div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>select M4(s1,&#39;timeInterval&#39;=&#39;$__interval_ms&#39;) from root.sg1.d1
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>where <code>timeInterval</code> is set as <code>(tqe-tqs)/w</code> automatically. Note that the time precision here is assumed to be milliseconds.</p><h4 id="comparison-with-other-functions" tabindex="-1"><a class="header-anchor" href="#comparison-with-other-functions"><span>Comparison with Other Functions</span></a></h4>`,3),w=s("thead",null,[s("tr",null,[s("th",null,"SQL"),s("th",null,"Whether support M4 aggregation"),s("th",null,"Sliding window type"),s("th",null,"Example"),s("th",null,"Docs")])],-1),q=s("td",null,"1. native built-in aggregate functions with Group By clause",-1),E=s("td",null,[n("No. Lack "),s("code",null,"BOTTOM_TIME"),n(" and "),s("code",null,"TOP_TIME"),n(", which are respectively the time of the points that have the mininum and maximum value.")],-1),_=s("td",null,"Time Window",-1),O=s("td",null,[s("code",null,"select count(status), max_value(temperature) from root.ln.wf01.wt01 group by ([2017-11-01 00:00:00, 2017-11-07 23:00:00), 3h, 1d)")],-1),I={href:"https://iotdb.apache.org/UserGuide/Master/Query-Data/Aggregate-Query.html#built-in-aggregate-functions",target:"_blank",rel:"noopener noreferrer"},L=s("br",null,null,-1),N={href:"https://iotdb.apache.org/UserGuide/Master/Query-Data/Aggregate-Query.html#downsampling-aggregate-query",target:"_blank",rel:"noopener noreferrer"},D=s("td",null,"2. EQUAL_SIZE_BUCKET_M4_SAMPLE (built-in UDF)",-1),A=s("td",null,"Yes*",-1),S=s("td",null,[n("Size Window. "),s("code",null,"windowSize = 4*(int)(1/proportion)")],-1),B=s("td",null,[s("code",null,"select equal_size_bucket_m4_sample(temperature, 'proportion'='0.1') as M4_sample from root.ln.wf01.wt01")],-1),U={href:"https://iotdb.apache.org/UserGuide/Master/Query-Data/Select-Expression.html#time-series-generating-functions",target:"_blank",rel:"noopener noreferrer"},F=s("tr",null,[s("td",null,[s("strong",null,"3. M4 (built-in UDF)")]),s("td",null,"Yes*"),s("td",null,"Size Window, Time Window"),s("td",null,[n("(1) Size Window: "),s("code",null,"select M4(s1,'windowSize'='10') from root.vehicle.d1"),n(),s("br"),n("(2) Time Window: "),s("code",null,"select M4(s1,'timeInterval'='25','displayWindowBegin'='0','displayWindowEnd'='100') from root.vehicle.d1")]),s("td",null,"refer to this doc")],-1),M=s("tr",null,[s("td",null,"4. extend native built-in aggregate functions with Group By clause to support M4 aggregation"),s("td",null,"not implemented"),s("td",null,"not implemented"),s("td",null,"not implemented"),s("td",null,"not implemented")],-1),R=t('<p>Further compare <code>EQUAL_SIZE_BUCKET_M4_SAMPLE</code> and <code>M4</code>:</p><p><strong>(1) Different M4 aggregation definition:</strong></p><p>For each window, <code>EQUAL_SIZE_BUCKET_M4_SAMPLE</code> extracts the top and bottom points from points <strong>EXCLUDING</strong> the first and last points.</p><p>In contrast, <code>M4</code> extracts the top and bottom points from points <strong>INCLUDING</strong> the first and last points, which is more consistent with the semantics of <code>max_value</code> and <code>min_value</code> stored in metadata.</p><p>It is worth noting that both functions sort and deduplicate the aggregated points in a window before outputting them to the collectors.</p><p><strong>(2) Different sliding windows:</strong></p><p><code>EQUAL_SIZE_BUCKET_M4_SAMPLE</code> uses SlidingSizeWindowAccessStrategy and <strong>indirectly</strong> controls sliding window size by sampling proportion. The conversion formula is <code>windowSize = 4*(int)(1/proportion)</code>.</p><p><code>M4</code> supports two types of sliding window: SlidingSizeWindowAccessStrategy and SlidingTimeWindowAccessStrategy. <code>M4</code> <strong>directly</strong> controls the window point size or time length using corresponding parameters.</p><h3 id="user-defined-timeseries-generating-functions" tabindex="-1"><a class="header-anchor" href="#user-defined-timeseries-generating-functions"><span>User Defined Timeseries Generating Functions</span></a></h3>',9),C=s("p",null,"Known Implementation UDF Libraries:",-1),z={href:"https://thulab.github.io/iotdb-quality",target:"_blank",rel:"noopener noreferrer"},P=t(`<h2 id="nested-expressions" tabindex="-1"><a class="header-anchor" href="#nested-expressions"><span>Nested Expressions</span></a></h2><p>IoTDB supports the calculation of arbitrary nested expressions. Since time series query and aggregation query can not be used in a query statement at the same time, we divide nested expressions into two types, which are nested expressions with time series query and nested expressions with aggregation query.</p><p>The following is the syntax definition of the <code>select</code> clause:</p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code>selectClause
    : <span class="token keyword">SELECT</span> resultColumn <span class="token punctuation">(</span><span class="token string">&#39;,&#39;</span> resultColumn<span class="token punctuation">)</span><span class="token operator">*</span>
    <span class="token punctuation">;</span>

resultColumn
    : expression <span class="token punctuation">(</span><span class="token keyword">AS</span> ID<span class="token punctuation">)</span>?
    <span class="token punctuation">;</span>

expression
    : <span class="token string">&#39;(&#39;</span> expression <span class="token string">&#39;)&#39;</span>
    <span class="token operator">|</span> <span class="token string">&#39;-&#39;</span> expression
    <span class="token operator">|</span> expression <span class="token punctuation">(</span><span class="token string">&#39;*&#39;</span> <span class="token operator">|</span> <span class="token string">&#39;/&#39;</span> <span class="token operator">|</span> <span class="token string">&#39;%&#39;</span><span class="token punctuation">)</span> expression
    <span class="token operator">|</span> expression <span class="token punctuation">(</span><span class="token string">&#39;+&#39;</span> <span class="token operator">|</span> <span class="token string">&#39;-&#39;</span><span class="token punctuation">)</span> expression
    <span class="token operator">|</span> functionName <span class="token string">&#39;(&#39;</span> expression <span class="token punctuation">(</span><span class="token string">&#39;,&#39;</span> expression<span class="token punctuation">)</span><span class="token operator">*</span> functionAttribute<span class="token operator">*</span> <span class="token string">&#39;)&#39;</span>
    <span class="token operator">|</span> timeSeriesSuffixPath
    <span class="token operator">|</span> number
    <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 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><h3 id="nested-expressions-with-time-series-query" tabindex="-1"><a class="header-anchor" href="#nested-expressions-with-time-series-query"><span>Nested Expressions with Time Series Query</span></a></h3><p>IoTDB supports the calculation of arbitrary nested expressions consisting of <strong>numbers, time series, time series generating functions (including user-defined functions) and arithmetic expressions</strong> in the <code>select</code> clause.</p><h4 id="example-8" tabindex="-1"><a class="header-anchor" href="#example-8"><span>Example</span></a></h4><p>Input1：</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> a<span class="token punctuation">,</span>
       b<span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token punctuation">(</span>a <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">2</span> <span class="token operator">-</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">%</span> <span class="token number">2</span> <span class="token operator">+</span> <span class="token number">1.5</span><span class="token punctuation">,</span>
       sin<span class="token punctuation">(</span>a <span class="token operator">+</span> sin<span class="token punctuation">(</span>a <span class="token operator">+</span> sin<span class="token punctuation">(</span>b<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token operator">-</span><span class="token punctuation">(</span>a <span class="token operator">+</span> b<span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token punctuation">(</span>sin<span class="token punctuation">(</span>a <span class="token operator">+</span> b<span class="token punctuation">)</span> <span class="token operator">*</span> sin<span class="token punctuation">(</span>a <span class="token operator">+</span> b<span class="token punctuation">)</span> <span class="token operator">+</span> cos<span class="token punctuation">(</span>a <span class="token operator">+</span> b<span class="token punctuation">)</span> <span class="token operator">*</span> cos<span class="token punctuation">(</span>a <span class="token operator">+</span> b<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">1</span>
<span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<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 class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>Result1：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+----------+----------+----------------------------------------+---------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                         Time|root.sg1.a|root.sg1.b|((((root.sg1.a + 1) * 2) - 1) % 2) + 1.5|sin(root.sg1.a + sin(root.sg1.a + sin(root.sg1.b)))|(-root.sg1.a + root.sg1.b * ((sin(root.sg1.a + root.sg1.b) * sin(root.sg1.a + root.sg1.b)) + (cos(root.sg1.a + root.sg1.b) * cos(root.sg1.a + root.sg1.b)))) + 1|
+-----------------------------+----------+----------+----------------------------------------+---------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|1970-01-01T08:00:00.010+08:00|         1|         1|                                     2.5|                                 0.9238430524420609|                                                                                                                      -1.0|
|1970-01-01T08:00:00.020+08:00|         2|         2|                                     2.5|                                 0.7903505371876317|                                                                                                                      -3.0|
|1970-01-01T08:00:00.030+08:00|         3|         3|                                     2.5|                                0.14065207680386618|                                                                                                                      -5.0|
|1970-01-01T08:00:00.040+08:00|         4|      null|                                     2.5|                                               null|                                                                                                                      null|
|1970-01-01T08:00:00.050+08:00|      null|         5|                                    null|                                               null|                                                                                                                      null|
|1970-01-01T08:00:00.060+08:00|         6|         6|                                     2.5|                                -0.7288037411970916|                                                                                                                     -11.0|
+-----------------------------+----------+----------+----------------------------------------+---------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
Total line number = 6
It costs 0.048s
</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></div><p>Input2：</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 punctuation">(</span>a <span class="token operator">+</span> b<span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">2</span> <span class="token operator">+</span> sin<span class="token punctuation">(</span>a<span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result2：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+----------------------------------------------+
|                         Time|((root.sg.a + root.sg.b) * 2) + sin(root.sg.a)|
+-----------------------------+----------------------------------------------+
|1970-01-01T08:00:00.010+08:00|                             59.45597888911063|
|1970-01-01T08:00:00.020+08:00|                            100.91294525072763|
|1970-01-01T08:00:00.030+08:00|                            139.01196837590714|
|1970-01-01T08:00:00.040+08:00|                            180.74511316047935|
|1970-01-01T08:00:00.050+08:00|                            219.73762514629607|
|1970-01-01T08:00:00.060+08:00|                             259.6951893788978|
|1970-01-01T08:00:00.070+08:00|                             300.7738906815579|
|1970-01-01T08:00:00.090+08:00|                             39.45597888911063|
|1970-01-01T08:00:00.100+08:00|                             39.45597888911063|
+-----------------------------+----------------------------------------------+
Total line number = 9
It costs 0.011s
</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></div><p>Input3：</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 punctuation">(</span>a <span class="token operator">+</span> <span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">/</span> <span class="token number">2</span>  <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result3：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+-----------------------------+-----------------------------+
|                         Time|(root.sg1.a + root.sg1.a) / 2|(root.sg1.a + root.sg1.b) / 2|
+-----------------------------+-----------------------------+-----------------------------+
|1970-01-01T08:00:00.010+08:00|                          1.0|                          1.0|
|1970-01-01T08:00:00.020+08:00|                          2.0|                          2.0|
|1970-01-01T08:00:00.030+08:00|                          3.0|                          3.0|
|1970-01-01T08:00:00.040+08:00|                          4.0|                         null|
|1970-01-01T08:00:00.060+08:00|                          6.0|                          6.0|
+-----------------------------+-----------------------------+-----------------------------+
Total line number = 5
It costs 0.011s
</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>Input4：</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 punctuation">(</span>a <span class="token operator">+</span> b<span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">3</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">,</span> root<span class="token punctuation">.</span>ln
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Result4：</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
|                         Time|(root.sg.a + root.sg.b) * 3|(root.sg.a + root.ln.b) * 3|(root.ln.a + root.sg.b) * 3|(root.ln.a + root.ln.b) * 3|
+-----------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
|1970-01-01T08:00:00.010+08:00|                       90.0|                      270.0|                      360.0|                      540.0|
|1970-01-01T08:00:00.020+08:00|                      150.0|                      330.0|                      690.0|                      870.0|
|1970-01-01T08:00:00.030+08:00|                      210.0|                      450.0|                      570.0|                      810.0|
|1970-01-01T08:00:00.040+08:00|                      270.0|                      240.0|                      690.0|                      660.0|
|1970-01-01T08:00:00.050+08:00|                      330.0|                       null|                       null|                       null|
|1970-01-01T08:00:00.060+08:00|                      390.0|                       null|                       null|                       null|
|1970-01-01T08:00:00.070+08:00|                      450.0|                       null|                       null|                       null|
|1970-01-01T08:00:00.090+08:00|                       60.0|                       null|                       null|                       null|
|1970-01-01T08:00:00.100+08:00|                       60.0|                       null|                       null|                       null|
+-----------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
Total line number = 9
It costs 0.014s
</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></div><h4 id="explanation" tabindex="-1"><a class="header-anchor" href="#explanation"><span>Explanation</span></a></h4><ul><li>Only when the left operand and the right operand under a certain timestamp are not <code>null</code>, the nested expressions will have an output value. Otherwise this row will not be included in the result. <ul><li>In Result1 of the Example part, the value of time series <code>root.sg.a</code> at time 40 is 4, while the value of time series <code>root.sg.b</code> is <code>null</code>. So at time 40, the value of nested expressions <code>(a + b) * 2 + sin(a)</code> is <code>null</code>. So in Result2, this row is not included in the result.</li></ul></li><li>If one operand in the nested expressions can be translated into multiple time series (For example, <code>*</code>), the result of each time series will be included in the result (Cartesian product). Please refer to Input3, Input4 and corresponding Result3 and Result4 in Example.</li></ul><h4 id="note" tabindex="-1"><a class="header-anchor" href="#note"><span>Note</span></a></h4><blockquote><p>Please note that Aligned Time Series has not been supported in Nested Expressions with Time Series Query yet. An error message is expected if you use it with Aligned Time Series selected in a query statement.</p></blockquote><h3 id="nested-expressions-query-with-aggregations" tabindex="-1"><a class="header-anchor" href="#nested-expressions-query-with-aggregations"><span>Nested Expressions query with aggregations</span></a></h3><p>IoTDB supports the calculation of arbitrary nested expressions consisting of <strong>numbers, aggregations and arithmetic expressions</strong> in the <code>select</code> clause.</p><h4 id="example-9" tabindex="-1"><a class="header-anchor" href="#example-9"><span>Example</span></a></h4><p>Aggregation query without <code>GROUP BY</code>.</p><p>Input1:</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 function">avg</span><span class="token punctuation">(</span>temperature<span class="token punctuation">)</span><span class="token punctuation">,</span>
       sin<span class="token punctuation">(</span><span class="token function">avg</span><span class="token punctuation">(</span>temperature<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token function">avg</span><span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span>
       <span class="token operator">-</span><span class="token function">sum</span><span class="token punctuation">(</span>hardware<span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token function">avg</span><span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token function">sum</span><span class="token punctuation">(</span>hardware<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 punctuation">;</span>
</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></div><p>Result1:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+--------------------------------------------------------------------+
|avg(root.ln.wf01.wt01.temperature)|sin(avg(root.ln.wf01.wt01.temperature))|avg(root.ln.wf01.wt01.temperature) + 1|-sum(root.ln.wf01.wt01.hardware)|avg(root.ln.wf01.wt01.temperature) + sum(root.ln.wf01.wt01.hardware)|
+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+--------------------------------------------------------------------+
|                15.927999999999999|                   -0.21826546964855045|                    16.927999999999997|                         -7426.0|                                                            7441.928|
+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+--------------------------------------------------------------------+
Total line number = 1
It costs 0.009s
</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>Input2:</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 function">avg</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span><span class="token punctuation">,</span> 
	   <span class="token punctuation">(</span><span class="token function">avg</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">3</span> <span class="token operator">/</span> <span class="token number">2</span> <span class="token operator">-</span><span class="token number">1</span> 
<span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1
</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></div><p>Result2:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+---------------+---------------+-------------------------------------+-------------------------------------+
|avg(root.sg1.a)|avg(root.sg1.b)|(((avg(root.sg1.a) + 1) * 3) / 2) - 1|(((avg(root.sg1.b) + 1) * 3) / 2) - 1|
+---------------+---------------+-------------------------------------+-------------------------------------+
|            3.2|            3.4|                    5.300000000000001|                   5.6000000000000005|
+---------------+---------------+-------------------------------------+-------------------------------------+
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>Aggregation with <code>GROUP BY</code>.</p><p>Input3:</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 function">avg</span><span class="token punctuation">(</span>temperature<span class="token punctuation">)</span><span class="token punctuation">,</span>
       sin<span class="token punctuation">(</span><span class="token function">avg</span><span class="token punctuation">(</span>temperature<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token function">avg</span><span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span>
       <span class="token operator">-</span><span class="token function">sum</span><span class="token punctuation">(</span>hardware<span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token function">avg</span><span class="token punctuation">(</span>temperature<span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token function">sum</span><span class="token punctuation">(</span>hardware<span class="token punctuation">)</span> <span class="token keyword">as</span> custom_sum
<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">10</span><span class="token punctuation">,</span> <span class="token number">90</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">10</span>ms<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 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>Result3:</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>+-----------------------------+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+----------+
|                         Time|avg(root.ln.wf01.wt01.temperature)|sin(avg(root.ln.wf01.wt01.temperature))|avg(root.ln.wf01.wt01.temperature) + 1|-sum(root.ln.wf01.wt01.hardware)|custom_sum|
+-----------------------------+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+----------+
|1970-01-01T08:00:00.010+08:00|                13.987499999999999|                     0.9888207947857667|                    14.987499999999999|                         -3211.0| 3224.9875|
|1970-01-01T08:00:00.020+08:00|                              29.6|                    -0.9701057337071853|                                  30.6|                         -3720.0|    3749.6|
|1970-01-01T08:00:00.030+08:00|                              null|                                   null|                                  null|                            null|      null|
|1970-01-01T08:00:00.040+08:00|                              null|                                   null|                                  null|                            null|      null|
|1970-01-01T08:00:00.050+08:00|                              null|                                   null|                                  null|                            null|      null|
|1970-01-01T08:00:00.060+08:00|                              null|                                   null|                                  null|                            null|      null|
|1970-01-01T08:00:00.070+08:00|                              null|                                   null|                                  null|                            null|      null|
|1970-01-01T08:00:00.080+08:00|                              null|                                   null|                                  null|                            null|      null|
+-----------------------------+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+----------+
Total line number = 8
It costs 0.012s
</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></div><h4 id="explanation-1" tabindex="-1"><a class="header-anchor" href="#explanation-1"><span>Explanation</span></a></h4><ul><li>Only when the left operand and the right operand under a certain timestamp are not <code>null</code>, the nested expressions will have an output value. Otherwise this row will not be included in the result. But for nested expressions with <code>GROUP BY</code> clause, it is better to show the result of all time intervals. Please refer to Input3 and corresponding Result3 in Example.</li><li>If one operand in the nested expressions can be translated into multiple time series (For example, <code>*</code>), the result of each time series will be included in the result (Cartesian product). Please refer to Input2 and corresponding Result2 in Example.</li></ul><h4 id="note-1" tabindex="-1"><a class="header-anchor" href="#note-1"><span>Note</span></a></h4><blockquote><p>Automated fill (<code>FILL</code>) and grouped by level (<code>GROUP BY LEVEL</code>) are not supported in an aggregation query with expression nested. They may be supported in future versions.</p><p>The aggregation expression must be the lowest level input of one expression tree. Any kind expressions except timeseries are not valid as aggregation function parameters。</p><p>In a word, the following queries are not valid.</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 function">avg</span><span class="token punctuation">(</span>s1<span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d1<span class="token punctuation">;</span> <span class="token comment">-- The aggregation function has expression parameters.</span>
<span class="token keyword">SELECT</span> <span class="token function">avg</span><span class="token punctuation">(</span>s1<span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token function">avg</span><span class="token punctuation">(</span>s2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">LEVEL</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span> <span class="token comment">-- Grouped by level</span>
<span class="token keyword">SELECT</span> <span class="token function">avg</span><span class="token punctuation">(</span>s1<span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token function">avg</span><span class="token punctuation">(</span>s2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d1 <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">0</span><span class="token punctuation">,</span> <span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">1</span>s<span class="token punctuation">)</span> FILL<span class="token punctuation">(</span>previous<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- Automated fill</span>
</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></div></blockquote><h2 id="use-alias" tabindex="-1"><a class="header-anchor" href="#use-alias"><span>Use Alias</span></a></h2><p>Since the unique data model of IoTDB, lots of additional information like device will be carried before each sensor. Sometimes, we want to query just one specific device, then these prefix information show frequently will be redundant in this situation, influencing the analysis of result set. At this time, we can use <code>AS</code> function provided by IoTDB, assign an alias to time series selected in query.</p><p>For example：</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> s1 <span class="token keyword">as</span> temperature<span class="token punctuation">,</span> s2 <span class="token keyword">as</span> speed <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 punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>The result set is：</p><table><thead><tr><th>Time</th><th>temperature</th><th>speed</th></tr></thead><tbody><tr><td>...</td><td>...</td><td>...</td></tr></tbody></table>`,54);function G(Q,V){const p=l("RouteLink"),e=l("ExternalLinkIcon");return c(),i("div",null,[d,s("ul",null,[k,s("li",null,[n("function "),s("ul",null,[s("li",null,[n("Built-in aggregation functions, see "),a(p,{to:"/UserGuide/V0.13.x/Query-Data/Aggregate-Query.html"},{default:o(()=>[n("Aggregate Query")]),_:1}),n(" for details.")]),m,s("li",null,[n("User-defined functions, see "),a(p,{to:"/UserGuide/V0.13.x/Process-Data/UDF-User-Defined-Function.html"},{default:o(()=>[n("UDF")]),_:1}),n(" for details.")])])]),b,v]),h,s("p",null,[n("Referring to paper "),s("a",g,[n('"M4: A Visualization-Oriented Time Series Data Aggregation"'),a(e)]),n(", M4 is a downsampling method to facilitate large-scale time series visualization without deforming the shape in terms of a two-color line chart.")]),T,s("p",null,[n("To eliminate the hassle of hardcoding parameters, we recommend the following usage of Grafana's "),s("a",f,[n("template variable"),a(e)]),n(),y,n(" when Grafana is used for visualization:")]),x,s("table",null,[w,s("tbody",null,[s("tr",null,[q,E,_,O,s("td",null,[s("a",I,[n("https://iotdb.apache.org/UserGuide/Master/Query-Data/Aggregate-Query.html#built-in-aggregate-functions"),a(e)]),n(),L,s("a",N,[n("https://iotdb.apache.org/UserGuide/Master/Query-Data/Aggregate-Query.html#downsampling-aggregate-query"),a(e)])])]),s("tr",null,[D,A,S,B,s("td",null,[s("a",U,[n("https://iotdb.apache.org/UserGuide/Master/Query-Data/Select-Expression.html#time-series-generating-functions"),a(e)])])]),F,M])]),R,s("p",null,[n("Please refer to "),a(p,{to:"/UserGuide/V0.13.x/Process-Data/UDF-User-Defined-Function.html"},{default:o(()=>[n("UDF (User Defined Function)")]),_:1}),n(".")]),C,s("ul",null,[s("li",null,[s("a",z,[n("IoTDB-Quality"),a(e)]),n(", a UDF library about data quality, including data profiling, data quality evalution and data repairing, etc.")])]),P])}const X=r(u,[["render",G],["__file","Select-Expression.html.vue"]]),Y=JSON.parse('{"path":"/UserGuide/V0.13.x/Query-Data/Select-Expression.html","title":"Select Expression","lang":"en-US","frontmatter":{"description":"Select Expression Syntax Definition A selection expression (selectExpr) is a component of a SELECT clause, each selectExpr corresponds to a column in the query result set, and i...","head":[["link",{"rel":"alternate","hreflang":"zh-cn","href":"https://iotdb.apache.org/zh/UserGuide/V0.13.x/Query-Data/Select-Expression.html"}],["meta",{"property":"og:url","content":"https://iotdb.apache.org/UserGuide/V0.13.x/Query-Data/Select-Expression.html"}],["meta",{"property":"og:site_name","content":"IoTDB Website"}],["meta",{"property":"og:title","content":"Select Expression"}],["meta",{"property":"og:description","content":"Select Expression Syntax Definition A selection expression (selectExpr) is a component of a SELECT clause, each selectExpr corresponds to a column in the query result set, and i..."}],["meta",{"property":"og:type","content":"article"}],["meta",{"property":"og:locale","content":"en-US"}],["meta",{"property":"og:locale:alternate","content":"zh-CN"}],["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\\":\\"Select Expression\\",\\"image\\":[\\"\\"],\\"dateModified\\":\\"2023-07-10T03:11:17.000Z\\",\\"author\\":[]}"]]},"headers":[{"level":2,"title":"Syntax Definition","slug":"syntax-definition","link":"#syntax-definition","children":[]},{"level":2,"title":"Arithmetic Query","slug":"arithmetic-query","link":"#arithmetic-query","children":[{"level":3,"title":"Operators","slug":"operators","link":"#operators","children":[]},{"level":3,"title":"Example","slug":"example","link":"#example","children":[]}]},{"level":2,"title":"Time Series Generating Functions","slug":"time-series-generating-functions","link":"#time-series-generating-functions","children":[{"level":3,"title":"Mathematical Functions","slug":"mathematical-functions","link":"#mathematical-functions","children":[]},{"level":3,"title":"String Processing Functions","slug":"string-processing-functions","link":"#string-processing-functions","children":[]},{"level":3,"title":"Selector Functions","slug":"selector-functions","link":"#selector-functions","children":[]},{"level":3,"title":"Variation Trend Calculation Functions","slug":"variation-trend-calculation-functions","link":"#variation-trend-calculation-functions","children":[]},{"level":3,"title":"Constant Timeseries Generating Functions","slug":"constant-timeseries-generating-functions","link":"#constant-timeseries-generating-functions","children":[]},{"level":3,"title":"Data Type Conversion Function","slug":"data-type-conversion-function","link":"#data-type-conversion-function","children":[]},{"level":3,"title":"Condition Functions","slug":"condition-functions","link":"#condition-functions","children":[]},{"level":3,"title":"Continuous Interval Functions","slug":"continuous-interval-functions","link":"#continuous-interval-functions","children":[]},{"level":3,"title":"Equal Size Bucket Sample Function","slug":"equal-size-bucket-sample-function","link":"#equal-size-bucket-sample-function","children":[]},{"level":3,"title":"M4 Function","slug":"m4-function","link":"#m4-function","children":[]},{"level":3,"title":"User Defined Timeseries Generating Functions","slug":"user-defined-timeseries-generating-functions","link":"#user-defined-timeseries-generating-functions","children":[]}]},{"level":2,"title":"Nested Expressions","slug":"nested-expressions","link":"#nested-expressions","children":[{"level":3,"title":"Nested Expressions with Time Series Query","slug":"nested-expressions-with-time-series-query","link":"#nested-expressions-with-time-series-query","children":[]},{"level":3,"title":"Nested Expressions query with aggregations","slug":"nested-expressions-query-with-aggregations","link":"#nested-expressions-query-with-aggregations","children":[]}]},{"level":2,"title":"Use Alias","slug":"use-alias","link":"#use-alias","children":[]}],"git":{"createdTime":1688958677000,"updatedTime":1688958677000,"contributors":[{"name":"CritasWang","email":"critas@outlook.com","commits":1}]},"readingTime":{"minutes":26.45,"words":7934},"filePathRelative":"UserGuide/V0.13.x/Query-Data/Select-Expression.md","localizedDate":"July 10, 2023","autoDesc":true}');export{X as comp,Y as data};