1 使用聚集函数汇总数据（AVG、COUNT、MAX、MIN、SUM）
我们经常需要汇总数据而不用把它们都检索出来。比如：
- 确定表中行数（或者满足某个条件或包含某个特定值的行数）。
- 获得表中某些行的和。
- 找出表列（或所有行或者某些特定行）的最大值、最小值、平均值。
聚集函数（aggregate function）：对某些行运行的函数，计算并返回一个值。
SQL给了5个聚集函数。
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值的总和 |
1.1 AVG()函数
AVG()函数通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以返回特定列或行的平均值。
计算Products表中所有产品的平均价格：
SELECT AVG(prod_price) AS avg_price
FROM Products;
计算特定供应商DLL01所提供产品的平均价格：
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
注意：AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数，如AVG(num1)、AVG(num2)、AVG(num3)等。
AVG()函数在计算时，会忽略列值为NULL的行。
1.2 COUNT()函数
COUNT()函数进行计数，可确定表中行的数目或符合特定条件的行的数目。
COUNT()函数有两种使用方式：
- 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值NULL还是非空值。
- 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
计算Customers表中顾客的总数：
SELECT COUNT(*) AS num_cust
FROM Customers;
只计算有Email的顾客：
SELECT COUNT(cust_email) AS num_cust
FROM Customers;
1.3 MAX()函数
MAX()函数返回指定列中的最大值，要求必须指定列名。
计算Products表中最贵物品的价格：
SELECT MAX(prod_price) AS max_price
FROM Products;
虽然MAX()一般用来找出最大的数值或日期值，但许多DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。用于文本数据时，MAX()返回按该列排序后的最后一行。
MAX()函数会忽略列值为NULL的行。
1.4 MIN()函数
MIN()函数返回指定列中的最小值，要求必须指定列名。
计算Products表中最便宜物品的价格：
SELECT MIN(prod_price) AS min_price
FROM Products;
虽然MIN()一般用来找出最小的数值或日期值，但许多DBMS允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。用于文本数据时，MIN()返回按该列排序后最前面的行。
MIN()函数会忽略列值为NULL的行。
1.5 SUM()函数
SUM()函数用来返回指定列值的和（总计）。
计算订单号为20005的所购物品的总数：
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;
计算订单号为20005的所购物品，花费的总金额：
SELECT SUM(item_price * quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;
SUM()函数会忽略列值为NULL的行。
1.6 聚集不同的值
- 对所有行执行计算，指定ALL参数或者不指定参数（ALL是默认行为）。
- 只包含不同的值，指定DISTINCT参数。
计算特定供应商DLL01提供的产品的不同价格的平均值，也就是排除相同价格的产品。
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
在使用了DISTINCT之后，发现avg_price比较高，是因为有多个产品具有相同的较低价格，排除它们，提高了平均价格。
注意：如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。DISTINCT必须使用列名，不能用于计算或表达式。
有些DBMS支持对查询结果子集进行计算的TOP和TOP PERCENT。
1.7 组合聚集函数
计算Products表中物品的数目，产品价格的最大值、最小值以及平均值。
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;
2 分组数据（GROUP BY、HAVING）
2.1 创建分组（GROUP BY）
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
在使用GROUP BY子句前，需要知道一些重要的规定。
- GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
- 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算，不能从个别的列取回数据。
- GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。
- 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
- 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
- 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
有的SQL实现允许根据SELECT列表中的位置指定GROUP BY的列，例如，GROUP BY 2,1可表示按选择的第二个列分组，然后再按第一个列分组。
2.2 过滤分组（HAVING）
除了能用GROUP BY分组数据外，SQL还允许过滤分组。规定包括哪些分组，排除哪些分组。例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行过滤。
在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。
那么，不使用WHERE使用什么呢？SQL为此提供了另一个子句，就是HAVING子句。HAVING非常类似于WHERE，唯一的区别是，WHERE过滤行，而HAVING过滤分组。
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
HAVING过滤了COUNT(*)>=2（两个以上订单）的那些分组。
WHERE子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值。
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
这条语句中，第一行是使用了聚集函数的基本SELECT语句，很像前面的例子。WHERE子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。如果没有WHERE子句，就会多检索一行（供应商DLL01，销售4个产品，价格都在4以下）。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 2;
注意：使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。
2.3 分组和排序（GROUP BY、ORDER BY）
GROUP BY和ORDER BY经常完成相同的工作，但它们非常不同。
| ORDER BY | GROUP BY |
|---|---|
| 对产生的输出排序 | 对行分组，但输出可能不是分组的顺序 |
| 任意列都可以使用（甚至非选择的列也可以使用） | 只可能使用选择列或表达式列，而且必须使用每个选择列表达式 |
| 不一定需要 | 如果与聚集函数一起使用列（或表达式），则必须使用 |
一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法，千万不要仅依赖GROUP BY排序数据。
检索包含三个或更多物品的订单号和订购物品的数目。
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;
要按订购物品的数目排序输出，需要添加ORDER BY子句。
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
2.4 SELECT子句顺序
SELECT子句及其顺序
| 子句 | 说明 | 是否必须 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
如果您发现文中有不清楚或者有问题的地方，请在下方评论区留言，我会根据您的评论，更新文中相关内容，谢谢！
网友评论