mysql 數據匯總與分組

Posted on by

我們經常需要匯總數據而不用把它們實際檢索出來，為此MySQL提供瞭專門的函數。使用這些函數，MySQL查詢可用於檢索數據，以便分析和報表生成。

這種類型的檢索例子有以下幾種：

  • 確定表中行數（或者滿足某個條件或包含某個特定值的行數）。
  • 獲得表中行組的和。
  • 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值

如:

AVG() 返回某列的平均值
COUNT() 返回某列的行數
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

舉個例子:

select AVG(prod_price) as avg_price
from products

再比如說：

這裡有些函數裡面可以設置distinct，比如avg，如果設置瞭的話，那麼會有一個這樣的現象。就是隻統計瞭不同值之間的平均值，具體看業務需求。

下面介紹分組數據:

返回每個供應商提供的產品數目:

select COUNT(*) as num_prods,vend_id
from products
GROUP BY vend_id

使用group by 需要註意的地方:

  • GROUP BY子句可以包含任意數目的列。這使得能對分組進行嵌套，為數據分組提供更細致的控制
  • 如果在GROUP BY子句中嵌套瞭分組，數據將在最後規定的分組上進行匯總。換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回數據）
  • GROUP BY子句中列出的每個列都必須是檢索列或有效的表達式（但不能是聚集函數）。如果在SELECT中使用表達式，則必須在GROUP BY子句中指定相同的表達式。不能使用別名。
  • 除聚集計算語句外，SELECT語句中的每個列都必須在GROUP BY子句中給出。
  • 如果分組列中具有NULL值，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。
  • GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前。

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

如下所示：

select COUNT(*) as num_prods,vend_id
from products
GROUP BY vend_id with ROLLUP

除瞭能用GROUP BY分組數據外，MySQL還允許過濾分組，規定包括哪些分組，排除哪些分組。例如，可能想要列出至少有兩個訂單的所有顧客。為得出這種數據，必須基於完整的分組而不是個別的行進行過濾。

HAVING和WHERE的差別 這裡有另一種理解方法，WHERE在數據分組前進行過濾，HAVING在數據分組後進行過濾。這是一個重要的區別，WHERE排除的行不包括在分組中。這可能會改變計算值，從而影響HAVING子句中基於這些值過濾掉的分組。 

select COUNT(*) as num_prods,vend_id
from products
GROUP BY vend_id
HAVING COUNT(*)>2

那麼，有沒有在一條語句中同時使用WHERE和HAVING子句的需要呢？事實上，確實有。假如想進一步過濾上面的語句，使它返回過去12個月內具有兩個以上訂單的顧客。為達到這一點，可增加一條WHERE子句，過濾出過去12個月內下過的訂單。然後再增加HAVING子句過濾出具有兩個
以上訂單的分組。

select COUNT(*) as num_prods,vend_id
from products
where prod_price>=10
GROUP BY vend_id

下面介紹一下order by：

雖然GROUP BY和ORDER BY經常完成相同的工作，但它們是非常不同的。

這裡為什麼說group by 與 order by完成相同的工作呢？這是因為前面我們發現沒，就是其實看起來好像是經過瞭vend_id的排序的。

我們經常發現用GROUP BY分組的數據確實是以分組順序輸出的。但情況並不總是這樣，它並不是SQL規范所要求的。此外，用戶也可能會要求以不同於分組的順序排序。僅因為你以某種方式分組數據（獲得特定的分組聚集值），並不表示你需要以相同的方式排序輸出。

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

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

例如：

select COUNT(*) as num_prods,vend_id
from products
where prod_price>=10
GROUP BY vend_id
ORDER BY num_prods

select 語句順序:

SELECT 要返回的列或表達式 是
FROM 從中檢索數據的表 僅在從表選擇數據時使用
WHERE 行級過濾 否
GROUP BY 分組說明 僅在按組計算聚集時使用
HAVING 組級過濾 否
ORDER BY 輸出排序順序 否
LIMIT 要檢索的行數 否

到此這篇關於mysql 數據匯總與分組的文章就介紹到這瞭,更多相關mysql 分組內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：