SQL adapté au classement sportifs (comprenez Foot)

Présentation

Pas la peine de crier, pensez ce que vous voulez du foot, ça m'amuse de compiler des données là-dessus et d'établir la meilleure structure de données possible afin d'obtenir non seulement des résultats le plus simplement du monde (comprenez une seule requête SQL) et d'extraire des informations d'un amas plutôt brut de résultats de matchs (ex : Juninho à 75% de chance de marquer un but s'il rentre avant la 23ème minute d'un match. Cherchez pas je viens de l'inventer mais on peut calculer ça !).

L'objectif : le classement du championnat de France

Chaque journée (groupe de N matchs entre les 2N équipes de ligue 1 ou 2 du championnat) permet, avec les journées précédentes, d'établir un classement de ces 2N équipes en fonction d'un barème simple et précis : une victoire = 3 points, un match nul = 1 point et une défaite = aucun point. On passera pour l'instant sur les tris utilisés quand ces scores sont égaux et qui permettent de départager les ex-aequo.

Les données

On va créer une table extrèmement simpl(ist)e pour stocker les matches :

CREATE TABLE `matches` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `home` varchar(100) collate utf8_unicode_ci NOT NULL,
  `visitor` varchar(100) collate utf8_unicode_ci NOT NULL,
  `nbGoalsHome` int(10) default NULL,
  `nbGoalsVisitor` int(10) default NULL,
  `dayIndex` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Matches de foot';

La plupart des champs sont évident, à l'exception de dayIndex qui est l'indice de la "journée" à laquelle appartient le match. Ceci posé on va la remplir avec les résultats des 8 premières journées du championnat dont je dispose à cette date, ce qui nous fait 80 matches car il y a 20 équipes en lice.

Premier essai : mix-up de PHP et SQL

La version du code qui vient immédiatement consiste à procéder à des requêtes simples et à traiter les résultats de ces requêtes en programmation classique et enfin a afficher le résultat. D'où :

//On détermine les noms des différentes équipes évoluant dans le championnat
    $Result = mysql_query("SELECT home as name from matches group by home;");
    while ($Item = mysql_fetch_array($Result))      $Buckets[$Item["name"]] = 0;

    //Pour chaque équipe on calcule les points issus des ses rencontres jouées à domicile
    $Result = mysql_query("SELECT
        home, nbGoalsHome, sum(if (nbGoalsHome>nbGoalsVisitor,3,if(nbGoalsHome=nbGoalsVisitor,1,0))) as homeScore,
        visitor, nbGoalsVisitor, sum(if (nbGoalsHome<nbGoalsVisitor,3,if(nbGoalsHome=nbGoalsVisitor,1,0))) as visitorScore
        FROM `matches` group by visitor;");
    while ($Item = mysql_fetch_array($Result))      $Buckets[$Item["visitor"]] += $Item["visitorScore"];

    //Comme précédemment mais pour les rencontres jouées à l'extérieur
    $Result = mysql_query("SELECT
        home, nbGoalsHome, sum(if (nbGoalsHome>nbGoalsVisitor,3,if(nbGoalsHome=nbGoalsVisitor,1,0))) as homeScore,
        visitor, nbGoalsVisitor, sum(if (nbGoalsHome<nbGoalsVisitor,3,if(nbGoalsHome=nbGoalsVisitor,1,0))) as visitorScore
        FROM `matches` group by home;");
    while ($Item = mysql_fetch_array($Result))      $Buckets[$Item["home"]] += $Item["homeScore"];

    //Le tri final et l'affichage
    arsort($Buckets);
    foreach($Buckets as $TeamName=>$TeamScore)      echo "$TeamName = $TeamScore<br/>";

Ce qui renvoie :

Nancy = 16
Rennes = 15
Lyon = 15
Bordeaux = 15
Valenciennes = 14
Monaco = 13
Le mans = 13
Lorient = 12
Strasbourg = 12
Paris-SG = 11
Saint-Etienne = 11
Nice = 11
Toulouse = 10
Lille = 9
Marseille = 7
Auxerre = 6
Lens = 5
Caen = 4
Sochaux = 4
Metz = 2

On a ici une première version du classement attendu mais avec quelques petites différences dûes aux ex-aequo qui ne sont pas bien placés du fait de la présence d'un seul tri (sur le score global) qui ne suffit pas.

Deuxième essai : une seule requête SQL

Le chemin vers une requête SQL unique plutôt qu'un mélange de deux langages est assez direct :

• on doit cumuler les points de chaque équipe qu'elle joue à domicile ou à l'extérieur
• ayant cumulé les points engrangés à chaque match avec une requête on les cumule avec une deuxième qui va l'encapsuler et procéder à des additions (grossièrement)
• on va trier ces cumuls pour refléter le classement correct au sens de la LFP

Calcul des points par match

Le premier point nous oblige à doubler les matchs contenus dans notre table, car sinon les cumuls vont être impossibles à réaliser en une seule requête. Je m'explique : les points d'une équipe à domicile ne pourront être cumulés à ceux qu'elle a rapportés de ses voyage à l'extérieur, les champs (home et visitor) sont tout simplement différents ! On va donc leurrer le code qui réalise les cumuls en ajoutant pour chaque match son match miroir, ainsi Toulouse-Marseille est l'équivalent de Marseille-Toulouse et les cumuls réalisés sur chaque équipe sont valides. On va faire tout ceci en utilisant un UNION :

select
        t1.id, t1.home, t1.nbGoalsHome, t1.visitor, t1.nbGoalsVisitor
    from matches as t1
UNION ALL
    select
        t2.id, t2.visitor, t2.nbGoalsVisitor, t2.home, t2.nbGoalsHome
    from
        matches as t2
order by id

Cette requête renvoie 180 lignes, deux fois plus de lignes que de matchs, comme suit, du moins pour les 10 premières lignes :

On voit clairement les lignes doublées, elles ont la même id mais avec les équipes inversées.

On peut lire ce résultat comme : "je vais traiter les résultats de chaque équipe de chaque match peu importe qu'elle joue à l'extérieur ou à domicile".

Pour calculer le nombre de points engrangés par chaque équipe on doit utiliser une clause IF pour chaque SELECT de l'opération UNION, placé juste après le nom de l'équipe qui apparaît en premier sur chaque ligne :

select
    t1.id, t1.home,
    IF(t1.nbGoalsHome>t1.nbGoalsVisitor, 3, IF(t1.nbGoalsHome=t1.nbGoalsVisitor, 1, 0)) as score,
    t1.nbGoalsHome, t1.visitor, t1.nbGoalsVisitor
from matches as t1

UNION ALL

select
    t2.id, t2.visitor,
    IF(t2.nbGoalsHome<t2.nbGoalsVisitor, 3, IF(t2.nbGoalsHome=t2.nbGoalsVisitor, 1, 0)),
    t2.nbGoalsVisitor, t2.home, t2.nbGoalsHome
from matches as t2

order by id

Ce qui renvoie :

Ce qui se lit en français "chaque équipe remporte 3, 1 ou zéro points suivant le résultat du match qu'elle a joué à domicile ou à l'extérieur".

Les cumuls des points

Tout ceci est bien beau mais il faut accumuler les points calculés précédemment pour obtenir des totaux nous permettant d'ordonner les équipes entre elles, ce qui est le but initial... Pour ça, on peut transformer la requête précédente en sous-requête d'une requête principale de cumuls :

select nomEquipe, sum(score) as totalScore
    from (sous-requête) as liste
group by nomEquipe
order by totalScore desc

si on remplace la mention sous-requête par celle qu'on a écrit précédemment. Ce qui renvoie :

et qui correspond presque au classement officiel de cette 8ème journée, si ce n'est que quelques équipes à égalité de points sont mal placées et qui nous fait arriver au dernier point.

Les tris officiels

On peut consulter les modes de calculs et de tri des résultats dans les règles du championnat, article 307 éditées par la Ligue de Football Professionnel. Je cite :

En cas dâ??égalité de points, le classement des clubs ex-aequo est déter- miné par la différence entre les buts marqués et les buts concédés par chacun dâ??eux au cours des matches joués pour lâ??ensemble de la divi- sion. En cas de nouvelle égalité, avantage sera donné au club ayant marqué le plus grand nombre de buts.

On va donc transposer ça en mettant bout à bout des tris au sein d'une clause ORDER BY, en considérant que totalScore est le nombre de points d'une équipe, Diff est la différence de buts cumulée et BP est le nombre de buts marqués cumulé :

order by totalScore desc, Diff desc, BP desc

Le résultat final

Voici donc, en utilisant tout ce qui précède, la requête finale à exécuter pour obtenir en quelques millièmes de secondes le classement des équipes jouant le championnat de France :

select
    nomEquipe, sum(score) as totalScore, sum(J) as J, sum(G) as G, sum(N) as N, sum(P) as P, sum(bp) as BP, sum(bc) as BC, sum(diff) as Diff
    from (
        SELECT
            t1.id, t1.home as nomEquipe,
            IF (t1.nbGoalsHome > t1.nbGoalsVisitor, 3, IF (t1.nbGoalsHome = t1.nbGoalsVisitor, 1, 0)) as score,
            if(t1.nbGoalsHome is null, 0, 1) as J,
            if(t1.nbGoalsHome>t1.nbGoalsVisitor, 1, 0) as G, if(t1.nbGoalsHome=t1.nbGoalsVisitor, 1, 0) as N,
            if(t1.nbGoalsHome<t1.nbGoalsVisitor, 1, 0) as P,
            t1.nbGoalsHome as bp, t1.nbGoalsVisitor as bc,
            t1.nbGoalsHome-t1.nbGoalsVisitor as diff
        FROM matches AS t1
        UNION ALL
        SELECT
            t2.id, t2.visitor as nomEquipe,
            IF (t2.nbGoalsVisitor > t2.nbGoalsHome, 3, IF (t2.nbGoalsVisitor = t2.nbGoalsHome, 1, 0)),
            if(t2.nbGoalsVisitor is null, 0, 1),
            if(t2.nbGoalsVisitor>t2.nbGoalsHome, 1, 0),
            if(t2.nbGoalsHome=t2.nbGoalsVisitor, 1, 0),
            if(t2.nbGoalsVisitor<t2.nbGoalsHome, 1, 0),
            t2.nbGoalsVisitor, t2.nbGoalsHome,
            t2.nbGoalsVisitor-t2.nbGoalsHome as diff
        FROM matches AS t2
    ) as resultat
    group by nomEquipe
    order by totalScore desc, Diff desc, BP desc

Cette requête renvoie ce qu'on attend d'elle, à savoir :

Vous pouvez vérifier que ce classement est correct en vous rendant sur la page officielle de cette 8ème journée 2007/2008 de la LFP.

Dernier arrangement

On voit tout de suite que les deux SELECT que l'on mélange avec le UNION de la requête précédente sont optimisables : il suffit de déplacer les deux listes de champs et de les mélanger avec les sommes qu'exécutent la requête supérieure. �a marche très bien, la requête est plus courte, plus claire et elle s'exécute 20% plus rapidement sur plusieurs installations de MySQL :

ELECT
        nomEquipe,
        sum(IF(nbGoals1 > nbGoals2, 3, IF (nbGoals1 = nbGoals2, 1, 0))) as totalScore,
        sum(if(nbGoals1 is null, 0, 1)) as J,
        sum(if(nbGoals1>nbGoals2, 1, 0)) as G,
        sum(if(nbGoals1=nbGoals2, 1, 0)) as N,
        sum(if(nbGoals1<nbGoals1, 1, 0)) as P,
        sum(nbGoals1) as BP,
        sum(nbGoals2) as BC,
        sum(nbGoals1-nbGoals2) as Diff
    FROM (
            SELECT
                t1.home as nomEquipe, t1.nbGoalsHome as nbGoals1, t1.nbGoalsVisitor as nbGoals2
            FROM matches as t1
        UNION ALL
            SELECT
                t2.visitor, t2.nbGoalsVisitor, t2.nbGoalsHome
            FROM matches as t2
    ) as mList
    GROUP BY nomEquipe
    ORDER BY totalScore desc, Diff desc, BP desc

Fonctionnalités supplémentaires

En ajoutant une clause WHERE très simple, WHERE dayIndex<=X, dans la grosse requête précédente, on obtient le classement après une journée quelconque, X, et plus sur l'ensemble des journées écoulées. En répètant cette requête pour toutes les valeurs de X partant de 1 jusqu'à la dernière journée jouée, on a les classements successifs journées après journées et on peut établir, par exemple, la progression d'une équipe dans ce classement.

On pourra ajouter beaucoup de clauses WHERE dans cette requête histoire de gérer un design plus poussé mais ceci est l'objet d'un autre article plus complet. Je voulais ici me concentrer sur le point précis de l'obtention de ce classement et ce qu'il supposait du point de vue SQL.

Dernier point

Il y a une chose étrange : après avoir ajouté cet indice de la journée de championnat qui correspond au match j'ai cru constater un ralentissement de l'exécution de la requête quand ce nombre était déclaré comme INDEX alors que je croyais que cette déclaration allait optimiser mon WHERE... A suivre donc.