mysql 聚合函数

小灰灰 2022-05-22 09:09 219阅读 0赞

#### 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）语法：Ｇｒｏｕｐ　ＢＹ　＊　ＷＩＴＨ　ＲＯＬＬＵＰ

3）实例：（以现有项目的退货表查询为例）

-- ｓｑｌ１：按供应商分组查询退货总金额
    SELECT SUM(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id;
    
    -- ｓｑｌ２：按供应商分组查询退货总金额，并且计算所有退货总金额
    SELECT r.comp_id,SUM(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id WITH ROLLUP;

SQL１返回的是各个供应商的退货金额，如果你还想确定所有供应商的退货总金额，就需要自己添加单个值或运行额外的查询。若使用ROLLUP，则不需要进行额外的查询。ＳＱＬ２的返回结果的最后一行是查询到的所有数据金额的汇总，两个查询结果如下图所示：  
![这里写图片描述][70]

![这里写图片描述][70 1]

##### 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默认是关闭的，所以查询不会出错。

[70]: /images/20220522/0515a3572cbf4bdd88b3a80110c2b23f.png
[70 1]: /images/20220522/b76d8389819b4478986705f4261410e0.png