MySQL - 对某些行进行平均值计算，对所有行进行总和计算

Question

我正在尝试对特定行的值进行平均，但对所有行的总和进行求和。

我有两个表格，一个名为exchanges，另一个名为valid_country。

exchanges

+-----------+----------+--------------+----------+--------+
|     id    |   ref    |     country  |   value  | volume |
+-----------+----------+--------------+----------+--------+
|     1     |  1029    |      DE      |   1 000  |   100  |
+-----------+----------+--------------+----------+--------+
|     2     |  1029    |      US      |   2 000  |   250  |
+-----------+----------+--------------+----------+--------+
|     3     |  1029    |      FR      |   3 500  |   300  |
+-----------+----------+--------------+----------+--------+
|     4     |  1053    |      UK      |   1 200  |   110  |
+-----------+----------+--------------+----------+--------+
|     5     |  1029    |      RU      |     900  |    70  |
+-----------+----------+--------------+----------+--------+

这张表格包含许多参考文献(ref)，它们来自不同的国家，拥有不同的价值和数量。 valid_country

+--------------+--------------+
|     ref      |   country    |
+--------------+--------------+
|    1029      |     US       |
+--------------+--------------+
|    1029      |     RU       |
+--------------+--------------+
|    1053      |     UK       |
+--------------+--------------+

这个表格列出了所有可以计算平均值的“好”国家。

我想得到的查询结果是：

+----------+------------+-------------+
|   ref    | AVG(value) | SUM(volume) |
+----------+------------+-------------+
|  1029    |    1 450   |     720     |
+----------+------------+-------------+
|  1053    |    1 200   |     110     |
+----------+------------+-------------+

首先，参考文献是 GROUP BY。 在表格有效国家的影响下，参考文献1029应该对美国和俄罗斯的值进行AVERAGE，但对所有国家的数量进行SUM。 参考文献1053也是同样的情况，但由于只有一行，所以很容易处理。 这里有一个小的Fiddle。SQL请求是错误的，因为它将所有国家的平均值计算了出来，而不仅仅是正确的那个。

- Tainmar

---

首先，您需要加入两个表，以便在查询中确定哪些国家被认为是“好”的。 - CBroe

（顺便说一句，您的fiddle将第二个表的值插入到第一个表中。由于第一个表也包含这些列，它不会引发错误，但它不会给出实际可测试的结果。） - CBroe

@CBroe 谢谢！我已经编辑了我的 fiddle：http://sqlfiddle.com/#!9/e16969/4 - Tainmar

Answer 1

您可以使用 LEFT JOIN 和 CASE 语句来忽略 AVG 中的一些值 (SQLFiddle)：

SELECT e.ref,
       AVG(CASE WHEN vc.country IS NOT NULL THEN e.value END) AS average,
       SUM(e.volume) AS volume
FROM exchanges e
LEFT JOIN valid_country vc ON ( vc.country = e.country )
GROUP BY e.ref

CASE 返回 NULL 如果没有匹配，而 AVG 忽略这些值：

|  ref | average | volume |
|------|---------|--------|
| 1029 |    1450 |    720 |
| 1053 |    1200 |    110 |

- Peter Lang

---

谢谢。这很好用！我会在我的大型数据库上比较您的查询和Nico Weisenauer的查询，以确定哪一个更快。测试后我会给出答案。 - Tainmar

Answer 2

你可以在MySQL的聚合函数中使用case语句来实现这一点。演示

SELECT e.ref,e.country , AVG(case when c.country = e.country then e.value end) as avge,SUM(volume)
FROM exchanges e left join valid_country as c on e.country = c.country
GROUP BY e.ref

- Vishal JAIN

Answer 3

这个查询使用子查询来提供正确的结果：

SELECT e1.ref,subq.avg,SUM(volume)
FROM exchanges e1,
 (SELECT e2.ref, AVG(value) as avg 
  FROM exchanges e2, valid_country vc
  WHERE e2.country = vc.country
  GROUP BY e2.ref) as subq
WHERE e1.ref = subq.ref
GROUP BY ref

- Nico Weisenauer

---

谢谢。那工作得很好！我正在比较您的查询和Peter Lang在我的大型数据库上的查询，以确定哪个更快。测试后我会给出答案。 - Tainmar

Answer 4

我认为与valid_country的比较需要同时使用ref和country：

SELECT e.ref,
       AVG(CASE WHEN vc.country IS NOT NULL THEN e.value END) AS average,
       SUM(e.volume) AS volume
FROM exchanges e LEFT JOIN
     valid_country vc
     ON vc.country = e.country AND vc.ref = e.ref
GROUP BY e.ref;

这对于您的示例数据并不重要，但对于更大的问题可能很重要。

- Gordon Linoff

---

嗯，很好，Gordon。这确实是我需要的完整比较。 - Tainmar