Basic Functions

1. Comparison Functions and Operators

1.1 Basic Comparison Operators

Comparison operators are used to compare two values and return the comparison result (true or false).

Operators	Description
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
=	Equal to
<>	Not equal to
!=	Not equal to

1.1.1 Comparison rules:

All types can be compared with themselves.
Numeric types (INT32, INT64, FLOAT, DOUBLE, TIMESTAMP) can be compared with each other.
Character types (STRING, TEXT) can also be compared with each other.
Comparisons between types other than those mentioned above will result in an error.

1.2 BETWEEN Operator

The BETWEEN operator is used to determine whether a value falls within a specified range.
The NOT BETWEEN operator is used to determine whether a value does not fall within a specified range.
The BETWEEN and NOT BETWEEN operators can be used to evaluate any sortable type.
The value, minimum, and maximum parameters for BETWEEN and NOT BETWEEN must be of the same type, otherwise an error will occur.

Syntax：

 value BETWEEN min AND max：
 value NOT BETWEEN min AND max：

Example 1 ：BETWEEN

-- Query records where temperature is between 85.0 and 90.0
SELECT * FROM table1 WHERE temperature BETWEEN 85.0 AND 90.0;

Example 2 : NOT BETWEEN

-- Query records where humidity is not between 35.0 and 40.0
SELECT * FROM table1 WHERE humidity NOT BETWEEN 35.0 AND 40.0;

1.3 IS NULL Operator

These operators apply to all data types.

Example 1: Query records where temperature is NULL

SELECT * FROM table1 WHERE temperature IS NULL;

Example 2: Query records where humidity is not NULL

SELECT * FROM table1 WHERE humidity IS NOT NULL;

1.4 IN Operator

The IN operator can be used in the WHERE clause to compare a column with a list of values.
These values can be provided by a static array or scalar expressions.

Syntax:

... WHERE column [NOT] IN ('value1','value2', expression1)

Example 1: Static array: Query records where region is ‘Beijing’ or ‘Shanghai’

SELECT * FROM table1 WHERE region IN ('Beijing', 'Shanghai');
--Equivalent to
SELECT * FROM region WHERE name = 'Beijing' OR name = 'Shanghai';

Example 2: Scalar expression: Query records where temperature is among specific values

SELECT * FROM table1 WHERE temperature IN (85.0, 90.0);

Example 3: Query records where region is not ‘Beijing’ or ‘Shanghai’

SELECT * FROM table1 WHERE region NOT IN ('Beijing', 'Shanghai');

1.5 GREATEST and LEAST

The GREATEST function returns the maximum value from a list of arguments, while the LEAST function returns the minimum value. The return type matches the input data type.

Key Behaviors:

NULL Handling: Returns NULL if all arguments are NULL.
Parameter Requirements: Requires at least 2 arguments.
Type Constraints: All arguments must have the same data type.
Supported Types: BOOLEAN、FLOAT、DOUBLE、INT32、INT64、STRING、TEXT、TIMESTAMP、DATE

Syntax:

  greatest(value1, value2, ..., valueN)
  least(value1, value2, ..., valueN)

Examples:

-- Retrieve the maximum value between `temperature` and `humidity` in `table2`  
SELECT GREATEST(temperature,humidity) FROM table2;

-- Retrieve the minimum value between `temperature` and `humidity` in `table2`  
SELECT LEAST(temperature,humidity) FROM table2;

2. Aggregate functions

2.1 Overview

Aggregate functions are many-to-one functions. They perform aggregate calculations on a set of values to obtain a single aggregate result.
Except for COUNT(), all other aggregate functions ignore null values and return null when there are no input rows or all values are null. For example, SUM() returns null instead of zero, and AVG() does not include null values in the count.

2.2 Supported Aggregate Functions

Function Name	Description	Allowed Input Types	Output Type
COUNT	Counts the number of data points.	All types	INT64
COUNT_IF	COUNT_IF(exp) counts the number of rows that satisfy a specified boolean expression.	`exp` must be a boolean expression,(e.g. `count_if(temperature>20)`)	INT64
APPROX_COUNT_DISTINCT	The APPROX_COUNT_DISTINCT(x[, maxStandardError]) function provides an approximation of COUNT(DISTINCT x), returning the estimated number of distinct input values.	`x`: The target column to be calculated, supports all data types. `maxStandardError` (optional): Specifies the maximum standard error allowed for the function's result. Valid range is [0.0040625, 0.26]. Defaults to 0.023 if not specified.	INT64
SUM	Calculates the sum.	INT32 INT64 FLOAT DOUBLE	DOUBLE
AVG	Calculates the average.	INT32 INT64 FLOAT DOUBLE	DOUBLE
MAX	Finds the maximum value.	All types	Same as input type
MIN	Finds the minimum value.	All types	Same as input type
FIRST	Finds the value with the smallest timestamp that is not NULL.	All types	Same as input type
LAST	Finds the value with the largest timestamp that is not NULL.	All types	Same as input type
STDDEV	Alias for STDDEV_SAMP, calculates the sample standard deviation.	INT32 INT64 FLOAT DOUBLE	DOUBLE
STDDEV_POP	Calculates the population standard deviation.	INT32 INT64 FLOAT DOUBLE	DOUBLE
STDDEV_SAMP	Calculates the sample standard deviation.	INT32 INT64 FLOAT DOUBLE	DOUBLE
VARIANCE	Alias for VAR_SAMP, calculates the sample variance.	INT32 INT64 FLOAT DOUBLE	DOUBLE
VAR_POP	Calculates the population variance.	INT32 INT64 FLOAT DOUBLE	DOUBLE
VAR_SAMP	Calculates the sample variance.	INT32 INT64 FLOAT DOUBLE	DOUBLE
EXTREME	Finds the value with the largest absolute value. If the largest absolute values of positive and negative values are equal, returns the positive value.	INT32 INT64 FLOAT DOUBLE	Same as input type
MODE	Finds the mode. Note: 1. There is a risk of memory exception when the number of distinct values in the input sequence is too large; 2. If all elements have the same frequency, i.e., there is no mode, a random element is returned; 3. If there are multiple modes, a random mode is returned; 4. NULL values are also counted in frequency, so even if not all values in the input sequence are NULL, the final result may still be NULL.	All types	Same as input type
MAX_BY	MAX_BY(x, y) finds the value of x corresponding to the maximum y in the binary input x and y. MAX_BY(time, x) returns the timestamp when x is at its maximum.	x and y can be of any type	Same as the data type of the first input x
MIN_BY	MIN_BY(x, y) finds the value of x corresponding to the minimum y in the binary input x and y. MIN_BY(time, x) returns the timestamp when x is at its minimum.	x and y can be of any type	Same as the data type of the first input x
FIRST_BY	FIRST_BY(x, y) finds the value of x in the same row when y is the first non-null value.	x and y can be of any type	Same as the data type of the first input x
LAST_BY	LAST_BY(x, y) finds the value of x in the same row when y is the last non-null value.	x and y can be of any type	Same as the data type of the first input x

2.3 Examples

2.3.1 Example Data

The Example Data page contains SQL statements for building table structures and inserting data. Download and execute these statements in the IoTDB CLI to import the data into IoTDB. You can use this data to test and execute the SQL statements in the examples and obtain the corresponding results.

2.3.2 Count

Counts the number of rows in the entire table and the number of non-null values in the temperature column.

IoTDB> select count(*), count(temperature) from table1;