Skip to main content

Aggregate Functions

About 5 min

Aggregate Functions

Aggregate functions are many-to-one functions. They perform aggregate calculations on a set of values, resulting in a single aggregated result.

All aggregate functions except COUNT(), COUNT_IF() 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.

The aggregate functions supported by IoTDB are as follows:

Function NameDescriptionAllowed Input Series Data TypesRequired AttributesOutput Series Data Type
SUMSummation.INT32 INT64 FLOAT DOUBLE/DOUBLE
COUNTCounts the number of data points.All data 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
COUNT_TIMEThe number of timestamps in the query data set. When used with align by device, the result is the number of timestamps in the data set per device.All data Types, the input parameter can only be */INT64

COUNT

example

select count(status) from root.ln.wf01.wt01;

Result:

+-------------------------------+
|count(root.ln.wf01.wt01.status)|
+-------------------------------+
|                          10080|
+-------------------------------+
Total line number = 1
It costs 0.016s

COUNT_IF

Grammar

count_if(predicate, [keep >=/>/=/!=/</<=]threshold[, 'ignoreNull'='true/false'])

predicate: legal expression with BOOLEAN return type

use of threshold and ignoreNull can see above table

Note: count_if is not supported to use with SlidingWindow in group by time now

example

raw data

+-----------------------------+-------------+-------------+
|                         Time|root.db.d1.s1|root.db.d1.s2|
+-----------------------------+-------------+-------------+
|1970-01-01T08:00:00.001+08:00|            0|            0|
|1970-01-01T08:00:00.002+08:00|         null|            0|
|1970-01-01T08:00:00.003+08:00|            0|            0|
|1970-01-01T08:00:00.004+08:00|            0|            0|
|1970-01-01T08:00:00.005+08:00|            1|            0|
|1970-01-01T08:00:00.006+08:00|            1|            0|
|1970-01-01T08:00:00.007+08:00|            1|            0|
|1970-01-01T08:00:00.008+08:00|            0|            0|
|1970-01-01T08:00:00.009+08:00|            0|            0|
|1970-01-01T08:00:00.010+08:00|            0|            0|
+-----------------------------+-------------+-------------+

Not use ignoreNull attribute (Ignore Null)

SQL:

select count_if(s1=0 & s2=0, 3), count_if(s1=1 & s2=0, 3) from root.db.d1

Result:

+--------------------------------------------------+--------------------------------------------------+
|count_if(root.db.d1.s1 = 0 & root.db.d1.s2 = 0, 3)|count_if(root.db.d1.s1 = 1 & root.db.d1.s2 = 0, 3)|
+--------------------------------------------------+--------------------------------------------------+
|                                                 2|                                                 1|
+--------------------------------------------------+--------------------------------------------------

Use ignoreNull attribute

SQL:

select count_if(s1=0 & s2=0, 3, 'ignoreNull'='false'), count_if(s1=1 & s2=0, 3, 'ignoreNull'='false') from root.db.d1

Result:

+------------------------------------------------------------------------+------------------------------------------------------------------------+
|count_if(root.db.d1.s1 = 0 & root.db.d1.s2 = 0, 3, "ignoreNull"="false")|count_if(root.db.d1.s1 = 1 & root.db.d1.s2 = 0, 3, "ignoreNull"="false")|
+------------------------------------------------------------------------+------------------------------------------------------------------------+
|                                                                       1|                                                                       1|
+------------------------------------------------------------------------+------------------------------------------------------------------------+

TIME_DURATION

Grammar

    time_duration(Path)

Example

raw data

+----------+-------------+
|      Time|root.db.d1.s1|
+----------+-------------+
|         1|           70|
|         3|           10|
|         4|          303|
|         6|          110|
|         7|          302|
|         8|          110|
|         9|           60|
|        10|           70|
|1677570934|           30|
+----------+-------------+

Insert sql

"CREATE DATABASE root.db",
"CREATE TIMESERIES root.db.d1.s1 WITH DATATYPE=INT32, ENCODING=PLAIN tags(city=Beijing)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(1, 2, 10, true)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(2, null, 20, true)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(3, 10, 0, null)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(4, 303, 30, true)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(5, null, 20, true)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(6, 110, 20, true)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(7, 302, 20, true)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(8, 110, null, true)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(9, 60, 20, true)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(10,70, 20, null)",
"INSERT INTO root.db.d1(timestamp,s1,s2,s3) values(1677570934, 30, 0, true)",

SQL:

select time_duration(s1) from root.db.d1

Result:

+----------------------------+
|time_duration(root.db.d1.s1)|
+----------------------------+
|                  1677570933|
+----------------------------+

Note: Returns 0 if there is only one data point, or null if the data point is null.

COUNT_TIME

Grammar

    count_time(*)

Example

raw data

+----------+-------------+-------------+-------------+-------------+
|      Time|root.db.d1.s1|root.db.d1.s2|root.db.d2.s1|root.db.d2.s2|
+----------+-------------+-------------+-------------+-------------+
|         0|            0|         null|         null|            0|
|         1|         null|            1|            1|         null|
|         2|         null|            2|            2|         null|
|         4|            4|         null|         null|            4|
|         5|            5|            5|            5|            5|
|         7|         null|            7|            7|         null|
|         8|            8|            8|            8|            8|
|         9|         null|            9|         null|         null|
+----------+-------------+-------------+-------------+-------------+

Insert sql

CREATE DATABASE root.db;
CREATE TIMESERIES root.db.d1.s1 WITH DATATYPE=INT32, ENCODING=PLAIN;
CREATE TIMESERIES root.db.d1.s2 WITH DATATYPE=INT32, ENCODING=PLAIN;
CREATE TIMESERIES root.db.d2.s1 WITH DATATYPE=INT32, ENCODING=PLAIN;
CREATE TIMESERIES root.db.d2.s2 WITH DATATYPE=INT32, ENCODING=PLAIN;
INSERT INTO root.db.d1(time, s1) VALUES(0, 0), (4,4), (5,5), (8,8);
INSERT INTO root.db.d1(time, s2) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8), (9,9);
INSERT INTO root.db.d2(time, s1) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8);
INSERT INTO root.db.d2(time, s2) VALUES(0, 0), (4,4), (5,5), (8,8);

Query-Example - 1:

select count_time(*) from root.db.**

Result

+-------------+
|count_time(*)|
+-------------+
|            8|
+-------------+

Query-Example - 2:

select count_time(*) from root.db.d1, root.db.d2

Result

+-------------+
|count_time(*)|
+-------------+
|            8|
+-------------+

Query-Example - 3:

select count_time(*) from root.db.** group by([0, 10), 2ms)

Result

+-----------------------------+-------------+
|                         Time|count_time(*)|
+-----------------------------+-------------+
|1970-01-01T08:00:00.000+08:00|            2|            
|1970-01-01T08:00:00.002+08:00|            1|            
|1970-01-01T08:00:00.004+08:00|            2|            
|1970-01-01T08:00:00.006+08:00|            1|            
|1970-01-01T08:00:00.008+08:00|            2|            
+-----------------------------+-------------+

Query-Example - 4:

select count_time(*) from root.db.** group by([0, 10), 2ms) align by device

Result

+-----------------------------+----------+-------------+
|                         Time|    Device|count_time(*)|
+-----------------------------+----------+-------------+
|1970-01-01T08:00:00.000+08:00|root.db.d1|            2|
|1970-01-01T08:00:00.002+08:00|root.db.d1|            1|
|1970-01-01T08:00:00.004+08:00|root.db.d1|            2|
|1970-01-01T08:00:00.006+08:00|root.db.d1|            1|
|1970-01-01T08:00:00.008+08:00|root.db.d1|            2|
|1970-01-01T08:00:00.000+08:00|root.db.d2|            2|
|1970-01-01T08:00:00.002+08:00|root.db.d2|            1|
|1970-01-01T08:00:00.004+08:00|root.db.d2|            2|
|1970-01-01T08:00:00.006+08:00|root.db.d2|            1|
|1970-01-01T08:00:00.008+08:00|root.db.d2|            1|
+-----------------------------+----------+-------------+

Note:

  1. The parameter in count_time can only be *.
  2. Count_time aggregation cannot be used with other aggregation functions.
  3. Count_time aggregation used with having statement is not supported, and count_time aggregation can not appear in the having statement.
  4. Count_time does not support use with group by level, group by tag.

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.