Aggregate functions group multiple input records together to form a single output record. The input and output records have a many-to-one relationship. This topic describes the syntax, parameters, and examples of aggregate functions supported by the data analysis feature.
AVG
Syntax
double avg(double <colname>) decimal avg(decimal <colname>)Description
Returns the average value of a column.
Parameters
value: required. The value of this parameter can be of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into the DOUBLE type before calculation. If the input value is of another data type, an error is returned. If the value of this parameter is NULL, the value is used for calculation. Values of the BOOLEAN type cannot be used for calculation.
Return value
If the input value is of the DECIMAL type, a value of the DECIMAL type is returned. If the input value is of another valid data type, a value of the DOUBLE type is returned.
Example
Calculate the average temperature value of the current day.
avg(temperature);
COUNT
Syntax
bigint count([distinct|all] <value>)Description
Returns the number of records that match the specified criteria.
Parameters
distinct|all: optional. This parameter specifies whether to remove duplicates during the count operation. The default value is all, which specifies that all records are counted. If this parameter is set to distinct, only records that have distinct values are counted.
value: required. The value of this parameter can be of any data type. If a value in the specified column is NULL, the row that contains this value is not included in the calculation.
Return value
A value of the BIGINT type is returned.
Examples
Example 1: Calculate the number of temperature records on the current day.
count(temperature);Example 2: Use distinct to deduplicate temperature records by temperate value and calculate the number of temperature values.
count(distinct temperature);
MAX
Syntax
max(<colname>)Description
Returns the maximum value of a column.
Parameters
value: required. The name of a column. The column can be of any data type. If a value in the specified column is NULL, the row that contains this value is not included in the calculation. The values of the BOOLEAN type are not used for the calculation.
Return value
This function returns a value of the same type as value.
Example
Calculate the maximum temperature value among the temperature records of the current day.
max(temperature);
MIN
Syntax
min(<value>)Description
Returns the minimum value of a column.
Parameters
value: required. The name of a column. The column can be of any data type. If a value in the specified column is NULL, the row that contains this value is not included in the calculation. The values of the BOOLEAN type are not used for the calculation.
Return value
This function returns a value of the same type as value.
Example
Calculate the minimum temperature value among the temperature records of the current day.
min(temperature);
SUM
Syntax
sum(<value>)Description
Returns the sum of a column.
Parameters
value: required. The name of a column. The column is of the DOUBLE, DECIMAL, or BIGINT type. If the input value is of the STRING type, the value is implicitly converted into the DOUBLE type before calculation. If a value in the specified column is NULL, the row that contains this value is not included in the calculation. The values of the BOOLEAN type are not used for the calculation.
Return value
If the input parameter is of the BIGINT type, a value of the BIGINT type is returned. If the input parameter is of the DOUBLE or STRING type, a value of the DOUBLE type is returned.
Example
Calculates the sum of all temperature values on the current day.
sum(temperature);