13. 分組資料

hisun9發表於2024-10-29

1. 資料分組

分組允許把資料分為多個邏輯組，以便能對每個組進行聚集計算

2. 建立分組

分組是在SELECT語句的GROUP BY子句中建立的。

比如：

select vend_id, count(*) as num_prods
from products
group by vend_id;

輸出如下：

img

上面的SELECT語句指定了兩個列，vend_id包含產品供應商的ID，num_prods為計算欄位（用COUNT(*)函式建立）。GROUP BY子句指示MySQL按vend_id排序並分組資料。這導致對每個vend_id而不是整個表計算num_prods一次。

從輸出中可以看到，供應商1001有3個產品，供應商1002有2個產品，供應商1003有7個產品，而供應商1005有2個產品。

因為使用了GROUP BY，就不必指定要計算和估值的每個組了。系統會自動完成。

GROUP BY子句指示MySQL分組資料，然後對每個組而不是整個結果集進行聚集。

在具體使用GROUP BY子句前，需要知道一些重要的規定:

  • GROUP BY子句可以包含任意數目的列。這使得能對分組進行巢狀（“GROUP BY 可以巢狀分組”是指在 GROUP BY 子句中可以指定多個列進行分組），為資料分組提供更細緻的控制。

  • 如果在GROUP BY子句中巢狀了分組，資料將在最後規定的分組上進行彙總。換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回資料）。

    補充：

    “如果在 GROUP BY 子句中巢狀了分組，資料將在最後規定的分組上進行彙總”指的是在多層分組時，最終的聚合結果是基於最後一個分組條件進行彙總的。這意味著在所有指定的分組條件中，最後一個分組條件決定了最終的彙總結果。

    具體解釋

    • 分組的順序： 當你在 GROUP BY 中指定多個列時，資料庫會首先按第一個列分組，然後在每個分組內再按第二個列分組，以此類推，直到最後一個列。最終的彙總是基於最後一個列。

    • 最終彙總的依據： 最後一個列的分組決定了如何計算聚合函式（如 SUM、COUNT 等）。這意味著在所有的分組中，最後一個分組的結果將是你查詢返回的主要彙總資訊。

  • GROUP BY子句中列出的每個列都必須是檢索列或有效的表示式（但不能是聚集函式）。如果在SELECT中使用表示式，則必須在GROUP BY子句中指定相同的表示式。不能使用別名。（不太理解，總感覺有點問題，不管這條了）

    不過這樣寫是錯的：

    img

    這樣寫也是錯的：

    img

  • 除聚集計算語句外，SELECT語句中的每個列都必須在GROUP BY子句中給出。

  • 如果分組列中具有NULL值，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。

  • GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前。

補充：

使用ROLLUP：

使用WITH ROLLUP關鍵字，可以得到每個分組以及每個分組彙總級別（針對每個分組）的值。

比如：

select vend_id, count(*) as num_prods
from products
group by vend_id with rollup;

輸出如下：

img

3. 過濾分組

除了能用GROUP BY分組資料外，MySQL還允許過濾分組，規定包括哪些分組，排除哪些分組。

例如，可能想要列出至少有兩個訂單的所有顧客。為得出這種資料，必須基於完整的分組而不是個別的行進行過濾。

我們已經看到了WHERE子句的作用（第6章中引入）。但是，在這個例子中WHERE不能完成任務，因為WHERE過濾指定的是行而不是分組。事實上，WHERE沒有分組的概念。

MySQL為此目的提供了另外的子句，那就是HAVING子句。

HAVING非常類似於WHERE。事實上，目前為止所學過的所有型別的WHERE子句都可以用HAVING來替代。唯一的差別是WHERE過濾行，而HAVING過濾分組。

補充：

  • HAVING支援所有WHERE運算子：

    在第6章和第7章中，我們學習了WHERE子句的條件（包括萬用字元條件和帶多個運算子的子句）。所學過的有關WHERE的所有這些技術和選項都適用於HAVING。它們的句法是相同的，只是關鍵字有差別。

怎麼過濾分組呢？請看：

select cust_id, count(*) as orders
from orders
group by cust_id
having count(*) >= 2;

輸出如下：

img

最後一行增加了HAVING子句，它過濾COUNT(*) >=2（兩個以上的訂單）的那些分組。

注意：

  • HAVING和WHERE的差別

    這裡有另一種理解方法，WHERE在資料分組前進行過濾，HAVING在資料分組後進行過濾。這是一個重要的區別，WHERE排除的行不包括在分組中。

那麼，有沒有在一條語句中同時使用WHERE和HAVING子句的需要呢？事實上，確實有。

假如想進一步過濾上面的語句，使它返回過去12個月內具有兩個以上訂單的顧客。為達到這一點，可增加一條WHERE子句，過濾出過去12個月內下過的訂單。然後再增加HAVING子句過濾出具有兩個以上訂單的分組。

比如下面的例子，它列出具有2個（含）以上、價格為10（含）以上的產品的供應商：

select vend_id, count(*) as num_prods
from products
where prod_price >= 10
group by vend_id
having count(*) >= 2;

輸出如下：

img

4. 分組和排序

GROUP BY 和 ORDER BY 是非常不同的。表13-1彙總了它們之間的差別。

img

（這個圖也有點不理解，特別是第三條，不知道這個對不對）

表13-1中列出的第一項差別極為重要。我們經常發現用GROUP BY分組的資料確實是以分組順序輸出的。但情況並不總是這樣，它並不是SQL規範所要求的。

此外，使用者也可能會要求以不同於分組的順序排序。僅因為你以某種方式分組資料（獲得特定的分組聚集值），並不表示你需要以相同的方式排序輸出。

應該提供明確的ORDER BY子句，即使其效果等同於GROUP BY子句也是如此。

解釋下上面那段話是啥意思：

img

注意：

  • 不要忘記ORDER BY：

    一般在使用GROUP BY子句時，應該也給出ORDER BY子句。這是保證資料正確排序的唯一方法。千萬不要僅依賴GROUP BY排序資料。

為說明GROUP BY和ORDER BY的使用方法，舉個例子，它檢索總計訂單價格大於等於50的訂單的訂單號和總計訂單價格：

select order_num, sum(quantity * item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity * item_price) >= 50;

輸出如下：

img

為按總計訂單價格排序輸出，需要新增ORDER BY子句，如下所示：

select order_num, sum(quantity * item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity * item_price) >= 50
order by ordertotal;

img

在這個例子中，GROUP BY子句用來按訂單號（order_num列）分組資料，以便SUM(*)函式能夠返回總計訂單價格。HAVING子句過濾資料，使得只返回總計訂單價格大於等於50的訂單。最後，用ORDER BY子句排序輸出。

5. SELECT子句順序

表13-2以在SELECT語句中使用時必須遵循的次序，列出迄今為止所學過的子句。

img
img

相關文章