MySQL：聚合结果不正确，但不确定原因是什么？

Question

因此，我试图找出每个月（本例中为6月至7月）销售额变化最大的客户。

以下是我为了这个实践而创建的模型数据：

mysql> select * from Sales1;
+------------+------------+-----------------+
| CustomerID | mydate     | purchase_amount |
+------------+------------+-----------------+
|         10 | 1996-08-02 |         2540.78 |
|         20 | 1999-01-30 |         1800.54 |
|         30 | 1995-07-14 |          460.33 |
|         10 | 1998-06-29 |            2400 |
|         50 | 1998-02-03 |          600.28 |
|         60 | 1998-03-02 |             720 |
|         10 | 1998-07-06 |             150 |
+------------+------------+-----------------+
mysql> select * from Sales2;
+------------+------------+-----------------+
| CustomerID | mydate     | purchase_amount |
+------------+------------+-----------------+
|         10 | 1996-06-02 |          540.78 |
|         20 | 1999-09-30 |          800.54 |
|         30 | 1995-07-14 |           60.33 |
|         40 | 1998-01-29 |             400 |
|         10 | 1998-07-03 |         2600.28 |
|         60 | 1998-03-02 |            1720 |
|         70 | 1998-05-04 |            4150 |
+------------+------------+-----------------+

根据以上两个表，答案应该是CustomerID为10的客户，1998年6月至7月销售额增加350.28。

下面是我实现目标的代码；基本上，我创建了两个视图，一个是每年每个客户6月份的销售额总和，另一个是每年每个客户7月份的销售额总和，然后从7月份的销售额中减去6月份的销售额：

CREATE VIEW sum6 AS
(
SELECT CustomerID, 
YEAR(mydate) AS year, 
MONTH(mydate) AS month,
SUM(purchase_amount) as amount
FROM Sales1
GROUP BY CustomerID, year, month
HAVING month = 6
) 
UNION ALL (
SELECT CustomerID,
YEAR(mydate) AS year, 
MONTH(mydate) AS month,
SUM(purchase_amount) as amount
FROM Sales2
GROUP BY CustomerID, year, month
HAVING month = 6) 
;

CREATE VIEW sum7 AS
(
SELECT CustomerID, 
YEAR(mydate) AS year, 
MONTH(mydate) AS month,
SUM(purchase_amount) as amount
FROM Sales1
GROUP BY CustomerID, year, month
HAVING month = 7
) 
UNION ALL (
SELECT CustomerID,
YEAR(mydate) AS year, 
MONTH(mydate) AS month,
SUM(purchase_amount) as amount
FROM Sales2
GROUP BY CustomerID, year, month
HAVING month = 7) 
;

SELECT CustomerID, year, (SUM(sum7.amount)-SUM(sum6.amount)) as diff
FROM sum6
JOIN sum7
USING(CustomerID, year)
GROUP BY CustomerID, year
;

但是，我的输出是：

+------------+------+--------------------+
| CustomerID | year | diff               |
+------------+------+--------------------+
|         10 | 1998 | -2049.719970703125 |
+------------+------+--------------------+

虽然是，CustomerID和year值是正确的，但差异金额是不正确的。

我单独检查了Sum6和Sum7的总和是否正确地由CustomeID和年份计算：

mysql> SELECT CustomerID, year, SUM(amount)
    -> FROM sum7
    -> GROUP BY CustomerID, year
    -> ;
+------------+------+-------------------+
| CustomerID | year | SUM(amount)       |
+------------+------+-------------------+
|         10 | 1998 | 2750.280029296875 |
|         30 | 1995 | 520.6599884033203 |
+------------+------+-------------------+
mysql> SELECT CustomerID, year, SUM(amount)
    -> FROM sum6
    -> GROUP BY CustomerID, year
    -> ;
+------------+------+------------------+
| CustomerID | year | SUM(amount)      |
+------------+------+------------------+
|         10 | 1996 | 540.780029296875 |
|         10 | 1998 |             2400 |
+------------+------+------------------+

他们是这样的，所以我知道组员是正确的。

然后我试着看一下个人的金额：

mysql> SELECT CustomerID, year, SUM(sum7.amount), SUM(sum6.amount)
    -> FROM sum6
    -> JOIN sum7
    -> USING(CustomerID, year)
    -> GROUP BY CustomerID, year
    -> ;
+------------+------+-------------------+------------------+
| CustomerID | year | SUM(sum7.amount)  | SUM(sum6.amount) |
+------------+------+-------------------+------------------+
|         10 | 1998 | 2750.280029296875 |             4800 |
+------------+------+-------------------+------------------+

所以SUM（sum7.amount）是正确的，但是SUM（sum6.amount）是不正确的。但是为什么它们在单独拉的时候可以正确地相加，而在组合的时候只有一个总结错误呢？这种矛盾快把我逼疯了...

Answer 1

多亏了Fritz，我想出了另一个更简单的解决方案（至少对我来说）。

以下是我实现目标的准则：

CREATE VIEW all67 AS
(
SELECT CustomerID, YEAR(mydate) AS year,  MONTH(mydate) AS month, SUM(purchase_amount) AS amount
FROM Sales1
GROUP BY CustomerID, year, month
HAVING month = 6 OR month  = 7 
)
UNION ALL 
(
SELECT CustomerID, YEAR(mydate) AS year,  MONTH(mydate) AS month, SUM(purchase_amount) AS amount
FROM Sales2
GROUP BY CustomerID, year, month
HAVING month = 6 OR month  = 7 
)
;

SELECT CustomerID, year, july.amount - june.amount AS diff
FROM
(
SELECT CustomerID, year, month, SUM(amount) AS amount
FROM all67
GROUP BY CustomerID, year, month
HAVING month = 6
) june
JOIN
(
SELECT CustomerID, year, month, SUM(amount) AS amount
FROM all67
GROUP BY CustomerID, year, month
HAVING month = 7
) july
USING (CustomerID, year)
;

现在我的答案终于完全正确了！！！非常感谢弗里茨。希望我的回答能帮助你们中的许多人提出类似的问题。

干杯！

Answer 2

您将sum6加入到sum7太松散了。使用上一个案例，您的JOIN以某种方式复制记录。(2400 * 2 = 4800)

当您合计它们时，您不知何故从其中一个视图中获得了重复的记录，因为您的联接是如何设置的。你需要检查一下你的情况

为了帮助缩小范围，包括所有行，在验证数据之前不要进行数学运算。从以下内容开始：

SELECT *
FROM sum6
JOIN sum7
USING(CustomerID, year)

并验证只有要配对的行是配对的，然后从那里开始。