根据不同的ID计算平均值，同时保留表中的所有数据?

Question

如果有这样的数据:

+------+----+-------+-------+
| year | id | value | group |
+------+----+-------+-------+
| 2019 |  1 |    10 | A     |
| 2019 |  1 |    10 | B     |
| 2019 |  2 |    20 | A     |
| 2019 |  3 |    30 | A     |
| 2019 |  2 |    20 | B     |
| 2020 |  1 |     5 | A     |
| 2020 |  1 |     5 | B     |
| 2020 |  2 |    10 | A     |
| 2020 |  3 |    15 | A     |
| 2020 |  2 |    10 | B     |
+------+----+-------+-------+

是否有一种方法可以在保留所有数据的同时根据不同的id计算平均value?

我需要这样做，因为我也将有WHERE子句来过滤表中的其他列，但我还需要在没有添加WHERE子句的情况下获得数据的总体视图(这些WHERE过滤器将由我无法控制的OUTERMOST查询中的自动软件添加)。

以group列为例。

对于上面的例子，结果应该是:

Overall --> 20 for 2019 and 10 for 2020

WHERE group = 'A'——>2019年20,2020年10

WHERE group = 'B'——>2019年15,2020年7.5

我试着做以下事情:

SELECT 
year,
AVG(IF(id = LAG(id) OVER (ORDER BY id), NULL, value)) AS avg
FROM table
WHERE group = 'A' -- this clause may or may not exist
GROUP BY year

基本上我在想，如果我按id排序并检查前一行，看看它是否具有相同的id，值应该是NULL，因此它不会被计算在内，但不幸的是，我不能把分析函数放在aggregate函数内。

Answer 1

虽然数据模型不合适且未规范化(您正在冗余地存储值)，但真正的问题是后期自动SQL注入(可选添加的where子句)。

当where子句被添加到你的查询中，一切都很好，因为where子句正确地限制了要考虑的行(a组或B组)。然而，当没有where子句被添加时，你将不得不处理一个聚合的数据集(不同的年份/id行)。后者意味着聚合上的聚合，这可以通过子查询完成，如DineshDB在前面的回答中所示。但是这里你有一个问题，where子句必须在中间结果(子查询)上工作，你说你的软件将where子句添加到主查询中。

令人惊讶的解决方案是创建这三个聚合。在下面的查询中，我混合了MAX(第一次聚合)、AVG OVER(第二次聚合)和DISTINCT(第三次聚合)，这三者可以愉快地共存于一个查询中。不需要子查询。

SELECT DISTINCT
year,
AVG(MAX(value)) OVER (PARTITION BY year)
FROM yourtable
WHERE `group` = ... -- optional where clause
GROUP BY year, id
ORDER BY year;

演示:https://dbfiddle.uk/?rdbms=mysql_8.0&小提琴= 973 ae4f260597392c55f260d3c260084

Answer 2

下面的查询将给出预期的输出。

SELECT 
`Year`,
AVG(DISTINCT `value`*1.0) `value`
FROM table
WHERE `group` = 'B' -- this clause is optional
GROUP BY `Year`;

查询将返回以下结果:

Year | Value
2019 | 20
2020 | 10

SQLFiddle