alias, MySQL - rextester

Language: Layout:

DROP TABLE IF EXISTS player_b;
DROP TABLE IF EXISTS `match`;
DROP TABLE IF EXISTS player_match;

CREATE TABLE player_b (id int, team_id int, lastname varchar(50));
CREATE TABLE `match` ( id int, home_team_id int, away_team_id int, day int);
CREATE TABLE player_match (player_id int, match_id int, rating int, substitute int, goals int);

INSERT INTO player_b (id, team_id, lastname) VALUES (1, 1, 'Moubandje');
INSERT INTO player_b (id, team_id, lastname) VALUES (2, 2, 'Rüfli');
INSERT INTO player_b (id, team_id, lastname) VALUES (3, 3, 'Selnaes');
INSERT INTO player_b (id, team_id, lastname) VALUES (4, 1, 'Somália');
INSERT INTO player_b (id, team_id, lastname) VALUES (5, 4, 'Kerbra');

INSERT INTO `match` (id, home_team_id, away_team_id, day) VALUES (1, 1, 2, 1);
INSERT INTO `match` (id, home_team_id, away_team_id, day) VALUES (2, 2, 1, 2);
INSERT INTO `match` (id, home_team_id, away_team_id, day) VALUES (3, 2, 3, 3);
INSERT INTO `match` (id, home_team_id, away_team_id, day) VALUES (4, 3, 4, 4);
INSERT INTO `match` (id, home_team_id, away_team_id, day) VALUES (5, 3, 5, 5);

INSERT INTO player_match (player_id, match_id, rating, substitute, goals) VALUES (1, 1, 6, 0, 2);
INSERT INTO player_match (player_id, match_id, rating, substitute, goals) VALUES(2, 2, 5, 1, 0);
INSERT INTO player_match (player_id, match_id, rating, substitute, goals) VALUES (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_b 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
;

SELECT
  SUM(pm1.rating) / COUNT(m1.id) last_5_matches_rating,
  GROUP_CONCAT(CONCAT(m1.day, '=', COALESCE(pm1.rating, '~')) ORDER BY m1.day) AS str
FROM
  `match` m1
  INNER JOIN (
    SELECT m2.id, m2.home_team_id, m2.away_team_id
    FROM `match` m2
    WHERE (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 IN (pm1.match_id) AND pm1.player_id=1;


absolute service time: 0,42 sec