关系数据库的大部分功能来自过滤数据和将表连接在一起。这就是为什么我们首先代表这些关系的原因。但是现代数据库系统提供了另一种有价值的技术:分组。
分组使您可以从数据库中提取摘要信息。它使您可以合并结果以创建有用的统计数据。分组使您无需编写代码来处理常见情况,例如对图形列表进行平均。它可以使系统效率更高。
GROUP BY子句有什么作用?
顾名思义,GROUP BY将结果分成较小的一组。对于分组列的每个不同值,结果由一行组成。我们可以通过查看一些示例数据以及共享某些共同值的行来显示其用法。
以下是一个非常简单的数据库,其中有两个表代表唱片集。您可以通过为所选数据库系统编写基本模式来建立这样的数据库。专辑表有9行,其中有一个主键ID列,以及名称,艺术家,发行年份和销售量的列:
+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+
Artists表甚至更简单。它具有ID和name列的七行:
+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+
您只需要像这样的简单数据集就可以了解GROUP BY的各个方面。当然,现实生活中的数据集会包含很多行,但是原理保持不变。
按单列分组
假设我们要找出每个艺术家有多少张专辑。从典型的SELECT查询开始,以获取artist_id列:
SELECT artist_id FROM albums
按预期返回所有九行:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+
要按艺术家将这些结果分组,请添加短语GROUP BY artist_id :
SELECT artist_id FROM albums GROUP BY artist_id
得到以下结果:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+
结果集中有7行,相较于专辑表中的9行减少了。每个唯一的artist_id都有一行。最后,要获取实际计数,请将COUNT(*)添加到所选列:
SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+
结果将ID为2和6的艺术家分为两对行。每个人在我们的数据库中都有两张专辑。
如何使用汇总功能访问分组数据
您可能以前使用过COUNT函数,尤其是如上所述的COUNT(*)形式。它获取一组结果的数量。您可以使用它来获取表中的记录总数:
SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
COUNT是一个汇总函数。该术语是指将多行中的值转换为单个值的函数。它们通常与GROUP BY语句结合使用。
我们不仅可以计算行数,还可以将聚合函数应用于分组值:
SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+
上面显示的艺术家2和6的总销售额是其多张专辑的总销售额:
SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+
按多列分组
您可以按多个列进行分组。仅包括多个列或表达式,用逗号分隔。结果将根据这些列的组合进行分组。
SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, sales
与按单个列分组相比,这通常会产生更多结果:
+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+
请注意,在我们的小例子中,只有两张专辑具有相同的发行年份和销量(1977年为28张)。
有用的汇总功能
除了COUNT以外,GROUP还可以使用几个功能。每个函数基于属于每个结果组的记录返回一个值。
- COUNT()返回匹配记录的总数。
- SUM()返回给定列中所有值的总和。
- MIN()返回给定列中的最小值。
- MAX()返回给定列中的最大值。
- AVG()返回平均值。相当于SUM()/ COUNT()。
您也可以在不使用GROUP子句的情况下使用以下功能:
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+
在WHERE子句中使用GROUP BY
与普通的SELECT一样,您仍然可以使用WHERE来过滤结果集:
SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+
现在,只有按艺术家分组的1990年以后发行的专辑。您还可以独立于GROUP BY使用WHERE子句的联接:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
+---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+
但是请注意,如果尝试基于聚合列进行过滤:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;
您会得到一个错误:
ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'
基于汇总数据的列不可用于WHERE子句。
使用HAVING子句
那么,如何在进行分组后过滤结果集? HAVING子句满足以下需求:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;
请注意,HAVING子句位于GROUP BY之后。否则,它实际上是用HAVING替换WHERE的简单方法。结果是:
+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+
您仍然可以使用WHERE条件在分组之前过滤结果。它将与HAVING子句一起使用,以便在分组后进行过滤:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;
1990年之后,我们数据库中只有一位歌手发行了多张专辑:
+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+
将结果与GROUP BY结合
GROUP BY语句是SQL语言非常有用的一部分。例如,它可以提供内容页面的数据摘要信息。它是获取大量数据的绝佳选择。由于数据库的设计使其非常适合工作,因此数据库可以很好地处理这些额外的工作负载。
了解分组以及如何联接多个表后,您将能够利用关系数据库的大多数功能。