Sommes de nombre


1 décembre 2007

Le problème

Le but de cette page est d'arriver à exprimer par une formule simple, i.e. sans sommation, l'expression suivante pour k et n donnés :

On connait tous la réponse pour les cas simples k=1 et k=2 :


On peut se demander "Pourquoi ça t'intéresse ?". Ma réponse est très simple : "parce que". C'est quelque chose qui m'attire personnellement et que je n'explique pas. J'ai appris, bien après avoir trouvé comment obtenir la formule pour n'importe quel k, que le travail avait déjà été fait de la même façon par A.W.F. Edwards au vingtième siècle et d'une autre complètement différente par Jacobi en 1631.

�a peut sembler très naïf mais je ne savais pas que cette formule existait depuis un bon moment et s'appelait la formule de Faulhaber, donc il faut considérer le texte ci-dessous comme un voyage d'un aveugle dans un labyrinthe imaginaire...

Pour citer mes sources, c'est Knuth qui m'a appris l'existence de Faulhaber.

La méthode

On veut donc calculer la somme S des N premiers entiers chacun élevé à la puissance k mais ça n'est pas évident à priori. L'idée est de passer par une intégrale, c'est toujours plus simple à calculer qu'une somme discrète, mais ça nous oblige à trouver une mystérieuse fonction f telle que :

Pour trouver f(x) et pour expliquer pourquoi les bornes de l'intégration ne sont pas celles de la somme, on établit l'équation suivante :

On aboutit, en posant , un polynôme en x de degré k, à une résolution qui fait intervenir le développement du binôme, par celui de , et des inversions de matrices triangulaires LU.

Résolution

L'équation {2} fait intervenir les matrices naturellement : quand on écrit f(x) sous la forme d'un polynôme, l'intégration, longue pour des valeurs de k élevée, mais très facile, donne Q(i+1)-Q(i), où Q(x) est la primitive de f(x), et cette différence contient des termes en (i+1)^j pour j <= k+1.

Cette différence, et donc l'intégrale, est une expression contenant les coefficients du polynôme f recherché. Cette expression devant être égale à , on sait tout de suite que le terme de plus haut degré de f est égal à 1. On a donc :

avec w un polynôme de degré k-1

Pour poursuivre la résolution on peut écrire l'équation {2} sous forme matricielle : A c = (1 0 0 ... 0), avec A la matrice contenant les coefficients du triangle de Pascal issus des développements binomiaux précédent, a le vecteur contenant les coefficients du polynôme f recherché et le membre de droite figurant l'égalité à .

Pour résoudre cette équation on va écrire la matrice A sous la forme A = LU, de manière à pouvoir obtenir :

où U et L sont aisément inversables puisques triangulaires. Je ne m'étends d'ailleurs pas ici sur cette inversion dont on peut trouver la description facilement.

Implémentation

On veut donc convertir la méthode ci-dessus en C++ mais il faut bien faire attention aux problèmes de précisions sur les nombres flottants qui peuvent surgir quand on fait des inversions de matrices ou des multiplications de polynômes successives. Ma méthode pour éviter ça ? Utiliser une classe représentant des fractions et manipuler des entiers ! Les calculs deviennent exacts (ce qui est le but) mais effectivement un peu plus lent (ce qui pour une fois ne m'embète pas du tout !).

Pour le code ci-dessous, les classes utilisées (fraction, polynôme, matrice et vecteur) appartiennent à ma librairie mathématiques en cours de construction.

void    Faulhaber(int k)
{
    //On remplit une matrice avec les coefficients du triangle de pascal
    QiMatrix<QiFraction<long long int>> Pascal(k+1);
    Pascal.null();
    QiPolynomial< QiFraction<long long int> >   Basic(k+1);
    Basic.setRaw(1, 1.0f, 1.0f);    //x+1
    QiPolynomial< QiFraction<long long int> >   Current(Basic);
    int i, j;
    for(i=0; i<=k; i++)     {
        for(j=0; j<=i; j++)     Pascal.set(k-j, k-i, Current.get(j));
        Current *= Basic;
    }
    Pascal.inverse();
    //On construit la matrice diagonale T avec k, k-1, ..., 2, 1 (qui est l'inverse de la matrice 1/k, 1/(k-1), ..., 1)
    QiMatrix< QiFraction<long long int> >   Diag(k+1);
    Diag.null();
    for(i=0; i<=k; i++)     Diag.set(i, i, (long long int)k+1-i);
    Diag *= Pascal;         //On fait L^-1 * T^-1
    //On multiplie par (1, 0, ..., 0) pour récupérer la première colonne
    QiVector< QiFraction<long long int> >   BaseVector(k+1), Result(k+1);
    BaseVector.base(0);
    Result = Diag * BaseVector;
    //On transforme ce vecteur en polynôme
    QiPolynomial< QiFraction<long long int> >   Formula(k+1);
    Formula.null();
    for(i=0; i<=k; i++)     Formula.set(k-i, Result.get(i));
    //On intègre ce polynôme entre 0 et N+1 en calculant sa primitive et en générant un deuxième polynôme dans lequel on remplace x par n+1
    Formula.primitive();
    QiPolynomial< QiFraction<long long int> >   NPlus1(1);
    NPlus1.setRaw(1, 1.0f, 1.0f);
    Formula.substitute(NPlus1);
    //Présentation des résultats
    printf("Formule finale :\n");   Formula.dumpObjects();
    printf("On divise par 1/%d :\n", k+1);
    Formula /= QiFraction<long long int>(1,k+1);
    Formula.dumpObjects();
}

La sortie de ce programme pour quelques valeurs de k ressemble à ce qui suit :

Formule finale : 1X+1
On divise par 1/1 : 1X+1
Résultats pour k=1 :
Formule finale : 1/2X^2+1/2X
On divise par 1/2 : 1X^2+1X
Résultats pour k=2 :
Formule finale : 1/3X^3+1/2X^2+1/6X
On divise par 1/3 : 1X^3+3/2X^2+1/2X
Résultats pour k=3 :
Formule finale : 1/4X^4+1/2X^3+1/4X^2
On divise par 1/4 : 1X^4+2X^3+1X^2
Résultats pour k=4 :
Formule finale : 1/5X^5+1/2X^4+1/3X^3-1/30X
On divise par 1/5 : 1X^5+5/2X^4+5/3X^3-1/6X
Résultats pour k=5 :
Formule finale : 1/6X^6+1/2X^5+5/12X^4-1/12X^2
On divise par 1/6 : 1X^6+3X^5+5/2X^4-1/2X^2
Résultats pour k=6 :
Formule finale : 1/7X^7+1/2X^6+1/2X^5-1/6X^3+1/42X
On divise par 1/7 : 1X^7+7/2X^6+7/2X^5-7/6X^3+1/6X
Résultats pour k=7 :
Formule finale : 1/8X^8+1/2X^7+7/12X^6-7/24X^4+1/12X^2
On divise par 1/8 : 1X^8+4X^7+14/3X^6-7/3X^4+2/3X^2
Résultats pour k=8 :
Formule finale : 1/9X^9+1/2X^8+2/3X^7-7/15X^5+2/9X^3-1/30X
On divise par 1/9 : 1X^9+9/2X^8+6X^7-21/5X^5+2X^3-3/10X

Résultats

Voici les formules des sommes calculées et factorisées à la fois à la main et par ordinateur pour les 8 premières valeurs de k :













On voit des relations entre toutes ces formules à deux indices d'écart (P2 est contenue dans P4, P6 et P8, P3 est contenue dans P5 et P7) mais je ne sais rien en faire pour l'instant. A poursuivre donc. on peut disposer les coefficients des polynômes successifs dans un tableau pour y voir plus clair, en y ajoutant quelques lignes supplémentaires pour donner plus de poids à nos conjectures :

kCoeff.012345678910111213
Les lignes 9 à 12 contiennent des coefficients calculés par le programme de cette page et n'ont pas été obtenus à la main...

On peut faire les constats suivants :

  • le coefficient de degré le plus élevé est toujours égal à 1 :
  • celui de plus bas degré est toujours nul, i.e. pas de terme constant ce qui est assez logique car :
  • la diagonale reliant les coefficients de degré k est une série arithmétique de raison 1/2 :
  • la diagonale située en-dessous est également une série avec pour formule :
  • quand la colonne 1 contient 0 (ce qui semble être le cas une fois sur deux) la diagonale issue de ce 0 ne contient que des 0 :
  • les autres diagonales sont également des séries avec pour formule :
  • La somme des coefficients de la ligne k est égale à k+1, ce qui permet de trouver les par simple soustraction.
Tout ceci semble indiquer qu'il y a une sorte de structure d'une ligne à l'autre. Je dis bien semble car je peux montrer comment calculer une ligne à partir de celle qui la précède mais je ne l'ai pas encore démontré rigoureusement, seulement vérifié jusqu'à un certain indice, c'est loin d'être la même chose.demonstration needed

� propos de la dernière remarque concernant les coefficients de la première colonne non-nulle je peux effectivement me débrouiller pour les calculer car la somme des autres coefficients est égale à k+1, mais il est important de remarquer que multipliés par le coefficient 1/k+1 ils sont égaux aux nombres de Bernouilli ! On vient donc de retrouver une méthode pour calculer ces nombres analogue à celle du triangle de Pascal pour les coefficients du binôme !formula needed.

Ce qui me fait croire qu'une partie de la démonstration des formules précédentes et de la procédure utilisée pour les obtenir pourrait résider dans ces fameux nombres de Bernouilli mais ça reste un terrain à investiguer.

Usages

Ces formules sont des ingrédients que l'on peut utiliser dans une décomposition polynomiale des termes d'une série (citation neeeded :)).

Questions ouvertes

Qu'en est-il des cas avec k non entier ? A faire ! �a peut sembler intéressant de formuler la sommes des racines carrées des N premiers entiers non ?

Références

Un article complet sur les sommes
La fameuse formule de Faulhaber
L'article de Knuth sur Faulhaber
Un mathématicien/logisticien/généticien qui s'est intéressé à la formule de Faulhaber
Les nombres de Bernouilli

Rendu web de formules mathématiques


1 décembre 2007
Cette page date un peu et le paquet texgd n'étant pas utilisable sur mon serveur actuel, j'ai modifié le processus de transformation de formules LaTeX en PNG que j'ai documenté sur une nouvelle page.

Introduction

J'utilise le langage TeX pour afficher toutes les formules mathématiques sur ce site et cette page explique la mise en oeuvre de cette fonctionnalité.

Tex2Png : texgd + PHP

Le coeur de la solution c'est l'exécutable texgd, une application qui s'installe sur le serveur envisagé. Utilisant Ubuntu je n'ai eu besoin que d'exécuter la commande suivante pour disposer de cette application sur ma machine :

apt-get install texgd

La doc ("man texgd") est courte mais suffit pour utiliser cette application, plutôt spartiate dans son genre. En gros : on exporte des variables d'environnement Bash pour passer des paramètres à l'application, que l'on lance directement, sans arguments. Elle génère une image selon les variables d'environnement exportées qui sont la formule, le chemin du fichier image généré etc.

La passerelle web

Il ne reste maintenant qu'à décrire la partie PHP : c'est un script qui gère un dossier de cache d'images pour appeler tex2gd uniquement quand il y en a besoin et économiser beaucoup de temps machine qu'un serveur web doit économiser par-dessus tout :

<?
    //Ce script nécessite d'installer le package texgd et la présence de plusieurs répertoires ayant les privilèges d'écriture règlés pour le serveur web (l'utilisateur sous lequel tourne ce script).
    $Formula = str_replace("\\\\", "\\", rawurldecode($_SERVER["QUERY_STRING"]));
    $PicName = md5($Formula).".png";
    $CacheDir = "mathcache/";
    $PicFilePath = "http://www.flubb.net/"."$CacheDir$PicName";
    $TmpPath = $CacheDir."tmp/";
    //On ne crée l'image que si elle n'existe pas déjà dans le cache
    if (!file_exists($PicFilePath))     {
        $TexGDCommand = "
            export texgd_src='$Formula'
            export texgd_tmpdir=$TmpPath
            export texgd_fontdir=".$CacheDir."mathfonts
            export texgd_outfile=$CacheDir$PicName
            export texgd_texheader=".$CacheDir."header.tex
            export texgd_style='\$\$'
            export texgd_density=10
            export texgd_compressratio='3'
            texgd";
        $Ret = exec($TexGDCommand, $Output);
    }
    //On envoie un tag HTML contenant la référence de l'image générée.
    echo "<img style='vertical-align:middle;' src='$PicFilePath' border='0' title='$Formula'/>";
?>

Ce script est une page à part entière. L'intérêt ? Et bien si on écrit l'URL suivante dans la barre d'adresse de son navigateur :

http://www.flubb.net/tex2png.php?I_2=\frac{x-a}{2}f^{\prime\prime}(a)+\int_{a}^x\frac{x-t}{2}f^{(3)}(t)dt

et bien vous verrez l'image suivante apparaître dans votre navigateur :

Exemples

En cours...

Liens externes

J'aurais pu utiliser d'autres applications/scripts pour faire des conversions de formules TeX en PNG, comme ceux-ci : Charles' Latex Page : une page intéressante qui propose des infos sur TeX mais également des schémas de conversion. TeX2png.php : un code qui fait la conversion TeX en PNG mais en partant de bien plus bas, et plus intéressant, que texgd. Là, on appelle TeX directement. Note : c'est issu du projet PHPWiki. Des vidéos de Knuth : ça n'a presque rien à voir avec ce qui précède, si ce n'est qu'il cause de TeX :)

Variantes d'écriture d'une requête SQL simple


28 octobre 2007

Présentation

Pendant la lecture du chapitre 6, pages 126 et 127, du livre de Joe Celko "Sql Programming Style", Elsevier, 2005, j'ai eu envie de vérifier un schéma et une requête qu'il exécute sur un certain type de données.

Les données sont des prêts pour lesquels on stocke des paiements successifs qui peuvent avoir chacun un status (S, U ou F) qui détermine, si on veut, un paiement a échéance, en retard ou envoyé (la signification de ce status n'a pas d'importance). Il cherche, étant donné ce schéma, la liste des prêts dont TOUS les paiements ont le status 'F'.

Simple ? Oui, mais on peut diverger quand à la construction des requêtes possibles pour trouver ce résultat.

Le schéma et la solution de Celko

Son schéma, que je reprend, est le suivant :

CREATE TABLE loans (
        loanId int not null,
        paymentId int not null,
        paymentStatus = ENUM('F', 'U', 'S'),
        primary key (loanId, paymentId)
    )

Sa solution est élégante et rapide (cf ci-dessous) et je voulais savoir si on pouvais faire mieux. Tout d'abord présentons sa solution :

select loanId, count(*) as nbPayments
        FROM loans
        GROUP BY loanId
        HAVING MAX(paymentStatus)='F' and MIN(paymentStatus)='F';

On parcourt donc la totalité de la table et on extrait bien les prêts dont les paiements n'ont pas d'autres statuts que 'F'.

Les alternatives

J'ai deux solutions alternatives. La première utilise deux sous-requêtes, une qui donne pour chaque prêts le nombre de paiements ayant le statuts 'F' et une autre qui donne le nombre de paiements total. Une clause WHERE est présente pour ne garder que les prêts pour lesquels ces décomptes sont égaux.

select GlobalStats.loanId, GlobalStats.nbPayments
        from
        (select loanId, count(*) as nbPayments from loans where paymentStatus='F' group by loanId) as FStats,
        (select loanId, count(*) as nbPayments from loans group by loanId) as GlobalStats
        where FStats.loanId=GlobalStats.loanId and FStats.nbPayments=GlobalStats.nbPayments;

La deuxième solution calcule directement pour chaque prêt le nombre de paiements et le nombre de paiements ayant le statut 'F', en utilisant la somme d'une valeur issue d'un IF.

select loanId, nbPayments
        from (
            select loanId, count(loanId) as nbPayments, sum(if(paymentStatus='F', 1, 0)) as nbFPayments from loans group by loanId
        ) as inter
        where inter.nbPayments=inter.nbFPayments;

Conclusion

Le résultat ? Déprimant ! Mes deux solutions peuvent être plus simples à comprendre au premier coup d'oeil mais sont jusqu'à 30% plus lente à l'exécution, alors que la solution de Celko qui peut surprendre au premier regard, est en fait très simple à comprendre, plus rapide et plus constante dans son temps d'exécution.

Pour tester ça j'ai utilisé un petit script PHP qui initialise la table, la remplit avec des milliers de données, règle certains prêts de manière à ce qu'ils apparaissent dans le résultat des requêtes de cette page et compare les temps d'exécution des trois variantes.

C'est moi ou Joe Celko ressemble drôlement à Anton Lavey ?

SQL adapté au classement sportifs (comprenez Foot)


28 octobre 2007

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.

Note : j'ai fait un tri simple (la ligne du arsort) parce que trier au sens de la LFP comme évoqué plus loin m'ennuie profondément en PHP.

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 :

idhomenbGoalsHomevisitornbGoalsVisitor
1Toulouse2Marseille1
1Marseille1Toulouse2
2Lens1Nancy0
2Nancy0Lens1
3Valenciennes0Le mans2
3Le mans2Valenciennes0
4Bordeaux1Lille1
4Lille1Bordeaux1
5Lyon5Metz1
5Metz1Lyon5

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 :

idhomescorenbGoalsHomevisitornbGoalsVisitor
1Marseille01Toulouse2
1Toulouse32Marseille1
2Lens31Nancy0
2Nancy00Lens1
3Le mans32Valenciennes0
3Valenciennes00Le mans2
4Lille11Bordeaux1
4Bordeaux11Lille1
5Metz01Lyon5
5Lyon35Metz1

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 :

nomEquipetotalScore
Nancy16
Rennes15
Bordeaux15
Lyon15
Valenciennes14
Le mans13
Monaco13
Strasbourg12
Lorient12
Paris-SG11
Saint-Etienne11
Nice11
Toulouse10
Lille9
Marseille7
Auxerre6
Lens5
Caen4
Sochaux4
Metz2

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 :

nomEquipetotalScoreJGNPBPBCDiff
1Nancy1675111358
2Lyon1575021477
3Bordeaux1584311064
4Rennes158431954
5Valenciennes1484221293
6Monaco1384131385
7Le mans13841312111
8Strasbourg128332853
9Lorient1283321091
10Saint-Etienne118323963
11Paris-SG118251761
12Nice118323770
13Toulouse106312880
14Lille98161880
15Marseille78143710-3
16Auxerre68206415-11
17Lens5612324-2
18Caen4611449-5
19Sochaux48044614-8
20Metz28026213-11

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.

//Je n'ai pas pu résister à l'envie de faire un petit graphique regroupant les positions successives des équipes renvoyées par cette nouvelle requête : //<IMAGE>

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.

Premier programme en OpenGL sous X


22 octobre 2007

Premier code en OpenGL sous X

Il faut bien démarrer quelque part et c'est ici ! C'est sûr que c'est pas terrible, beaucoup de code pour un résultat bien consternant, mais je jette mes bases. Je dois reprendre ce qui était naturel il y a quelques années...

#include <stdio.h>
#include <stdlib.h>
#include <X11/Xlib.h>
#include <GL/glx.h>
#include <GL/gl.h>

static  int snglBuf[] = {GLX_RGBA, GLX_RED_SIZE, 1, GLX_GREEN_SIZE, 1, GLX_BLUE_SIZE, 1, GLX_DEPTH_SIZE, 12, None};
static  int dblBuf[] =  {GLX_RGBA, GLX_RED_SIZE, 1, GLX_GREEN_SIZE, 1, GLX_BLUE_SIZE, 1, GLX_DEPTH_SIZE, 12, GLX_DOUBLEBUFFER, None};

Display*    Dpy;
Window  Win;
Bool    DoubleBuffer = True;

GLfloat XAngle = 42.0, YAngle = 82.0, ZAngle = 112.0;

int FatalError(int errorCode, char* str)
{
    printf("%s\n", str);
    return errorCode;
}

void    redraw()
{
static  Bool    DisplayListInited = False;
    if (DisplayListInited)      {
        glCallList(1);
    }   else    {
        glNewList(1, GL_COMPILE_AND_EXECUTE);
        //
        glClear(GL_COLOR_BUFFER_BIT | GL_DEPTH_BUFFER_BIT);
        glBegin(GL_QUADS);
        glColor3f(0.0, 0.7, 0.1);       //Green
        glVertex3f(-1.0, 1.0, 1.0);
        glVertex3f(1.0, 1.0, 1.0);
        glVertex3f(1.0, -1.0, 1.0);
        glVertex3f(-1.0, -1.0, 1.0);
        glColor3f(0.9, 1.0, 0.0);       //Yellow
        glVertex3f(-1.0, 1.0, -1.0);
        glVertex3f(1.0, 1.0, -1.0);
        glVertex3f(1.0, -1.0, -1.0);
        glVertex3f(-1.0, -1.0, -1.0);
        glColor3f(0.2, 0.2, 1.0);       //Blue
        glVertex3f(-1.0, 1.0, 1.0);
        glVertex3f(1.0, 1.0, 1.0);
        glVertex3f(1.0, 1.0, -1.0);
        glVertex3f(-1.0, 1.0, -1.0);
        glColor3f(0.7, 0.0, 0.1);       //Red
        glVertex3f(-1.0, -1.0, 1.0);
        glVertex3f(1.0, -1.0, 1.0);
        glVertex3f(1.0, -1.0, -1.0);
        glVertex3f(-1.0, -1.0, -1.0);
        glEnd();
        //
        glEndList();
        DisplayListInited = True;
    }
    //On affiche réellement quelque chose...
    if (DoubleBuffer)       glXSwapBuffers(Dpy, Win);
    else                    glFlush();
}

int main(int argc, char** argv)
{
    XVisualInfo*    vi;
    Colormap    CMap;
    XSetWindowAttributes    swa;
    GLXContext  cx;
    XEvent  Event;
    Bool    NeedRedraw = False, RecalcModelView = True;
    int Dummy;
    //
    Dpy = XOpenDisplay(NULL);
    if (!Dpy)       return FatalError(1, "Couldn't open display...");
    if (!glXQueryExtension(Dpy, &Dummy, &Dummy))        return FatalError(2, "X n'a pas d'extension OpenGL.");
    //
    vi = glXChooseVisual(Dpy, DefaultScreen(Dpy), dblBuf);
    if (!vi)        {
        vi = glXChooseVisual(Dpy, DefaultScreen(Dpy), snglBuf);
        if (!vi)        return FatalError(3, "Pas de visuel RGB avec buffer de profondeur.");
        DoubleBuffer = False;
    }
    printf("DoubleBuffer : %d\n", DoubleBuffer);
    if (vi->class != TrueColor)     return FatalError(4, "J'ai besoin d'un visuel en true color !");
    //
    cx = glXCreateContext(Dpy, vi, None, True);
    if (!cx)        return FatalError(5, "Impossible de créer le contexte de rendu.");
    //
    CMap = XCreateColormap(Dpy, RootWindow(Dpy, vi->screen), vi->visual, AllocNone);
    swa.colormap = CMap;
    swa.border_pixel = 0;
    swa.event_mask = ExposureMask | ButtonPressMask | StructureNotifyMask;
    Win = XCreateWindow(Dpy, RootWindow(Dpy, vi->screen), 0, 0, 300, 300, 0, vi->depth, InputOutput, vi->visual, CWBorderPixel|CWColormap|CWEventMask, &swa);
    XSetStandardProperties(Dpy, Win, "glxsimple", "glxsimple", None, argv, argc, NULL);
    //
    glXMakeCurrent(Dpy, Win, cx);
    XMapWindow(Dpy, Win);
    //
    glEnable(GL_DEPTH_TEST);
    glMatrixMode(GL_PROJECTION);
    glLoadIdentity();
    glFrustum(-1.0, 1.0, -1.0, 1.0, 1.0, 10.0);
    //
    while (True)        {
        do {
            XNextEvent(Dpy, &Event);
            switch (Event.type)     {
                case    ButtonPress:
                    RecalcModelView = True;
                    switch(Event.xbutton.button)        {
                        case    1:  XAngle += 10.0;     break;
                        case    2:  YAngle += 10.0;     break;
                        case    3:  ZAngle += 10.0;     break;
                    }
                    break;
                case    ConfigureNotify:
                    glViewport(0, 0, Event.xconfigure.width, Event.xconfigure.height);
                case    Expose:
                    NeedRedraw = True;
                    break;
            }
        } while(XPending(Dpy));
        //
        if (RecalcModelView)        {
            glMatrixMode(GL_MODELVIEW);
            glLoadIdentity();
            glTranslatef(0.0, 0.0, -3.0);
            glRotatef(XAngle, 0.1, 0.0, 0.0);
            glRotatef(YAngle, 0.0, 0.1, 0.0);
            glRotatef(ZAngle, 0.0, 0.0, 1.0);
            //
            RecalcModelView = False;
            NeedRedraw = True;
        }
        if (NeedRedraw)     {
            redraw();
            NeedRedraw = False;
        }
    }
    //
    return 0;
}

Pour compiler ça, si on suppose que le code est sauvé dans un fichier simplegl.c, on lance :

gcc -o simplegl -lGL -lX11 simplegl.c

Le résulat du gloubi-boulga ci-dessus ? Et bien voilà, bow you sucker !

1 2 3........ 12 13 14 15 16 17 18 19 20