汇总数据——聚集函数

2019-08-06
聚集函数（aggregate function）：运行在行组上，计算和返回单个值的函数。
SQL聚集函数

函数	说明
AVG()	返回某列的平均值，忽略NULL值
COUNT()	返回某列的行数，忽略NULL值，但使用通配符*时不忽略
MAX()	返回某列的最大值，忽略NULL值
MIN()	返回某列的最小值，忽略NULL值
SUM()	返回某列值之和，忽略NULL值

以上五个聚集函数都可以如下使用：

• 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
• 只包含不同的值，指定DISTINCT参数。
  注意：

• 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT(DISTINCT *)，否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
• 虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。

mysql> select cust_country from customers;
+--------------+
| cust_country |
+--------------+
| USA          |
| USA          |
| USA          |
| USA          |
| USA          |
+--------------+
5 rows in set (0.00 sec)

mysql> select count(*) from customers where cust_country = 'USA';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select count(DISTINCT *) from customers where cust_country = 'USA';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near '*) from customers where cust_country = 'USA'' at line 1
mysql> select count(DISTINCT cust_country) from customers where cust_country = 'USA';
+------------------------------+
| count(DISTINCT cust_country) |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.07 sec)

1. AVG()函数

• AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
• NULL值，AVG()函数忽略列值为NULL的行

2. MAX()函数和MIN()

对非数值数据使用MAX()和MIN()

虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列升序排序，则MAX()返回最后一行，MIN()返回最前面的行；如果数据按相应的列降序排序，则MAX()返回最前面的行，MIN()返回最后一行。

mysql> SELECT cust_name
    -> FROM customers;
+----------------+
| cust_name      |
+----------------+
| Coyote Inc.    |
| Mouse House    |
| Wascals        |
| Yosemite Place |
| E Fudd         |
+----------------+
5 rows in set (0.00 sec)

mysql> SELECT cust_name
    -> FROM customers
    -> ORDER BY cust_name;
+----------------+
| cust_name      |
+----------------+
| Coyote Inc.    |
| E Fudd         |
| Mouse House    |
| Wascals        |
| Yosemite Place |
+----------------+
5 rows in set (0.00 sec)

mysql> SELECT cust_name
    -> FROM customers
    -> ORDER BY cust_name DESC;
+----------------+
| cust_name      |
+----------------+
| Yosemite Place |
| Wascals        |
| Mouse House    |
| E Fudd         |
| Coyote Inc.    |
+----------------+
5 rows in set (0.00 sec)

mysql> SELECT MAX(cust_name)
    -> FROM customers
    -> ORDER BY cust_name;
+----------------+
| MAX(cust_name) |
+----------------+
| Yosemite Place |
+----------------+
1 row in set (0.07 sec)

mysql>  SELECT MAX(cust_name)
    ->  FROM customers
    -> ORDER BY cust_name DESC;
+----------------+
| MAX(cust_name) |
+----------------+
| Yosemite Place |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(cust_name)
    -> FROM customers
    -> ORDER BY cust_name;
+----------------+
| MIN(cust_name) |
+----------------+
| Coyote Inc.    |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(cust_name)
    -> FROM customers
    -> ORDER BY cust_name DESC;
+----------------+
| MIN(cust_name) |
+----------------+
| Coyote Inc.    |
+----------------+
1 row in set (0.00 sec)

3. SUM()函数

mysql> SELECT SUM(quantity) AS items_ordered
    -> FROM orderitems
    -> WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+
1 row in set (0.07 sec)

mysql># 利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算
mysql> SELECT SUM(item_price * quantity) AS total_price
    -> FROM orderitems
    -> WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
|      149.87 |
+-------------+
1 row in set (0.00 sec)

4. 组合聚集函数

mysql> SELECT COUNT(*) AS num_items,
    ->        MIN(prod_price) AS price_min,
    ->        MAX(prod_price) AS price_max,
    ->        AVG(prod_price) AS price_avg
    -> FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)