| import{_ as s,O as n,P as a,ah as e,aW as t}from"./framework-62ad666a.js";const p={},o=t(`<h2 id="conditional-expressions" tabindex="-1"><a class="header-anchor" href="#conditional-expressions" aria-hidden="true">#</a> Conditional Expressions</h2><h3 id="case" tabindex="-1"><a class="header-anchor" href="#case" aria-hidden="true">#</a> CASE</h3><h4 id="introduction" tabindex="-1"><a class="header-anchor" href="#introduction" aria-hidden="true">#</a> Introduction</h4><p>The CASE expression is a kind of conditional expression that can be used to return different values based on specific conditions, similar to the if-else statements in other languages.</p><p>The CASE expression consists of the following parts:</p><ul><li>CASE keyword: Indicates the start of the CASE expression.</li><li>WHEN-THEN clauses: There may be multiple clauses used to define conditions and give results. This clause is divided into two parts, WHEN and THEN. The WHEN part defines the condition, and the THEN part defines the result expression. If the WHEN condition is true, the corresponding THEN result is returned.</li><li>ELSE clause: If none of the WHEN conditions is true, the result in the ELSE clause will be returned. The ELSE clause can be omitted.</li><li>END keyword: Indicates the end of the CASE expression.</li></ul><p>The CASE expression is a scalar operation that can be used in combination with any other scalar operation or aggregate function.</p><p>In the following text, all THEN parts and ELSE clauses will be collectively referred to as result clauses.</p><h4 id="syntax" tabindex="-1"><a class="header-anchor" href="#syntax" aria-hidden="true">#</a> Syntax</h4><p>The CASE expression supports two formats.</p><ul><li><p>Format 1:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">CASE</span> |
| <span class="token keyword">WHEN</span> condition1 <span class="token keyword">THEN</span> expression1 |
| <span class="token punctuation">[</span><span class="token keyword">WHEN</span> condition2 <span class="token keyword">THEN</span> expression2<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 keyword">ELSE</span> expression_end<span class="token punctuation">]</span> |
| <span class="token keyword">END</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></div><p>The <code>condition</code>s will be evaluated one by one.</p><p>The first <code>condition</code> that is true will return the corresponding expression.</p></li><li><p>Format 2:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">CASE</span> caseValue |
| <span class="token keyword">WHEN</span> whenValue1 <span class="token keyword">THEN</span> expression1 |
| <span class="token punctuation">[</span><span class="token keyword">WHEN</span> whenValue2 <span class="token keyword">THEN</span> expression2<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 keyword">ELSE</span> expression_end<span class="token punctuation">]</span> |
| <span class="token keyword">END</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></div><p>The <code>caseValue</code> will be evaluated first, and then the <code>whenValue</code>s will be evaluated one by one. The first <code>whenValue</code> that is equal to the <code>caseValue</code> will return the corresponding <code>expression</code>.</p><p>Format 2 will be transformed into an equivalent Format 1 by iotdb.</p><p>For example, the above SQL statement will be transformed into:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">CASE</span> |
| <span class="token keyword">WHEN</span> caseValue<span class="token operator">=</span>whenValue1 <span class="token keyword">THEN</span> expression1 |
| <span class="token punctuation">[</span><span class="token keyword">WHEN</span> caseValue<span class="token operator">=</span>whenValue1 <span class="token keyword">THEN</span> expression1<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 keyword">ELSE</span> expression_end<span class="token punctuation">]</span> |
| <span class="token keyword">END</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></div></li></ul><p>If none of the conditions are true, or if none of the <code>whenValue</code>s match the <code>caseValue</code>, the <code>expression_end</code> will be returned.</p><p>If there is no ELSE clause, <code>null</code> will be returned.</p><h4 id="notes" tabindex="-1"><a class="header-anchor" href="#notes" aria-hidden="true">#</a> Notes</h4><ul><li>In format 1, all WHEN clauses must return a BOOLEAN type.</li><li>In format 2, all WHEN clauses must be able to be compared to the CASE clause.</li><li>All result clauses in a CASE expression must satisfy certain conditions for their return value types: <ul><li>BOOLEAN types cannot coexist with other types and will cause an error if present.</li><li>TEXT types cannot coexist with other types and will cause an error if present.</li><li>The other four numeric types can coexist, and the final result will be of DOUBLE type, with possible precision loss during conversion.</li><li>If necessary, you can use the CAST function to convert the result to a type that can coexist with others.</li></ul></li><li>The CASE expression does not implement lazy evaluation, meaning that all clauses will be evaluated.</li><li>The CASE expression does not support mixing with UDFs.</li><li>Aggregate functions cannot be used within a CASE expression, but the result of a CASE expression can be used as input for an aggregate function.</li><li>When using the CLI, because the CASE expression string can be lengthy, it is recommended to provide an alias for the expression using AS.</li></ul><h4 id="using-examples" tabindex="-1"><a class="header-anchor" href="#using-examples" aria-hidden="true">#</a> Using Examples</h4><h5 id="example-1" tabindex="-1"><a class="header-anchor" href="#example-1" aria-hidden="true">#</a> Example 1</h5><p>The CASE expression can be used to analyze data in a visual way. For example:</p><ul><li>The preparation of a certain chemical product requires that the temperature and pressure be within specific ranges.</li><li>During the preparation process, sensors will detect the temperature and pressure, forming two time-series T (temperature) and P (pressure) in IoTDB. In this application scenario, the CASE expression can indicate which time parameters are appropriate, which are not, and why they are not.</li></ul><p>data:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code>IoTDB<span class="token operator">></span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test1 |
| <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>test1<span class="token punctuation">.</span>P<span class="token operator">|</span>root<span class="token punctuation">.</span>test1<span class="token punctuation">.</span>T<span class="token operator">|</span> |
| <span class="token operator">+</span><span class="token comment">-----------------------------+------------+------------+</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">29</span>T11:<span class="token number">25</span>:<span class="token number">54.724</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">1000000.0</span><span class="token operator">|</span> <span class="token number">1025.0</span><span class="token operator">|</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">29</span>T11:<span class="token number">26</span>:<span class="token number">13.445</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">1000094.0</span><span class="token operator">|</span> <span class="token number">1040.0</span><span class="token operator">|</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">29</span>T11:<span class="token number">27</span>:<span class="token number">36.988</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">1000095.0</span><span class="token operator">|</span> <span class="token number">1041.0</span><span class="token operator">|</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">29</span>T11:<span class="token number">27</span>:<span class="token number">56.446</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">1000095.0</span><span class="token operator">|</span> <span class="token number">1059.0</span><span class="token operator">|</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">29</span>T11:<span class="token number">28</span>:<span class="token number">20.838</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">1200000.0</span><span class="token operator">|</span> <span class="token number">1040.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></div><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> T<span class="token punctuation">,</span> P<span class="token punctuation">,</span> <span class="token keyword">case</span> |
| <span class="token keyword">when</span> <span class="token number">1000</span><span class="token operator"><</span>T <span class="token operator">and</span> T<span class="token operator"><</span><span class="token number">1050</span> <span class="token operator">and</span> <span class="token number">1000000</span><span class="token operator"><</span>P <span class="token operator">and</span> P<span class="token operator"><</span><span class="token number">1100000</span> <span class="token keyword">then</span> <span class="token string">"good!"</span> |
| <span class="token keyword">when</span> T<span class="token operator"><=</span><span class="token number">1000</span> <span class="token operator">or</span> T<span class="token operator">>=</span><span class="token number">1050</span> <span class="token keyword">then</span> <span class="token string">"bad temperature"</span> |
| <span class="token keyword">when</span> P<span class="token operator"><=</span><span class="token number">1000000</span> <span class="token operator">or</span> P<span class="token operator">>=</span><span class="token number">1100000</span> <span class="token keyword">then</span> <span class="token string">"bad pressure"</span> |
| <span class="token keyword">end</span> <span class="token keyword">as</span> <span class="token identifier"><span class="token punctuation">\`</span>result<span class="token punctuation">\`</span></span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span>test1 |
| </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>output:</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>+-----------------------------+------------+------------+---------------+ |
| | Time|root.test1.T|root.test1.P| result| |
| +-----------------------------+------------+------------+---------------+ |
| |2023-03-29T11:25:54.724+08:00| 1025.0| 1000000.0| bad pressure| |
| |2023-03-29T11:26:13.445+08:00| 1040.0| 1000094.0| good!| |
| |2023-03-29T11:27:36.988+08:00| 1041.0| 1000095.0| good!| |
| |2023-03-29T11:27:56.446+08:00| 1059.0| 1000095.0|bad temperature| |
| |2023-03-29T11:28:20.838+08:00| 1040.0| 1200000.0| bad pressure| |
| +-----------------------------+------------+------------+---------------+ |
| </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><h5 id="example-2" tabindex="-1"><a class="header-anchor" href="#example-2" aria-hidden="true">#</a> Example 2</h5><p>The CASE expression can achieve flexible result transformation, such as converting strings with a certain pattern to other strings.</p><p>data:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code>IoTDB<span class="token operator">></span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test2 |
| <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>test2<span class="token punctuation">.</span>str<span class="token operator">|</span> |
| <span class="token operator">+</span><span class="token comment">-----------------------------+--------------+</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">23</span>:<span class="token number">33.427</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span> abccd<span class="token operator">|</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">23</span>:<span class="token number">39.389</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span> abcdd<span class="token operator">|</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">23</span>:<span class="token number">43.463</span><span class="token operator">+</span><span class="token number">08</span>:<span class="token number">00</span><span class="token operator">|</span> abcdefg<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></div><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> str<span class="token punctuation">,</span> <span class="token keyword">case</span> |
| <span class="token keyword">when</span> str <span class="token operator">like</span> <span class="token string">"%cc%"</span> <span class="token keyword">then</span> <span class="token string">"has cc"</span> |
| <span class="token keyword">when</span> str <span class="token operator">like</span> <span class="token string">"%dd%"</span> <span class="token keyword">then</span> <span class="token string">"has dd"</span> |
| <span class="token keyword">else</span> <span class="token string">"no cc and dd"</span> <span class="token keyword">end</span> <span class="token keyword">as</span> <span class="token identifier"><span class="token punctuation">\`</span>result<span class="token punctuation">\`</span></span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span>test2 |
| </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></div><p>output:</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>+-----------------------------+--------------+------------+ |
| | Time|root.test2.str| result| |
| +-----------------------------+--------------+------------+ |
| |2023-03-27T18:23:33.427+08:00| abccd| has cc| |
| |2023-03-27T18:23:39.389+08:00| abcdd| has dd| |
| |2023-03-27T18:23:43.463+08:00| abcdefg|no cc and dd| |
| +-----------------------------+--------------+------------+ |
| </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><h5 id="example-3-work-with-aggregation-functions" tabindex="-1"><a class="header-anchor" href="#example-3-work-with-aggregation-functions" aria-hidden="true">#</a> Example 3: work with aggregation functions</h5><h6 id="valid-aggregation-function-←-case-expression" tabindex="-1"><a class="header-anchor" href="#valid-aggregation-function-←-case-expression" aria-hidden="true">#</a> valid: aggregation function ← CASE expression</h6><p>The CASE expression can be used as a parameter for aggregate functions. For example, used in conjunction with the COUNT function, it can implement statistics based on multiple conditions simultaneously.</p><p>data:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code>IoTDB<span class="token operator">></span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test3 |
| <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>test3<span class="token punctuation">.</span>x<span class="token operator">|</span> |
| <span class="token operator">+</span><span class="token comment">-----------------------------+------------+</span> |
| <span class="token operator">|</span><span class="token number">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">11.300</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">14.658</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">15.981</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">17.668</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">19.112</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">20.822</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">22.462</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">24.174</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">25.858</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">2023</span><span class="token operator">-</span><span class="token number">03</span><span class="token operator">-</span><span class="token number">27</span>T18:<span class="token number">11</span>:<span class="token number">27.979</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 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></div><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> |
| <span class="token function">count</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> x<span class="token operator"><=</span><span class="token number">1</span> <span class="token keyword">then</span> <span class="token number">1</span> <span class="token keyword">end</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token identifier"><span class="token punctuation">\`</span>(-∞,1]<span class="token punctuation">\`</span></span><span class="token punctuation">,</span> |
| <span class="token function">count</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> <span class="token number">1</span><span class="token operator"><</span>x <span class="token operator">and</span> x<span class="token operator"><=</span><span class="token number">3</span> <span class="token keyword">then</span> <span class="token number">1</span> <span class="token keyword">end</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token identifier"><span class="token punctuation">\`</span>(1,3]<span class="token punctuation">\`</span></span><span class="token punctuation">,</span> |
| <span class="token function">count</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> <span class="token number">3</span><span class="token operator"><</span>x <span class="token operator">and</span> x<span class="token operator"><=</span><span class="token number">7</span> <span class="token keyword">then</span> <span class="token number">1</span> <span class="token keyword">end</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token identifier"><span class="token punctuation">\`</span>(3,7]<span class="token punctuation">\`</span></span><span class="token punctuation">,</span> |
| <span class="token function">count</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> <span class="token number">7</span><span class="token operator"><</span>x <span class="token keyword">then</span> <span class="token number">1</span> <span class="token keyword">end</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token identifier"><span class="token punctuation">\`</span>(7,+∞)<span class="token punctuation">\`</span></span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span>test3 |
| </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>output:</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>+------+-----+-----+------+ |
| |(-∞,1]|(1,3]|(3,7]|(7,+∞)| |
| +------+-----+-----+------+ |
| | 2| 2| 4| 2| |
| +------+-----+-----+------+ |
| </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></div><h6 id="invalid-case-expression-←-aggregation-function" tabindex="-1"><a class="header-anchor" href="#invalid-case-expression-←-aggregation-function" aria-hidden="true">#</a> invalid: CASE expression ← aggregation function</h6><p>Using aggregation function in CASE expression is not supported</p><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token keyword">case</span> <span class="token keyword">when</span> x<span class="token operator"><=</span><span class="token number">1</span> <span class="token keyword">then</span> <span class="token function">avg</span><span class="token punctuation">(</span>x<span class="token punctuation">)</span> <span class="token keyword">else</span> <span class="token function">sum</span><span class="token punctuation">(</span>x<span class="token punctuation">)</span> <span class="token keyword">end</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test3 |
| </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"><pre class="language-text"><code>Msg: 701: Raw data and aggregation result hybrid calculation is not supported. |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h5 id="example-4-kind-2" tabindex="-1"><a class="header-anchor" href="#example-4-kind-2" aria-hidden="true">#</a> Example 4: kind 2</h5><p>Here is a simple example that uses the format 2 syntax. If all conditions are equality tests, it is recommended to use format 2 to simplify SQL statements.</p><p>data:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code>IoTDB<span class="token operator">></span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test4 |
| <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>test4<span class="token punctuation">.</span>x<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">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 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></div><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> x<span class="token punctuation">,</span> <span class="token keyword">case</span> x <span class="token keyword">when</span> <span class="token number">1</span> <span class="token keyword">then</span> <span class="token string">"one"</span> <span class="token keyword">when</span> <span class="token number">2</span> <span class="token keyword">then</span> <span class="token string">"two"</span> <span class="token keyword">else</span> <span class="token string">"other"</span> <span class="token keyword">end</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test4 |
| </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"><pre class="language-text"><code>+-----------------------------+------------+-----------------------------------------------------------------------------------+ |
| | Time|root.test4.x|CASE WHEN root.test4.x = 1 THEN "one" WHEN root.test4.x = 2 THEN "two" ELSE "other"| |
| +-----------------------------+------------+-----------------------------------------------------------------------------------+ |
| |1970-01-01T08:00:00.001+08:00| 1.0| one| |
| |1970-01-01T08:00:00.002+08:00| 2.0| two| |
| |1970-01-01T08:00:00.003+08:00| 3.0| other| |
| |1970-01-01T08:00:00.004+08:00| 4.0| other| |
| +-----------------------------+------------+-----------------------------------------------------------------------------------+ |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="example-5-type-of-return-clauses" tabindex="-1"><a class="header-anchor" href="#example-5-type-of-return-clauses" aria-hidden="true">#</a> Example 5: type of return clauses</h5><p>The result clause of a CASE expression needs to satisfy certain type restrictions.</p><p>In this example, we continue to use the data from Example 4.</p><h6 id="invalid-boolean-cannot-coexist-with-other-types" tabindex="-1"><a class="header-anchor" href="#invalid-boolean-cannot-coexist-with-other-types" aria-hidden="true">#</a> Invalid: BOOLEAN cannot coexist with other types</h6><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> x<span class="token punctuation">,</span> <span class="token keyword">case</span> x <span class="token keyword">when</span> <span class="token number">1</span> <span class="token keyword">then</span> <span class="token boolean">true</span> <span class="token keyword">when</span> <span class="token number">2</span> <span class="token keyword">then</span> <span class="token number">2</span> <span class="token keyword">end</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test4 |
| </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"><pre class="language-text"><code>Msg: 701: CASE expression: BOOLEAN and other types cannot exist at same time |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h6 id="valid-only-boolean-type-exists" tabindex="-1"><a class="header-anchor" href="#valid-only-boolean-type-exists" aria-hidden="true">#</a> Valid: Only BOOLEAN type exists</h6><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> x<span class="token punctuation">,</span> <span class="token keyword">case</span> x <span class="token keyword">when</span> <span class="token number">1</span> <span class="token keyword">then</span> <span class="token boolean">true</span> <span class="token keyword">when</span> <span class="token number">2</span> <span class="token keyword">then</span> <span class="token boolean">false</span> <span class="token keyword">end</span> <span class="token keyword">as</span> <span class="token identifier"><span class="token punctuation">\`</span>result<span class="token punctuation">\`</span></span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test4 |
| </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"><pre class="language-text"><code>+-----------------------------+------------+------+ |
| | Time|root.test4.x|result| |
| +-----------------------------+------------+------+ |
| |1970-01-01T08:00:00.001+08:00| 1.0| true| |
| |1970-01-01T08:00:00.002+08:00| 2.0| false| |
| |1970-01-01T08:00:00.003+08:00| 3.0| null| |
| |1970-01-01T08:00:00.004+08:00| 4.0| null| |
| +-----------------------------+------------+------+ |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h6 id="invalid-text-cannot-coexist-with-other-types" tabindex="-1"><a class="header-anchor" href="#invalid-text-cannot-coexist-with-other-types" aria-hidden="true">#</a> Invalid:TEXT cannot coexist with other types</h6><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> x<span class="token punctuation">,</span> <span class="token keyword">case</span> x <span class="token keyword">when</span> <span class="token number">1</span> <span class="token keyword">then</span> <span class="token number">1</span> <span class="token keyword">when</span> <span class="token number">2</span> <span class="token keyword">then</span> <span class="token string">"str"</span> <span class="token keyword">end</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>test4 |
| </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"><pre class="language-text"><code>Msg: 701: CASE expression: TEXT and other types cannot exist at same time |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h6 id="valid-only-text-type-exists" tabindex="-1"><a class="header-anchor" href="#valid-only-text-type-exists" aria-hidden="true">#</a> Valid: Only TEXT type exists</h6><p>See in Example 1.</p><h6 id="valid-numerical-types-coexist" tabindex="-1"><a class="header-anchor" href="#valid-numerical-types-coexist" aria-hidden="true">#</a> Valid: Numerical types coexist</h6><p>SQL statements:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> x<span class="token punctuation">,</span> <span class="token keyword">case</span> x |
| <span class="token keyword">when</span> <span class="token number">1</span> <span class="token keyword">then</span> <span class="token number">1</span> |
| <span class="token keyword">when</span> <span class="token number">2</span> <span class="token keyword">then</span> <span class="token number">222222222222222</span> |
| <span class="token keyword">when</span> <span class="token number">3</span> <span class="token keyword">then</span> <span class="token number">3.3</span> |
| <span class="token keyword">when</span> <span class="token number">4</span> <span class="token keyword">then</span> <span class="token number">4.4444444444444</span> |
| <span class="token keyword">end</span> <span class="token keyword">as</span> <span class="token identifier"><span class="token punctuation">\`</span>result<span class="token punctuation">\`</span></span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span>test4 |
| </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>output:</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>+-----------------------------+------------+-------------------+ |
| | Time|root.test4.x| result| |
| +-----------------------------+------------+-------------------+ |
| |1970-01-01T08:00:00.001+08:00| 1.0| 1.0| |
| |1970-01-01T08:00:00.002+08:00| 2.0|2.22222222222222E14| |
| |1970-01-01T08:00:00.003+08:00| 3.0| 3.299999952316284| |
| |1970-01-01T08:00:00.004+08:00| 4.0| 4.44444465637207| |
| +-----------------------------+------------+-------------------+ |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div>`,81);function r(l,c){return n(),a("div",null,[e(` |
| |
| Licensed to the Apache Software Foundation (ASF) under one |
| or more contributor license agreements. See the NOTICE file |
| distributed with this work for additional information |
| regarding copyright ownership. The ASF licenses this file |
| to you under the Apache License, Version 2.0 (the |
| "License"); you may not use this file except in compliance |
| with the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, |
| software distributed under the License is distributed on an |
| "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| KIND, either express or implied. See the License for the |
| specific language governing permissions and limitations |
| under the License. |
| |
| `),o])}const d=s(p,[["render",r],["__file","Conditional.html.vue"]]);export{d as default}; |
