mysql - 如何让 MIN、MAX、AVG 与内连接一起使用？

Question

我需要从数据集中获取每种行业类型的平均值、最小值和最大值。当我使用 MIN、MAX、AVG 函数时，它仅返回与 Amount 列相同的值。

我的尝试

SELECT c.Custid, c.Cname, c.City, c.IndustryType, o.OrderNo, o.OrderDate, o.SalesPersonID, o.Amount, 
AVG(o.Amount) AS 'Average Amount',
MIN(o.Amount) AS 'Minimum Amount',
MAX(o.Amount) AS 'Maximum Amount'
FROM customer c
INNER JOIN orders o
ON c.custid = o.custid
GROUP BY c.IndustryType,o.orderdate;

Answer 1

SQL 标准要求 GROUP BY 子句包含ALL不使用聚合函数的列(即 min()、max()、avg() 等)。

SQL92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

然而，MySQL 实现了非标准的 GROUP BY 附加功能，并且在旧版本的 MySQL 中，它默认采用非标准方法。 (有一个服务器设置，您可以更改它以从标准分组语法翻转到非标准分组语法。)从 MySQL 5.7.5 开始，默认设置更改为 SQL 标准方法。

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

参见:ONLY_FULL_GROUP_BY

几乎可以肯定，您的原始查询不起作用的原因是出于上述考虑。许多人会告诉您，使用非标准方法是不好的做法，实际上，如果您进一步研究该主题，您将了解到非标准方法为所有未包含在 group by 子句中的列返回“不确定的结果”(除了在极少数情况下)。始终使用标准方法会更好。例如

SELECT
      c.IndustryType
    , o.OrderDate
    , AVG(o.Amount) AS "Average Amount"
    , MIN(o.Amount) AS "Minimum Amount"
    , MAX(o.Amount) AS "Maximum Amount"
FROM customer c
INNER JOIN orders o ON c.custid = o.custid
GROUP BY
      c.IndustryType
    , o.OrderDate
;

或

SELECT
      c.Custid
    , c.Cname
    , c.City
    , c.IndustryType
    , o.OrderNo
    , o.OrderDate
    , o.SalesPersonID
    , o.Amount
    , AVG(o.Amount) AS "Average Amount"
    , MIN(o.Amount) AS "Minimum Amount"
    , MAX(o.Amount) AS "Maximum Amount"
FROM customer c
INNER JOIN orders o ON c.custid = o.custid
GROUP BY
      c.Custid
    , c.Cname
    , c.City
    , c.IndustryType
    , o.OrderNo
    , o.OrderDate
    , o.SalesPersonID
    , o.Amount
;

值得一提的是，您的原始查询似乎需要为(IndustryType 和 OrderDate)的每个唯一组合计算聚合，但要在更多详细信息行上重复这些聚合。有一些“窗口函数”允许这种情况发生，它们正在开发中，打算与 MySQL 8.x 一起发布，这些函数已经存在于其他数据库中，例如 DB2、Oracle、SQL Server、Postgre、SQL Lite、MariaDB(以及更多)。

窗口聚合的语法如下所示:

SELECT
      c.Custid
    , c.Cname
    , c.City
    , c.IndustryType
    , o.OrderNo
    , o.OrderDate
    , o.SalesPersonID
    , o.Amount
    , AVG(o.Amount) OVER(PARTITION BY c.IndustryType, o.OrderDate)
        AS "Average Amount" 
    , MIN(o.Amount) OVER(PARTITION BY c.IndustryType, o.OrderDate)
        AS "Minimum Amount"
    , MAX(o.Amount) OVER(PARTITION BY c.IndustryType, o.OrderDate)
        AS "Maximum Amount"
FROM customer c
INNER JOIN orders o ON c.custid = o.custid