MySQL avg() 和 count() 在一个带有 group by 的语句中

Question

今天我正在与 MySQL 进行斗争:我有两个表，其中包含类似的记录(实际上还有更多列，但我认为这不相关):

表测光:

id, value
1000, 0.117
1000, 0.689
1001, 0.050
...

表资源(此表中每个 id 不超过一条记录):

id, number_residents
1001, 2
...

我尝试获取以下格式的结果:

number_residents, avg, count(id)
2, 0.1234, 456
3, 0.5678, 567
...

换言之:我尝试找出具有相同 number_residents 的 value 字段的平均值。 id字段是两个表之间的连接。 count(id) 列应该显示在该 number_residents 中找到了多少个 id。我可以提出的查询如下:

select number_residents,count(distinct Metering.id),avg(value)
  from Metering, Res
  where Metering.id = Res.id
  group by number_residents;

结果看起来像我搜索的内容，但当我尝试验证它们时，我变得不安全。我一开始在没有 distinct 的情况下尝试过，但这会导致结果计数列中的值过高。我的说法是否正确，可以得到我想要的东西？我认为这可能与执行顺序有关，例如询问 here ，但实际上我找不到任何关于此的官方文档...

感谢您的帮助!

Answer 1

从表名称来看，Res 是“父”表，Metering us 是“子”表 - 即每个住宅有 0-n 个计量表.

您使用了“老派”连接(我的意思是旧 - 连接语法已经存在 25 年了)，它们是内部连接，意思是住宅没有计量将不参与结果。

使用外部连接:

select
    number_residents,
    count(distinct r.id) residences_count,
    avg(value) average_value
from Res r
left join Metering m on m.id = r.id
group by number_residents

虽然meterings.id = res.id，但使用左连接对它们进行计数可能会产生不同的结果:我已将计数更改为对住宅进行计数，这对于左加入意味着没有计量的住宅仍然有效。

现在，空值(即从没有匹配行的左连接表中获得的值)不参与 avg() - 无论对于分子还是分母，如果您希望在计算平均值时计算没有任何计量的住宅(就好像它们有一个零计量来除总值(value)一样)，请使用以下查询:

select
    number_residents,
    count(distinct r.id) residences_count,
    sum(value) / count(r.id) average_value
from Res r
left join Metering m on m.id = r.id
group by number_residents

因为 res.id 永远不会为空，所以 count(r.id) 会计算每个没有任何计量的住宅的计量数量加 1。