MySQL 聚合函数

聚合函数通常用于聚合查询时统计数据，官方文档：https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html。

函数	功能
AVG()	返回平均值
SUM()	返回总和
MAX()	返回最大值
MIN()	返回最小值
COUNT()	返回记录数,NULL 不会被统计
BIT_AND()	按位返回 AND
BIT_OR()	按位返回 OR
BIT_XOR()	按位返回 异或
GROUP_CONCAT()	将查询的多个结果，使用逗号拼接

我们以此表为例，演示聚合函数用法：

mysql> CREATE TABLE test(
    -> type varchar(10),
    -> number int(11)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT type,number,bin(number) FROM test;
+------+--------+-------------+
| type | number | bin(number) |
+------+--------+-------------+
| a    |      7 | 111         |
| a    |      6 | 110         |
| a    |      4 | 100         |
| b    |      0 | 0           |
| b    |      1 | 1           |
| b    |      3 | 11          |
+------+--------+-------------+
6 rows in set (0.00 sec)

AVG 函数

返回查询列的平均值

mysql> SELECT AVG(number) FROM test;
+-------------+
| AVG(number) |
+-------------+
|      3.5000 |
+-------------+
1 row in set (0.00 sec)

SUM 函数

返回查询列的总和

mysql> SELECT SUM(number) FROM test;
+-------------+
| SUM(number) |
+-------------+
|          21 |
+-------------+
1 row in set (0.00 sec)

MAX 函数

返回查询列的最大值

mysql> SELECT MAX(number) FROM test;
+-------------+
| MAX(number) |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

MIN 函数

返回查询列的最小值

mysql> SELECT MIN(number) FROM test;
+-------------+
| MIN(number) |
+-------------+
|           0 |
+-------------+
1 row in set (0.02 sec)

COUNT 函数

返回查询列中值不为 NULL 的记录数，也可以配合流程函数做逻辑判断。

mysql> SELECT count(*),count(if(number < 5,1,null)) FROM test;
+----------+------------------------------+
| count(*) | count(if(number < 5,1,null)) |
+----------+------------------------------+
|        6 |                            4 |
+----------+------------------------------+
1 row in set (0.01 sec)

BIT_AND 函数

对字段的 bit 位进行逻辑与操作，相应 bit 位都为 1 则结果位置为 1，否则结果位为 0。

mysql> SELECT type,BIT_AND(number),BIN(BIT_AND(number)) FROM test GROUP BY type;
+------+-----------------+----------------------+
| type | BIT_AND(number) | BIN(BIT_AND(number)) |
+------+-----------------+----------------------+
| a    |               4 | 100                  |
| b    |               0 | 0                    |
+------+-----------------+----------------------+
2 rows in set (0.00 sec)

类型 a 的 number 字段分别为 111、110、100 ，相应 bit 位逻辑与运算后结果为 100，转换为十进制显示为 4。
类型 b 的 number 字段分别为: 000、001、011，相应 bit 位逻辑与运算后结果为 000，转换为十进制显示为 0。

BIT_OR 函数

对字段的 bit 位进行逻辑与操作，相应 bit 位有一位为 1 则结果位置为 1，否则结果位为 0。

mysql> SELECT type,BIT_OR(number),BIN(BIT_OR(number)) FROM test GROUP BY type;
+------+----------------+---------------------+
| type | BIT_OR(number) | BIN(BIT_OR(number)) |
+------+----------------+---------------------+
| a    |              7 | 111                 |
| b    |              3 | 11                  |
+------+----------------+---------------------+
2 rows in set (0.00 sec)

类型 a 的 number 字段分别为 111、110、100 ，相应 bit 位逻辑或运算后结果为 111，转换为十进制显示为 7。
类型 b 的 number 字段分别为: 000、001、011，相应 bit 位逻辑或运算后结果为 011，转换为十进制显示为 3。

BIT_XOR 函数

对字段的 bit 位进行异或操作，相应 bit 位逐个进行异或操作，然后拿结果再与下一个值的相应 bit 位进行异或操作，如果两个相应 bit 位相同，则结果为0，否则为1。

mysql> SELECT type,BIT_XOR(number),BIN(BIT_XOR(number)) FROM test GROUP BY type;
+------+-----------------+----------------------+
| type | BIT_XOR(number) | BIN(BIT_XOR(number)) |
+------+-----------------+----------------------+
| a    |               5 | 101                  |
| b    |               2 | 10                   |
+------+-----------------+----------------------+
2 rows in set (0.00 sec)

类型 a 的 number 字段，第一次异或结果为 001，然后在与 100 进行异或，结果为 101，转换为十进制为 5。
类型 a 的 number 字段，第一次异或结果为 001，然后在与 011 进行异或，结果为 010，转换为十进制为 3。

GROUP_CONCAT 函数

使用 GROUP_CONCAT() 函数可以将一列数据进行串联，返回一个字符串结果。

mysql> SELECT type,GROUP_CONCAT(number) FROM test GROUP BY type;
+------+----------------------+
| type | GROUP_CONCAT(number) |
+------+----------------------+
| a    | 7,6,4                |
| b    | 0,1,3                |
+------+----------------------+
2 rows in set (0.00 sec)

可以通过关键字 "SEPARATOR" 对拼接符进行修改，默认为英文逗号。

mysql> SELECT type,GROUP_CONCAT(number SEPARATOR '|') FROM test GROUP BY type;
+------+------------------------------------+
| type | GROUP_CONCAT(number SEPARATOR '|') |
+------+------------------------------------+
| a    | 7|6|4                              |
| b    | 0|1|3                              |
+------+------------------------------------+
2 rows in set (0.00 sec)

可以通过 "ORDER BY" 对拼接顺序进行排序。

mysql> SELECT type,GROUP_CONCAT(number ORDER BY number) FROM test GROUP BY type;
+------+--------------------------------------+
| type | GROUP_CONCAT(number ORDER BY number) |
+------+--------------------------------------+
| a    | 4,6,7                                |
| b    | 0,1,3                                |
+------+--------------------------------------+
2 rows in set (0.00 sec)

还可以通过 "DISTINCT"，对拼接结果进行去重。

mysql> INSERT INTO test(type,number) VALUES('a',6),('b',3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT type,GROUP_CONCAT(number),GROUP_CONCAT(DISTINCT number) FROM test GROUP BY type;
+------+----------------------+-------------------------------+
| type | GROUP_CONCAT(number) | GROUP_CONCAT(DISTINCT number) |
+------+----------------------+-------------------------------+
| a    | 7,6,4,6              | 4,6,7                         |
| b    | 0,1,3,3              | 0,1,3                         |
+------+----------------------+-------------------------------+
2 rows in set (0.00 sec)