_10_ SQL 数据分组

本文DEMO

https://gitee.com/changsanjiang/SQLDemo/tree/master

上一节汇总数据时, 每个示例都是在表所有数据或匹配特定的WHERE子句的数据上进行的.
如果要返回每个供应商提供的产品数目, 该怎么办? 或者返回只提供一项产品的供应商, 或者返回提供10个以上产品的供应商?

这时, 我们就需要使用分组GROUP BY子句了, 使用分组可以将数据分为多个逻辑组, 从而对每个组进行聚集计算.

创建分组

到底什么是分组, 我们先看例子:

输入:

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

输出:

image.png

分析:

vend_id 为产品供应商的 ID, num_prods 为计算字段(用 COUNT(*) 函数建立).
GROUP BY子句指示 DBMS 按 vend_id 排序并分组. 因此DBMS对每个 vend_id 计算 num_prods. 从输出可看出, BRS01有3个产品.

---

过滤分组

除了能用GROUP BY分组数据外, SQL还允许过滤分组, 规定包括哪些分组, 排除哪些分组. 例如, 你可能想列出至少有两个订单的所有顾客. 为此, 必须基于完整的分组进行过滤, 而不是个别的行.

我们之前使用WHERE子句, 但在此处, WHRER并不能完成任务, 因为WHERE过滤指定的是行而不是分组. 事实上, WHERE没有分组的概念.

完成这个任务需要使用HAVING子句, HAVING非常类似于WHERE. 事实上目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代. 唯一的差别是, WHERE过滤行, HAVING过滤分组.

输入:

SELECT  vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

输出:

image.png

分析:

HAVING子句过滤COUNT(*) >= 2的那些分组. 如图返回结构, 可以看到: WHERE子句在这里不起作用, 因为过滤是基于分组, 而不是特定行的值.

那么, 有么有在一条语句中同时使用WHERE和HAVING子句的需求呢? 事实上有, 例如: 列出具有两个以上产品且价格大于等于4的供应商 如下:

SELECT vend_id, COUNT(*) AS num_pods -- 查询产品个数
FROM Products
WHERE prod_price >= 4 -- 需价格大于等于4美元的产品
GROUP BY vend_id  -- 根据供应商ID进行分组
HAVING count(*) >= 2; -- 过滤出提供两个以上产品的供应商

输出:

image.png

分组+排序

一般在使用Group BY子句时, 应该也给出ORDER BY子句. 这是保证数据正确排序的唯一方法. 千万不要依赖GROUP BY排序数据.

输入:

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

输出:

image.png

分析:

order_num -> 订单号
items -> 每个订单的物品数目

此例中, 使用GROUP BY子句按订单号(order_num列)将数据进行分组, 使得COUNT(*)函数能够返回每个订单中的物品数目. HAVING子句过滤数据, 使得只返回包含三个以上的订单. 最后ORDER BY子句进行排序输出.