mysql - 使用 MySQL 为每个返回的行执行相关子查询

Question

我正在尝试使用 MySQL 计算有关体育运动员的一些统计数据。数据库有 3 个表。

相关的 Rextester:http://rextester.com/SNAL27886

播放器

玩家列表。

+----+---------+-----------+
| id | team_id | lastname  |
+----+---------+-----------+
|  1 |       1 | Moubandje |
|  2 |       2 | Rüfli     |
|  3 |       3 | Selnaes   |
|  4 |       1 | Somália   |
|  5 |       4 | Kerbrat   |
+----+---------+-----------+

匹配

团队列表。

+----+--------------+--------------+-----+
| id | home_team_id | away_team_id | day |
+----+--------------+--------------+-----+
|  1 |            1 |            2 |   1 |
|  2 |            2 |            1 |   2 |
|  3 |            2 |            3 |   3 |
|  4 |            3 |            4 |   4 |
|  5 |            3 |            5 |   5 |
+----+--------------+--------------+-----+

玩家匹配

球员每场比赛的统计数据。

+-----------+----------+-----------+------------+-------+
| player_id | match_id | rating    | substitute | goals |
+-----------+----------+-----------+------------+-------+
|         1 |        1 |         6 |          0 |     2 |
|         2 |        2 |         5 |          1 |     0 |
|         1 |       10 |         3 |          0 |     0 |
+-----------+----------+---------+-----------+----------+

这是我计算球员各种统计数据(比如他的进球数，或者他的全局平均评分)的查询:

SELECT
  p.id AS p_id,
  p.lastname AS lastname,
  p.team_id as team_id,
  AVG(pm.rating) AS avg_rating,
  COUNT(pm.player_id) AS nb_matches,
  SUM(pm.substitute) AS nb_matches_substitute,
  SUM(pm.goals) AS goals,
  (SUM(pm.goals) / COUNT(pm.player_id)) AS goals_per_matches
FROM
  player p
  INNER JOIN player_match pm ON pm.player_id = p.id
  INNER JOIN `match` m ON pm.match_id = m.id AND (m.home_team_id = p.team_id OR m.away_team_id = p.team_id)
GROUP BY
  p.id,
  p.lastname,
  p.team_id
ORDER BY
  avg_rating DESC, lastname ASC
;

我还想计算该球员在他的球队 的最后 5 场比赛中的平均评分(如果该球员没有参加过一场比赛，他的评分必须为 0)。然后，我想按照这个特定的平均评分对列表的结果进行排序。

这是我的查询，用于检索给定球员在他球队的最后 5 场比赛中的平均评分以及每场比赛的评分作为字符串:

SELECT
  SUM(pm1.rating) / COUNT(m1.id) last_5_matches_rating,
  GROUP_CONCAT(CONCAT(m1.day, '=', COALESCE(pm1.rating, '~')) ORDER BY m1.day)
FROM
  `match` m1
  INNER JOIN (
    SELECT m2.id, m2.home_team_id, m2.away_team_id
    FROM `match` m2
    AND (m2.home_team_id=1 OR m2.away_team_id=1)
    ORDER BY m2.day DESC
    LIMIT 5
  ) last_5_games ON m1.id = last_5_games.id
  LEFT JOIN player_match pm1 ON m1.id = pm1.match_id AND pm1.player_id=4

这是第 1 队第 4 名球员的结果。

有没有办法将最后一个查询作为前一个查询的子查询执行，并按 last_5_matches_rating 对结果进行排序？

我期望的是包含以下列的结果:

| id | lastname | team_id | avg_rating | last_5_matches_rating | nb_matches | ...

Answer 1

我仍然无法获得返回预期结果的查询，但我已切换到另一种明显的方式来完成这项工作。

我在玩家表中添加了两个计算列:last_five_matches_avg_rating 和 last_five_matches_ratings。

每次数据库更新后，都会执行一个脚本:它遍历所有玩家，执行第二个查询以检索统计数据并将这些计算的统计数据存储在相应的列中。

这是这个 PHP 脚本(它使用 Doctrine ORM):

<?php

namespace App;

use App\Entity\Player;
use Doctrine\Common\Persistence\ManagerRegistry;

class StatsComputer
{
    private $doctrine;

    public function __construct(ManagerRegistry $doctrine)
    {
        $this->doctrine = $doctrine;
    }

    public function update()
    {
        $manager = $this->doctrine->getManager();
        $stmt = $manager->getConnection()->prepare(<<<SQL
SELECT
  SUM(pm1.rating) / COUNT(m1.id) last_5_matches_avg_rating,
  GROUP_CONCAT(COALESCE(pm1.rating, '0')) ORDER BY m1.day) last_5_matches_ratings
FROM
  `match` m1
  INNER JOIN (
    SELECT m2.id, m2.home_team_id, m2.away_team_id
    FROM `match` m2
    AND (m2.home_team_id=:team_id OR m2.away_team_id=:team_id)
    ORDER BY m2.day DESC
    LIMIT 5
  ) last_5_games ON m1.id = last_5_games.id
  LEFT JOIN player_match pm1 ON m1.id = pm1.match_id AND pm1.player_id=:player_id
SQL
);

        foreach ($this->doctrine->getRepository(Player::class)->findAll() as $player) {
            /**
             * @var $player Player
             */
            $stmt->execute(['team_id' => $player->team->id, 'player_id' => $player->id]);
            $results = $stmt->fetch();

            $player->last5MatchesAvgRating = $results['last_5_matches_avg_rating'];
            $player->last5MatchesRatings = array_map('intval', explode(',', $results['last_5_matches_ratings']));
        }

        $manager->flush();
    }
}

第一个查询的更新版本:

SELECT
  p.id AS p_id,
  p.lastname AS lastname,
  p.team_id as team_id,
  p.last_5_matches_avg_rating as last_5_matches_avg_rating,
  p.last_5_matches_ratings as last_5_matches_ratings,
  AVG(pm.rating) AS avg_rating,
  COUNT(pm.player_id) AS nb_matches,
  SUM(pm.substitute) AS nb_matches_substitute,
  SUM(pm.goals) AS goals,
  (SUM(pm.goals) / COUNT(pm.player_id)) AS goals_per_matches
FROM
  player p
  INNER JOIN player_match pm ON pm.player_id = p.id
  INNER JOIN `match` m ON pm.match_id = m.id AND (m.home_team_id = p.team_id OR m.away_team_id = p.team_id)
GROUP BY
  p.id,
  p.lastname,
  p.team_id,
  p.last_5_matches_avg_rating,
  p.last_5_matches_ratings
ORDER BY
  last_5_matches_avg_rating DESC, avg_rating DESC, lastname ASC
;

这并不是我最初想要的，但它确实起到了作用(这个数据库的更新次数很少)而且速度很快。

希望对大家有帮助。