| <!-- |
| |
| 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. |
| |
| --> |
| |
| # 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 Name | Function Description | Allowed Input Data Types | Output Data Types | |
| | ------------- |------------------------------------------------------------------------------------------------------------------------------------------------------| ------------------------ |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | SUM | Summation. | INT32 INT64 FLOAT DOUBLE | DOUBLE | |
| | COUNT | Counts the number of data points. | All types | INT | |
| | AVG | Average. | INT32 INT64 FLOAT DOUBLE | DOUBLE | |
| | EXTREME | Finds 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_VALUE | Find the maximum value. | INT32 INT64 FLOAT DOUBLE | Consistent with the input data type | |
| | MIN_VALUE | Find the minimum value. | INT32 INT64 FLOAT DOUBLE | Consistent with the input data type | |
| | FIRST_VALUE | Find the value with the smallest timestamp. | All data types | Consistent with input data type | |
| | LAST_VALUE | Find the value with the largest timestamp. | All data types | Consistent with input data type | |
| | MAX_TIME | Find the maximum timestamp. | All data Types | Timestamp | |
| | MIN_TIME | Find the minimum timestamp. | All data Types | Timestamp | |
| | COUNT_IF | Find 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`<br/> `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 broken | INT64 | |
| |
| ## COUNT |
| |
| ### example |
| |
| ```sql |
| 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 |
| ```sql |
| 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: |
| ```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: |
| ```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| |
| +------------------------------------------------------------------------+------------------------------------------------------------------------+ |
| ``` |
