Use AnalyticDB for MySQL aggregate functions - AnalyticDB

You can use aggregate functions to perform summary calculations on a set of data. This topic describes the aggregate function syntax and provides examples on how to use the aggregate functions in AnalyticDB for MySQL.

AnalyticDB for MySQL supports the following aggregate functions:

ARBITRARY: randomly returns a value from a set of data.
AVG: calculates the average value of a set of numeric values.
BIT_AND: returns the result of a bitwise AND operation on all bits of the input values.
BIT_OR: returns the result of a bitwise OR operation on all bits of the input values.
BIT_XOR: returns the result of a bitwise XOR operation on all bits of the input values.
COUNT: counts the number of records returned by a SELECT query.
MAX: calculates the maximum value of a set of numeric values.
MIN: calculates the minimum value of a set of numeric values.
STD, STDDEV, or STDDEV_POP: returns the population standard deviation of all input values.
STDDEV_SAMP: returns the sample standard deviation of a set of integers, decimals, or floating-point numbers.
SUM: calculates the sum of all input values.
VARIANCE (non-standard SQL function): returns the population standard variance of a set of integers, decimals, or floating-point numbers.
VAR_POP (standard SQL function): returns the population standard variance of a set of integers, decimals, or floating-point numbers.
VAR_SAMP: returns the sample variance of a set of integers, decimals, or floating-point numbers.
GROUP_CONCAT: returns a string result of the concatenated values from a group. The values come from the returned results of the GROUP BY clause.

Note

In this topic, a table named testtable is used in all aggregate functions except the GROUP_CONCAT() function. The following statement is used 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 set 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 information is returned:

+--------------+
| arbitrary(a) |
+--------------+
|            2 |
+--------------+

AVG

avg(x)

Description: This function calculates the average value of a set of numeric 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 information is returned:

+--------+
| avg(a) |
+--------+
|    2.0 |
+--------+

BIT_AND

bit_and(x)

Description: This function returns the result of a bitwise AND operation on all bits of the input values.
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 information is returned:

+------------+
| bit_and(a) |
+------------+
|          0 |
+------------+

BIT_OR

bit_or(x)

Description: This function returns the result of a bitwise OR operation on all bits of the input values.
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 information is returned:

+-----------+
| bit_or(a) |
+-----------+
|         3 |
+-----------+

BIT_XOR

bit_xor(x)

Description: This function returns the result of a bitwise XOR operation on all bits of the input values.
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 information 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
Description: distinct and all specify whether to remove duplicate records during counting. The default value is all, which specifies that all records are counted. If distinct is specified, only records that have unique values are counted.
Data type of the input value: NUMERIC, STRING, or BOOLEAN.
Data type of the return value: BIGINT.

Examples

Count the number of records whose values are unique in the a column of the testtable table.

SELECT count(distinct a) FROM testtable;

The following information is returned:

+-------------------+
| count(distinct a) |
+-------------------+
|                 3 |
+-------------------+

Count the number of all records in the testtable table based on the values in the a column.

SELECT count(all a) FROM testtable;

The following information is returned:

+--------------+
| count(all a) |
+--------------+
|            3 |
+--------------+

MAX

max(x)

Description: This function calculates the maximum value of a set of numeric values.
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 a specific 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 information is returned:

+--------+
| max(a) |
+--------+
|      3 |
+--------+

MIN

min(value x)

Description: This function calculates the minimum value of a set of numeric values.
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 a specific 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 information is returned:

+--------+
| min(a) |
+--------+
|      1 |
+--------+

STD, STDDEV, or STDDEV_POP

std(x)
stddev(x)
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.

Examples

Example 1:

SELECT std(a) FROM testtable;

The following information is returned:

+-------------------+
| std(a)            |
+-------------------+
| 0.816496580927726 |
+-------------------+

Example 2:

SELECT stddev_pop(a) FROM testtable;

The following information is returned:

+-------------------+
| stddev_pop(a)     |
+-------------------+
| 0.816496580927726 |
+-------------------+

STDDEV_SAMP

stddev_samp(x)

Description: This function returns the sample standard deviation of a set 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 information 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 information is returned:

+--------+
| sum(a) |
+--------+
|      6 |
+--------+

VARIANCE

variance(x)

Description: This function returns the population standard variance of a set of integers, decimals, or floating-point numbers.
Note
- The VARIANCE() function ignores rows that have NULL values. If all values in a set are NULL, the VARIANCE() function directly returns NULL.
- The VARIANCE() function is an extension to standard SQL and can be replaced by the standard SQL function VAR_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 of a set of integers, decimals, or floating-point numbers.
Note
- The VAR_POP() function ignores rows that have NULL values. If all values in a set are NULL, the VAR_POP() function directly returns NULL.
- You can also use the VARIANCE() function, which is equivalent to the VAR_POP() function 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 information is returned:

+--------------------+
| var_pop(a)         |
+--------------------+
| 0.6666666666666666 |
+--------------------+

VAR_SAMP

var_samp(x)

Description: This function returns the sample variance of a set 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 information 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 from which you want to remove duplicates.
`ORDER BY`		Specifies the columns that you want to sort within a group. The following sorting methods are supported: `ASC`: ascending order. `DESC`: descending order. If you do not specify a sorting method, the ascending order is used.
`SEPARATOR`		Specifies the delimiter used to separate values within a group. If you do not specify a delimiter, a comma (,) is used.

Description: This function returns a string result of the concatenated values from a group. The values come from the returned results of the GROUP BY clause.
Note
NULL is returned only if all values in the columns that you want to concatenate by using the GROUP_CONCAT() function are NULL.
Data type of the input value: STRING.
Data type of the return value: STRING.

Example:

Create a table named person that uses the GROUP_CONCAT() function.

CREATE TABLE person(id INT,name VARCHAR,age INT ) DISTRIBUTED BY HASH(id);

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);

Group the person table by the id column. Use the GROUP_CONCAT() function to display the distinct values of the name column with the same ID, sort the table in descending order by the name column, and then separate the name column values with number signs (#) in each group.

SELECT
  id,
  GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#')
FROM person 
  GROUP BY id;

The following information is returned:

+------+--------------------------------------------------------------+
| id   | GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#') |
+------+--------------------------------------------------------------+
|    2 | eva#adam                                                     |
|    1 | mary                                                         |
|    4 | NULL                                                         |
|    3 | eva                                                          |
+------+--------------------------------------------------------------+