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

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，则不需要进行额外的查询。ＳＱＬ２的返回结果的最后一行是查询到的所有数据金额的汇总，两个查询结果如下图所示：

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