Google Git Google Git
Sign in
apache/iotdb/refs/heads/lwh/ut1/./docs/UserGuide/Operators-Functions/Comparison.md
blob: 1581188d7b49967bc564ed6bbef65a85415cae5a [file] [view]
<!--
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.
-->
# Comparison Operators and Functions
## Basic comparison operators
Supported operators `>`, `>=`, `<`, `<=`, `==`, `!=` (or `<>` )
Supported input data types: `INT32`, `INT64`, `FLOAT` and `DOUBLE`
Note: It will transform all type to `DOUBLE` then do computation.
Output data type: `BOOLEAN`
**Example:**
```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 ...` operator
|operator |meaning|
|-----------------------------|-----------|
|`BETWEEN ... AND ...` |within the specified range|
|`NOT BETWEEN ... AND ...` |Not within the specified range|
**Example:** Select data within or outside the interval [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;
```
## Fuzzy matching operator
For TEXT type data, support fuzzy matching of data using `Like` and `Regexp` operators.
|operator |meaning|
|-----------------------------|-----------|
|`LIKE` | matches simple patterns|
|`NOT LIKE` |cannot match simple pattern|
|`REGEXP` | Match regular expression|
|`NOT REGEXP` |Cannot match regular expression|
Input data type: `TEXT`
Return type: `BOOLEAN`
### Use `Like` for fuzzy matching
**Matching rules:**
- `%` means any 0 or more characters.
- `_` means any single character.
**Example 1:** Query the data under `root.sg.d1` that contains `'cc'` in `value`.
```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
```
**Example 2:** Query the data under `root.sg.d1` with `'b'` in the middle of `value` and any single character before and after.
```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
```
### Use `Regexp` for fuzzy matching
The filter condition that needs to be passed in is **Java standard library style regular expression**.
**Common regular matching examples:**
```
All characters with a length of 3-20: ^.{3,20}$
Uppercase English characters: ^[A-Z]+$
Numbers and English characters: ^[A-Za-z0-9]+$
Starting with a: ^a.*
```
**Example 1:** Query the string of 26 English characters for value under root.sg.d1.
```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
```
**Example 2:** Query root.sg.d1 where the value is a string consisting of 26 lowercase English characters and the time is greater than 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
```
**Example 3:**
```sql
select b, b like '1%', b regexp '[0-2]' from root.test;
```
operation result
```
+-----------------------------+-----------+------- ------------------+--------------------------+
| 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` operator
|operator |meaning|
|-----------------------------|-----------|
|`IS NULL` |is a null value|
|`IS NOT NULL` |is not a null value|
**Example 1:** Select data with empty values:
```sql
select code from root.sg1.d1 where temperature is null;
```
**Example 2:** Select data with non-null values:
```sql
select code from root.sg1.d1 where temperature is not null;
```
## `IN` operator
|operator |meaning|
|-----------------------------|-----------|
|`IN` / `CONTAINS` | are the values ​​in the specified list|
|`NOT IN` / `NOT CONTAINS` |not a value in the specified list|
Input data type: `All Types`
return type `BOOLEAN`
**Note: Please ensure that the values ​​in the collection can be converted to the type of the input data. **
>
> For example:
>
> `s1 in (1, 2, 3, 'test')`, the data type of `s1` is `INT32`
>
> We will throw an exception because `'test'` cannot be converted to type `INT32`
>
**Example 1:** Select data with values ​​within a certain range:
```sql
select code from root.sg1.d1 where code in ('200', '300', '400', '500');
```
**Example 2:** Select data with values ​​outside a certain range:
```sql
select code from root.sg1.d1 where code not in ('200', '300', '400', '500');
```
**Example 3:**
```sql
select a, a in (1, 2) from root.test;
```
Output 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|
+-----------------------------+-----------+------- -------------+
```
## Condition Functions
Condition functions are used to check whether timeseries data points satisfy some specific condition.
They return BOOLEANs.
Currently, IoTDB supports the following condition functions:
| Function Name | Allowed Input Series Data Types | Required Attributes | Output Series Data Type | Series Data Type Description |
|---------------|---------------------------------|-----------------------------------------------|-------------------------|-----------------------------------------------|
| ON_OFF | INT32 / INT64 / FLOAT / DOUBLE | `threshold`: a double type variate | BOOLEAN | Return `ts_value >= threshold`. |
| IN_RANGR | INT32 / INT64 / FLOAT / DOUBLE | `lower`: DOUBLE type<br/>`upper`: DOUBLE type | BOOLEAN | Return `ts_value >= lower && value <= upper`. |
Example Data:
```
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|
+-----------------------------+------------+
```
##### Test 1
SQL:
```sql
select ts, on_off(ts, 'threshold'='2') from root.test;
```
Output:
```
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|
+-----------------------------+------------+-------------------------------------+
```
##### Test 2
Sql:
```sql
select ts, in_range(ts, 'lower'='2', 'upper'='3.1') from root.test;
```
Output:
```
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|
+-----------------------------+------------+--------------------------------------------------+
```