| <!-- |
| |
| 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. |
| |
| --> |
| |
| ## 比较运算符和函数 |
| |
| ### 基本比较运算符 |
| |
| - 输入数据类型： `INT32`, `INT64`, `FLOAT`, `DOUBLE`。 |
| - 注意：会将所有数据转换为`DOUBLE`类型后进行比较。`==`和`!=`可以直接比较两个`BOOLEAN`。 |
| - 返回类型：`BOOLEAN`。 |
| |
| |运算符 |含义| |
| |----------------------------|-----------| |
| |`>` |大于| |
| |`>=` |大于等于| |
| |`<` |小于| |
| |`<=` |小于等于| |
| |`==` |等于| |
| |`!=` / `<>` |不等于| |
| |
| **示例：** |
| |
| ```sql |
| select a, b, a > 10, a <= b, !(a <= b), a > 10 && a > b from root.test; |
| ``` |
| |
| 运行结果 |
| ``` |
| IoTDB> select a, b, a > 10, a <= b, !(a <= b), a > 10 && a > b from root.test; |
| +-----------------------------+-----------+-----------+----------------+--------------------------+---------------------------+------------------------------------------------+ |
| | Time|root.test.a|root.test.b|root.test.a > 10|root.test.a <= root.test.b|!root.test.a <= root.test.b|(root.test.a > 10) & (root.test.a > root.test.b)| |
| +-----------------------------+-----------+-----------+----------------+--------------------------+---------------------------+------------------------------------------------+ |
| |1970-01-01T08:00:00.001+08:00| 23| 10.0| true| false| true| true| |
| |1970-01-01T08:00:00.002+08:00| 33| 21.0| true| false| true| true| |
| |1970-01-01T08:00:00.004+08:00| 13| 15.0| true| true| false| false| |
| |1970-01-01T08:00:00.005+08:00| 26| 0.0| true| false| true| true| |
| |1970-01-01T08:00:00.008+08:00| 1| 22.0| false| true| false| false| |
| |1970-01-01T08:00:00.010+08:00| 23| 12.0| true| false| true| true| |
| +-----------------------------+-----------+-----------+----------------+--------------------------+---------------------------+------------------------------------------------+ |
| ``` |
| |
| ### `BETWEEN ... AND ...` 运算符 |
| |
| |运算符 |含义| |
| |----------------------------|-----------| |
| |`BETWEEN ... AND ...` |在指定范围内| |
| |`NOT BETWEEN ... AND ...` |不在指定范围内| |
| |
| **示例：** 选择区间 [36.5,40] 内或之外的数据： |
| |
| ```sql |
| select temperature from root.sg1.d1 where temperature between 36.5 and 40; |
| ``` |
| |
| ```sql |
| select temperature from root.sg1.d1 where temperature not between 36.5 and 40; |
| ``` |
| |
| ### 模糊匹配运算符 |
| |
| 对于 TEXT 类型的数据，支持使用 `Like` 和 `Regexp` 运算符对数据进行模糊匹配 |
| |
| |运算符 |含义| |
| |----------------------------|-----------| |
| |`LIKE` |匹配简单模式| |
| |`NOT LIKE` |无法匹配简单模式| |
| |`REGEXP` |匹配正则表达式| |
| |`NOT REGEXP` |无法匹配正则表达式| |
| |
| 输入数据类型：`TEXT` |
| |
| 返回类型：`BOOLEAN` |
| |
| #### 使用 `Like` 进行模糊匹配 |
| |
| **匹配规则：** |
| |
| - `%` 表示任意0个或多个字符。 |
| - `_` 表示任意单个字符。 |
| |
| **示例 1：** 查询 `root.sg.d1` 下 `value` 含有`'cc'`的数据。 |
| |
| ```shell |
| 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 |
| ``` |
| |
| **示例 2：** 查询 `root.sg.d1` 下 `value` 中间为 `'b'`、前后为任意单个字符的数据。 |
| |
| ```shell |
| 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 |
| ``` |
| |
| #### 使用 `Regexp` 进行模糊匹配 |
| |
| 需要传入的过滤条件为 **Java 标准库风格的正则表达式**。 |
| |
| **常见的正则匹配举例：** |
| |
| ``` |
| 长度为3-20的所有字符：^.{3,20}$ |
| 大写英文字符：^[A-Z]+$ |
| 数字和英文字符：^[A-Za-z0-9]+$ |
| 以a开头的：^a.* |
| ``` |
| |
| **示例 1：** 查询 root.sg.d1 下 value 值为26个英文字符组成的字符串。 |
| |
| ```shell |
| 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 |
| ``` |
| |
| **示例 2：** 查询 root.sg.d1 下 value 值为26个小写英文字符组成的字符串且时间大于100的。 |
| |
| ```shell |
| 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 |
| ``` |
| |
| **示例 3：** |
| |
| ```sql |
| select b, b like '1%', b regexp '[0-2]' from root.test; |
| ``` |
| |
| 运行结果 |
| ``` |
| +-----------------------------+-----------+-------------------------+--------------------------+ |
| | Time|root.test.b|root.test.b LIKE '^1.*?$'|root.test.b REGEXP '[0-2]'| |
| +-----------------------------+-----------+-------------------------+--------------------------+ |
| |1970-01-01T08:00:00.001+08:00| 111test111| true| true| |
| |1970-01-01T08:00:00.003+08:00| 333test333| false| false| |
| +-----------------------------+-----------+-------------------------+--------------------------+ |
| ``` |
| |
| ### `IS NULL` 运算符 |
| |
| |运算符 |含义| |
| |----------------------------|-----------| |
| |`IS NULL` |是空值| |
| |`IS NOT NULL` |不是空值| |
| |
| **示例 1：** 选择值为空的数据: |
| |
| ```sql |
| select code from root.sg1.d1 where temperature is null; |
| ``` |
| |
| **示例 2：** 选择值为非空的数据: |
| |
| ```sql |
| select code from root.sg1.d1 where temperature is not null; |
| ``` |
| |
| ### `IN` 运算符 |
| |
| |运算符 |含义| |
| |----------------------------|-----------| |
| |`IN` / `CONTAINS` |是指定列表中的值| |
| |`NOT IN` / `NOT CONTAINS` |不是指定列表中的值| |
| |
| 输入数据类型：`All Types` |
| |
| 返回类型 `BOOLEAN` |
| |
| **注意：请确保集合中的值可以被转为输入数据的类型。** |
| > 例如： |
| > |
| >`s1 in (1, 2, 3, 'test')`，`s1`的数据类型是`INT32` |
| > |
| > 我们将会抛出异常，因为`'test'`不能被转为`INT32`类型 |
| |
| **示例 1：** 选择值在特定范围内的数据： |
| |
| ```sql |
| select code from root.sg1.d1 where code in ('200', '300', '400', '500'); |
| ``` |
| |
| **示例 2：** 选择值在特定范围外的数据： |
| |
| ```sql |
| select code from root.sg1.d1 where code not in ('200', '300', '400', '500'); |
| ``` |
| |
| **示例 3：** |
| |
| ```sql |
| select a, a in (1, 2) from root.test; |
| ``` |
| |
| 输出2: |
| ``` |
| +-----------------------------+-----------+--------------------+ |
| | Time|root.test.a|root.test.a IN (1,2)| |
| +-----------------------------+-----------+--------------------+ |
| |1970-01-01T08:00:00.001+08:00| 1| true| |
| |1970-01-01T08:00:00.003+08:00| 3| false| |
| +-----------------------------+-----------+--------------------+ |
| ``` |
| |
| ### 条件函数 |
| |
| 条件函数针对每个数据点进行条件判断，返回布尔值。 |
| |
| | 函数名 | 可接收的输入序列类型 | 必要的属性参数 | 输出序列类型 | 功能类型 | |
| |----------|--------------------------------|---------------------------------------|------------|--------------------------------------------------| |
| | ON_OFF | INT32 / INT64 / FLOAT / DOUBLE | `threshold`:DOUBLE类型 | BOOLEAN 类型 | 返回`ts_value >= threshold`的bool值 | |
| | IN_RANGE | INT32 / INT64 / FLOAT / DOUBLE | `lower`:DOUBLE类型<br/>`upper`:DOUBLE类型 | BOOLEAN类型 | 返回`ts_value >= lower && ts_value <= upper`的bool值 | | |
| |
| 测试数据： |
| |
| ``` |
| IoTDB> select ts from root.test; |
| +-----------------------------+------------+ |
| | Time|root.test.ts| |
| +-----------------------------+------------+ |
| |1970-01-01T08:00:00.001+08:00| 1| |
| |1970-01-01T08:00:00.002+08:00| 2| |
| |1970-01-01T08:00:00.003+08:00| 3| |
| |1970-01-01T08:00:00.004+08:00| 4| |
| +-----------------------------+------------+ |
| ``` |
| |
| **示例 1：** |
| |
| SQL语句： |
| ```sql |
| select ts, on_off(ts, 'threshold'='2') from root.test; |
| ``` |
| |
| 输出： |
| ``` |
| IoTDB> select ts, on_off(ts, 'threshold'='2') from root.test; |
| +-----------------------------+------------+-------------------------------------+ |
| | Time|root.test.ts|on_off(root.test.ts, "threshold"="2")| |
| +-----------------------------+------------+-------------------------------------+ |
| |1970-01-01T08:00:00.001+08:00| 1| false| |
| |1970-01-01T08:00:00.002+08:00| 2| true| |
| |1970-01-01T08:00:00.003+08:00| 3| true| |
| |1970-01-01T08:00:00.004+08:00| 4| true| |
| +-----------------------------+------------+-------------------------------------+ |
| ``` |
| |
| **示例 2：** |
| |
| Sql语句： |
| ```sql |
| select ts, in_range(ts, 'lower'='2', 'upper'='3.1') from root.test; |
| ``` |
| |
| 输出： |
| ``` |
| IoTDB> select ts, in_range(ts, 'lower'='2', 'upper'='3.1') from root.test; |
| +-----------------------------+------------+--------------------------------------------------+ |
| | Time|root.test.ts|in_range(root.test.ts, "lower"="2", "upper"="3.1")| |
| +-----------------------------+------------+--------------------------------------------------+ |
| |1970-01-01T08:00:00.001+08:00| 1| false| |
| |1970-01-01T08:00:00.002+08:00| 2| true| |
| |1970-01-01T08:00:00.003+08:00| 3| true| |
| |1970-01-01T08:00:00.004+08:00| 4| false| |
| +-----------------------------+------------+--------------------------------------------------+ |
| ``` |
