UDF Libraries

Based on the ability of user-defined functions, IoTDB provides a series of functions for temporal data processing, including data quality, data profiling, anomaly detection, frequency domain analysis, data matching, data repairing, sequence discovery, machine learning, etc., which can meet the needs of industrial fields for temporal data processing.

Note: The functions in the current UDF library only support millisecond level timestamp accuracy.

Installation steps

Please obtain the compressed file of the UDF library JAR package that is compatible with the IoTDB version.

UDF installation package	Supported IoTDB versions	Download link
apache-UDF-1.3.3.zip	V1.3.3 and above	Please contact Timecho for assistance
apache-UDF-1.3.2.zip	V1.0.0～V1.3.2	Please contact Timecho for assistance
2. Place the library-udf.jar file in the compressed file obtained in the directory `/ext/udf` of all nodes in the IoTDB cluster
3. In the SQL operation interface of IoTDB’s SQL command line terminal (CLI), execute the corresponding function registration statement as follows.
4. Batch registration: Two registration methods: registration script or SQL full statement

Register Script
Copy the registration script (register-UDF.sh or register-UDF.bat) from the compressed package to the tools directory of IoTDB as needed, and modify the parameters in the script (default is host=127.0.0.1, rpcPort=6667, user=root, pass=root);
Start IoTDB service, run registration script to batch register UDF
All SQL statements
Open the SQl file in the compressed package, copy all SQL statements, and in the SQL operation interface of IoTDB’s SQL command line terminal (CLI), execute all SQl statements to batch register UDFs

Data Quality

Completeness

Registration statement

1. create function completeness as 'org.apache.iotdb.library.dquality.UDTFCompleteness'

Usage

This function is used to calculate the completeness of time series. The input series are divided into several continuous and non overlapping windows. The timestamp of the first data point and the completeness of each window will be output.

Name: COMPLETENESS

Input Series: Only support a single input series. The type is INT32 / INT64 / FLOAT / DOUBLE.

Parameters:

window: The size of each window. It is a positive integer or a positive number with an unit. The former is the number of data points in each window. The number of data points in the last window may be less than it. The latter is the time of the window. The unit is ‘ms’ for millisecond, ‘s’ for second, ‘m’ for minute, ‘h’ for hour and ‘d’ for day. By default, all input data belongs to the same window.
downtime: Whether the downtime exception is considered in the calculation of completeness. It is ‘true’ or ‘false’ (default). When considering the downtime exception, long-term missing data will be considered as downtime exception without any influence on completeness.

Output Series: Output a single series. The type is DOUBLE. The range of each value is [0,1].

Note: Only when the number of data points in the window exceeds 10, the calculation will be performed. Otherwise, the window will be ignored and nothing will be output.

Examples

Default Parameters

With default parameters, this function will regard all input data as the same window.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:02.000+08:00|          100.0|
5. |2020-01-01T00:00:03.000+08:00|          101.0|
6. |2020-01-01T00:00:04.000+08:00|          102.0|
7. |2020-01-01T00:00:06.000+08:00|          104.0|
8. |2020-01-01T00:00:08.000+08:00|          126.0|
9. |2020-01-01T00:00:10.000+08:00|          108.0|
10. |2020-01-01T00:00:14.000+08:00|          112.0|
11. |2020-01-01T00:00:15.000+08:00|          113.0|
12. |2020-01-01T00:00:16.000+08:00|          114.0|
13. |2020-01-01T00:00:18.000+08:00|          116.0|
14. |2020-01-01T00:00:20.000+08:00|          118.0|
15. |2020-01-01T00:00:22.000+08:00|          120.0|
16. |2020-01-01T00:00:26.000+08:00|          124.0|
17. |2020-01-01T00:00:28.000+08:00|          126.0|
18. |2020-01-01T00:00:30.000+08:00|            NaN|
19. +-----------------------------+---------------+

SQL for query:

1. select completeness(s1) from root.test.d1 where time <= 2020-01-01 00:00:30

Output series:

1. +-----------------------------+-----------------------------+
2. |                         Time|completeness(root.test.d1.s1)|
3. +-----------------------------+-----------------------------+
4. |2020-01-01T00:00:02.000+08:00|                        0.875|
5. +-----------------------------+-----------------------------+

Specific Window Size

When the window size is given, this function will divide the input data as multiple windows.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:02.000+08:00|          100.0|
5. |2020-01-01T00:00:03.000+08:00|          101.0|
6. |2020-01-01T00:00:04.000+08:00|          102.0|
7. |2020-01-01T00:00:06.000+08:00|          104.0|
8. |2020-01-01T00:00:08.000+08:00|          126.0|
9. |2020-01-01T00:00:10.000+08:00|          108.0|
10. |2020-01-01T00:00:14.000+08:00|          112.0|
11. |2020-01-01T00:00:15.000+08:00|          113.0|
12. |2020-01-01T00:00:16.000+08:00|          114.0|
13. |2020-01-01T00:00:18.000+08:00|          116.0|
14. |2020-01-01T00:00:20.000+08:00|          118.0|
15. |2020-01-01T00:00:22.000+08:00|          120.0|
16. |2020-01-01T00:00:26.000+08:00|          124.0|
17. |2020-01-01T00:00:28.000+08:00|          126.0|
18. |2020-01-01T00:00:30.000+08:00|            NaN|
19. |2020-01-01T00:00:32.000+08:00|          130.0|
20. |2020-01-01T00:00:34.000+08:00|          132.0|
21. |2020-01-01T00:00:36.000+08:00|          134.0|
22. |2020-01-01T00:00:38.000+08:00|          136.0|
23. |2020-01-01T00:00:40.000+08:00|          138.0|
24. |2020-01-01T00:00:42.000+08:00|          140.0|
25. |2020-01-01T00:00:44.000+08:00|          142.0|
26. |2020-01-01T00:00:46.000+08:00|          144.0|
27. |2020-01-01T00:00:48.000+08:00|          146.0|
28. |2020-01-01T00:00:50.000+08:00|          148.0|
29. |2020-01-01T00:00:52.000+08:00|          150.0|
30. |2020-01-01T00:00:54.000+08:00|          152.0|
31. |2020-01-01T00:00:56.000+08:00|          154.0|
32. |2020-01-01T00:00:58.000+08:00|          156.0|
33. |2020-01-01T00:01:00.000+08:00|          158.0|
34. +-----------------------------+---------------+

SQL for query:

1. select completeness(s1,"window"="15") from root.test.d1 where time <= 2020-01-01 00:01:00

Output series:

1. +-----------------------------+--------------------------------------------+
2. |                         Time|completeness(root.test.d1.s1, "window"="15")|
3. +-----------------------------+--------------------------------------------+
4. |2020-01-01T00:00:02.000+08:00|                                       0.875|
5. |2020-01-01T00:00:32.000+08:00|                                         1.0|
6. +-----------------------------+--------------------------------------------+

Consistency

Registration statement

1. create function consistency as 'org.apache.iotdb.library.dquality.UDTFConsistency'

Usage

This function is used to calculate the consistency of time series. The input series are divided into several continuous and non overlapping windows. The timestamp of the first data point and the consistency of each window will be output.

Name: CONSISTENCY

Input Series: Only support a single input series. The type is INT32 / INT64 / FLOAT / DOUBLE.

Parameters:

window: The size of each window. It is a positive integer or a positive number with an unit. The former is the number of data points in each window. The number of data points in the last window may be less than it. The latter is the time of the window. The unit is ‘ms’ for millisecond, ‘s’ for second, ‘m’ for minute, ‘h’ for hour and ‘d’ for day. By default, all input data belongs to the same window.

Output Series: Output a single series. The type is DOUBLE. The range of each value is [0,1].

Note: Only when the number of data points in the window exceeds 10, the calculation will be performed. Otherwise, the window will be ignored and nothing will be output.

Examples

Default Parameters

With default parameters, this function will regard all input data as the same window.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:02.000+08:00|          100.0|
5. |2020-01-01T00:00:03.000+08:00|          101.0|
6. |2020-01-01T00:00:04.000+08:00|          102.0|
7. |2020-01-01T00:00:06.000+08:00|          104.0|
8. |2020-01-01T00:00:08.000+08:00|          126.0|
9. |2020-01-01T00:00:10.000+08:00|          108.0|
10. |2020-01-01T00:00:14.000+08:00|          112.0|
11. |2020-01-01T00:00:15.000+08:00|          113.0|
12. |2020-01-01T00:00:16.000+08:00|          114.0|
13. |2020-01-01T00:00:18.000+08:00|          116.0|
14. |2020-01-01T00:00:20.000+08:00|          118.0|
15. |2020-01-01T00:00:22.000+08:00|          120.0|
16. |2020-01-01T00:00:26.000+08:00|          124.0|
17. |2020-01-01T00:00:28.000+08:00|          126.0|
18. |2020-01-01T00:00:30.000+08:00|            NaN|
19. +-----------------------------+---------------+

SQL for query:

1. select consistency(s1) from root.test.d1 where time <= 2020-01-01 00:00:30

Output series:

1. +-----------------------------+----------------------------+
2. |                         Time|consistency(root.test.d1.s1)|
3. +-----------------------------+----------------------------+
4. |2020-01-01T00:00:02.000+08:00|          0.9333333333333333|
5. +-----------------------------+----------------------------+

Specific Window Size

When the window size is given, this function will divide the input data as multiple windows.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:02.000+08:00|          100.0|
5. |2020-01-01T00:00:03.000+08:00|          101.0|
6. |2020-01-01T00:00:04.000+08:00|          102.0|
7. |2020-01-01T00:00:06.000+08:00|          104.0|
8. |2020-01-01T00:00:08.000+08:00|          126.0|
9. |2020-01-01T00:00:10.000+08:00|          108.0|
10. |2020-01-01T00:00:14.000+08:00|          112.0|
11. |2020-01-01T00:00:15.000+08:00|          113.0|
12. |2020-01-01T00:00:16.000+08:00|          114.0|
13. |2020-01-01T00:00:18.000+08:00|          116.0|
14. |2020-01-01T00:00:20.000+08:00|          118.0|
15. |2020-01-01T00:00:22.000+08:00|          120.0|
16. |2020-01-01T00:00:26.000+08:00|          124.0|
17. |2020-01-01T00:00:28.000+08:00|          126.0|
18. |2020-01-01T00:00:30.000+08:00|            NaN|
19. |2020-01-01T00:00:32.000+08:00|          130.0|
20. |2020-01-01T00:00:34.000+08:00|          132.0|
21. |2020-01-01T00:00:36.000+08:00|          134.0|
22. |2020-01-01T00:00:38.000+08:00|          136.0|
23. |2020-01-01T00:00:40.000+08:00|          138.0|
24. |2020-01-01T00:00:42.000+08:00|          140.0|
25. |2020-01-01T00:00:44.000+08:00|          142.0|
26. |2020-01-01T00:00:46.000+08:00|          144.0|
27. |2020-01-01T00:00:48.000+08:00|          146.0|
28. |2020-01-01T00:00:50.000+08:00|          148.0|
29. |2020-01-01T00:00:52.000+08:00|          150.0|
30. |2020-01-01T00:00:54.000+08:00|          152.0|
31. |2020-01-01T00:00:56.000+08:00|          154.0|
32. |2020-01-01T00:00:58.000+08:00|          156.0|
33. |2020-01-01T00:01:00.000+08:00|          158.0|
34. +-----------------------------+---------------+

SQL for query:

1. select consistency(s1,"window"="15") from root.test.d1 where time <= 2020-01-01 00:01:00

Output series:

1. +-----------------------------+-------------------------------------------+
2. |                         Time|consistency(root.test.d1.s1, "window"="15")|
3. +-----------------------------+-------------------------------------------+
4. |2020-01-01T00:00:02.000+08:00|                         0.9333333333333333|
5. |2020-01-01T00:00:32.000+08:00|                                        1.0|
6. +-----------------------------+-------------------------------------------+

Timeliness

Registration statement

1. create function timeliness as 'org.apache.iotdb.library.dquality.UDTFTimeliness'

Usage

This function is used to calculate the timeliness of time series. The input series are divided into several continuous and non overlapping windows. The timestamp of the first data point and the timeliness of each window will be output.

Name: TIMELINESS

Input Series: Only support a single input series. The type is INT32 / INT64 / FLOAT / DOUBLE.

Parameters:

window: The size of each window. It is a positive integer or a positive number with an unit. The former is the number of data points in each window. The number of data points in the last window may be less than it. The latter is the time of the window. The unit is ‘ms’ for millisecond, ‘s’ for second, ‘m’ for minute, ‘h’ for hour and ‘d’ for day. By default, all input data belongs to the same window.

Output Series: Output a single series. The type is DOUBLE. The range of each value is [0,1].

Note: Only when the number of data points in the window exceeds 10, the calculation will be performed. Otherwise, the window will be ignored and nothing will be output.

Examples

Default Parameters

With default parameters, this function will regard all input data as the same window.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:02.000+08:00|          100.0|
5. |2020-01-01T00:00:03.000+08:00|          101.0|
6. |2020-01-01T00:00:04.000+08:00|          102.0|
7. |2020-01-01T00:00:06.000+08:00|          104.0|
8. |2020-01-01T00:00:08.000+08:00|          126.0|
9. |2020-01-01T00:00:10.000+08:00|          108.0|
10. |2020-01-01T00:00:14.000+08:00|          112.0|
11. |2020-01-01T00:00:15.000+08:00|          113.0|
12. |2020-01-01T00:00:16.000+08:00|          114.0|
13. |2020-01-01T00:00:18.000+08:00|          116.0|
14. |2020-01-01T00:00:20.000+08:00|          118.0|
15. |2020-01-01T00:00:22.000+08:00|          120.0|
16. |2020-01-01T00:00:26.000+08:00|          124.0|
17. |2020-01-01T00:00:28.000+08:00|          126.0|
18. |2020-01-01T00:00:30.000+08:00|            NaN|
19. +-----------------------------+---------------+

SQL for query:

1. select timeliness(s1) from root.test.d1 where time <= 2020-01-01 00:00:30

Output series:

1. +-----------------------------+---------------------------+
2. |                         Time|timeliness(root.test.d1.s1)|
3. +-----------------------------+---------------------------+
4. |2020-01-01T00:00:02.000+08:00|         0.9333333333333333|
5. +-----------------------------+---------------------------+

Specific Window Size

When the window size is given, this function will divide the input data as multiple windows.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:02.000+08:00|          100.0|
5. |2020-01-01T00:00:03.000+08:00|          101.0|
6. |2020-01-01T00:00:04.000+08:00|          102.0|
7. |2020-01-01T00:00:06.000+08:00|          104.0|
8. |2020-01-01T00:00:08.000+08:00|          126.0|
9. |2020-01-01T00:00:10.000+08:00|          108.0|
10. |2020-01-01T00:00:14.000+08:00|          112.0|
11. |2020-01-01T00:00:15.000+08:00|          113.0|
12. |2020-01-01T00:00:16.000+08:00|          114.0|
13. |2020-01-01T00:00:18.000+08:00|          116.0|
14. |2020-01-01T00:00:20.000+08:00|          118.0|
15. |2020-01-01T00:00:22.000+08:00|          120.0|
16. |2020-01-01T00:00:26.000+08:00|          124.0|
17. |2020-01-01T00:00:28.000+08:00|          126.0|
18. |2020-01-01T00:00:30.000+08:00|            NaN|
19. |2020-01-01T00:00:32.000+08:00|          130.0|
20. |2020-01-01T00:00:34.000+08:00|          132.0|
21. |2020-01-01T00:00:36.000+08:00|          134.0|
22. |2020-01-01T00:00:38.000+08:00|          136.0|
23. |2020-01-01T00:00:40.000+08:00|          138.0|
24. |2020-01-01T00:00:42.000+08:00|          140.0|
25. |2020-01-01T00:00:44.000+08:00|          142.0|
26. |2020-01-01T00:00:46.000+08:00|          144.0|
27. |2020-01-01T00:00:48.000+08:00|          146.0|
28. |2020-01-01T00:00:50.000+08:00|          148.0|
29. |2020-01-01T00:00:52.000+08:00|          150.0|
30. |2020-01-01T00:00:54.000+08:00|          152.0|
31. |2020-01-01T00:00:56.000+08:00|          154.0|
32. |2020-01-01T00:00:58.000+08:00|          156.0|
33. |2020-01-01T00:01:00.000+08:00|          158.0|
34. +-----------------------------+---------------+

SQL for query:

1. select timeliness(s1,"window"="15") from root.test.d1 where time <= 2020-01-01 00:01:00

Output series:

1. +-----------------------------+------------------------------------------+
2. |                         Time|timeliness(root.test.d1.s1, "window"="15")|
3. +-----------------------------+------------------------------------------+
4. |2020-01-01T00:00:02.000+08:00|                        0.9333333333333333|
5. |2020-01-01T00:00:32.000+08:00|                                       1.0|
6. +-----------------------------+------------------------------------------+

Validity

Registration statement

1. create function validity as 'org.apache.iotdb.library.dquality.UDTFValidity'

Usage

This function is used to calculate the Validity of time series. The input series are divided into several continuous and non overlapping windows. The timestamp of the first data point and the Validity of each window will be output.

Name: VALIDITY

Input Series: Only support a single input series. The type is INT32 / INT64 / FLOAT / DOUBLE.

Parameters:

window: The size of each window. It is a positive integer or a positive number with an unit. The former is the number of data points in each window. The number of data points in the last window may be less than it. The latter is the time of the window. The unit is ‘ms’ for millisecond, ‘s’ for second, ‘m’ for minute, ‘h’ for hour and ‘d’ for day. By default, all input data belongs to the same window.

Output Series: Output a single series. The type is DOUBLE. The range of each value is [0,1].

Note: Only when the number of data points in the window exceeds 10, the calculation will be performed. Otherwise, the window will be ignored and nothing will be output.

Examples

Default Parameters

With default parameters, this function will regard all input data as the same window.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:02.000+08:00|          100.0|
5. |2020-01-01T00:00:03.000+08:00|          101.0|
6. |2020-01-01T00:00:04.000+08:00|          102.0|
7. |2020-01-01T00:00:06.000+08:00|          104.0|
8. |2020-01-01T00:00:08.000+08:00|          126.0|
9. |2020-01-01T00:00:10.000+08:00|          108.0|
10. |2020-01-01T00:00:14.000+08:00|          112.0|
11. |2020-01-01T00:00:15.000+08:00|          113.0|
12. |2020-01-01T00:00:16.000+08:00|          114.0|
13. |2020-01-01T00:00:18.000+08:00|          116.0|
14. |2020-01-01T00:00:20.000+08:00|          118.0|
15. |2020-01-01T00:00:22.000+08:00|          120.0|
16. |2020-01-01T00:00:26.000+08:00|          124.0|
17. |2020-01-01T00:00:28.000+08:00|          126.0|
18. |2020-01-01T00:00:30.000+08:00|            NaN|
19. +-----------------------------+---------------+

SQL for query:

1. select Validity(s1) from root.test.d1 where time <= 2020-01-01 00:00:30

Output series:

1. +-----------------------------+-------------------------+
2. |                         Time|validity(root.test.d1.s1)|
3. +-----------------------------+-------------------------+
4. |2020-01-01T00:00:02.000+08:00|       0.8833333333333333|
5. +-----------------------------+-------------------------+

Specific Window Size

When the window size is given, this function will divide the input data as multiple windows.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:02.000+08:00|          100.0|
5. |2020-01-01T00:00:03.000+08:00|          101.0|
6. |2020-01-01T00:00:04.000+08:00|          102.0|
7. |2020-01-01T00:00:06.000+08:00|          104.0|
8. |2020-01-01T00:00:08.000+08:00|          126.0|
9. |2020-01-01T00:00:10.000+08:00|          108.0|
10. |2020-01-01T00:00:14.000+08:00|          112.0|
11. |2020-01-01T00:00:15.000+08:00|          113.0|
12. |2020-01-01T00:00:16.000+08:00|          114.0|
13. |2020-01-01T00:00:18.000+08:00|          116.0|
14. |2020-01-01T00:00:20.000+08:00|          118.0|
15. |2020-01-01T00:00:22.000+08:00|          120.0|
16. |2020-01-01T00:00:26.000+08:00|          124.0|
17. |2020-01-01T00:00:28.000+08:00|          126.0|
18. |2020-01-01T00:00:30.000+08:00|            NaN|
19. |2020-01-01T00:00:32.000+08:00|          130.0|
20. |2020-01-01T00:00:34.000+08:00|          132.0|
21. |2020-01-01T00:00:36.000+08:00|          134.0|
22. |2020-01-01T00:00:38.000+08:00|          136.0|
23. |2020-01-01T00:00:40.000+08:00|          138.0|
24. |2020-01-01T00:00:42.000+08:00|          140.0|
25. |2020-01-01T00:00:44.000+08:00|          142.0|
26. |2020-01-01T00:00:46.000+08:00|          144.0|
27. |2020-01-01T00:00:48.000+08:00|          146.0|
28. |2020-01-01T00:00:50.000+08:00|          148.0|
29. |2020-01-01T00:00:52.000+08:00|          150.0|
30. |2020-01-01T00:00:54.000+08:00|          152.0|
31. |2020-01-01T00:00:56.000+08:00|          154.0|
32. |2020-01-01T00:00:58.000+08:00|          156.0|
33. |2020-01-01T00:01:00.000+08:00|          158.0|
34. +-----------------------------+---------------+

SQL for query:

1. select Validity(s1,"window"="15") from root.test.d1 where time <= 2020-01-01 00:01:00

Output series:

1. +-----------------------------+----------------------------------------+
2. |                         Time|validity(root.test.d1.s1, "window"="15")|
3. +-----------------------------+----------------------------------------+
4. |2020-01-01T00:00:02.000+08:00|                      0.8833333333333333|
5. |2020-01-01T00:00:32.000+08:00|                                     1.0|
6. +-----------------------------+----------------------------------------+

Data Profiling

ACF

Registration statement

1. create function acf as 'org.apache.iotdb.library.dprofile.UDTFACF'

Usage

This function is used to calculate the auto-correlation factor of the input time series, which equals to cross correlation between the same series. For more information, please refer to XCorr function.

Name: ACF

Input Series: Only support a single input numeric series. The type is INT32 / INT64 / FLOAT / DOUBLE.

Output Series: Output a single series. The type is DOUBLE. There are 2N−12N-12N−1 data points in the series, and the values are interpreted in details in XCorr function.

Note:

null and NaN values in the input series will be ignored and treated as 0.

Examples

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:01.000+08:00|              1|
5. |2020-01-01T00:00:02.000+08:00|           null|
6. |2020-01-01T00:00:03.000+08:00|              3|
7. |2020-01-01T00:00:04.000+08:00|            NaN|
8. |2020-01-01T00:00:05.000+08:00|              5|
9. +-----------------------------+---------------+

SQL for query:

1. select acf(s1) from root.test.d1 where time <= 2020-01-01 00:00:05

Output series:

1. +-----------------------------+--------------------+
2. |                         Time|acf(root.test.d1.s1)|
3. +-----------------------------+--------------------+
4. |1970-01-01T08:00:00.001+08:00|                 1.0|
5. |1970-01-01T08:00:00.002+08:00|                 0.0|
6. |1970-01-01T08:00:00.003+08:00|                 3.6|
7. |1970-01-01T08:00:00.004+08:00|                 0.0|
8. |1970-01-01T08:00:00.005+08:00|                 7.0|
9. |1970-01-01T08:00:00.006+08:00|                 0.0|
10. |1970-01-01T08:00:00.007+08:00|                 3.6|
11. |1970-01-01T08:00:00.008+08:00|                 0.0|
12. |1970-01-01T08:00:00.009+08:00|                 1.0|
13. +-----------------------------+--------------------+

Distinct

Registration statement

1. create function distinct as 'org.apache.iotdb.library.dprofile.UDTFDistinct'

Usage

This function returns all unique values in time series.

Name: DISTINCT

Input Series: Only support a single input series. The type is arbitrary.

Output Series: Output a single series. The type is the same as the input.

Note:

The timestamp of the output series is meaningless. The output order is arbitrary.
Missing points and null points in the input series will be ignored, but NaN will not.
Case Sensitive.

Examples

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d2.s2|
3. +-----------------------------+---------------+
4. |2020-01-01T08:00:00.001+08:00|          Hello|
5. |2020-01-01T08:00:00.002+08:00|          hello|
6. |2020-01-01T08:00:00.003+08:00|          Hello|
7. |2020-01-01T08:00:00.004+08:00|          World|
8. |2020-01-01T08:00:00.005+08:00|          World|
9. +-----------------------------+---------------+

SQL for query:

1. select distinct(s2) from root.test.d2

Output series:

1. +-----------------------------+-------------------------+
2. |                         Time|distinct(root.test.d2.s2)|
3. +-----------------------------+-------------------------+
4. |1970-01-01T08:00:00.001+08:00|                    Hello|
5. |1970-01-01T08:00:00.002+08:00|                    hello|
6. |1970-01-01T08:00:00.003+08:00|                    World|
7. +-----------------------------+-------------------------+

Histogram

Registration statement

1. create function histogram as 'org.apache.iotdb.library.dprofile.UDTFHistogram'

Usage

This function is used to calculate the distribution histogram of a single column of numerical data.

Name: HISTOGRAM

Input Series: Only supports a single input sequence, the type is INT32 / INT64 / FLOAT / DOUBLE

Parameters:

min: The lower limit of the requested data range, the default value is -Double.MAX_VALUE.
max: The upper limit of the requested data range, the default value is Double.MAX_VALUE, and the value of start must be less than or equal to end.
count: The number of buckets of the histogram, the default value is 1. It must be a positive integer.

Output Series: The value of the bucket of the histogram, where the lower bound represented by the i-th bucket (index starts from 1) is min+(i−1)⋅max−mincountmin+ (i-1)\cdot\frac{max-min}{count}min+(i−1)⋅countmax−min and the upper bound is min+i⋅max−mincountmin + i \cdot \frac{max-min}{count}min+i⋅countmax−min.

Note:

If the value is lower than min, it will be put into the 1st bucket. If the value is larger than max, it will be put into the last bucket.
Missing points, null points and NaN in the input series will be ignored.

Examples

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:00.000+08:00|            1.0|
5. |2020-01-01T00:00:01.000+08:00|            2.0|
6. |2020-01-01T00:00:02.000+08:00|            3.0|
7. |2020-01-01T00:00:03.000+08:00|            4.0|
8. |2020-01-01T00:00:04.000+08:00|            5.0|
9. |2020-01-01T00:00:05.000+08:00|            6.0|
10. |2020-01-01T00:00:06.000+08:00|            7.0|
11. |2020-01-01T00:00:07.000+08:00|            8.0|
12. |2020-01-01T00:00:08.000+08:00|            9.0|
13. |2020-01-01T00:00:09.000+08:00|           10.0|
14. |2020-01-01T00:00:10.000+08:00|           11.0|
15. |2020-01-01T00:00:11.000+08:00|           12.0|
16. |2020-01-01T00:00:12.000+08:00|           13.0|
17. |2020-01-01T00:00:13.000+08:00|           14.0|
18. |2020-01-01T00:00:14.000+08:00|           15.0|
19. |2020-01-01T00:00:15.000+08:00|           16.0|
20. |2020-01-01T00:00:16.000+08:00|           17.0|
21. |2020-01-01T00:00:17.000+08:00|           18.0|
22. |2020-01-01T00:00:18.000+08:00|           19.0|
23. |2020-01-01T00:00:19.000+08:00|           20.0|
24. +-----------------------------+---------------+

SQL for query:

1. select histogram(s1,"min"="1","max"="20","count"="10") from root.test.d1

Output series:

1. +-----------------------------+---------------------------------------------------------------+
2. |                         Time|histogram(root.test.d1.s1, "min"="1", "max"="20", "count"="10")|
3. +-----------------------------+---------------------------------------------------------------+
4. |1970-01-01T08:00:00.000+08:00|                                                              2|
5. |1970-01-01T08:00:00.001+08:00|                                                              2|
6. |1970-01-01T08:00:00.002+08:00|                                                              2|
7. |1970-01-01T08:00:00.003+08:00|                                                              2|
8. |1970-01-01T08:00:00.004+08:00|                                                              2|
9. |1970-01-01T08:00:00.005+08:00|                                                              2|
10. |1970-01-01T08:00:00.006+08:00|                                                              2|
11. |1970-01-01T08:00:00.007+08:00|                                                              2|
12. |1970-01-01T08:00:00.008+08:00|                                                              2|
13. |1970-01-01T08:00:00.009+08:00|                                                              2|
14. +-----------------------------+---------------------------------------------------------------+

Integral

Registration statement

1. create function integral as 'org.apache.iotdb.library.dprofile.UDAFIntegral'

Usage

This function is used to calculate the integration of time series, which equals to the area under the curve with time as X-axis and values as Y-axis.

Name: INTEGRAL

Input Series: Only support a single input numeric series. The type is INT32 / INT64 / FLOAT / DOUBLE.

Parameters:

unit: The unit of time used when computing the integral. The value should be chosen from “1S”, “1s”, “1m”, “1H”, “1d”(case-sensitive), and each represents taking one millisecond / second / minute / hour / day as 1.0 while calculating the area and integral.

Output Series: Output a single series. The type is DOUBLE. There is only one data point in the series, whose timestamp is 0 and value is the integration.

Note:

The integral value equals to the sum of the areas of right-angled trapezoids consisting of each two adjacent points and the time-axis. Choosing different unit implies different scaling of time axis, thus making it apparent to convert the value among those results with constant coefficient.
NaN values in the input series will be ignored. The curve or trapezoids will skip these points and use the next valid point.

Examples

Default Parameters

With default parameters, this function will take one second as 1.0.

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:01.000+08:00|              1|
5. |2020-01-01T00:00:02.000+08:00|              2|
6. |2020-01-01T00:00:03.000+08:00|              5|
7. |2020-01-01T00:00:04.000+08:00|              6|
8. |2020-01-01T00:00:05.000+08:00|              7|
9. |2020-01-01T00:00:08.000+08:00|              8|
10. |2020-01-01T00:00:09.000+08:00|            NaN|
11. |2020-01-01T00:00:10.000+08:00|             10|
12. +-----------------------------+---------------+

SQL for query:

1. select integral(s1) from root.test.d1 where time <= 2020-01-01 00:00:10

Output series:

1. +-----------------------------+-------------------------+
2. |                         Time|integral(root.test.d1.s1)|
3. +-----------------------------+-------------------------+
4. |1970-01-01T08:00:00.000+08:00|                     57.5|
5. +-----------------------------+-------------------------+

Calculation expression:

12[(1+2)×1+(2+5)×1+(5+6)×1+(6+7)×1+(7+8)×3+(8+10)×2]\=57.5\frac{1}{2}[(1+2) \times 1 + (2+5) \times 1 + (5+6) \times 1 + (6+7) \times 1 + (7+8) \times 3 + (8+10) \times 2] = 57.5 21[(1+2)×1+(2+5)×1+(5+6)×1+(6+7)×1+(7+8)×3+(8+10)×2]\=57.5

Specific time unit

With time unit specified as “1m”, this function will take one minute as 1.0.

Input series is the same as above, the SQL for query is shown below:

1. select integral(s1, "unit"="1m") from root.test.d1 where time <= 2020-01-01 00:00:10

Output series:

1. +-----------------------------+-------------------------+
2. |                         Time|integral(root.test.d1.s1)|
3. +-----------------------------+-------------------------+
4. |1970-01-01T08:00:00.000+08:00|                    0.958|
5. +-----------------------------+-------------------------+

Calculation expression:

12×60[(1+2)×1+(2+5)×1+(5+6)×1+(6+7)×1+(7+8)×3+(8+10)×2]\=0.958\frac{1}{2\times 60}[(1+2) \times 1 + (2+5) \times 1 + (5+6) \times 1 + (6+7) \times 1 + (7+8) \times 3 + (8+10) \times 2] = 0.958 2×601[(1+2)×1+(2+5)×1+(5+6)×1+(6+7)×1+(7+8)×3+(8+10)×2]\=0.958

IntegralAvg

Registration statement

1. create function integralavg as 'org.apache.iotdb.library.dprofile.UDAFIntegralAvg'

Usage

This function is used to calculate the function average of time series. The output equals to the area divided by the time interval using the same time unit. For more information of the area under the curve, please refer to Integral function.

Name: INTEGRALAVG

Input Series: Only support a single input numeric series. The type is INT32 / INT64 / FLOAT / DOUBLE.

Output Series: Output a single series. The type is DOUBLE. There is only one data point in the series, whose timestamp is 0 and value is the time-weighted average.

Note:

The time-weighted value equals to the integral value with any unit divided by the time interval of input series. The result is irrelevant to the time unit used in integral, and it’s consistent with the timestamp precision of IoTDB by default.
NaN values in the input series will be ignored. The curve or trapezoids will skip these points and use the next valid point.
If the input series is empty, the output value will be 0.0, but if there is only one data point, the value will equal to the input value.

Examples

Input series:

1. +-----------------------------+---------------+
2. |                         Time|root.test.d1.s1|
3. +-----------------------------+---------------+
4. |2020-01-01T00:00:01.000+08:00|              1|
5. |2020-01-01T00:00:02.000+08:00|              2|
6. |2020-01-01T00:00:03.000+08:00|              5|
7. |2020-01-01T00:00:04.000+08:00|              6|
8. |2020-01-01T00:00:05.000+08:00|              7|
9. |2020-01-01T00:00:08.000+08:00|              8|
10. |2020-01-01T00:00:09.000+08:00|            NaN|
11. |2020-01-01T00:00:10.000+08:00|             10|
12. +-----------------------------+---------------+

SQL for query:

1. select integralavg(s1) from root.test.d1 where time <= 2020-01-01 00:00:10

Output series:

1. +-----------------------------+----------------------------+
2. |                         Time|integralavg(root.test.d1.s1)|
3. +-----------------------------+----------------------------+
4. |1970-01-01T08:00:00.000+08:00|                        5.75|
5. +-----------------------------+----------------------------+

Calculation expression:

12[(1+2)×1+(2+5)×1+(5+6)×1+(6+7)×1+(7+8)×3+(8+10)×2]/10\=5.75\frac{1}{2}[(1+2) \times 1 + (2+5) \times 1 + (5+6) \times 1 + (6+7) \times 1 + (7+8) \times 3 + (8+10) \times 2] / 10 = 5.75 21[(1+2)×1+(2+5)×1+(5+6)×1+(6+7)×1+(7+8)×3+(8+10)×2]/10\=5.75

Mad

Registration statement

1. create function mad as 'org.apache.iotdb.library.dprofile.UDAFMad'

Usage

The function is used to compute the exact or approximate median absolute deviation (MAD) of a numeric time series. MAD is the median of the deviation of each element from the elements’ median.

Take a dataset {1,3,3,5,5,6,7,8,9}{1,3,3,5,5,6,7,8,9}{1,3,3,5,5,6,7,8,9} as an instance. Its median is 5 and the deviation of each element from the median is {0,0,1,2,2,2,3,4,4}{0,0,1,2,2,2,3,4,4}{0,0,1,2,2,2,3,4,4}, whose median is 2. Therefore, the MAD of the original dataset is 2.

Name: MAD

Input Series: Only support a single input series. The data type is INT32 / INT64 / FLOAT / DOUBLE.

Parameter:

error: The relative error of the approximate MAD. It should be within [0,1) and the default value is 0. Taking error\=0.01 as an instance, suppose the exact MAD is aaa and the approximate MAD is bbb, we have 0.99a≤b≤1.01a0.99a \le b \le 1.01a0.99a≤b≤1.01a. With error\=0, the output is the exact MAD.

Output Series: Output a single series. The type is DOUBLE. There is only one data point in the series, whose timestamp is 0 and value is the MAD.

Note: Missing points, null points and NaN in the input series will be ignored.

Examples

Exact Query

With the default error(error\=0), the function queries the exact MAD.

Input series:

1. +-----------------------------+------------+
2. |                         Time|root.test.s1|
3. +-----------------------------+------------+
4. |1970-01-01T08:00:00.100+08:00|         0.0|
5. |1970-01-01T08:00:00.200+08:00|         0.0|
6. |1970-01-01T08:00:00.300+08:00|         1.0|
7. |1970-01-01T08:00:00.400+08:00|        -1.0|
8. |1970-01-01T08:00:00.500+08:00|         0.0|
9. |1970-01-01T08:00:00.600+08:00|         0.0|
10. |1970-01-01T08:00:00.700+08:00|        -2.0|
11. |1970-01-01T08:00:00.800+08:00|         2.0|
12. |1970-01-01T08:00:00.900+08:00|         0.0|
13. |1970-01-01T08:00:01.000+08:00|         0.0|
14. |1970-01-01T08:00:01.100+08:00|         1.0|
15. |1970-01-01T08:00:01.200+08:00|        -1.0|
16. |1970-01-01T08:00:01.300+08:00|        -1.0|
17. |1970-01-01T08:00:01.400+08:00|         1.0|
18. |1970-01-01T08:00:01.500+08:00|         0.0|
19. |1970-01-01T08:00:01.600+08:00|         0.0|
20. |1970-01-01T08:00:01.700+08:00|        10.0|
21. |1970-01-01T08:00:01.800+08:00|         2.0|
22. |1970-01-01T08:00:01.900+08:00|        -2.0|
23. |1970-01-01T08:00:02.000+08:00|         0.0|
24. +-----------------------------+------------+
25. ............
26. Total line number = 20

SQL for query:

1. select mad(s1) from root.test

Output series:

1. +-----------------------------+---------------------------------+
2. |                         Time|median(root.test.s1, "error"="0")|
3. +-----------------------------+---------------------------------+
4. |1970-01-01T08:00:00.000+08:00|                              0.0|
5. +-----------------------------+---------------------------------+

Approximate Query

By setting error within (0,1), the function queries the approximate MAD.

SQL for query:

1. select mad(s1, "error"="0.01") from root.test

Output series:

1. +-----------------------------+---------------------------------+
2. |                         Time|mad(root.test.s1, "error"="0.01")|
3. +-----------------------------+---------------------------------+
4. |1970-01-01T08:00:00.000+08:00|               0.9900000000000001|
5. +-----------------------------+---------------------------------+

Median

Registration statement

1. create function median as 'org.apache.iotdb.library.dprofile.UDAFMedian'

Usage

The function is used to compute the exact or approximate median of a numeric time series. Median is the value separating the higher half from the lower half of a data sample.

Name: MEDIAN

Input Series: Only support a single input series. The data type is INT32 / INT64 / FLOAT / DOUBLE.

Parameter:

error: The rank error of the approximate median. It should be within [0,1) and the default value is 0. For instance, a median with error\=0.01 is the value of the element with rank percentage 0.49~0.51. With error\=0, the output is the exact median.

Output Series: Output a single series. The type is DOUBLE. There is only one data point in the series, whose timestamp is 0 and value is the median.

Examples

Input series:

1. +-----------------------------+------------+
2. |                         Time|root.test.s1|
3. +-----------------------------+------------+
4. |1970-01-01T08:00:00.100+08:00|         0.0|
5. |1970-01-01T08:00:00.200+08:00|         0.0|
6. |1970-01-01T08:00:00.300+08:00|         1.0|
7. |1970-01-01T08:00:00.400+08:00|        -1.0|
8. |1970-01-01T08:00:00.500+08:00|         0.0|
9. |1970-01-01T08:00:00.600+08:00|         0.0|
10. |1970-01-01T08:00:00.700+08:00|        -2.0|
11. |1970-01-01T08:00:00.800+08:00|         2.0|
12. |1970-01-01T08:00:00.900+08:00|         0.0|
13. |1970-01-01T08:00:01.000+08:00|         0.0|
14. |1970-01-01T08:00:01.100+08:00|         1.0|
15. |1970-01-01T08:00:01.200+08:00|        -1.0|
16. |1970-01-01T08:00:01.300+08:00|        -1.0|
17. |1970-01-01T08:00:01.400+08:00|         1.0|
18. |1970-01-01T08:00:01.500+08:00|         0.0|
19. |1970-01-01T08:00:01.600+08:00|         0.0|
20. |1970-01-01T08:00:01.700+08:00|        10.0|
21. |1970-01-01T08:00:01.800+08:00|         2.0|
22. |1970-01-01T08:00:01.900+08:00|        -2.0|
23. |1970-01-01T08:00:02.000+08:00|         0.0|
24. +-----------------------------+------------+
25. Total line number = 20

SQL for query:

1. select median(s1, "error"="0.01") from root.test

Output series:

1. +-----------------------------+------------------------------------+
2. |                         Time|median(root.test.s1, "error"="0.01")|
3. +-----------------------------+------------------------------------+
4. |1970-01-01T08:00:00.000+08:00|                                 0.0|
5. +-----------------------------+------------------------------------+

MinMax

Registration statement

1. create function minmax as 'org.apache.iotdb.library.dprofile.UDTFMinMax'

Usage

This function is used to standardize the input series with min-max. Minimum value is transformed to 0; maximum value is

（本文超出站点单文档体积上限，已截断）