This topic describes the aggregate functions used in AnalyticDB for MySQL.
- AVG: calculates the average value.
- BIT_AND: returns the result of bitwise AND operations for all bits of the argument.
- BIT_OR: returns the result of bitwise OR operations for all bits of the argument.
- BIT_XOR: returns the result of bitwise XOR operations for all bits of the argument.
- COUNT: calculates the number of records.
- MAX: calculates the maximum value.
- MIN: calculates the minimum value.
- STD/STDDEV: returns the sample standard deviation of all input values.
- STDDEV_POP: returns the population standard deviation of all input values.
- STDDEV_SAMP: returns the population standard deviation for a group of integers, decimals, or floating-point numbers.
- SUM: calculates the sum of all input values.
- VAR_POP: returns the population variance for a group of integers, decimals, or floating-point numbers.
- VAR_SAMP: returns the sample variance for a group of integers, decimals, or floating-point numbers.
- VARIANCE: returns the population variance for a group of integers, decimals, or floating-point numbers.
The aggregate functions in this topic use the testtable
table as test data.
create table testtable(a int) distributed by hash(a);
insert into testtable values (1),(2),(3);
AVG
avg(bigint x)
avg(double x)
avg(float x)
- Description: This function calculates the average value.
- Return value type: DOUBLE.
- Example:
select avg(a) from testtable; +--------+ | avg(a) | +--------+ | 2.0 |
BIT_AND
bit_and(float x)
bit_and(bigint x)
bit_and(double x)
- Description: This function returns the result of bitwise
AND
operations for all bits of the argument. - Return value type: BIGINT.
- Example:
select bit_and(a) from testtable; +------------+ | bit_and(a) | +------------+ | 0 |
BIT_OR
bit_or(float x)
bit_or(bigint x)
bit_or(double x)
- Description: This function returns the result of bitwise
OR
operations for all bits of the argument. - Return value type: BIGINT.
- Example:
select bit_or(a) from testtable; +----------------------------+ | bit_or(a) | +----------------------------+ | 3 |
BIT_XOR
bit_xor(double x)
bit_xor(bigint x)
bit_xor(float x)
- Description: This function returns the result of bitwise XOR operations for all bits of the argument.
- Return value type: BIGINT.
- Example:
select bit_xor(a) from testtable; +----------------------------+ | bit_xor(a) | +----------------------------+ | 0 |
COUNT
count([distinct|all] value x)
- Description: This function counts the number of records.
distinct
andall
specify whether to exclude duplicate records in the counting process. The default value isall
, which indicates that all records are counted. Ifdistinct
is specified, only records with distinct values are counted. - Return value type: BIGINT.
- Example:
select count(distinct a) from testtable; +-------------------+ | count(DISTINCT a) | +-------------------+ | 3 |
MAX
max(value x)
- Description: This function calculates the maximum value.
value
can be of any data type exceptBOOLEAN
.If the value for a row in the specified column is
null
, this row is ignored. - Return value type: LONG.
- Example:
select max(a) from testtable; +--------+ | max(a) | +--------+ | 3 |
MIN
min(value x)
- Description: This function calculates the minimum value.
value
can be of any data type exceptBOOLEAN
.If the value for a row in the specified column is
null
, this row is ignored. - Return value type: LONG.
- Example:
select min(a) from testtable; +--------+ | min(a) | +--------+ | 1 |
STD/STDDEV
std(double x)
std(bigint x)
stddev(double x)
stddev(bigint x)
- Description: This function returns the sample standard deviation of all input values.
- Return value type: DOUBLE.
- Example:
select std(a) from testtable; +-------------------+ | std(a) | +-------------------+ | 0.816496580927726 |
STDDEV_POP
stddev_pop(double x)
stddev_pop(bigint x)
- Description: This function returns the population standard deviation of all input values.
- Return value type: DOUBLE.
- Example:
select stddev_pop(a) from testtable; +----------------------------+ | stddev_pop(a) | +----------------------------+ | 0.816496580927726 |
STDDEV_SAMP
stddev_samp(double x)
stddev_samp(bigint x)
- Description: This function returns the population standard deviation for a group of integers, decimals, or floating-point numbers.
- Return value type: DOUBLE.
- Example:
select stddev_samp(a) from testtable; +----------------------------+ | stddev_samp(a) | +----------------------------+ | 1.0 |
SUM
sum(double x)
sum(float x)
sum(bigint x)
- Description: This function calculates the sum of all input values.
- Return value type: BIGINT.
- Example:
select sum(a) from testtable; +----------------------------+ | sum(a) | +----------------------------+ | 6 |
VAR_POP
var_pop(double x)
var_pop(bigint x)
- Description: This function returns the population variance for a group of integers, decimals, or floating-point numbers. You can also use the VARIANCE() function, which has the same meaning as the VAR_POP function. However, the VARIANCE() function is not a standard SQL function. If no matches are found, VAR_POP() returns the value NULL.
- Return value type: DOUBLE.
- Example:
select var_pop(a) from testtable; +----------------------------+ | var_pop(a) | +----------------------------+ | 0.6666666666666666 |
VAR_SAMP
var_samp(double x)
var_samp(bigint x)
- Description: This function returns the sample variance for a group of integers, decimals, or floating-point numbers.
- Return value type: DOUBLE.
- Example:
select var_samp(a) from testtable; +----------------------------+ | var_samp(a) | +----------------------------+ | 1.0 |
VARIANCE
variance(double x)
variance(bigint x)
- Description: This function returns the population standard variance for a group of integers, decimals, or floating-point numbers. The VARIANCE() function is an extension to standard SQL and can be replaced by the standard SQL function VAR_POP(). If no matches are found, VARIANCE() returns the value NULL.
- Return value type: DOUBLE.
- Example:
select variance(a) from testtable; +----------------------------+ | variance(a) | +----------------------------+ | 0.6666666666666666 |