跳转到内容

结构化查询语言/SELECT：分组

Add links
来自维基教科书，开放的书籍，开放的世界
SELECT：联接操作 结构化查询语言
SELECT：分组 		SELECT：IS NULL 谓词


在本章中，我们将离开单个行的级别，描述引用行组的语句。在 SQL 的上下文中，这种“行组”（或行集）是通过 GROUP BY 子句构建的，并由 HAVING 子句进一步处理。


构成组

[编辑 | 编辑源代码]

首先，我们必须建立根据组过滤行的标准。为此，我们使用所涉及表中一个或多个列的内容。如果值相同，则行属于同一组。考虑表person中的lastname。在我们的示例中，我们可以推测姓氏相同的人组成一个家庭。因此，要查看有关家庭的信息，我们应该使用此列作为分组标准。这种分组允许我们提出有关整个家庭的问题，例如“有哪些家庭？”、“有多少个家庭？”、“每个家庭有多少人？”。所有这些都是关于整个组（意味着家庭）的问题，而不是关于单个行（意味着个人）的问题。

在 SQL 语法中，标准是在 GROUP BY 关键字之后指定的，它包含一个或多个列名。

SELECT ...             -- as usual
FROM   ...             -- as usual (optionally plus JOINs)
GROUP BY <column-name>  -- optionally more column names
...                    -- optionally other elements of SELECT command
;


我们关于家庭的具体示例如下所示

SELECT lastname
FROM   person
GROUP BY lastname;

该查询从十行中检索出七个“家庭名称”。有几个人姓“Goldstein”或“de Winter”。

我们可以在没有 GROUP BY 的情况下，通过在 SELECT 中应用 DISTINCT 关键字来检索相同的七个“家庭名称”。

SELECT DISTINCT lastname
FROM   person;
-- no GROUP BY clause

有什么区别？DISTINCT 关键字仅限于删除重复值。它不能对结果集中的其他行和列进行计算。相反，GROUP BY 还会将接收到的中间行排列成多个组，并提供获取有关这些组中每个组的信息的可能性。即使是这种情况，在这些组中，所有列都是可用的，不仅仅是“标准”列。为了确认关于“所有”列的声明，我们使用weight，它不是“标准”列。

SELECT lastname, avg(weight)  -- avg() is a function to compute the arithmetic mean of numerical values
FROM   person
GROUP BY lastname;

结果显示了七个家庭名称（如前所示），以及每个家庭的平均体重。没有显示个人成员的体重。（在只有一个人的组中，组的平均体重当然与单个人员的体重相同。）

跨多个列分组

[编辑 | 编辑源代码]

如果有必要，我们可以将分组定义在多列上。在这种情况下，我们可以将列的连接视为分组规则。

-- Group over one column: place_of_birth leads to 6 resulting rows
SELECT place_of_birth, count(*)
FROM   person
GROUP BY place_of_birth;
-- Group over two columns: place_of_birth plus lastname leads to 8 resulting rows with Richland and SF shown twice
SELECT place_of_birth, lastname, count(*)
FROM   person
GROUP BY place_of_birth, lastname;

检查组

[编辑 | 编辑源代码]

在使用 GROUP BY 关键字定义了组之后，我们可以选择有关每个组的更多信息，例如：每个家庭（行组）中有多少人（行）？

SELECT lastname, count(*)  -- count() is a function which counts values or rows
FROM   person
GROUP BY lastname;

我们可以看到，在我们的示例数据库中，有一个家庭有三个成员，另一个家庭有两个成员，其他家庭只有一个成员。

在执行命令期间，幕后发生了什么？

  1. 表person的所有十行都被检索（在上面的命令中，没有 WHERE 子句）。
  2. 根据lastname列的值将行排列成七个组。
  3. 每个组及其所有行都传递给 SELECT 子句。
  4. SELECT 为每个接收到的组构建一个结果行（在“现实世界”数据库中，每个组可能包含数千行）。

在步骤 4 中，每个组只生成一行结果。由于 SELECT 每个组只创建一行结果，因此无法显示可能在组内不同行之间不同的列的值，例如firstname。SELECT 只能显示在组中所有行内都相同的值：“标准”列。

-- It is not possible to show the 'firstname' of a group! 'firstname' is an attribute of single person.
-- Within a group 'firstname' varies from row to row.
-- The DBMS should recognize this problem and should issue an error message.
SELECT lastname, firstname
FROM   person
GROUP BY lastname;
-- A hint to users of MySQL:
-- To receive correct results (the error message) you must deactivate a special performance feature by issuing the command
-- set sql_mode = 'ONLY_FULL_GROUP_BY'; or set it in the workbench or in the ini-file.

然而，我们可以获取有关非标准列的信息。但是，此信息更通用。DBMS 提供了一组特殊的函数，这些函数从一组行中构建一个值。考虑 avg() 函数，它计算数值的算术平均值。该函数接收一个列名并对一组行进行操作。如果我们的命令包含 GROUP BY 子句，则 avg() 函数将为每个组计算一个值，而不是像往常一样为所有行计算一个值。因此，可以将此类函数的结果与“标准”列的值一起显示。

以下是一些此类函数的不完整列表：count()、max()、min()、sum()、avg()。并非所有函数都是这种类型，例如函数 concat()，它连接两个字符串，对单个行进行操作并为每行创建一个值。

-- compute avg() by your own formula
SELECT lastname, sum(weight) / count(weight) as "Mean weight 1", avg(weight) as "Mean weight 2"
FROM   person
GROUP BY lastname;

关注所需组

[编辑 | 编辑源代码]

您了解 WHERE 子句。它定义了表中哪些行将成为结果集的一部分。HAVING 子句在组级具有相同的含义。它决定哪些组将成为结果集的一部分。

-- The HAVING complements the GROUP BY
SELECT ...
FROM   ...
GROUP BY <columnname>
HAVING <having clause>; -- specify a criterion which can be applied to groups

我们仅检索具有多于 1 个成员的家庭

SELECT lastname
FROM   person
GROUP BY lastname    -- grouping over lastname
HAVING count(*) > 1; -- more than one person within the group

所有只有一个成员的家庭都不再是结果的一部分。

在第二个示例中，我们关注的是那些在firstname列上满足标准的组。假设firstname不是分组列。

-- Groups containing a person whose firstname has more than 4 characters: 5 resulting rows
SELECT lastname
FROM   person
GROUP BY lastname
HAVING max(length(firstname)) > 4; -- max() returns ONE value (the highest one) for all rows of each 'lastname'-group

结果显示了 Baker、de Winter、Goldstein、Rich 和 Stefanos 五个家庭（但没有显示firstname很长的行）。

请注意，此结果与以下类似问题非常不同：firstname 超过四个字符的人

-- Persons whose firstname has more than four characters: six resulting rows!!
SELECT lastname, firstname
FROM   person
WHERE  length(firstname) > 4;
-- no GROUP BY and no HAVING. The WHERE isn't an equivalent replacement for the HAVING!!

额外的行从哪里来？在 de Winter 家庭中，有两个人的firstname 超过四个字符：James 和 Victor。因为在没有 GROUP BY 的命令中，我们选择的是人而不是家庭，所以这两行都单独显示。

总而言之，我们可以说 HAVING 子句决定哪些组是结果集的一部分，哪些不是。

总体情况

[编辑 | 编辑源代码]

GROUP BY 和 HAVING 子句是 SELECT 命令的一部分，我们可以根据需要将它们与 SELECT 的任何其他子句组合。只有子句的顺序是强制性的。

-- This is the obligatory order of clauses
SELECT ...
FROM   ...
WHERE  ...
GROUP BY ...
HAVING   ...
ORDER BY ...
;

如前所述，WHERE 子句在行级起作用，而 HAVING 子句在组级起作用。首先，WHERE 被评估，然后是 GROUP BY，然后是 HAVING，然后是 ORDER BY，最后是 SELECT。每个步骤都基于上一步的结果。

最后，我们提供两个额外的示例

-- Are there persons born on the same day?
SELECT date_of_birth  -- In a later chapter, you will learn how to select the name of these persons.
FROM   person
GROUP BY date_of_birth
HAVING count(date_of_birth) > 1 -- more than one on the same day?
ORDER BY date_of_birth;

-- Families with long first- and lastname. Comment out some lines to see differences to the original query.
SELECT lastname, count(*) as cnt 
FROM   person
WHERE  length(firstname) > 4  
GROUP BY lastname
HAVING length(lastname) > 4
ORDER BY cnt desc, lastname
;

练习

[编辑 | 编辑源代码]

是否有人在同一天出生在同一个城市？提示：对这两个标准进行分组

点击查看解决方案
SELECT date_of_birth, place_of_birth
FROM   person
GROUP BY date_of_birth, place_of_birth
HAVING   count(*) > 1;

根据以下公式对人员进行分类：“round (weight / 10)”: 10 到 19 公斤 -> 1, 20 到 29 公斤 -> 2, ...
每个类别有多少人？

点击查看解决方案
SELECT round (weight / 10), count(*)
FROM   person
GROUP BY round (weight / 10)
-- ORDER BY round (weight / 10)  -- order by category
ORDER BY count(*)                -- order by frequency
;

contact 表中使用了哪些联系类型以及频率？

点击查看解决方案
SELECT contact_type, count(*)
FROM   contact
GROUP BY contact_type
-- ORDER BY contact_type  -- order by contact_type
ORDER BY count(*)         -- order by frequency
;

将上述结果限制为出现次数超过一次的联系类型。

点击查看解决方案
SELECT contact_type, count(*)
FROM   contact
GROUP BY contact_type
HAVING   count(*) > 1
-- order by contact_type  -- order by contact_type
ORDER BY count(*)         -- order by frequency
;

是否有人从事超过 2 个爱好？提示：检查 person_hobby 表。

点击查看解决方案
SELECT person_id, count(*)
FROM   person_hobby
GROUP BY person_id
HAVING   count(*) > 2
;

是否有人只从事一个爱好？

点击查看解决方案
SELECT person_id, count(*)
FROM   person_hobby
GROUP BY person_id
HAVING   count(*) = 1
;

是否有人没有从事任何爱好？

点击查看解决方案
There are persons, who do not perform a hobby. But the nearby formulation 'count(*) = 0' 
will not lead to the expected result because for such persons there are no rows 
in table person_hobby, so the DBMS cannot create any group and hence cannot display anything.

Looking for something that does NOT exist is often more difficult than looking for the 
existence of something. In such cases, you usually have to use one of: NOT EXISTS, NOT IN,
a combination of OUTER JOIN and IS NULL, a combination of OUTER JOIN and MINUS together
with INNER JOIN.


SELECT：联接操作 结构化查询语言
SELECT：分组 		SELECT：IS NULL 谓词
检索自“https://wikibooks.cn/w/index.php?title=Structured_Query_Language/SELECT:_Grouping&oldid=4099517”
华夏公益教科书