assets/Operator-and-Expression.html-Dw0gc7jr.js - iotdb-website - Git at Google

 import{_ as r,r as i,o as l,c,b as e,d as t,a as n,w as s,e as o}from"./app-Bx8hKGcu.js";const p={},u=e("h1",{id:"overview",tabindex:"-1"},[e("a",{class:"header-anchor",href:"#overview"},[e("span",null,"Overview")])],-1),h=e("p",null,[t("A list of all available functions, both built-in and custom, can be displayed with "),e("code",null,"SHOW FUNCTIONS"),t(" command.")],-1),m=o(`<h2 id="operators" tabindex="-1"><a class="header-anchor" href="#operators"><span>OPERATORS</span></a></h2><h3 id="arithmetic-operators" tabindex="-1"><a class="header-anchor" href="#arithmetic-operators"><span>Arithmetic Operators</span></a></h3><table><thead><tr><th>Operator</th><th>Meaning</th></tr></thead><tbody><tr><td><code>+</code></td><td>positive (unary operator)</td></tr><tr><td><code>-</code></td><td>negative (unary operator)</td></tr><tr><td><code>*</code></td><td>multiplication</td></tr><tr><td><code>/</code></td><td>division</td></tr><tr><td><code>%</code></td><td>modulo</td></tr><tr><td><code>+</code></td><td>addition</td></tr><tr><td><code>-</code></td><td>subtraction</td></tr></tbody></table><h3 id="comparison-operators" tabindex="-1"><a class="header-anchor" href="#comparison-operators"><span>Comparison Operators</span></a></h3><table><thead><tr><th>Operator</th><th>Meaning</th></tr></thead><tbody><tr><td><code>&gt;</code></td><td>greater than</td></tr><tr><td><code>&gt;=</code></td><td>greater than or equal to</td></tr><tr><td><code>&lt;</code></td><td>less than</td></tr><tr><td><code>&lt;=</code></td><td>less than or equal to</td></tr><tr><td><code>==</code></td><td>equal to</td></tr><tr><td><code>!=</code> / <code>&lt;&gt;</code></td><td>not equal to</td></tr><tr><td><code>BETWEEN ... AND ...</code></td><td>within the specified range</td></tr><tr><td><code>NOT BETWEEN ... AND ...</code></td><td>not within the specified range</td></tr><tr><td><code>LIKE</code></td><td>match simple pattern</td></tr><tr><td><code>NOT LIKE</code></td><td>cannot match simple pattern</td></tr><tr><td><code>REGEXP</code></td><td>match regular expression</td></tr><tr><td><code>NOT REGEXP</code></td><td>cannot match regular expression</td></tr><tr><td><code>IS NULL</code></td><td>is null</td></tr><tr><td><code>IS NOT NULL</code></td><td>is not null</td></tr><tr><td><code>IN</code> / <code>CONTAINS</code></td><td>is a value in the specified list</td></tr><tr><td><code>NOT IN</code> / <code>NOT CONTAINS</code></td><td>is not a value in the specified list</td></tr></tbody></table><h3 id="logical-operators" tabindex="-1"><a class="header-anchor" href="#logical-operators"><span>Logical Operators</span></a></h3><table><thead><tr><th>Operator</th><th>Meaning</th></tr></thead><tbody><tr><td><code>NOT</code> / <code>!</code></td><td>logical negation (unary operator)</td></tr><tr><td><code>AND</code> / <code>&amp;</code> / <code>&amp;&amp;</code></td><td>logical AND</td></tr><tr><td><code>OR</code>/ | / ||</td><td>logical OR</td></tr></tbody></table><h3 id="operator-precedence" tabindex="-1"><a class="header-anchor" href="#operator-precedence"><span>Operator Precedence</span></a></h3><p>The precedence of operators is arranged as shown below from high to low, and operators on the same row have the same precedence.</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 punctuation">,</span> <span class="token operator">-</span> <span class="token punctuation">(</span>unary operator<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token operator">+</span> <span class="token punctuation">(</span>unary operator<span class="token punctuation">)</span>
 <span class="token operator">*</span><span class="token punctuation">,</span> <span class="token operator">/</span><span class="token punctuation">,</span> <span class="token operator">DIV</span><span class="token punctuation">,</span> <span class="token operator">%</span><span class="token punctuation">,</span> MOD
 <span class="token operator">-</span><span class="token punctuation">,</span> <span class="token operator">+</span>
 <span class="token operator">=</span><span class="token punctuation">,</span> <span class="token operator">=</span><span class="token operator">=</span><span class="token punctuation">,</span> <span class="token operator">&lt;=&gt;</span><span class="token punctuation">,</span> <span class="token operator">&gt;=</span><span class="token punctuation">,</span> <span class="token operator">&gt;</span><span class="token punctuation">,</span> <span class="token operator">&lt;=</span><span class="token punctuation">,</span> <span class="token operator">&lt;</span><span class="token punctuation">,</span> <span class="token operator">&lt;&gt;</span><span class="token punctuation">,</span> <span class="token operator">!=</span>
 <span class="token operator">LIKE</span><span class="token punctuation">,</span> <span class="token operator">REGEXP</span><span class="token punctuation">,</span> <span class="token operator">NOT</span> <span class="token operator">LIKE</span><span class="token punctuation">,</span> <span class="token operator">NOT</span> <span class="token operator">REGEXP</span>
 <span class="token operator">BETWEEN</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token operator">AND</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">NOT</span> <span class="token operator">BETWEEN</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token operator">AND</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
 <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
 <span class="token operator">IN</span><span class="token punctuation">,</span> <span class="token keyword">CONTAINS</span><span class="token punctuation">,</span> <span class="token operator">NOT</span> <span class="token operator">IN</span><span class="token punctuation">,</span> <span class="token operator">NOT</span> <span class="token keyword">CONTAINS</span>
 <span class="token operator">AND</span><span class="token punctuation">,</span> <span class="token operator">&amp;</span><span class="token punctuation">,</span> <span class="token operator">&amp;&amp;</span>
 <span class="token operator">OR</span><span class="token punctuation">,</span> <span class="token operator">|</span><span class="token punctuation">,</span> <span class="token operator">||</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></div><h2 id="built-in-functions" tabindex="-1"><a class="header-anchor" href="#built-in-functions"><span>BUILT-IN FUNCTIONS</span></a></h2><p>The built-in functions can be used in IoTDB without registration, and the functions in the data quality function library need to be registered by referring to the registration steps in the next chapter before they can be used.</p><h3 id="aggregate-functions" tabindex="-1"><a class="header-anchor" href="#aggregate-functions"><span>Aggregate Functions</span></a></h3><table><thead><tr><th>Function Name</th><th>Description</th><th>Allowed Input Series Data Types</th><th>Required Attributes</th><th>Output Series Data Type</th></tr></thead><tbody><tr><td>SUM</td><td>Summation.</td><td>INT32 INT64 FLOAT DOUBLE</td><td>/</td><td>DOUBLE</td></tr><tr><td>COUNT</td><td>Counts the number of data points.</td><td>All types</td><td>/</td><td>INT</td></tr><tr><td>AVG</td><td>Average.</td><td>INT32 INT64 FLOAT DOUBLE</td><td>/</td><td>DOUBLE</td></tr><tr><td>EXTREME</td><td>Finds the value with the largest absolute value. Returns a positive value if the maximum absolute value of positive and negative values is equal.</td><td>INT32 INT64 FLOAT DOUBLE</td><td>/</td><td>Consistent with the input data type</td></tr><tr><td>MAX_VALUE</td><td>Find the maximum value.</td><td>INT32 INT64 FLOAT DOUBLE</td><td>/</td><td>Consistent with the input data type</td></tr><tr><td>MIN_VALUE</td><td>Find the minimum value.</td><td>INT32 INT64 FLOAT DOUBLE</td><td>/</td><td>Consistent with the input data type</td></tr><tr><td>FIRST_VALUE</td><td>Find the value with the smallest timestamp.</td><td>All data types</td><td>/</td><td>Consistent with input data type</td></tr><tr><td>LAST_VALUE</td><td>Find the value with the largest timestamp.</td><td>All data types</td><td>/</td><td>Consistent with input data type</td></tr><tr><td>MAX_TIME</td><td>Find the maximum timestamp.</td><td>All data Types</td><td>/</td><td>Timestamp</td></tr><tr><td>MIN_TIME</td><td>Find the minimum timestamp.</td><td>All data Types</td><td>/</td><td>Timestamp</td></tr><tr><td>COUNT_IF</td><td>Find the number of data points that continuously meet a given condition and the number of data points that meet the condition (represented by keep) meet the specified threshold.</td><td>BOOLEAN</td><td><code>[keep &gt;=/&gt;/=/!=/&lt;/&lt;=]threshold</code>：The specified threshold or threshold condition, it is equivalent to <code>keep &gt;= threshold</code> if <code>threshold</code> is used alone, type of <code>threshold</code> is <code>INT64</code> <code>ignoreNull</code>：Optional, default value is <code>true</code>；If the value is <code>true</code>, null values are ignored, it means that if there is a null value in the middle, the value is ignored without interrupting the continuity. If the value is <code>true</code>, null values are not ignored, it means that if there are null values in the middle, continuity will be broken</td><td>INT64</td></tr><tr><td>TIME_DURATION</td><td>Find the difference between the timestamp of the largest non-null value and the timestamp of the smallest non-null value in a column</td><td>All data Types</td><td>/</td><td>INT64</td></tr><tr><td>MODE</td><td>Find the mode. Note: 1.Having too many different values in the input series risks a memory exception; 2.If all the elements have the same number of occurrences, that is no Mode, return the value with earliest time; 3.If there are many Modes, return the Mode with earliest time.</td><td>All data Types</td><td>/</td><td>Consistent with the input data type</td></tr></tbody></table><h3 id="arithmetic-functions" tabindex="-1"><a class="header-anchor" href="#arithmetic-functions"><span>Arithmetic Functions</span></a></h3><table><thead><tr><th>Function Name</th><th>Allowed Input Series Data Types</th><th>Output Series Data Type</th><th>Required Attributes</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>/</td><td>Math#sin(double)</td></tr><tr><td>COS</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#cos(double)</td></tr><tr><td>TAN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#tan(double)</td></tr><tr><td>ASIN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#asin(double)</td></tr><tr><td>ACOS</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#acos(double)</td></tr><tr><td>ATAN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#atan(double)</td></tr><tr><td>SINH</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#sinh(double)</td></tr><tr><td>COSH</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#cosh(double)</td></tr><tr><td>TANH</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#tanh(double)</td></tr><tr><td>DEGREES</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#toDegrees(double)</td></tr><tr><td>RADIANS</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</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>/</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>/</td><td>Math#signum(double)</td></tr><tr><td>CEIL</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#ceil(double)</td></tr><tr><td>FLOOR</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#floor(double)</td></tr><tr><td>ROUND</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>&#39;places&#39; : Round the significant number, positive number is the significant number after the decimal point, negative number is the significant number of whole number</td><td>Math#rint(Math#pow(10,places))/Math#pow(10,places)</td></tr><tr><td>EXP</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#exp(double)</td></tr><tr><td>LN</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#log(double)</td></tr><tr><td>LOG10</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#log10(double)</td></tr><tr><td>SQRT</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>DOUBLE</td><td>/</td><td>Math#sqrt(double)</td></tr></tbody></table><h3 id="comparison-functions" tabindex="-1"><a class="header-anchor" href="#comparison-functions"><span>Comparison Functions</span></a></h3><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>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 <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><h3 id="string-processing-functions" tabindex="-1"><a class="header-anchor" href="#string-processing-functions"><span>String Processing Functions</span></a></h3><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>: string to search for</td><td>BOOLEAN</td><td>Checks whether the substring <code>s</code> exists in the string.</td></tr><tr><td>STRING_MATCHES</td><td>TEXT</td><td><code>regex</code>: Java standard library-style regular expressions.</td><td>BOOLEAN</td><td>Judges whether a string can be matched by the regular expression <code>regex</code>.</td></tr><tr><td>LENGTH</td><td>TEXT</td><td>/</td><td>INT32</td><td>Get the length of input series.</td></tr><tr><td>LOCATE</td><td>TEXT</td><td><code>target</code>: The substring to be located.<br> <code>reverse</code>: Indicates whether reverse locate is required. The default value is <code>false</code>, means left-to-right locate.</td><td>INT32</td><td>Get the position of the first occurrence of substring <code>target</code> in input series. Returns -1 if there are no <code>target</code> in input.</td></tr><tr><td>STARTSWITH</td><td>TEXT</td><td><code>target</code>: The prefix to be checked.</td><td>BOOLEAN</td><td>Check whether input series starts with the specified prefix <code>target</code>.</td></tr><tr><td>ENDSWITH</td><td>TEXT</td><td><code>target</code>: The suffix to be checked.</td><td>BOOLEAN</td><td>Check whether input series ends with the specified suffix <code>target</code>.</td></tr><tr><td>CONCAT</td><td>TEXT</td><td><code>targets</code>: a series of K-V, key needs to start with <code>target</code> and be not duplicated, value is the string you want to concat.<br><code>series_behind</code>: Indicates whether series behind targets. The default value is <code>false</code>.</td><td>TEXT</td><td>Concatenate input string and <code>target</code> string.</td></tr><tr><td>SUBSTRING</td><td>TEXT</td><td><code>from</code>: Indicates the start position of substring.<br><code>for</code>: Indicates how many characters to stop after of substring.</td><td>TEXT</td><td>Extracts a substring of a string, starting with the first specified character and stopping after the specified number of characters.The index start at 1.</td></tr><tr><td>REPLACE</td><td>TEXT</td><td>first parameter: The target substring to be replaced.<br>second parameter: The substring to replace with.</td><td>TEXT</td><td>Replace a substring in the input sequence with the target substring.</td></tr><tr><td>UPPER</td><td>TEXT</td><td>/</td><td>TEXT</td><td>Get the string of input series with all characters changed to uppercase.</td></tr><tr><td>LOWER</td><td>TEXT</td><td>/</td><td>TEXT</td><td>Get the string of input series with all characters changed to lowercase.</td></tr><tr><td>TRIM</td><td>TEXT</td><td>/</td><td>TEXT</td><td>Get the string whose value is same to input series, with all leading and trailing space removed.</td></tr><tr><td>STRCMP</td><td>TEXT</td><td>/</td><td>TEXT</td><td>Get the compare result of two input series. Returns <code>0</code> if series value are the same, a <code>negative integer</code> if value of series1 is smaller than series2, <br>a <code>positive integer</code> if value of series1 is more than series2.</td></tr></tbody></table><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><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>CAST</td><td><code>type</code>: Output data type, INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT</td><td>determined by <code>type</code></td><td>Convert the data to the type specified by the <code>type</code> parameter.</td></tr></tbody></table><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><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 <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><h3 id="selector-functions" tabindex="-1"><a class="header-anchor" href="#selector-functions"><span>Selector Functions</span></a></h3><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><h3 id="continuous-interval-functions" tabindex="-1"><a class="header-anchor" href="#continuous-interval-functions"><span>Continuous Interval Functions</span></a></h3><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>ZERO_DURATION</td><td>INT32/ INT64/ FLOAT/ DOUBLE/ BOOLEAN</td><td><code>min</code>:Optional with default value <code>0L</code> <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> <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> <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> <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><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><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>TIME_DIFFERENCE</td><td>INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT</td><td>/</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>/</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>/</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>/</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>/</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><tr><td>DIFF</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td><code>ignoreNull</code>：optional，default is true. If is true, the previous data point is ignored when it is null and continues to find the first non-null value forwardly. If the value is false, previous data point is not ignored when it is null, the result is also null because null is used for subtraction</td><td>DOUBLE</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, so output is null</td></tr></tbody></table><h3 id="sample-functions" tabindex="-1"><a class="header-anchor" href="#sample-functions"><span>Sample Functions</span></a></h3><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>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><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><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><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><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><h3 id="change-points-function" tabindex="-1"><a class="header-anchor" href="#change-points-function"><span>Change Points Function</span></a></h3><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>CHANGE_POINTS</td><td>INT32 / INT64 / FLOAT / DOUBLE</td><td>/</td><td>Same type as the input series</td><td>Remove consecutive identical values from an input sequence.</td></tr></tbody></table><h2 id="data-quality-function-library" tabindex="-1"><a class="header-anchor" href="#data-quality-function-library"><span>DATA QUALITY FUNCTION LIBRARY</span></a></h2><h3 id="about" tabindex="-1"><a class="header-anchor" href="#about"><span>About</span></a></h3><p>For applications based on time series data, data quality is vital. <strong>UDF Library</strong> is IoTDB User Defined Functions (UDF) about data quality, including data profiling, data quality evalution and data repairing. It effectively meets the demand for data quality in the industrial field.</p><h3 id="quick-start" tabindex="-1"><a class="header-anchor" href="#quick-start"><span>Quick Start</span></a></h3><p>The functions in this function library are not built-in functions, and must be loaded into the system before use.</p>`,39),v={href:"https://archive.apache.org/dist/iotdb/1.0.1/apache-iotdb-1.0.1-library-udf-bin.zip",target:"_blank",rel:"noopener noreferrer"},b=e("li",null,[t("Copy the JAR package to "),e("code",null,"ext\\udf"),t(" under the directory of IoTDB system (Please put JAR to this directory of all DataNodes if you use Cluster).")],-1),g=e("li",null,[t("Run "),e("code",null,"sbin\\start-server.bat"),t(" (for Windows) or "),e("code",null,"sbin\\start-server.sh"),t(" (for Linux or MacOS) to start IoTDB server.")],-1),f=e("li",null,[t("Copy the script to the directory of IoTDB system (under the root directory, at the same level as "),e("code",null,"sbin"),t("), modify the parameters in the script if needed and run it to register UDF.")],-1),T=e("h3",{id:"implemented-functions",tabindex:"-1"},[e("a",{class:"header-anchor",href:"#implemented-functions"},[e("span",null,"Implemented Functions")])],-1),k=e("code",null,"Completeness",-1),O=e("code",null,"ACF",-1),E=e("code",null,"IQR",-1),N=e("code",null,"Conv",-1),I=e("code",null,"DTW",-1),y=e("code",null,"TimestampRepair",-1),x=e("code",null,"ConsecutiveSequences",-1),L=e("code",null,"AR",-1),D=o(`<h2 id="lambda-expression" tabindex="-1"><a class="header-anchor" href="#lambda-expression"><span>LAMBDA EXPRESSION</span></a></h2><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>JEXL</td><td>INT32 / INT64 / FLOAT / DOUBLE / TEXT / BOOLEAN</td><td><code>expr</code> is a lambda expression that supports standard one or multi arguments in the form <code>x -&gt; {...}</code> or <code>(x, y, z) -&gt; {...}</code>, e.g. <code>x -&gt; {x * 2}</code>, <code>(x, y, z) -&gt; {x + y * z}</code></td><td>INT32 / INT64 / FLOAT / DOUBLE / TEXT / BOOLEAN</td><td>Returns the input time series transformed by a lambda expression</td></tr></tbody></table><h2 id="conditional-expression" tabindex="-1"><a class="header-anchor" href="#conditional-expression"><span>CONDITIONAL EXPRESSION</span></a></h2><table><thead><tr><th>Expression Name</th><th>Description</th></tr></thead><tbody><tr><td><code>CASE</code></td><td>similar to &quot;if else&quot;</td></tr></tbody></table><h2 id="select-expression" tabindex="-1"><a class="header-anchor" href="#select-expression"><span>SELECT EXPRESSION</span></a></h2><p>The <code>SELECT</code> clause specifies the output of the query, consisting of several <code>selectExpr</code>. Each <code>selectExpr</code> defines one or more columns in the query result.</p><p>**<code>selectExpr</code> is an expression consisting of time series path suffixes, constants, functions, and operators. That is, <code>selectExpr</code> can contain: **</p><ul><li>Time series path suffix (wildcards are supported)</li><li>operator <ul><li>Arithmetic operators</li><li>comparison operators</li><li>Logical Operators</li></ul></li><li>function <ul><li>aggregate functions</li><li>Time series generation functions (including built-in functions and user-defined functions)</li></ul></li><li>constant</li></ul><h3 id="use-alias" tabindex="-1"><a class="header-anchor" href="#use-alias"><span>Use Alias</span></a></h3><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><h3 id="operator" tabindex="-1"><a class="header-anchor" href="#operator"><span>Operator</span></a></h3><p>See chapter 1 of this documentation for a list of operators supported in IoTDB.</p><h3 id="function" tabindex="-1"><a class="header-anchor" href="#function"><span>Function</span></a></h3><h4 id="aggregate-functions-1" tabindex="-1"><a class="header-anchor" href="#aggregate-functions-1"><span>Aggregate Functions</span></a></h4><p>Aggregate functions are many-to-one functions. They perform aggregate calculations on a set of values, resulting in a single aggregated result.</p><p><strong>A query that contains an aggregate function is called an aggregate query</strong>, otherwise, it is called a time series query.</p><blockquote><p>Please note that mixed use of <code>Aggregate Query</code> and <code>Timeseries Query</code> is not allowed. Below are examples for queries that are not allowed.</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>select a, count(a) from root.sg
 select sin(a), count(a) from root.sg
 select a, count(a) from root.sg group by ([10,100),10ms)
 </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><p>For the aggregation functions supported by IoTDB, see chapter 2.1 of this documentation.</p><h4 id="time-series-generation-function" tabindex="-1"><a class="header-anchor" href="#time-series-generation-function"><span>Time Series Generation Function</span></a></h4><p>A time series generation function takes several raw time series as input and produces a list of time series as output. Unlike aggregate functions, time series generators have a timestamp column in their result sets.</p><p>All time series generation functions accept * as input, and all can be mixed with raw time series queries.</p><h5 id="built-in-time-series-generation-functions" tabindex="-1"><a class="header-anchor" href="#built-in-time-series-generation-functions"><span>Built-in Time Series Generation Functions</span></a></h5><p>See chapter 2 of this documentation for a list of built-in functions supported in IoTDB.</p><h5 id="user-defined-time-series-generation-functions" tabindex="-1"><a class="header-anchor" href="#user-defined-time-series-generation-functions"><span>User-Defined Time Series Generation Functions</span></a></h5>`,28),w=o(`<h3 id="nested-expressions" tabindex="-1"><a class="header-anchor" href="#nested-expressions"><span>Nested Expressions</span></a></h3><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><h4 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></h4><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><h5 id="example" tabindex="-1"><a class="header-anchor" href="#example"><span>Example</span></a></h5><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><h5 id="explanation" tabindex="-1"><a class="header-anchor" href="#explanation"><span>Explanation</span></a></h5><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><h5 id="note" tabindex="-1"><a class="header-anchor" href="#note"><span>Note</span></a></h5><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><h4 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></h4><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><h5 id="example-1" tabindex="-1"><a class="header-anchor" href="#example-1"><span>Example</span></a></h5><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><h5 id="explanation-1" tabindex="-1"><a class="header-anchor" href="#explanation-1"><span>Explanation</span></a></h5><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>`,46);function A(U,F){const a=i("RouteLink"),d=i("ExternalLinkIcon");return l(),c("div",null,[u,e("p",null,[t("This chapter describes the operators and functions supported by IoTDB. IoTDB provides a wealth of built-in operators and functions to meet your computing needs, and supports extensions through the "),n(a,{to:"/UserGuide/V1.2.x/User-Manual/Database-Programming.html#USER-DEFINEDFUNCTION",title:"UDF"},{default:s(()=>[t("User-Defined Function")]),_:1}),t(".")]),h,e("p",null,[t("See the documentation "),n(a,{to:"/UserGuide/V1.2.x/User-Manual/Query-Data.html"},{default:s(()=>[t("Select-Expression")]),_:1}),t(" for the behavior of operators and functions in SQL.")]),m,e("ol",null,[e("li",null,[e("a",v,[t("Download"),n(d)]),t(" the JAR with all dependencies and the script of registering UDF.")]),b,g,f]),T,e("ol",null,[e("li",null,[t("Data Quality related functions, such as "),k,t(". For details and examples, see the document "),n(a,{to:"/UserGuide/V1.2.x/Reference/UDF-Libraries.html#Data-Quality"},{default:s(()=>[t("Data-Quality")]),_:1}),t(".")]),e("li",null,[t("Data Profiling related functions, such as "),O,t(". For details and examples, see the document "),n(a,{to:"/UserGuide/V1.2.x/Reference/UDF-Libraries.html#Data-Profiling"},{default:s(()=>[t("Data-Profiling")]),_:1}),t(".")]),e("li",null,[t("Anomaly Detection related functions, such as "),E,t(". For details and examples, see the document "),n(a,{to:"/UserGuide/V1.2.x/Reference/UDF-Libraries.html#Anomaly-Detection"},{default:s(()=>[t("Anomaly-Detection")]),_:1}),t(".")]),e("li",null,[t("Frequency Domain Analysis related functions, such as "),N,t(". For details and examples, see the document "),n(a,{to:"/UserGuide/V1.2.x/Reference/UDF-Libraries.html#Frequency-Domain"},{default:s(()=>[t("Frequency-Domain")]),_:1}),t(".")]),e("li",null,[t("Data Matching related functions, such as "),I,t(". For details and examples, see the document "),n(a,{to:"/UserGuide/V1.2.x/Reference/UDF-Libraries.html#Data-Matching"},{default:s(()=>[t("Data-Matching")]),_:1}),t(".")]),e("li",null,[t("Data Repairing related functions, such as "),y,t(". For details and examples, see the document "),n(a,{to:"/UserGuide/V1.2.x/Reference/UDF-Libraries.html#Data-Repairing"},{default:s(()=>[t("Data-Repairing")]),_:1}),t(".")]),e("li",null,[t("Series Discovery related functions, such as "),x,t(". For details and examples, see the document "),n(a,{to:"/UserGuide/V1.2.x/Reference/UDF-Libraries.html#Series-Discovery"},{default:s(()=>[t("Series-Discovery")]),_:1}),t(".")]),e("li",null,[t("Machine Learning related functions, such as "),L,t(". For details and examples, see the document "),n(a,{to:"/UserGuide/V1.2.x/Reference/UDF-Libraries.html#Machine-Learning"},{default:s(()=>[t("Machine-Learning")]),_:1}),t(".")])]),D,e("p",null,[t("IoTDB supports function extension through User Defined Function (click for "),n(a,{to:"/UserGuide/V1.2.x/User-Manual/Database-Programming.html"},{default:s(()=>[t("User-Defined Function")]),_:1}),t(") capability.")]),w])}const S=r(p,[["render",A],["__file","Operator-and-Expression.html.vue"]]),R=JSON.parse('{"path":"/UserGuide/V1.2.x/User-Manual/Operator-and-Expression.html","title":"Overview","lang":"en-US","frontmatter":{"description":"Overview This chapter describes the operators and functions supported by IoTDB. IoTDB provides a wealth of built-in operators and functions to meet your computing needs, and sup...","head":[["link",{"rel":"alternate","hreflang":"zh-cn","href":"https://iotdb.apache.org/zh/UserGuide/V1.2.x/User-Manual/Operator-and-Expression.html"}],["meta",{"property":"og:url","content":"https://iotdb.apache.org/UserGuide/V1.2.x/User-Manual/Operator-and-Expression.html"}],["meta",{"property":"og:site_name","content":"IoTDB Website"}],["meta",{"property":"og:title","content":"Overview"}],["meta",{"property":"og:description","content":"Overview This chapter describes the operators and functions supported by IoTDB. IoTDB provides a wealth of built-in operators and functions to meet your computing needs, and sup..."}],["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-09-19T12:06:04.000Z"}],["meta",{"property":"article:modified_time","content":"2023-09-19T12:06:04.000Z"}],["script",{"type":"application/ld+json"},"{\\"@context\\":\\"https://schema.org\\",\\"@type\\":\\"Article\\",\\"headline\\":\\"Overview\\",\\"image\\":[\\"\\"],\\"dateModified\\":\\"2023-09-19T12:06:04.000Z\\",\\"author\\":[]}"]]},"headers":[{"level":2,"title":"OPERATORS","slug":"operators","link":"#operators","children":[{"level":3,"title":"Arithmetic Operators","slug":"arithmetic-operators","link":"#arithmetic-operators","children":[]},{"level":3,"title":"Comparison Operators","slug":"comparison-operators","link":"#comparison-operators","children":[]},{"level":3,"title":"Logical Operators","slug":"logical-operators","link":"#logical-operators","children":[]},{"level":3,"title":"Operator Precedence","slug":"operator-precedence","link":"#operator-precedence","children":[]}]},{"level":2,"title":"BUILT-IN FUNCTIONS","slug":"built-in-functions","link":"#built-in-functions","children":[{"level":3,"title":"Aggregate Functions","slug":"aggregate-functions","link":"#aggregate-functions","children":[]},{"level":3,"title":"Arithmetic Functions","slug":"arithmetic-functions","link":"#arithmetic-functions","children":[]},{"level":3,"title":"Comparison Functions","slug":"comparison-functions","link":"#comparison-functions","children":[]},{"level":3,"title":"String Processing Functions","slug":"string-processing-functions","link":"#string-processing-functions","children":[]},{"level":3,"title":"Data Type Conversion Function","slug":"data-type-conversion-function","link":"#data-type-conversion-function","children":[]},{"level":3,"title":"Constant Timeseries Generating Functions","slug":"constant-timeseries-generating-functions","link":"#constant-timeseries-generating-functions","children":[]},{"level":3,"title":"Selector Functions","slug":"selector-functions","link":"#selector-functions","children":[]},{"level":3,"title":"Continuous Interval Functions","slug":"continuous-interval-functions","link":"#continuous-interval-functions","children":[]},{"level":3,"title":"Variation Trend Calculation Functions","slug":"variation-trend-calculation-functions","link":"#variation-trend-calculation-functions","children":[]},{"level":3,"title":"Sample Functions","slug":"sample-functions","link":"#sample-functions","children":[]},{"level":3,"title":"Change Points Function","slug":"change-points-function","link":"#change-points-function","children":[]}]},{"level":2,"title":"DATA QUALITY FUNCTION LIBRARY","slug":"data-quality-function-library","link":"#data-quality-function-library","children":[{"level":3,"title":"About","slug":"about","link":"#about","children":[]},{"level":3,"title":"Quick Start","slug":"quick-start","link":"#quick-start","children":[]},{"level":3,"title":"Implemented Functions","slug":"implemented-functions","link":"#implemented-functions","children":[]}]},{"level":2,"title":"LAMBDA EXPRESSION","slug":"lambda-expression","link":"#lambda-expression","children":[]},{"level":2,"title":"CONDITIONAL EXPRESSION","slug":"conditional-expression","link":"#conditional-expression","children":[]},{"level":2,"title":"SELECT EXPRESSION","slug":"select-expression","link":"#select-expression","children":[{"level":3,"title":"Use Alias","slug":"use-alias","link":"#use-alias","children":[]},{"level":3,"title":"Operator","slug":"operator","link":"#operator","children":[]},{"level":3,"title":"Function","slug":"function","link":"#function","children":[]},{"level":3,"title":"Nested Expressions","slug":"nested-expressions","link":"#nested-expressions","children":[]}]}],"git":{"createdTime":1690869728000,"updatedTime":1695125164000,"contributors":[{"name":"wanghui42","email":"105700158+wanghui42@users.noreply.github.com","commits":2},{"name":"Lei","email":"33376433+LeiRui@users.noreply.github.com","commits":1}]},"readingTime":{"minutes":16.24,"words":4873},"filePathRelative":"UserGuide/V1.2.x/User-Manual/Operator-and-Expression.md","localizedDate":"August 1, 2023","autoDesc":true}');export{S as comp,R as data};