mysql 聚合函式

1. AVG()

1)說明:返回表示式的平均值,加上DISTINCT可選項用來返回表示式中不同值的平均值。如果沒有匹配到任何資料,AVG()返回NULL.

2)語法:AVG([DISTINCT] expr)

3)例項:(以現有專案的退貨表查詢為例)

-- 查詢退貨單表wms_order_id不重複的結果數,不重複的order_money求和,不重複的order_money求平均
SELECT COUNT(DISTINCT r.wms_order_id) AS num,SUM(DISTINCT r.order_money) AS total,AVG(DISTINCT r.order_money) AS money FROM wms_orders_return r;
-- 查詢退貨單表(不考慮wms_order_id的重複),order_money求和(不考慮重複),order_money求平均(不考慮重複)
SELECT COUNT(r.wms_order_id) AS num,SUM(r.order_money) AS total,AVG(r.order_money) AS money FROM wms_orders_return r;

2. COUNT()

1)說明:返回SELECT語句中查詢到的表示式中的值不是NULL的行數,結果是BIGINT型別。如果沒有匹配到任何行數,結果返回0.

2)語法:COUNT(expr),COUNT([DISTINCT] expr)

3)例項:(以現有專案的退貨表查詢為例)

-- 查詢退貨單表所有的結果數
SELECT COUNT(*) FROM wms_orders_return; 
-- 查詢退貨單表中wms_order_id不重複的結果數
SELECT COUNT(DISTINCT wms_order_id) FROM wms_orders_return;
-- 查詢退貨單表中不考慮wms_order_id的重複的結果數
SELECT COUNT(wms_order_id) FROM wms_orders_return;

3. MAX()

1)說明:返回一組值中的最大值。如果新增DISTINCT運算子,則MAX函式返回不同值的最大值,它與所有值的最大值相同。 這意味著DISTINCT運算子不會對MAX函式產生任何影響(用不用DISTINCT運算子都可以)。如果沒有任何資料匹配,則結果返回NULL.

2)語法:MAX(DISTINCT expr)

3)例項:(以現有專案的退貨表查詢為例)

-- 查詢退貨表中退貨金額最大的結果
SELECT MAX(r.order_money) FROM wms_orders_return r;
-- 查詢退貨表中退貨金額的最大值,按賣家id分組,金額排序
SELECT r.id,MAX(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id ORDER BY MAX(r.order_money);
-- 查詢退貨表中退貨金額的最大值,按賣家id分組,金額排序,並且退貨金額大於100的結果
SELECT r.comp_id,MAX(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id HAVING MAX(r.order_money)>100 ORDER BY MAX(r.order_money);
Group By 的修改

GROUP BY子句可以使用ROLL UP修改,它會導致額外的行被新增到最終的結果。這些行表示高階彙總操作.

2)語法:Group BY * WITH ROLLUP

3)例項:(以現有專案的退貨表查詢為例)

-- sql1:按供應商分組查詢退貨總金額
SELECT SUM(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id;
-- sql2:按供應商分組查詢退貨總金額,並且計算所有退貨總金額
SELECT r.comp_id,SUM(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id WITH ROLLUP;

SQL1返回的是各個供應商的退貨金額,如果你還想確定所有供應商的退貨總金額,就需要自己新增單個值或執行額外的查詢。若使用ROLLUP,則不需要進行額外的查詢。SQL2的返回結果的最後一行是查詢到的所有資料金額的彙總,兩個查詢結果如下圖所示:
這裡寫圖片描述

這裡寫圖片描述

MySQL對Group By 的處理

若需要Group By的列不在Select查詢欄位內,則查詢是非法的。例如:

SELECT r.id,r.order_money FROM wms_orders_return r GROUP BY r.comp_id ORDER BY MAX(r.order_money);

在 MySQL5.7.5及以上版本實現了函式依賴檢測。如果ONLY_FULL_GROUP_BY模式開啟(預設是開啟的),則MYSQL會拒絕查詢,以上查詢就會出錯:“[Err] 1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘uqi_union.r.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”;在5.7.5版本之前,MYSQL未開啟檢測,並且ONLY_FULL_GROUP_BY預設是關閉的,所以查詢不會出錯。