时间戳

首页 > 解决方案 > 如何计算同一记录的不同平均值？

mysql - 如何计算同一记录的不同平均值？

问题描述

我有一个venue不同的地方teams可以播放的列表match。现在，一个teamfor eachmatch可以得到一个attendancefor the venue，所以我试图计算 的AVG，attendance问题是我得到的结果分组为 one venue，那是因为我使用了一个聚合函数并且我被迫使用GROUP BY，查询应该返回相同venue但不同teams且AVG attendance当然不同。

询问

SELECT m.venue_id,
MIN(m.venue_attendance) AS min_attendance,
MAX(m.venue_attendance) AS max_attendance,
SUM(m.venue_attendance) AS venue_sum,
v.name AS venue_name,
ROUND(AVG(m.venue_attendance), 2) AS average,
v.capacity, t.name AS team_name
FROM `match` m
INNER JOIN venue v ON v.id = m.venue_id
INNER JOIN team_info i ON i.venue_id = m.venue_id
INNER JOIN team t ON t.id = i.team_id
WHERE m.round_id = :round_id
GROUP BY m.venue_id, t.name
ORDER BY average DESC

数据样本

匹配

id      | round_id | home_team_id | away_team_id | venue_id | venue_attendance
2506177     28            70             71           10           6000
2506195     28            70             76           10            500
2506204     28            70             69           10           2000
2506219     28            70             72           10            500
2506230     28            70           2517           10            300
2506235     28            70           2522           10            500
2506244     28            70          10049           10            400
2506252     28            70          12573           10           6000
2506258     28          2518             70           10           4500
2506267     28            70             71           10           1000
2506285     28            70             76           10            700
2506294     28            70             69           10           1500
2506303     28            70           2518           10           2500
2506309     28            70             72           10           1200
2506320     28            70           2517           10           1200
2506325     28            70           2522           10            800
2506334     28            70          10049           10           5500
2506342     28            70          12573           10           1000

场地

id | name                 | address          | zip_code | city    | phone |
10   Stadiumi Loro Boriçi  Rruga Musa Luli 1    4000      Shkodër   NULL

团队信息

team_id | venue_id |
  70         10 
2518         10

团队

  id | name 
  70   Skënderbeu Korçë
2518   Vllaznia Shkodër

输出

{
    "venue_id": "10",
    "min_attendance": "300",
    "max_attendance": "6000",
    "venue_sum": "36100",
    "venue_name": "Stadiumi Loro Boriçi",
    "average": "2005.56",
    "capacity": "16000",
    "team_name": "Vllaznia Shkodër"
}

预期产出

{
    "venue_id": "10",
    "min_attendance": "300",
    "max_attendance": "6000",
    "venue_sum": "31600",
    "venue_name": "Stadiumi Loro Boriçi",
    "average": "1858",
    "capacity": "16000",
    "team_name": "Vllaznia Shkodër"
},
{
    "venue_id": "10",
    "min_attendance": "4500",
    "max_attendance": "4500",
    "venue_sum": "4500",
    "venue_name": "Stadiumi Loro Boriçi",
    "average": "4500",
    "capacity": "16000",
    "team_name": "Skënderbeu Korçe"
}

正如你所看到的，venue_sum应该只team在主场比赛时计算，看场home_team_id，平均值是 的总和venue_sum / matches played by the team，例如，Vllaznia Shkodër我们有平均：31600 / 17 = 1858。

完整数据库：https ://files.fm/u/2xwgkaxz

要访问示例中的数据，只需运行：

SELECT * FROM `match` WHERE round_id = 28

我该如何处理？

scaisEdge 答案：

scaisEdge 提出的解决方案仅适用于精化结果，实际上现在的平均值是正确的。主要问题仍然存在，事实上 scaisEdge 查询的实际结果是这样的：

{
    "venue_id": "10",
    "min_attendance": "300",
    "max_attendance": "6000",
    "venue_sum": "31600",
    "venue_name": "Stadiumi Loro Boriçi",
    "average": "1858.82",
    "capacity": "16000",
    "team_name": "Vllaznia Shkodër"
}

还有其他场地，但我想把注意力集中在这个上，正如我之前所说的，我需要为不同的团队返回同一个场地，所以我也应该得到：

{
    "venue_id": "10",
    "min_attendance": "4500",
    "max_attendance": "4500",
    "venue_sum": "4500",
    "venue_name": "Stadiumi Loro Boriçi",
    "average": "4500",
    "capacity": "16000",
    "team_name": "Skënderbeu Korçe"
}

但我只得到：

{
    "venue_id": "10",
    "min_attendance": "300",
    "max_attendance": "6000",
    "venue_sum": "31600",
    "venue_name": "Stadiumi Loro Boriçi",
    "average": "1858.82",
    "capacity": "16000",
    "team_name": "Vllaznia Shkodër"
}

所以预期的最终结果必须包括：

{
    "venue_id": "10",
    "min_attendance": "300",
    "max_attendance": "6000",
    "venue_sum": "31600",
    "venue_name": "Stadiumi Loro Boriçi",
    "average": "1858.82",
    "capacity": "16000",
    "team_name": "Vllaznia Shkodër"
},
{
    "venue_id": "10",
    "min_attendance": "4500",
    "max_attendance": "4500",
    "venue_sum": "4500",
    "venue_name": "Stadiumi Loro Boriçi",
    "average": "4500",
    "capacity": "16000",
    "team_name": "Skënderbeu Korçe"
}

标签： mysqlsql

解决方案

你应该加入 home_team_id 

  SELECT m.venue_id,
  MIN(m.venue_attendance) AS min_attendance,
  MAX(m.venue_attendance) AS max_attendance,
  SUM(m.venue_attendance) AS venue_sum,
  v.name AS venue_name,
  ROUND(AVG(m.venue_attendance), 2) AS average,
  v.capacity, t.name AS team_name
  FROM `match` m
  INNER JOIN venue v ON v.id = m.venue_id
  INNER JOIN team_info i ON i.venue_id = m.venue_id and i.team_id = m.home_team_id
  INNER JOIN team t ON t.id = m.home_team_id
  WHERE m.round_id = :round_id
  GROUP BY m.venue_id, t.name
  ORDER BY average DESC

推荐阅读