Skip to content

Résultats des championnats européens de football entre 2005 et 2021

Terrain fooball

Compétences mises en œuvre

  • Requêtes de base
  • Utilisation de Common Table Expressions (CTE)
  • Utilisation de Window Functions

Base de données

La base de données utilisée est en libre accès sur www.kaggle.fr. Il s'agit d'une base de données SQLite contenant les résultats des matchs de football des championnats européens entre les saisons 2005-2006 et 2020-2021. La base contient deux tables :

  • Table divisions :

    • division : code du championnat
    • name : nom du championnat
    • country : pays du championnat

  • Table matchs:

    • Div : code du championnat
    • Date : date du match
    • HomeTeam : équipe à domicile
    • AwayTeam : équipe visiteuse
    • FTHG : nombre de buts marqués par l'équipe à domicile
    • FTAG : nombre de buts marqués par l'équipe visiteuse
    • FTR : Résultat final (H=victoire de l'équipe à domicile, A=victoire de l'équipe visiteuse, D=match nul)
    • season : saison

Objectif

Il ne s'agit pas ici d'un projet. J'ai choisi d'écrire des questions et d'y répondre en effectuant des requêtes sur la base de données. L'ensemble des requêtes ont été réalisées sous le logiciel DBeaver.

Questions

1. Quelles équipes participaient au championnat de L1 en France lors de la saison 2020-2021 ?

SELECT GROUP_CONCAT(Equipe) AS Equipes
FROM(SELECT DISTINCT HomeTeam AS Equipe
     FROM matchs
     WHERE Div = 'F1' AND season = 2021
     ORDER BY HomeTeam ASC);
Equipes
Angers, Bordeaux, Brest, Dijon, Lens, Lille, Lorient, Lyon, Marseille, Metz, Monaco, Montpellier, Nantes, Nice, Nimes, Paris SG, Reims, Rennes, St Etienne, Strasbourg

2. Quel a été le nombre moyen de buts par match en L1 lors de la saison 2020-2021 ?

SELECT ROUND(AVG(FTHG + FTAG), 1) AS Nb_buts_moyens,
       ROUND(AVG(FTHG), 1) AS Nb_buts_domicile_moyen,
       ROUND(AVG(FTAG), 1) AS Nb_buts_exterieur_moyen
FROM matchs
WHERE Div = 'F1' AND season = 2021;
Nb_buts_moyens Nb_buts_domicile_moyen Nb_buts_exterieur_moyen
2,8 1,4 1,4

3. Quel a été le pourcentage de victoires à domicile, de victoires à l'extérieur et de matchs nuls en L1 lors de la saison 2020-2021 ?

/*Nombre total de matchs*/
WITH nb_matchs AS (
  SELECT COUNT(*) AS total
  FROM matchs m
  WHERE m.Div = 'F1' AND m.season = 2021
  ),

/*Nombre de victoires à domicile*/
nb_vict_dom AS (
  SELECT COUNT(*) AS total
  FROM matchs m
  WHERE m.Div = 'F1' AND m.season = 2021 AND m.FTR = 'H'
  ),

/*Nombre de victoires à l'extérieur*/
nb_vict_ext AS (
  SELECT COUNT(*) AS total
  FROM matchs m
  WHERE m.Div = 'F1' AND m.season = 2021 AND m.FTR = 'A'
  ),

/*Nombre de matchs nuls*/
nb_nuls AS (
  SELECT COUNT(*) AS total
  FROM matchs m
  WHERE m.Div = 'F1' AND m.season = 2021 AND m.FTR = 'D'
  )

/*Calcul des pourcentages*/
SELECT ROUND(d.total*100.0/m.total,1) AS Pourc_victoire_dom,
ROUND(e.total*100.0/m.total,1) AS Pourc_victoire_ext,
ROUND(n.total*100.0/m.total,1) AS Pourc_matchs_nuls
FROM nb_matchs AS m, nb_vict_dom AS d,
nb_vict_ext AS e,
nb_nuls AS n;
Pourc_victoire_dom Pourc_victoire_ext Pourc_matchs_nuls
37,4 37,6 25

4. Quel a été le nombre de buts marqués par équipe en L1 en 2020-2021 ?

/*Nombre de buts des équipes à domicile*/
WITH buts_pour_dom AS (
  SELECT m.HomeTeam as Equipe, SUM(m.FTHG) AS Nb_buts_dom
  FROM matchs m
  WHERE m.Div = 'F1' AND m.season = 2021
  GROUP BY m.HomeTeam
  ),

/*Nombre de buts des équipes à l'extérieur*/
buts_pour_ext AS (
  SELECT m.AwayTeam as Equipe, SUM(m.FTAG) AS Nb_buts_ext
  FROM matchs m
  WHERE m.Div = 'F1' AND m.season = 2021
  GROUP BY m.AwayTeam
  )

/*Nombre total de buts par équipe*/
SELECT Equipe, (d.Nb_buts_dom + e.Nb_buts_ext) AS Nb_buts_marqués
FROM buts_pour_dom d
INNER JOIN buts_pour_ext e
USING(Equipe)
ORDER BY Nb_buts_marqués DESC;
Equipe Nb_buts_marqués
Paris SG 86
Lyon 81
Monaco 76
Lille 64
Montpellier 60
Lens 55
Marseille 54
Rennes 52
Brest 50
Lorient 50
Nice 50
Strasbourg 49
Nantes 47
Metz 44
Bordeaux 42
Reims 42
St Etienne 42
Angers 40
Nimes 40
Dijon 25

5. Parmi les cinq grands championnats (Angleterre, Espagne, Allemagne, Italie, France), quelles sont les 5 équipes à avoir gagné le plus de matchs en 2020-2021 ?

/*Sélection des 5 championnats*/
WITH TopChamp AS (
  SELECT *
  FROM matchs m
  INNER JOIN divisions d
  ON m.Div = d.division
  WHERE season = 2021 AND d.division IN ('E0', 'SP1', 'D1', 'I1', 'F1')
  ),

/*Calcul du nombre de victoires à domicile par équipe*/
VictoiresDom AS (
  SELECT name AS Championnat, country AS Pays, HomeTeam AS Equipe, COUNT(*) AS Nb_Victoires
  FROM TopChamp
  WHERE FTHG>FTAG
  GROUP BY name, HomeTeam
  ),

/*Calcul du nombre de victoires à l'extérieur par équipe*/
VictoiresExt AS (
  SELECT name AS Championnat, country AS Pays, AwayTeam AS Equipe, COUNT(*) AS Nb_Victoires
  FROM TopChamp
  WHERE FTHG<FTAG
  GROUP BY name, AwayTeam
  )

/*Sélection des 5 équipes ayant le plus de victoires*/
SELECT d.Equipe, d.Championnat, d.Pays, (d.Nb_Victoires + e.Nb_Victoires) AS Nb_Victoires
FROM VictoiresDOM d
INNER JOIN VictoiresExt e
ON d.Championnat = e.Championnat AND d.Equipe = e.Equipe
ORDER BY Nb_Victoires DESC
LIMIT 5;
Equipe Championnat Pays Nb_Victoires
Inter Seria A Italy 28
Man City Premier League England 27
Ath Madrid LaLiga Spain 26
Paris SG Ligue 1 France 26
Real Madrid LaLiga Spain 25

6. Parmi les cinq grands championnats, quelles sont les 5 équipes à avoir marqué le plus de points en 2020-2021 ?

/*Sélection des 5 championnats*/
WITH TopChamp AS (
  SELECT *
  FROM matchs m
  INNER JOIN divisions d
  ON m.Div = d.division
  WHERE season = 2021 AND d.division IN ('E0', 'SP1', 'D1', 'I1', 'F1')
  ),

/*Calcul du nombre de points par équipe par match*/
AvecPoints AS (
  SELECT *,
  CASE
  WHEN FTHG>FTAG THEN 3
  WHEN FTHG<FTAG THEN 0
  ELSE 1
  END AS HomePoints,
  CASE
  WHEN FTHG>FTAG THEN 0
  WHEN FTHG<FTAG THEN 3
  ELSE 1
  END AS AwayPoints
  FROM TopChamp
  ),

/*Nombre total de points gagnés à domicile par équipe*/
PointsDom AS(
  SELECT HomeTeam AS Equipe, name AS Championnat, country AS Pays, SUM(HomePoints) AS Points
  FROM AvecPoints
  GROUP BY Championnat, Equipe
  ),

/*Nombre total de points gagnés à l'extérieur par équipe*/
PointsExt AS(
  SELECT AwayTeam AS Equipe, name AS Championnat, country AS Pays, SUM(AwayPoints) AS Points
  FROM AvecPoints
  GROUP BY Championnat, Equipe
  )

/*Podium par championnat*/
SELECT d.Equipe, d.Championnat, d.Pays, (d.Points+e.Points) AS Points
FROM PointsExt e
INNER JOIN PointsDom d
ON e.Championnat=d.Championnat AND e.Equipe=d.Equipe
ORDER BY Points DESC
LIMIT 5;
Equipe Championnat Pays Points
Inter Seria A Italy 91
Ath Madrid LaLiga Spain 86
Man City Premier League England 86
Real Madrid LaLiga Spain 84
Lille Ligue 1 France 83

7. Quel a été le podium des 5 grands championnats en 2020-2021 ?

/*Sélection des 5 championnats*/
WITH TopChamp AS (
  SELECT *
  FROM matchs m
  INNER JOIN divisions d
  ON m.Div = d.division
  WHERE season = 2021 AND d.division IN ('E0', 'SP1', 'D1', 'I1', 'F1')
  ),

/*Calcul du nombre de points par équipe par match*/
AvecPoints AS (
  SELECT *,
    CASE
      WHEN FTHG>FTAG THEN 3
      WHEN FTHG<FTAG THEN 0
      ELSE 1
    END AS HomePoints,
    CASE
      WHEN FTHG>FTAG THEN 0
      WHEN FTHG<FTAG THEN 3
      ELSE 1
    END AS AwayPoints
  FROM TopChamp
  ),

/*Nombre total de points gagnés à domicile par équipe*/
PointsDom AS(
  SELECT HomeTeam AS Equipe, name AS Championnat, country AS Pays, SUM(HomePoints) AS Points
  FROM AvecPoints
  GROUP BY Championnat, Equipe
  ),

/*Nombre total de points gagnés à l'extérieur par équipe*/
PointsExt AS(
  SELECT AwayTeam AS Equipe, name AS Championnat, country AS Pays, SUM(AwayPoints) AS Points
  FROM AvecPoints
  GROUP BY Championnat, Equipe
  ),

/*Classement par championnat*/
ClassementChamp AS (
  SELECT d.Equipe, d.Championnat, d.Pays, (d.Points+e.Points) AS Points,
    RANK() OVER(PARTITION BY d.Championnat
                ORDER BY d.Points+e.Points DESC
               ) AS Classement
  FROM PointsExt e
  INNER JOIN PointsDom d
  ON e.Championnat=d.Championnat AND e.Equipe=d.Equipe
)

/*Podium par championnat*/
SELECT Championnat, Pays, Classement, Equipe, Points
FROM ClassementChamp
WHERE Classement<4;
Championnat Pays Classement Equipe Points
Bundesliga Deutschland 1 Bayern Munich 78
Bundesliga Deutschland 2 RB Leipzig 65
Bundesliga Deutschland 3 Dortmund 64
LaLiga Spain 1 Ath Madrid 86
LaLiga Spain 2 Real Madrid 84
LaLiga Spain 3 Barcelona 79
Ligue 1 France 1 Lille 83
Ligue 1 France 2 Paris SG 82
Ligue 1 France 3 Monaco 78
Premier League England 1 Man City 86
Premier League England 2 Man United 74
Premier League England 3 Liverpool 69
Seria A Italy 1 Inter 91
Seria A Italy 2 Milan 79
Seria A Italy 3 Atalanta 78
Seria A Italy 3 Juventus 78

8. Quelles équipes ont été en L1 toutes les saisons de 2005 à 2021 ?

/*Sélection des équipes qui ont toujours été en Ligue 1 depuis la saison 2005-2006 */
SELECT GROUP_CONCAT(Equipe) AS Equipes
FROM(SELECT m.HomeTeam AS Equipe
     FROM matchs m
     WHERE m.Div = 'F1'
     GROUP BY m.HomeTeam
     HAVING COUNT(DISTINCT(season)) = (SELECT COUNT(DISTINCT season)
                                       FROM matchs)
     );
Equipes
Bordeaux, Lille, Lyon, Marseille, Nice, Paris SG, Rennes, St Etienne

9. Lors de quel(s) match(s) y-a-t-il eu le plus de buts ?

/*Calcul du nombre de buts par match*/
WITH Buts_par_match AS (
  SELECT m.date AS Date, m.HomeTeam AS Equipe_domicile ,m.AwayTeam AS Equipe_extérieur,
         m.FTHG AS Buts_équipe_dom, m.FTAG AS Buts_équipe_ext, m.FTHG+m.FTAG AS Nb_total_buts
  FROM matchs m
  )

/*Sélection du(des) match(s) avec le plus de buts*/
SELECT Date, Equipe_domicile, Equipe_extérieur, Buts_équipe_dom, Buts_équipe_ext, Nb_total_buts
FROM Buts_par_match
WHERE Nb_total_buts = (SELECT MAX(Nb_total_buts)
FROM Buts_par_match);
Date Equipe_domicile Equipe_extérieur Buts_équipe_dom Buts_équipe_ext Nb_total_buts
2020-10-24 VVV Venlo Ajax 0 13 13

10. Dans les 5 grand championnats, lors de quels matchs y-a-t-il eu le plus de buts lors de chaque saison ?

/*Calcul du nombre de buts par match dans les 5 grands championnats*/
WITH Buts_par_match AS (
  SELECT m.date AS Date, m.season, d.name AS Championnat, 
         m.HomeTeam AS Equipe_domicile ,m.AwayTeam AS Equipe_extérieur,
         m.FTHG AS Buts_équipe_dom, m.FTAG AS Buts_équipe_ext, m.FTHG+m.FTAG AS Nb_total_buts
  FROM matchs m
  INNER JOIN divisions d
  ON m.Div = d.division
  WHERE d.division IN ('E0', 'D1', 'I1', 'F1', 'SP1')
  ),

/*Calcul du nombre max de buts par saison*/
Max_buts AS (
  SELECT season, MAX(Nb_total_buts) AS maxi
  FROM Buts_par_match
  GROUP BY season
  )

/*Matchs avec le plus de buts pour chaque saison*/
SELECT bpm.season, bpm.Date, bpm.Championnat, bpm.Equipe_domicile, bpm.Equipe_extérieur, bpm.Buts_équipe_dom,
bpm.Buts_équipe_ext, bpm.Nb_total_buts
FROM Buts_par_match bpm
INNER JOIN Max_buts mb
ON bpm.season = mb.season AND bpm.Nb_total_buts = mb.maxi
ORDER BY bpm.season DESC;
Season Date Championnat Equipe_domicile Equipe_extérieur Buts_équipe_dom Buts_équipe_ext Nb_total_buts
2021 2020-10-04 Premier League Aston Villa Liverpool 7 2 9
2021 2021-02-02 Premier League Man United Southampton 9 0 9
2020 2019-10-25 Premier League Southampton Leicester 0 9 9
2020 2020-03-01 Seria A Lecce Atalanta 2 7 9
2019 2018-09-02 LaLiga Barcelona Huesca 8 2 10
2018 2017-10-14 Premier League Man City Stoke 7 2 9
2018 2018-05-13 Premier League Tottenham Leicester 5 4 9
2018 2018-02-02 Ligue 1 Marseille Metz 6 3 9
2018 2017-10-15 LaLiga Betis Valencia 3 6 9
2018 2018-03-18 LaLiga Real Madrid Girona 6 3 9
2018 2018-05-13 LaLiga Levante Barcelona 5 4 9
2017 2017-05-07 Seria A Lazio Sampdoria 7 3 10
2017 2016-08-20 LaLiga Sevilla Espanol 6 4 10
2016 2015-12-20 LaLiga Real Madrid Vallecano 10 2 12
2015 2014-09-20 LaLiga La Coruna Real Madrid 2 8 10
2015 2015-04-05 LaLiga Real Madrid Granada 9 1 10
2015 2015-05-23 LaLiga Real Madrid Getafe 7 3 10
2014 2013-10-30 LaLiga Real Madrid Sevilla 7 3 10
2013 2013-03-30 Bundesliga Bayern Munich Hamburg 9 2 11
2012 2011-08-28 Premier League Man United Arsenal 8 2 10
2011 2010-08-29 Bundesliga Leverkusen M'gladbach 3 6 9
2011 2011-05-07 Bundesliga St Pauli Bayern Munich 1 8 9
2011 2010-12-04 Ligue 1 Lille Lorient 6 3 9
2011 2011-04-23 LaLiga Valencia Real Madrid 3 6 9
2011 2011-05-21 LaLiga Real Madrid Almeria 8 1 9
2010 2009-11-22 Premier League Tottenham Wigan 9 1 10
2010 2009-11-08 Ligue 1 Lyon Marseille 5 5 10
2009 2008-09-27 Bundesliga Werder Bremen Hoffenheim 5 4 9
2008 2007-09-29 Premier League Portsmouth Reading 7 4 11
2007 2007-04-01 LaLiga Santander Ath Bilbao 5 4 9
2006 2006-02-11 Bundesliga Schalke 04 Leverkusen 7 4 11