SQL 汇总数据(聚合函数)

SQL 汇总数据(聚合函数)

一、聚合函数：

• 我们经常需要汇总数据而不用把它们检索出来，为此SQL提供了专门的函数。使用这些函数，SQL查询可用于检索数据。

• 这种类型的检索栗子有：

  1.确定表中行数(或者满足某个条件或包含某个特定值的行数)。

  2.表中某型行的和。

  3.找出表列(或所有行或某些特定的行)的最大值、最小值、平均值。

• 为方便这种类型的检索，SQL给出了5个聚集函数。

• 聚集函数(aggregate function)：对某些行运行的函数，计算并返回一个值。

• SQL聚集函数：

  函数	说明
  AVG（）	返回某列的平均值
  COUNT（）	返回某列的行数
  MAX（）	返回某列的最大值
  MIN（）	返回某列的最小值
  SUM（）	返回某列值之和

1.1：AVG()函数：

• AVG（）通过对表中行数计数并计算某列值之和，求得该列的平均值。AVG（）可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

  SELECT  AVG(prod_price) AS avg_price FROM products;
  

  • 输出结果如下：

    +-----------+
    | avg_price |
    +-----------+
    |  6.823333 |
    +-----------+
    1 row in set (0.02 sec)
    

  • 此SELECT语句返回值avg_price，它包含products表中所有产品的平均价格。(avg_price是一个别名)

• AVG（）也可以用来确定特定列或行的平均值。

   SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id='DLL01';
  

  • 输出结果如下：

    +-----------+
    | avg_price |
    +-----------+
    |  3.865000 |
    +-----------+
    1 row in set (0.00 sec)
    

  • 这条SELECT语句与前一条的不同之处在于，它包含了WHERE子句。此WHERE子句仅过滤出vend_id为DLL01的产品，因此avg_price中返回的值只是该供应商品的平均值。

• 警告：只用于单个列

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

• 注意：NULL值 AVG（）函数忽略列值为NULL的行。

1.2：COUNT()函数：

• COUNT（）函数进行计数。可利用COUNT（）确定表中行的数目或符合特定条件的行的数目。

• COUNT（）函数有两种使用方式：

  1.使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。

  SELECT COUNT(*) AS num_cust FROM customers;
  

  +----------+
  | num_cust |
  +----------+
  |        5 |
  +----------+
  1 row in set (0.00 sec)
  

  2.使用COUNT(列)对特定列中具有值的行进行计数，忽略NULL值。

   SELECT COUNT(cust_email) AS num_cust FROM customers ;
  

  +----------+
  | num_cust |
  +----------+
  |        3 |
  +----------+
  1 row in set (0.00 sec)
  

• 注意：NULL值

  • 如果指定列名，则COUNT（）函数会忽略指定列的值为空的行，但如果COUNT（）函数中用的是星号(*)，则不忽略。

1.3：MAX()函数：

• MAX()返回指定列中的最大值。MAX()要求指定列名：

  SELECT MAX(prod_price) AS max_price FROM products;
  

  +-----------+
  | max_price |
  +-----------+
  |     11.99 |
  +-----------+
  1 row in set (0.01 sec)
  

• 注意：对非数值数据使用MAX()

  • 虽然MAX（）一般用来找出最大的数值或日期值，但许多(并非所有) DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。

• 注意：NULL值

  • MAX()函数忽略列值为NULL的行。

1.4：MIN()函数：

• MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX（）一样，MIN（）要求指定列名：

  SELECT MIN(prod_price) AS min_price FROM products;
  

  +-----------+
  | min_price |
  +-----------+
  |      3.49 |
  +-----------+
  1 row in set (0.00 sec)
  

• 注意：对非数值数据使用MIN（）

  • 虽然MIN（）一般用来找出最小的数值或日期值，但许多(并非所有)DBMS允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，MIN（）返回该列排序后最前面的行。

• 注意：NULL值

  • MIN（）函数忽略列值为NULL的行。

1.5：SUM()函数：

• SUM（）用来返回指定列值的和(总计)。

• 首先我们看以下这个表都有什么数据？

  mysql> SELECT * FROM orderitems;
  +-----------+------------+---------+----------+------------+
  | order_num | order_item | prod_id | quantity | item_price |
  +-----------+------------+---------+----------+------------+
  |     20005 |          1 | BR01    |      100 |       5.49 |
  |     20005 |          2 | BR03    |      100 |      10.99 |
  |     20006 |          1 | BR01    |       20 |       5.99 |
  |     20006 |          2 | BR02    |       10 |       8.99 |
  |     20006 |          3 | BR03    |       10 |      11.99 |
  |     20007 |          1 | BR03    |       50 |      11.49 |
  |     20007 |          2 | BNBG01  |      100 |       2.99 |
  |     20007 |          3 | BNBG02  |      100 |       2.99 |
  |     20007 |          4 | BNBG03  |      100 |       2.99 |
  |     20007 |          5 | RGAN01  |       50 |       4.49 |
  |     20008 |          1 | RGAN01  |        5 |       4.99 |
  |     20008 |          2 | BR03    |        5 |      11.99 |
  |     20008 |          3 | BNBG01  |       10 |       3.49 |
  |     20008 |          4 | BNBG02  |       10 |       3.49 |
  |     20008 |          5 | BNBG03  |       10 |       3.49 |
  |     20009 |          1 | BNBG01  |      250 |       2.49 |
  |     20009 |          2 | BNBG02  |      250 |       2.49 |
  |     20009 |          3 | BNBG03  |      250 |       2.49 |
  +-----------+------------+---------+----------+------------+
  18 rows in set (0.01 sec)
  

• 下面举个栗子：

  • OrderItems包含订单中实际的物品，每个物品都有相应的数量。检索所订购物品的总数：

    SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = '20005';
    

    +---------------+
    | items_ordered |
    +---------------+
    |           200 |
    +---------------+
    1 row in set (0.00 sec)
    

  • 函数SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品。

• SUM()也可以用来合计计算值。

  • item_price * quantity :

    SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;
    

    +-------------+
    | total_price |
    +-------------+
    |     1648.00 |
    +-------------+
    1 row in set (0.00 sec)
    

  • 函数SUM(item_price * quantity)返回订单中所有物品价格之和，WHERE子句同样保证只统计某个物品订单中的物品。

• 注意：NULL值得

  • SUM（）函数忽略列值为NULL的行。

二、聚集不同值：

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

• 对所有执行计算，指定ALL参数或不指定参数(因为ALL是默认行为)。

  • 只包含不同的值，指定DISTINCT参数(去重)

• 注意：ALL为默认

  • ALL参数不需要指定，因为它是默认行为，如果不指定DISTINCT，则假定为ALL。

• 下面栗子使用AVG（）函数返回特定供应商提供的产品和平均价格。

  SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 'DLL01';
  

  +-----------+
  | avg_price |
  +-----------+
  |  4.240000 |
  +-----------+
  1 row in set (0.00 sec)
  

• 注意：DISTINCT 不能用COUNT（*）

  • 如果指定列名，则DISTINCT 只能用于COUNT()。DISTINCT不能用于COUNT(*)

三、组合聚集函数：

• SELECT语句可根据需要包含多个聚集函数。

  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 |
  +-----------+-----------+-----------+-----------+
  |         9 |      3.49 |     11.99 |  6.823333 |
  +-----------+-----------+-----------+-----------+
  1 row in set (0.00 sec)
  

• 用单条SELECT 语句执行了4个聚集计算，返回4个值。

• 注意：取别名

  • 在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。
  • 虽然这样做也合法，但许多SQL实现不支持，可能会产生模糊的错误消息。