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

q9yhzks0 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(276)

我有一份 venue 哪里不一样 teams 我能演奏一首曲子 match . 现在，一个 team 对于每个 match 我可以得到一个 attendance 对于 venue ，所以我在计算 AVG 的 attendance ，问题是我将结果分组为一个 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查询的实际结果是：

{
    "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"
}

sql mysql

来源：https://stackoverflow.com/questions/51347323/how-to-calculate-different-avg-for-the-same-record

4条答案

按热度按时间

wj8zmpe11#

你应该加入主队

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

赞(0）回复(0）举报 2021-06-20

vhmi4jdf2#

你要加入 team_info 表仅使用 venue_id . 这条路每行从 match 将加入到每一个团队中 team_info . 你应该添加条件 i.team_id = m.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, -- no such column in sample data
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 -- this is the fix
INNER JOIN team t ON t.id = i.team_id
WHERE m.round_id = 28
GROUP BY m.venue_id, t.name
ORDER BY average DESC

结果：

| venue_id | min_attendance | max_attendance | venue_sum |           venue_name | average |        team_name |
|----------|----------------|----------------|-----------|----------------------|---------|------------------|
|       10 |           4500 |           4500 |      4500 | Stadiumi Loro Boriçi |    4500 | Vllaznia Shkodër |
|       10 |            300 |           6000 |     31600 | Stadiumi Loro Boriçi | 1858.82 | Skënderbeu Korçë |

小提琴：http://sqlfiddle.com/#!9/9aaf7c9/1号
但你可以跳过 team_info 表并加入 team 表直接到 match :

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, -- no such column in sample data
t.name AS team_name
FROM `match` m
INNER JOIN venue v ON v.id = m.venue_id
INNER JOIN team t ON t.id = m.home_team_id  -- this is the fix
WHERE m.round_id = 28
GROUP BY m.venue_id, t.name
ORDER BY average DESC

结果是一样的。

赞(0）回复(0）举报 2021-06-20

qncylg1j3#

如果你想每个队都这样，你需要把每一行分成两行——一行是主队，一行是客队。
以下是每个场地每个团队的结果：

select venue_id, team_id,
       min(venue_attendance),
       max(venue_attendance),
       avg(venue_attendance)
from ((select m.venue_id, m.home_team_id as team_id, venue_attendance
       from match m
      ) union all
      (select m.venue_id, m.away_team_id as team_id, venue_attendance
       from match m
      )
     ) m
group by venue_id, team_id;

我将让您使用联接将名称和容量输入到查询中。
下面是一个简化的sql fiddle，按地点和团队显示结果。与上述查询的唯一区别是 matches 而不是 match ，因为后者是一个保留字。

赞(0）回复(0）举报 2021-06-20

lhcgjxsq4#

我认为要在查询中多次获得体育场，必须使用分析函数而不是GROUPBY子句。

SELECT
    venue_id,
    min_attendance,
    max_attendance,
    venue_sum,
    venue_name,
    round(average,2) AS average,
    team_name
FROM
    (
        SELECT
            m.venue_id,
            MIN(m.venue_attendance) OVER(
                PARTITION BY home_team_id
            ) AS min_attendance,
            MAX(m.venue_attendance) OVER(
                PARTITION BY home_team_id
            ) AS max_attendance,
            SUM(m.venue_attendance) OVER(
                PARTITION BY home_team_id
            ) AS venue_sum,
            v.name AS venue_name,
            AVG(m.venue_attendance) OVER(
                PARTITION BY home_team_id
                ORDER BY
                    NULL
            ) AS average,
  --M.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
    )
ORDER BY
    average DESC;

希望有帮助。
敬安基特

赞(0）回复(0）举报 2021-06-20

我来回答

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

4条答案

相关问题

热门标签

最新问答