Skip to main content

Overview

About 11 min

Overview

This chapter describes the operators and functions supported by IoTDB. IoTDB provides a wealth of built-in operators and functions to meet your computing needs, and supports extensions through the User-Defined Function.

A list of all available functions, both built-in and custom, can be displayed with SHOW FUNCTIONS command.

See the documentation Select-Expression for the behavior of operators and functions in SQL.

Operators

Arithmetic Operators

OperatorMeaning
+positive (unary operator)
-negative (unary operator)
*multiplication
/division
%modulo
+addition
-subtraction

For details and examples, see the document Arithmetic Operators and Functions.

Comparison Operators

OperatorMeaning
>greater than
>=greater than or equal to
<less than
<=less than or equal to
==equal to
!= / <>not equal to
BETWEEN ... AND ...within the specified range
NOT BETWEEN ... AND ...not within the specified range
LIKEmatch simple pattern
NOT LIKEcannot match simple pattern
REGEXPmatch regular expression
NOT REGEXPcannot match regular expression
IS NULLis null
IS NOT NULLis not null
IN / CONTAINSis a value in the specified list
NOT IN / NOT CONTAINSis not a value in the specified list

For details and examples, see the document Comparison Operators and Functions.

Logical Operators

OperatorMeaning
NOT / !logical negation (unary operator)
AND / & / &&logical AND
OR/ | / ||logical OR

For details and examples, see the document Logical Operators.

Operator Precedence

The precedence of operators is arranged as shown below from high to low, and operators on the same row have the same precedence.

!, - (unary operator), + (unary operator)
*, /, DIV, %, MOD
-, +
=, ==, <=>, >=, >, <=, <, <>, !=
LIKE, REGEXP, NOT LIKE, NOT REGEXP
BETWEEN ... AND ..., NOT BETWEEN ... AND ...
IS NULL, IS NOT NULL
IN, CONTAINS, NOT IN, NOT CONTAINS
AND, &, &&
OR, |, ||

Built-in Functions

The built-in functions can be used in IoTDB without registration, and the functions in the data quality function library need to be registered by referring to the registration steps in the next chapter before they can be used.

Aggregate Functions

Function NameDescriptionAllowed Input Series Data TypesRequired AttributesOutput Series Data Type
SUMSummation.INT32 INT64 FLOAT DOUBLE/DOUBLE
COUNTCounts the number of data points.All types/INT
AVGAverage.INT32 INT64 FLOAT DOUBLE/DOUBLE
EXTREMEFinds the value with the largest absolute value. Returns a positive value if the maximum absolute value of positive and negative values is equal.INT32 INT64 FLOAT DOUBLE/Consistent with the input data type
MAX_VALUEFind the maximum value.INT32 INT64 FLOAT DOUBLE/Consistent with the input data type
MIN_VALUEFind the minimum value.INT32 INT64 FLOAT DOUBLE/Consistent with the input data type
FIRST_VALUEFind the value with the smallest timestamp.All data types/Consistent with input data type
LAST_VALUEFind the value with the largest timestamp.All data types/Consistent with input data type
MAX_TIMEFind the maximum timestamp.All data Types/Timestamp
MIN_TIMEFind the minimum timestamp.All data Types/Timestamp
COUNT_IFFind the number of data points that continuously meet a given condition and the number of data points that meet the condition (represented by keep) meet the specified threshold.BOOLEAN[keep >=/>/=/!=/</<=]threshold:The specified threshold or threshold condition, it is equivalent to keep >= threshold if threshold is used alone, type of threshold is INT64 ignoreNull:Optional, default value is true;If the value is true, null values are ignored, it means that if there is a null value in the middle, the value is ignored without interrupting the continuity. If the value is true, null values are not ignored, it means that if there are null values in the middle, continuity will be brokenINT64
TIME_DURATIONFind the difference between the timestamp of the largest non-null value and the timestamp of the smallest non-null value in a columnAll data Types/INT64
MODEFind the mode. Note: 1.Having too many different values in the input series risks a memory exception; 2.If all the elements have the same number of occurrences, that is no Mode, return the value with earliest time; 3.If there are many Modes, return the Mode with earliest time.All data Types/Consistent with the input data type

For details and examples, see the document Aggregate Functions.

Arithmetic Functions

Function NameAllowed Input Series Data TypesOutput Series Data TypeRequired AttributesCorresponding Implementation in the Java Standard Library
SININT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#sin(double)
COSINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#cos(double)
TANINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#tan(double)
ASININT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#asin(double)
ACOSINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#acos(double)
ATANINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#atan(double)
SINHINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#sinh(double)
COSHINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#cosh(double)
TANHINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#tanh(double)
DEGREESINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#toDegrees(double)
RADIANSINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#toRadians(double)
ABSINT32 / INT64 / FLOAT / DOUBLESame type as the input series/Math#abs(int) / Math#abs(long) /Math#abs(float) /Math#abs(double)
SIGNINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#signum(double)
CEILINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#ceil(double)
FLOORINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#floor(double)
ROUNDINT32 / INT64 / FLOAT / DOUBLEDOUBLE'places' : Round the significant number, positive number is the significant number after the decimal point, negative number is the significant number of whole numberMath#rint(Math#pow(10,places))/Math#pow(10,places)
EXPINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#exp(double)
LNINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#log(double)
LOG10INT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#log10(double)
SQRTINT32 / INT64 / FLOAT / DOUBLEDOUBLE/Math#sqrt(double)

For details and examples, see the document Arithmetic Operators and Functions.

Comparison Functions

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
ON_OFFINT32 / INT64 / FLOAT / DOUBLEthreshold: a double type variateBOOLEANReturn ts_value >= threshold.
IN_RANGRINT32 / INT64 / FLOAT / DOUBLElower: DOUBLE type upper: DOUBLE typeBOOLEANReturn ts_value >= lower && value <= upper.

For details and examples, see the document Comparison Operators and Functions.

String Processing Functions

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
STRING_CONTAINSTEXTs: string to search forBOOLEANChecks whether the substring s exists in the string.
STRING_MATCHESTEXTregex: Java standard library-style regular expressions.BOOLEANJudges whether a string can be matched by the regular expression regex.
LENGTHTEXT/INT32Get the length of input series.
LOCATETEXTtarget: The substring to be located.
reverse: Indicates whether reverse locate is required. The default value is false, means left-to-right locate.
INT32Get the position of the first occurrence of substring target in input series. Returns -1 if there are no target in input.
STARTSWITHTEXTtarget: The prefix to be checked.BOOLEANCheck whether input series starts with the specified prefix target.
ENDSWITHTEXTtarget: The suffix to be checked.BOOLEANCheck whether input series ends with the specified suffix target.
CONCATTEXTtargets: a series of K-V, key needs to start with target and be not duplicated, value is the string you want to concat.
series_behind: Indicates whether series behind targets. The default value is false.
TEXTConcatenate input string and target string.
SUBSTRINGTEXTfrom: Indicates the start position of substring.
for: Indicates how many characters to stop after of substring.
TEXTExtracts a substring of a string, starting with the first specified character and stopping after the specified number of characters.The index start at 1.
REPLACETEXTfirst parameter: The target substring to be replaced.
second parameter: The substring to replace with.
TEXTReplace a substring in the input sequence with the target substring.
UPPERTEXT/TEXTGet the string of input series with all characters changed to uppercase.
LOWERTEXT/TEXTGet the string of input series with all characters changed to lowercase.
TRIMTEXT/TEXTGet the string whose value is same to input series, with all leading and trailing space removed.
STRCMPTEXT/TEXTGet the compare result of two input series. Returns 0 if series value are the same, a negative integer if value of series1 is smaller than series2,
a positive integer if value of series1 is more than series2.

For details and examples, see the document String Processing.

Data Type Conversion Function

Function NameRequired AttributesOutput Series Data TypeDescription
CASTtype: Output data type, INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXTdetermined by typeConvert the data to the type specified by the type parameter.

For details and examples, see the document Data Type Conversion Function.

Constant Timeseries Generating Functions

Function NameRequired AttributesOutput Series Data TypeDescription
CONSTvalue: the value of the output data point type: the type of the output data point, it can only be INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXTDetermined by the required attribute typeOutput the user-specified constant timeseries according to the attributes value and type.
PINoneDOUBLEData point value: a double value of π, the ratio of the circumference of a circle to its diameter, which is equals to Math.PI in the Java Standard Library.
ENoneDOUBLEData point value: a double value of e, the base of the natural logarithms, which is equals to Math.E in the Java Standard Library.

For details and examples, see the document Constant Timeseries Generating Functions.

Selector Functions

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
TOP_KINT32 / INT64 / FLOAT / DOUBLE / TEXTk: the maximum number of selected data points, must be greater than 0 and less than or equal to 1000Same type as the input seriesReturns k data points with the largest values in a time series.
BOTTOM_KINT32 / INT64 / FLOAT / DOUBLE / TEXTk: the maximum number of selected data points, must be greater than 0 and less than or equal to 1000Same type as the input seriesReturns k data points with the smallest values in a time series.

For details and examples, see the document Selector Functions.

Continuous Interval Functions

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
ZERO_DURATIONINT32/ INT64/ FLOAT/ DOUBLE/ BOOLEANmin:Optional with default value 0L max:Optional with default value Long.MAX_VALUELongReturn intervals' start times and duration times in which the value is always 0(false), and the duration time t satisfy t >= min && t <= max. The unit of t is ms
NON_ZERO_DURATIONINT32/ INT64/ FLOAT/ DOUBLE/ BOOLEANmin:Optional with default value 0L max:Optional with default value Long.MAX_VALUELongReturn intervals' start times and duration times in which the value is always not 0, and the duration time t satisfy t >= min && t <= max. The unit of t is ms
ZERO_COUNTINT32/ INT64/ FLOAT/ DOUBLE/ BOOLEANmin:Optional with default value 1L max:Optional with default value Long.MAX_VALUELongReturn intervals' start times and the number of data points in the interval in which the value is always 0(false). Data points number n satisfy n >= min && n <= max
NON_ZERO_COUNTINT32/ INT64/ FLOAT/ DOUBLE/ BOOLEANmin:Optional with default value 1L max:Optional with default value Long.MAX_VALUELongReturn intervals' start times and the number of data points in the interval in which the value is always not 0(false). Data points number n satisfy n >= min && n <= max

For details and examples, see the document Continuous Interval Functions.

Variation Trend Calculation Functions

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
TIME_DIFFERENCEINT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT/INT64Calculates the difference between the time stamp of a data point and the time stamp of the previous data point. There is no corresponding output for the first data point.
DIFFERENCEINT32 / INT64 / FLOAT / DOUBLE/Same type as the input seriesCalculates the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point.
NON_NEGATIVE_DIFFERENCEINT32 / INT64 / FLOAT / DOUBLE/Same type as the input seriesCalculates the absolute value of the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point.
DERIVATIVEINT32 / INT64 / FLOAT / DOUBLE/DOUBLECalculates the rate of change of a data point compared to the previous data point, the result is equals to DIFFERENCE / TIME_DIFFERENCE. There is no corresponding output for the first data point.
NON_NEGATIVE_DERIVATIVEINT32 / INT64 / FLOAT / DOUBLE/DOUBLECalculates the absolute value of the rate of change of a data point compared to the previous data point, the result is equals to NON_NEGATIVE_DIFFERENCE / TIME_DIFFERENCE. There is no corresponding output for the first data point.
DIFFINT32 / INT64 / FLOAT / DOUBLEignoreNull:optional,default is true. If is true, the previous data point is ignored when it is null and continues to find the first non-null value forwardly. If the value is false, previous data point is not ignored when it is null, the result is also null because null is used for subtractionDOUBLECalculates the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point, so output is null

For details and examples, see the document Variation Trend Calculation Functions.

Sample Functions

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
EQUAL_SIZE_BUCKET_RANDOM_SAMPLEINT32 / INT64 / FLOAT / DOUBLEproportion The value range is (0, 1], the default is 0.1INT32 / INT64 / FLOAT / DOUBLEReturns a random sample of equal buckets that matches the sampling ratio
EQUAL_SIZE_BUCKET_AGG_SAMPLEINT32 / INT64 / FLOAT / DOUBLEproportion The value range is (0, 1], the default is 0.1
type: The value types are avg, max, min, sum, extreme, variance, the default is avg
INT32 / INT64 / FLOAT / DOUBLEReturns equal bucket aggregation samples that match the sampling ratio
EQUAL_SIZE_BUCKET_M4_SAMPLEINT32 / INT64 / FLOAT / DOUBLEproportion The value range is (0, 1], the default is 0.1INT32 / INT64 / FLOAT / DOUBLEReturns equal bucket M4 samples that match the sampling ratio
EQUAL_SIZE_BUCKET_OUTLIER_SAMPLEINT32 / INT64 / FLOAT / DOUBLEThe value range of proportion is (0, 1], the default is 0.1
The value of type is avg or stendis or cos or prenextdis, the default is avg
The value of number should be greater than 0, the default is 3
INT32 / INT64 / FLOAT / DOUBLEReturns outlier samples in equal buckets that match the sampling ratio and the number of samples in the bucket
M4INT32 / INT64 / FLOAT / DOUBLEDifferent attributes used by the size window and the time window. The size window uses attributes windowSize and slidingStep. The time window uses attributes timeInterval, slidingStep, displayWindowBegin, and displayWindowEnd. More details see below.INT32 / INT64 / FLOAT / DOUBLEReturns the first, last, bottom, top points in each sliding window. M4 sorts and deduplicates the aggregated points within the window before outputting them.

For details and examples, see the document Sample Functions.

Change Points Function

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
CHANGE_POINTSINT32 / INT64 / FLOAT / DOUBLE/Same type as the input seriesRemove consecutive identical values from an input sequence.

For details and examples, see the document Time-Series.

Lambda Expression

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeSeries Data Type Description
JEXLINT32 / INT64 / FLOAT / DOUBLE / TEXT / BOOLEANexpr is a lambda expression that supports standard one or multi arguments in the form x -> {...} or (x, y, z) -> {...}, e.g. x -> {x * 2}, (x, y, z) -> {x + y * z}INT32 / INT64 / FLOAT / DOUBLE / TEXT / BOOLEANReturns the input time series transformed by a lambda expression

For details and examples, see the document Lambda.

Conditional Expressions

Expression NameDescription
CASEsimilar to "if else"

For details and examples, see the document Conditional Expressions.

Data Quality Function Library

About

For applications based on time series data, data quality is vital. UDF Library is IoTDB User Defined Functions (UDF) about data quality, including data profiling, data quality evalution and data repairing. It effectively meets the demand for data quality in the industrial field.

Quick Start

The functions in this function library are not built-in functions, and must be loaded into the system before use.

  1. Downloadopen in new window the JAR with all dependencies and the script of registering UDF.
  2. Copy the JAR package to ext\udf under the directory of IoTDB system (Please put JAR to this directory of all DataNodes if you use Cluster).
  3. Run sbin\start-server.bat (for Windows) or sbin\start-server.sh (for Linux or MacOS) to start IoTDB server.
  4. Copy the script to the directory of IoTDB system (under the root directory, at the same level as sbin), modify the parameters in the script if needed and run it to register UDF.

Implemented Functions

  1. Data Quality related functions, such as Completeness. For details and examples, see the document Data-Quality.
  2. Data Profiling related functions, such as ACF. For details and examples, see the document Data-Profiling.
  3. Anomaly Detection related functions, such as IQR. For details and examples, see the document Anomaly-Detection.
  4. Frequency Domain Analysis related functions, such as Conv. For details and examples, see the document Frequency-Domain.
  5. Data Matching related functions, such as DTW. For details and examples, see the document Data-Matching.
  6. Data Repairing related functions, such as TimestampRepair. For details and examples, see the document Data-Repairing.
  7. Series Discovery related functions, such as ConsecutiveSequences. For details and examples, see the document Series-Discovery.
  8. Machine Learning related functions, such as AR. For details and examples, see the document Machine-Learning.

Copyright © 2024 The Apache Software Foundation.
Apache and the Apache feather logo are trademarks of The Apache Software Foundation

Have a question? Connect with us on QQ, WeChat, or Slack. Join the community now.

We use Google Analytics to collect anonymous, aggregated usage information.