| import{_ as n,r as t,o,c as l,b as i,d as e,a as r,w as c,e as a}from"./app-Bx8hKGcu.js";const p={},d=a('<h1 id="query-filter" tabindex="-1"><a class="header-anchor" href="#query-filter"><span>Query Filter</span></a></h1><p>In IoTDB query statements, two filter conditions, <strong>time filter</strong> and <strong>value filter</strong>, are supported.</p><p>The supported operators are as follows:</p><ul><li>Comparison operators: greater than (<code>></code>), greater than or equal ( <code>>=</code>), equal ( <code>=</code> or <code>==</code>), not equal ( <code>!=</code> or <code><></code>), less than or equal ( <code><=</code>), less than ( <code><</code>).</li><li>Logical operators: and ( <code>AND</code> or <code>&</code> or <code>&&</code>), or ( <code>OR</code> or <code>|</code> or <code>||</code>), not ( <code>NOT</code> or <code>!</code>).</li><li>Range contains operator: contains ( <code>IN</code> ).</li><li>String matches operator: <code>LIKE</code>, <code>REGEXP</code>.</li></ul><h2 id="time-filter" tabindex="-1"><a class="header-anchor" href="#time-filter"><span>Time Filter</span></a></h2>',5),u=a(`<p>An example is as follows:</p><ol><li><p>Select data with timestamp greater than 2022-01-01T00:05:00.000:</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">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">></span> <span class="token number">2022</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T00:<span class="token number">05</span>:<span class="token number">00.000</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div></li><li><p>Select data with timestamp equal to 2022-01-01T00:05:00.000:</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">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">=</span> <span class="token number">2022</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T00:<span class="token number">05</span>:<span class="token number">00.000</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div></li><li><p>Select the data in the time interval [2017-11-01T00:05:00.000, 2017-11-01T00:12:00.000):</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">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> <span class="token keyword">time</span> <span class="token operator">>=</span> <span class="token number">2022</span><span class="token operator">-</span><span class="token number">01</span><span class="token operator">-</span><span class="token number">01</span>T00:<span class="token number">05</span>:<span class="token number">00.000</span> <span class="token operator">and</span> <span class="token keyword">time</span> <span class="token operator"><</span> <span class="token number">2017</span><span class="token operator">-</span><span class="token number">11</span><span class="token operator">-</span><span class="token number">01</span>T00:<span class="token number">12</span>:<span class="token number">00.000</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div></li></ol><p>Note: In the above example, <code>time</code> can also be written as <code>timestamp</code>.</p><h2 id="value-filter" tabindex="-1"><a class="header-anchor" href="#value-filter"><span>Value Filter</span></a></h2><p>Use value filters to filter data whose data values meet certain criteria. <strong>Allow</strong> to use a time series not selected in the select clause as a value filter.</p><p>An example is as follows:</p><ol><li><p>Select data with a value greater than 36.5:</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> temperature <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> temperature <span class="token operator">></span> <span class="token number">36.5</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div></li><li><p>Select data with value equal to true:</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 keyword">status</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> <span class="token keyword">status</span> <span class="token operator">=</span> <span class="token boolean">true</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div></li><li><p>Select data for the interval [36.5,40] or not:</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> temperature <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> temperature <span class="token operator">between</span> <span class="token number">36.5</span> <span class="token operator">and</span> <span class="token number">40</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><div class="language-sql line-numbers-mode" data-ext="sql" data-title="sql"><pre class="language-sql"><code><span class="token keyword">select</span> temperature <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> temperature <span class="token operator">not</span> <span class="token operator">between</span> <span class="token number">36.5</span> <span class="token operator">and</span> <span class="token number">40</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div></li><li><p>Select data with values within a specific range:</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> code <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> code <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token string">'200'</span><span class="token punctuation">,</span> <span class="token string">'300'</span><span class="token punctuation">,</span> <span class="token string">'400'</span><span class="token punctuation">,</span> <span class="token string">'500'</span><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></div></li><li><p>Select data with values outside a certain range:</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> code <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> code <span class="token operator">not</span> <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token string">'200'</span><span class="token punctuation">,</span> <span class="token string">'300'</span><span class="token punctuation">,</span> <span class="token string">'400'</span><span class="token punctuation">,</span> <span class="token string">'500'</span><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></div></li><li><p>Select data with values is null:</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> code <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> temperature <span class="token operator">is</span> <span class="token boolean">null</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div></li><li><p>Select data with values is not null:</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> code <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg1<span class="token punctuation">.</span>d1 <span class="token keyword">where</span> temperature <span class="token operator">is</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div></li></ol><h2 id="fuzzy-query" tabindex="-1"><a class="header-anchor" href="#fuzzy-query"><span>Fuzzy Query</span></a></h2><p>Fuzzy query is divided into Like statement and Regexp statement, both of which can support fuzzy matching of TEXT type data.</p><p>Like statement:</p><h3 id="fuzzy-matching-using-like" tabindex="-1"><a class="header-anchor" href="#fuzzy-matching-using-like"><span>Fuzzy matching using <code>Like</code></span></a></h3><p>In the value filter condition, for TEXT type data, use <code>Like</code> and <code>Regexp</code> operators to perform fuzzy matching on data.</p><p><strong>Matching rules:</strong></p><ul><li>The percentage (<code>%</code>) wildcard matches any string of zero or more characters.</li><li>The underscore (<code>_</code>) wildcard matches any single character.</li></ul><p><strong>Example 1:</strong> Query data containing <code>'cc'</code> in <code>value</code> under <code>root.sg.d1</code>.</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB> select * from root.sg.d1 where value like '%cc%' |
| +-----------------------------+----------------+ |
| | Time|root.sg.d1.value| |
| +-----------------------------+----------------+ |
| |2017-11-01T00:00:00.000+08:00| aabbccdd| |
| |2017-11-01T00:00:01.000+08:00| cc| |
| +-----------------------------+----------------+ |
| Total line number = 2 |
| It costs 0.002s |
| </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><strong>Example 2:</strong> Query data that consists of 3 characters and the second character is <code>'b'</code> in <code>value</code> under <code>root.sg.d1</code>.</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB> select * from root.sg.device where value like '_b_' |
| +-----------------------------+----------------+ |
| | Time|root.sg.d1.value| |
| +-----------------------------+----------------+ |
| |2017-11-01T00:00:02.000+08:00| abc| |
| +-----------------------------+----------------+ |
| Total line number = 1 |
| It costs 0.002s |
| </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><h3 id="fuzzy-matching-using-regexp" tabindex="-1"><a class="header-anchor" href="#fuzzy-matching-using-regexp"><span>Fuzzy matching using <code>Regexp</code></span></a></h3><p>The filter conditions that need to be passed in are regular expressions in the Java standard library style.</p><p><strong>Examples of common regular matching:</strong></p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>All characters with a length of 3-20: ^.{3,20}$ |
| Uppercase english characters: ^[A-Z]+$ |
| Numbers and English characters: ^[A-Za-z0-9]+$ |
| Beginning with a: ^a.* |
| </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></div><p><strong>Example 1:</strong> Query a string composed of 26 English characters for the value under root.sg.d1</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB> select * from root.sg.d1 where value regexp '^[A-Za-z]+$' |
| +-----------------------------+----------------+ |
| | Time|root.sg.d1.value| |
| +-----------------------------+----------------+ |
| |2017-11-01T00:00:00.000+08:00| aabbccdd| |
| |2017-11-01T00:00:01.000+08:00| cc| |
| +-----------------------------+----------------+ |
| Total line number = 2 |
| It costs 0.002s |
| </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><strong>Example 2:</strong> Query root.sg.d1 where the value value is a string composed of 26 lowercase English characters and the time is greater than 100</p><div class="language-text line-numbers-mode" data-ext="text" data-title="text"><pre class="language-text"><code>IoTDB> select * from root.sg.d1 where value regexp '^[a-z]+$' and time > 100 |
| +-----------------------------+----------------+ |
| | Time|root.sg.d1.value| |
| +-----------------------------+----------------+ |
| |2017-11-01T00:00:00.000+08:00| aabbccdd| |
| |2017-11-01T00:00:01.000+08:00| cc| |
| +-----------------------------+----------------+ |
| Total line number = 2 |
| It costs 0.002s |
| </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>`,26);function m(g,v){const s=t("RouteLink");return o(),l("div",null,[d,i("p",null,[e("Use time filters to filter data for a specific time range. For supported formats of timestamps, please refer to "),r(s,{to:"/UserGuide/V1.1.x/Data-Concept/Data-Type.html"},{default:c(()=>[e("Timestamp")]),_:1}),e(" .")]),u])}const h=n(p,[["render",m],["__file","Where-Condition.html.vue"]]),b=JSON.parse('{"path":"/UserGuide/V1.1.x/Query-Data/Where-Condition.html","title":"Query Filter","lang":"en-US","frontmatter":{"description":"Query Filter In IoTDB query statements, two filter conditions, time filter and value filter, are supported. The supported operators are as follows: Comparison operators: greater...","head":[["link",{"rel":"alternate","hreflang":"zh-cn","href":"https://iotdb.apache.org/zh/UserGuide/V1.1.x/Query-Data/Where-Condition.html"}],["meta",{"property":"og:url","content":"https://iotdb.apache.org/UserGuide/V1.1.x/Query-Data/Where-Condition.html"}],["meta",{"property":"og:site_name","content":"IoTDB Website"}],["meta",{"property":"og:title","content":"Query Filter"}],["meta",{"property":"og:description","content":"Query Filter In IoTDB query statements, two filter conditions, time filter and value filter, are supported. The supported operators are as follows: Comparison operators: greater..."}],["meta",{"property":"og:type","content":"article"}],["meta",{"property":"og:locale","content":"en-US"}],["meta",{"property":"og:locale:alternate","content":"zh-CN"}],["meta",{"property":"og:updated_time","content":"2023-07-10T03:11:17.000Z"}],["meta",{"property":"article:modified_time","content":"2023-07-10T03:11:17.000Z"}],["script",{"type":"application/ld+json"},"{\\"@context\\":\\"https://schema.org\\",\\"@type\\":\\"Article\\",\\"headline\\":\\"Query Filter\\",\\"image\\":[\\"\\"],\\"dateModified\\":\\"2023-07-10T03:11:17.000Z\\",\\"author\\":[]}"]]},"headers":[{"level":2,"title":"Time Filter","slug":"time-filter","link":"#time-filter","children":[]},{"level":2,"title":"Value Filter","slug":"value-filter","link":"#value-filter","children":[]},{"level":2,"title":"Fuzzy Query","slug":"fuzzy-query","link":"#fuzzy-query","children":[{"level":3,"title":"Fuzzy matching using Like","slug":"fuzzy-matching-using-like","link":"#fuzzy-matching-using-like","children":[]},{"level":3,"title":"Fuzzy matching using Regexp","slug":"fuzzy-matching-using-regexp","link":"#fuzzy-matching-using-regexp","children":[]}]}],"git":{"createdTime":1688958677000,"updatedTime":1688958677000,"contributors":[{"name":"CritasWang","email":"critas@outlook.com","commits":1}]},"readingTime":{"minutes":2.71,"words":814},"filePathRelative":"UserGuide/V1.1.x/Query-Data/Where-Condition.md","localizedDate":"July 10, 2023","autoDesc":true}');export{h as comp,b as data}; |
