This topic describes the aggregate functions supported by AnalyticDB for MySQL.
AnalyticDB for MySQL supports the following aggregate functions:
- ARBITRARY: randomly returns a value from a group of data.
- AVG: calculates the average value for a set of values.
- 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: counts the number of records returned by a SELECT query.
- MAX: calculates the maximum value.
- MIN: calculates the minimum value.
- STD or 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 sample standard deviation for a group of integers, decimals, or floating-point numbers.
- SUM: calculates the sum of all input values.
- VARIANCE (non-standard SQL function): returns the population variance for a group of integers, decimals, or floating-point numbers.
- VAR_POP (standard SQL function): 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.
- GROUP_CONCAT: returns a string result of the concatenated values from a group. The values are
the returned results of the
GROUP BY
clause.
Note
In this topic, a table named
testtable
is used for aggregate functions with the exception of the GROUP_CONCAT
function. The following statement shows how to create the table:
CREATE TABLE testtable(a INT) DISTRIBUTED BY HASH(a);
The following statement is used to insert test data into the testtable
table:
INSERT INTO testtable VALUES (1),(2),(3);
ARBITRARY
arbitrary(x)
- Description: This function randomly returns a value from a group of data.
- Data type of the input value: all data types.
- Data type of the return value: consistent with the data type of the input value of this function.
- Example:
SELECT arbitrary(a) FROM testtable;
The following result is returned:+--------------+ | arbitrary(a) | +--------------+ | 2 | +--------------+
AVG
avg(x)
- Description: This function calculates the average value for a set of values.
- Data type of the input value: BIGINT, DOUBLE, or FLOAT.
- Data type of the return value: DOUBLE.
- Example:
SELECT avg(a) FROM testtable;
The following result is returned:+--------+ | avg(a) | +--------+ | 2.0 | +--------+
BIT_AND
bit_and(x)
- Description: This function returns the result of bitwise
AND
operations for all bits of the argument. - Data type of the input value: BIGINT, DOUBLE, or FLOAT.
- Data type of the return value: BIGINT.
- Example:
SELECT bit_and(a) FROM testtable;
The following result is returned:+------------+ | bit_and(a) | +------------+ | 0 | +------------+
BIT_OR
bit_or(x)
- Description: This function returns the result of bitwise
OR
operations for all bits of the argument. - Data type of the input value: BIGINT, DOUBLE, or FLOAT.
- Data type of the return value: BIGINT.
- Example:
SELECT bit_or(a) FROM testtable;
The following result is returned:+-----------+ | bit_or(a) | +-----------+ | 3 | +-----------+
BIT_XOR
bit_xor(x)
- Description: This function returns the result of bitwise XOR operations for all bits of the argument.
- Data type of the input value: BIGINT, DOUBLE, or FLOAT.
- Data type of the return value: BIGINT.
- Example:
SELECT bit_xor(a) FROM testtable;
The following result is returned:+------------+ | bit_xor(a) | +------------+ | 0 | +------------+
COUNT
count([distinct|all] x)
- Description: This function counts the number of records returned by a SELECT query.
Note
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. - Data type of the input value: NUMERIC, STRING, or BOOLEAN.
- Data type of the return value: BIGINT.
- Examples:
- Calculate the number of records whose values are unique in
testtable
. Sample statement:SELECT count(distinct a) FROM testtable;
The following result is returned:+-------------------+ | count(distinct a) | +-------------------+ | 3 | +-------------------+
- Calculate the number of all records in
testtable
. Sample statement:SELECT count(all a) FROM testtable;
The following result is returned:+--------------+ | count(all a) | +--------------+ | 3 | +--------------+
- Calculate the number of records whose values are unique in
MAX
max(x)
- Description: This function calculates the maximum value.
- Data type of the input value: all data types. However, data of the BOOLEAN type is
not included in the calculation.
Note If a value in the specified column is
NULL
, the row that contains this value is not included in the calculation. - Data type of the return value: consistent with the data type of the input value of this function.
- Example:
SELECT max(a) FROM testtable;
The following result is returned:+--------+ | max(a) | +--------+ | 3 | +--------+
MIN
min(value x)
- Description: This function calculates the minimum value.
- Data type of the input value: all data types. However, data of the BOOLEAN type is
not included in the calculation.
Note If a value in the specified column is
NULL
, the row that contains this value is not included in the calculation. - Data type of the return value: consistent with the data type of the input value of this function.
- Example:
SELECT min(a) FROM testtable;
The following result is returned:+--------+ | min(a) | +--------+ | 1 | +--------+
STD or STDDEV
std(x)
stddev(x)
- Description: This function returns the sample standard deviation of all input values.
- Data type of the input value: BIGINT or DOUBLE.
- Data type of the return value: DOUBLE.
- Example:
SELECT std(a) FROM testtable;
The following result is returned:+-------------------+ | std(a) | +-------------------+ | 0.816496580927726 | +-------------------+
STDDEV_POP
stddev_pop(x)
- Description: This function returns the population standard deviation of all input values.
- Data type of the input value: BIGINT or DOUBLE.
- Data type of the return value: DOUBLE.
- Example:
SELECT stddev_pop(a) FROM testtable;
The following result is returned:+-------------------+ | stddev_pop(a) | +-------------------+ | 0.816496580927726 | +-------------------+
STDDEV_SAMP
stddev_samp(x)
- Description: This function returns the sample standard deviation for a group of integers, decimals, or floating-point numbers.
- Data type of the input value: BIGINT or DOUBLE.
- Data type of the return value: DOUBLE.
- Example:
SELECT stddev_samp(a) FROM testtable;
The following result is returned:+----------------+ | stddev_samp(a) | +----------------+ | 1.0 | +----------------+
SUM
sum(x)
- Description: This function calculates the sum of all input values.
- Data type of the input value: BIGINT, DOUBLE, or FLOAT.
- Data type of the return value: BIGINT.
- Example:
SELECT sum(a) FROM testtable;
The following result is returned:+--------+ | sum(a) | +--------+ | 6 | +--------+
VARIANCE
variance(x)
- Description: This function returns the population standard variance for a group of
integers, decimals, or floating-point numbers.
Note
VARIANCE()
ignores rows with NULL values. If a group of values are all NULL,VARIANCE()
directly returns NULL.- The
VARIANCE()
function is an extension to standard SQL and can be replaced by the standard SQL functionVAR_POP()
.
- Data type of the input value: BIGINT or DOUBLE.
- Data type of the return value: DOUBLE.
- Example:
SELECT variance(a) FROM testtable; +----------------------------+ | variance(a) | +----------------------------+ | 0.6666666666666666 |
VAR_POP
var_pop(x)
- Description: This function returns the population standard variance for a group of
integers, decimals, or floating-point numbers.
Note
VAR_POP()
ignores rows with NULL values. If a group of values are all NULL,VAR_POP()
directly returns NULL.- You can also use the
VARIANCE()
function, which is equivalent but is not a standard SQL function.
- Data type of the input value: BIGINT or DOUBLE.
- Data type of the return value: DOUBLE.
- Example:
SELECT var_pop(a) FROM testtable;
The following result is returned:+--------------------+ | var_pop(a) | +--------------------+ | 0.6666666666666666 | +--------------------+
VAR_SAMP
var_samp(x)
- Description: This function returns the sample variance for a group of integers, decimals, or floating-point numbers.
- Data type of the input value: BIGINT or DOUBLE.
- Data type of the return value: DOUBLE.
- Example:
SELECT var_samp(a) FROM testtable;
The following result is returned:+-------------+ | var_samp(a) | +-------------+ | 1.0 | +-------------+
GROUP_CONCAT
GROUP_CONCAT([DISTINCT] col_name
[ORDER BY col_name [ASC | DESC]]
[SEPARATOR str_val])
Clause | Required | Description |
---|---|---|
DISTINCT |
No | Specifies the columns for which to remove duplicate rows of data. |
ORDER BY |
Specifies the columns to be sorted within a group. The following sorting methods are
available:
The default sorting method is ascending. |
|
SEPARATOR |
Specifies the delimiter used to separate values within a group.
The default delimiter is a comma (,). |
- Description: This function returns a string result of the concatenated values from
a group. The values are the returned results of the
GROUP BY
clause.NoteNULL
is returned only if all values in the columns to be concatenated by using theGROUP_CONCAT
function are NULL. - Data type of the input value: STRING.
- Data type of the return value: STRING.
- Example:
The following example demonstrates how to use the
GROUP_CONCAT
function. A table namedperson
is created in the example by using the following statement:CREATE TABLE person(id INT,name VARCHAR,age INT ) DISTRIBUTED BY HASH(id);
The following statement is used to insert data into the table:INSERT INTO person VALUES (1,'mary',13),(2,'eva',14),(2,'adam',13),(3,'eva',13),(3,null,13),(3,null,null),(4,null,13),(4,null,null);
In the following statement, data is grouped by theid
column, and the values in thename
column that have the same ID are displayed by using theGROUP_CONCAT
function. Duplicate values in thename
column are removed, and the table is sorted in descending order by thename
column. Multiplename
column values with the same ID are separated by a number sign (#
).SELECT id, GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#') FROM person GROUP BY id;
The following result is returned:+------+--------------------------------------------------------------+ | id | GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#') | +------+--------------------------------------------------------------+ | 2 | eva#adam | | 1 | mary | | 4 | NULL | | 3 | eva | +------+--------------------------------------------------------------+