MYSQL-聚合函数

聚合函数

聚合函数aggregation function 可称为组函数, 一般可配合 group by 和 having 使用

count 计算行数

mysql> select count(1) from websites;
+----------+
| count(1) |
+----------+
|        6 |
+----------+
--字段去重后计算行数
mysql> select count(distinct country) from websites;
+-------------------------+
| count(distinct country) |
+-------------------------+
|                       2 |
+-------------------------+

avg 平均数

mysql> select avg(alexa) from websites;
+------------+
| avg(alexa) |
+------------+
|    93.3333 |
+------------+

max 最大值

mysql> select max(alexa) from websites;
+------------+
| max(alexa) |
+------------+
|        300 |
+------------+

min 最小值

mysql> select min(alexa) from websites;
+------------+
| min(alexa) |
+------------+
|          1 |
+------------+

sum 求和

mysql> select sum(alexa) from websites;
+------------+
| sum(alexa) |
+------------+
|        560 |
+------------+
mysql> select *, round(alexa/(select sum(alexa) from websites)*100, 2) as percent from websites;
+----+--------------+---------------------------+-------+---------+---------+
| id | name         | url                       | alexa | country | percent |
+----+--------------+---------------------------+-------+---------+---------+
|  1 | Google       | https://www.google.cm/    |     1 | USA     |    0.18 |
|  2 | 淘宝         | https://www.taobao.com/   |    13 | CN      |    2.32 |
|  3 | 菜鸟教程     | http://www.runoob.com/    |   300 | CN      |   53.57 |
|  4 | 微博         | http://weibo.com/         |    20 | CN      |    3.57 |
|  5 | Facebook     | https://www.facebook.com/ |     3 | USA     |    0.54 |
|  6 | 百度         | https://www.baidu.com     |   223 | CN      |   39.82 |
+----+--------------+---------------------------+-------+---------+---------+

查询alexa大于平均值的记录

mysql> select * from websites where alexa > (select avg(alexa) from websites);
+----+--------------+------------------------+-------+---------+
| id | name         | url                    | alexa | country |
+----+--------------+------------------------+-------+---------+
|  3 | 菜鸟教程     | http://www.runoob.com/ |   300 | CN      |
|  6 | 百度         | https://www.baidu.com  |   223 | CN      |
+----+--------------+------------------------+-------+---------+

分组求平均

mysql> select country, avg(alexa) from websites group by country;
+---------+------------+
| country | avg(alexa) |
+---------+------------+
| USA     |     2.0000 |
| CN      |   139.0000 |
+---------+------------+
2 rows in set (0.02 sec)

mysql> select country, avg(alexa) as avg_alexa from websites group by country having avg_alexa > 100;
+---------+-----------+
| country | avg_alexa |
+---------+-----------+
| CN      |  139.0000 |
+---------+-----------+
1 row in set (0.00 sec)

group by 分组

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name[, column_name2, ..., column_nameN];

having 筛选分组后的数据

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

group_concat 分组合并

mysql> select country, group_concat(name) from websites group by country;
+---------+-----------------------------------+
| country | group_concat(name)                |
+---------+-----------------------------------+
| CN      | 淘宝,菜鸟教程,微博,百度           |
| USA     | Google,Facebook                   |
+---------+-----------------------------------+
2 rows in set (0.00 sec)