跳轉到內容

結構化查詢語言/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://wikibook.tw/w/index.php?title=Structured_Query_Language/SELECT:_Grouping&oldid=4099517”
華夏公益教科書