QuinielaML - Préparation des données et gestion des modèles prédictifs

La série d'articles relatifs à l'application QuinielaML se poursuit. Dans cet article, nous verrons comment préparer les données brutes que nous avons capturées à l'aide de la fonctionnalité Embedded Python.

Bienvenue à toutes et à tous !

Introduction

Si vous vous souvenez de l'article précédent, nous avons capturé les données des résultats des matches de football de première et deuxième division pour les 23 dernières années en utilisant Embedded Python à partir d'un site web externe. Maintenant que nous disposons des données brutes, nous allons les transformer et les préparer pour faciliter la maintenance de l'application et l'utilisation de notre modèle de prédiction.

QUINIELA_Object.RawMatch

Voyons sous quelle forme se présentent les données que nous avons saisies dans notre base de données IRIS :

Comme vous le voyez dans la capture d'écran suivante, elles sont peu différentes des informations présentes sur le site web de BDFutbol :

 

Tableaux principaux :

Pour faciliter la maintenance ultérieure des données communes et améliorer les performances du modèle de prédiction, nous avons défini deux tableaux principaux pour stocker les équipes et les arbitres. Ces tableaux ne comprendront que la colonne contenant l'identifiant de l'enregistrement et le nom. Examinons les deux tableaux :

QUINIELA_Object.Referee:

Сorrespondance avec le tableau principal d'arbitres.

QUINIELA_Object.Team

Сorrespondance avec le tableau principal d'arbitres.

 

Préparation de données

Avec nos tableaux principaux et nos données brutes, nous pouvons maintenant entreprendre le processus de préparation de ces données en vue de la formation de notre modèle. Nous avons ici l'opération métier qui sera chargée de la préparation :

Class QUINIELA.BO.PrepareBO Extends Ens.BusinessOperation
{

Parameter INVOCATION = "Queue";
Method PrepareData(pRequest As QUINIELA.Message.PrepareRequest, pResponse As QUINIELA.Message.PrepareResponse) As %Status
{
    Set sc = $$$OK
    set pResponse = ##class(QUINIELA.Message.PrepareResponse).%New()
    set pResponse.Operation = pRequest.Operation
    
    set sqlTruncateTrain = "TRUNCATE TABLE QUINIELA_Object.MatchTrain"
    set statementTruncateTrain = ##class(%SQL.Statement).%New()
    set statusTruncateTrain = statementTruncateTrain.%Prepare(sqlTruncateTrain)
    if ($$$ISOK(statusTruncateTrain)) {
        set resultSetTruncateTrain = statementTruncateTrain.%Execute()
        if (resultSetTruncateTrain.%SQLCODE = 0) {
            set sqlMatchTrain = "INSERT INTO QUINIELA_Object.MatchTrain (Day, Division, Journey, LocalTeam, Referee, Result, VisitorTeam, IntDay) "_
                "SELECT "_
                "TO_DATE(RM.Day,'DD/MM/YYYY') AS DayTransformed, "_
                "RM.Division, "_
                "RM.Journey, "_
                "LT.ID as LocalTeam, "_
                "R.ID as Referee, "_
                "CASE WHEN CAST(RM.GoalsLocal As INTEGER) > CAST(RM.GoalsVisitor As INTEGER) THEN 1 WHEN CAST(RM.GoalsLocal As INTEGER) _
                "VT.ID as VisitorTeam, "_
                "CAST({fn CONCAT({fn CONCAT(SUBSTR(RM.Day,7,4),SUBSTR(RM.Day,4,2))},SUBSTR(RM.Day,1,2))} As INTEGER) as IntDay "_
                "FROM "_
                "QUINIELA_Object.RawMatch RM "_
                "LEFT JOIN QUINIELA_Object.Team LT ON UPPER(RM.LocalTeam) = UPPER(LT.Name) "_
                "LEFT JOIN QUINIELA_Object.Team VT ON UPPER(RM.VisitorTeam) = UPPER(VT.Name) "_
                "LEFT JOIN QUINIELA_Object.Referee R ON UPPER(RM.Referee) = UPPER(R.Name)"
            set statementMatchTrain = ##class(%SQL.Statement).%New()
            set statusMatchTrain = statementMatchTrain.%Prepare(sqlMatchTrain)
            if ($$$ISOK(statusMatchTrain)) {
                set resultSetMatchTrain = statementMatchTrain.%Execute()
                if (resultSetMatchTrain.%SQLCODE = 0) {
                    set sqlUpdateLocalStreak = "UPDATE QUINIELA_Object.MatchTrain SET QUINIELA_Object.MatchTrain.LocalStreak = "_
                        "(SELECT SUM(CASE WHEN IsVictory = 1 THEN 4-%VID ELSE 0 END) FROM "_
                        "(SELECT TOP 3 SubMatch.IntDay, "_
                        "CASE WHEN Result = 1 THEN 1 ELSE 0 END AS IsVictory "_
                        "FROM QUINIELA_Object.MatchTrain AS SubMatch "_
                        "WHERE "_
                        "UPPER(SubMatch.LocalTeam) = UPPER(QUINIELA_Object.MatchTrain.LocalTeam) "_
                        "AND SubMatch.IntDay _
                        "ORDER BY SubMatch.IntDay DESC)) "
                    set statementUpdateLocalStreak = ##class(%SQL.Statement).%New()
                    set statusUpdateLocalStreak = statementUpdateLocalStreak.%Prepare(sqlUpdateLocalStreak)
                    if ($$$ISOK(statusUpdateLocalStreak)) {
                        set resultSetUpdateLocalStreak = statementUpdateLocalStreak.%Execute()
                        if (resultSetUpdateLocalStreak.%SQLCODE = 0) {
                            set sqlUpdateVisitorStreak = "UPDATE QUINIELA_Object.MatchTrain SET QUINIELA_Object.MatchTrain.VisitorStreak = "_
                                "(SELECT SUM(CASE WHEN IsVictory = 1 THEN 4-%VID ELSE 0 END) FROM "_
                                "(SELECT TOP 3 SubMatch.IntDay, "_
                                "CASE WHEN Result = 2 THEN 1 ELSE 0 END AS IsVictory "_
                                "FROM QUINIELA_Object.MatchTrain AS SubMatch "_
                                "WHERE "_
                                "UPPER(SubMatch.VisitorTeam) = UPPER(QUINIELA_Object.MatchTrain.VisitorTeam) "_
                                "AND SubMatch.IntDay _
                                "ORDER BY SubMatch.IntDay DESC)) "
                            set statementUpdateVisitorStreak = ##class(%SQL.Statement).%New()
                            set statusUpdateVisitorStreak = statementUpdateVisitorStreak.%Prepare(sqlUpdateVisitorStreak)
                            if ($$$ISOK(statusUpdateVisitorStreak)) {
                                set resultSetUpdateVisitorStreak = statementUpdateVisitorStreak.%Execute()
                                set sc = statusUpdateVisitorStreak
                            }
                            else {
                                set sc = statusUpdateVisitorStreak
                            }
                        }
                    }
                    else {
                        set sc = statusUpdateLocalStreak
                    }
                }
            }
            else {
                set sc = statusMatchTrain
            }
        }
    }
    
    set pResponse.Status = "Finished"
    Return sc
}

XData MessageMap
{

  "QUINIELA.Message.PrepareRequest">
    PrepareData
  

}

}

Examinons maintenant en détail chacune des instructions SQL que nous exécutons :

1. Nous quittons le tableau de données d'apprentissage :

TRUNCATE TABLE QUINIELA_Object.MatchTrain

2. Nous lançons une insertion massive dans notre tableau d'apprentissage QUINIELA_Object.MatchTrain

INSERT INTO QUINIELA_Object.MatchTrain (Day, Division, Journey, LocalTeam, Referee, Result, VisitorTeam, IntDay) 
    SELECT
        TO_DATE(RM.Day,'DD/MM/YYYY') AS DayTransformed, 
        RM.Division, 
        RM.Journey, 
        LT.ID as LocalTeam, 
        R.ID as Referee, 
        CASE WHEN CAST(RM.GoalsLocal As INTEGER) > CAST(RM.GoalsVisitor As INTEGER) THEN 1 WHEN CAST(RM.GoalsLocal As INTEGER) CAST(RM.GoalsVisitor As INTEGER) THEN 2 ELSE 0 END as Result, 
        VT.ID as VisitorTeam, 
        CAST({fn CONCAT({fn CONCAT(SUBSTR(RM.Day,7,4),SUBSTR(RM.Day,4,2))},SUBSTR(RM.Day,1,2))} As INTEGER) as IntDay 
    FROM
        QUINIELA_Object.RawMatch RM 
        LEFT JOIN QUINIELA_Object.Team LT ON UPPER(RM.LocalTeam) = UPPER(LT.Name) 
        LEFT JOIN QUINIELA_Object.Team VT ON UPPER(RM.VisitorTeam) = UPPER(VT.Name) 
        LEFT JOIN QUINIELA_Object.Referee R ON UPPER(RM.Referee) = UPPER(R.Name)

Pour y parvenir, nous remplaçons le libellé par le nom de l'arbitre et les équipes par leur référence dans les tableaux principaux. Nous obtenons également le résultat à partir des scores du match, 0 pour le match nul, 1 pour la victoire à domicile et 2 pour la victoire à l'extérieur. La colonne

**Résultat** est celle qui définit notre modèle de prédiction comme un modèle de classification, c'est-à-dire que chaque correspondance est classée dans l'une des trois classes (1, X ou 2).  

3. Nous calculons les séries pour chaque équipe selon qu'elle joue à domicile ou à l'extérieur. Nous avons ajouté ces colonnes pour améliorer, autant que possible, la performance du modèle prédictif. Nous avons supposé qu'une équipe qui a plusieurs victoires consécutives à domicile ou à l'extérieur a plus de facilité à poursuivre les victoires en étant "sur une série". Le calcul se fait de la manière suivante : on obtient les 3 derniers matchs (à domicile pour l'équipe qui joue à domicile ou à l'extérieur pour celle qui joue à l'extérieur), si elle a gagné le dernier match on lui attribue 3 points, si elle a gagné le match avant-dernier on lui attribue 2 points et si elle a gagné le match précédent avant-dernier on lui attribue 1 point, enfin on additionne les points obtenus ce qui donne une valeur numérique à la série. Série à domicile :

UPDATE QUINIELA_Object.MatchTrain SET QUINIELA_Object.MatchTrain.LocalStreak = 
    (SELECT SUM(CASE WHEN IsVictory = 1 THEN 4-%VID ELSE 0 END) FROM
        (SELECT TOP 3 SubMatch.IntDay, 
            CASE WHEN Result = 1 THEN 1 ELSE 0 END AS IsVictory 
        FROM QUINIELA_Object.MatchTrain AS SubMatch 
        WHERE
            UPPER(SubMatch.LocalTeam) = UPPER(QUINIELA_Object.MatchTrain.LocalTeam) 
            AND SubMatch.IntDay ORDER BY SubMatch.IntDay DESC
        )
    )

Série à l'extérieur:

UPDATE QUINIELA_Object.MatchTrain SET QUINIELA_Object.MatchTrain.VisitorStreak = 
    (SELECT SUM(CASE WHEN IsVictory = 1 THEN 4-%VID ELSE 0 END) 
    FROM
        (SELECT TOP 3 SubMatch.IntDay, 
            CASE WHEN Result = 2 THEN 1 ELSE 0 END AS IsVictory 
        FROM QUINIELA_Object.MatchTrain AS SubMatch 
        WHERE
            UPPER(SubMatch.VisitorTeam) = UPPER(QUINIELA_Object.MatchTrain.VisitorTeam) 
            AND SubMatch.IntDay < QUINIELA_Object.MatchTrain.IntDay 
            ORDER BY SubMatch.IntDay DESC
        )
    )

Voyons quel est le résultat de cette série d'insertions et de mises à jour consultant le tableau QUINIELA_Object.MatchTrain :

Comme vous voyez, nous avons transformé les champs de texte en valeurs numériques... ou non ? Examinons la définition de la classe :

Class QUINIELA.Object.MatchTrain Extends (%Persistent, %JSON.Adaptor) [ DdlAllowed ]
{

/// Jour du match
Property Day As %Date;
/// Équipe locale
Property LocalTeam As %String;
/// Équipe jouant à l'extérieur
Property VisitorTeam As %String
/// Série locale
Property LocalStreak As %Integer;
/// Série de victoires de l'équipe jouant à l'extérieur
Property VisitorStreak As %Integer
/// Arbitre
Property Referee As %String;
/// Résultat
Property Result &As %String
/// Division
Property Division As %String;
/// Itinéraire
Property Journey As %String;
/// Nombre entier de jours
Property IntDay As %Integer;
}

Comme vous voyez, les références aux tableaux principaux sont toujours de type %String. Pourquoi ? Sur cette page vous trouverez l'explication de la documentation, mais en résumé, c'est parce que, bien qu'elles soient réellement numériques, elles ne correspondent pas à des valeurs quantifiables, mais à des identifiants.

Parfait, nous avons maintenant tout ce qu'il faut pour créer et former notre modèle prédictif.

 

Création et formation du modèle prédictif

Grâce à la fonctionnalité d'IntegratedML, cette étape est très simple pour nous, puisqu'il nous suffit d'exécuter deux commandes simples dans notre base de données. Examinons la transaction métier que nous avons créée à cette fin :

Class QUINIELA.BO.TrainBO Extends Ens.BusinessOperation
{

Parameter INVOCATION = "Queue";
/// Description
Method CreateAndTrainModel(pRequest As QUINIELA.Message.TrainRequest, pResponse As QUINIELA.Message.TrainResponse) As %Status
{
        Set tSC = $$$OK
        set pResponse = ##class(QUINIELA.Message.TrainResponse).%New()
        set pResponse.Operation = pRequest.Operation
        set pResponse.Status = "In Process"
        set sql = "SELECT MODEL_NAME FROM INFORMATION_SCHEMA.ML_MODELS WHERE MODEL_NAME = 'QuinielaModel'"
        set statement = ##class(%SQL.Statement).%New()
        set status = statement.%Prepare(sql)
        $$$TRACE(status)
        if ($$$ISOK(status)) {
            set resultSet = statement.%Execute()
            $$$TRACE(resultSet.%SQLCODE)
            if (resultSet.%SQLCODE = 0) {
                while (resultSet.%Next() '= 0) {
                    if (resultSet.%GetData(1) '= "") {
                        set sqlDrop = "DROP MODEL QuinielaModel"
                        set statementDrop = ##class(%SQL.Statement).%New()
                        set statusDrop = statementDrop.%Prepare(sqlDrop)
                        if ($$$ISOK(statusDrop)) {
                            set resultSetDrop = statementDrop.%Execute()
                            if (resultSetDrop.%SQLCODE = 0) {
                                set tSC = statusDrop                                                                
                            }
                        }
                    }
                }
            }            
        }
        $$$TRACE("Creating model")
        set sqlCreate = "CREATE MODEL QuinielaModel PREDICTING (Result) FROM QUINIELA_Object.MatchTrain"
        set statementCreate = ##class(%SQL.Statement).%New()
        set statusCreate = statementCreate.%Prepare(sqlCreate)
        if ($$$ISOK(statusCreate)) {
            $$$TRACE("Model created")
            set resultSetCreate = statementCreate.%Execute()
            if (resultSetCreate.%SQLCODE = 0) {
                set tSC = statusCreate                                
            }
        }
        else
        {
            set tSC = statusDrop
        }

        $$$TRACE("Training model")
        set sqlTrain = "TRAIN MODEL QuinielaModel"
        set statementTrain = ##class(%SQL.Statement).%New()
        set statusTrain = statementTrain.%Prepare(sqlTrain)
        if ($$$ISOK(statusTrain)) {
            set resultSetTrain = statementTrain.%Execute()
            if (resultSetTrain.%SQLCODE = 0) {
                // VALIDATION OF THE MODEL WITH THE PRE-LOADED MATCHES
                set sqlValidate = "VALIDATE MODEL QuinielaModel FROM QUINIELA_Object.MatchTrain"
                set statementValidate = ##class(%SQL.Statement).%New()
                set statusValidate = statementValidate.%Prepare(sqlValidate)
                set resultSetValidate = statementValidate.%Execute()
                set tSC = statusValidate                                    
            }
        }
        else {
            set tSC = statusTrain
        }
        
        set pResponse.Status = "Finished"
        Return tSC
}

XData MessageMap
{

  "QUINIELA.Message.TrainRequest">
    CreateAndTrainModel
  

}

}

Analysons ce que fait notre BO :

1. Création du modèle prédictif et définition de la valeur à prédire en indiquant la colonne de notre tableau d'entraînement correspondant.

CREATE MODEL QuinielaModel PREDICTING (Result) FROM QUINIELA_Object.MatchTrain

2. Formation de notre modèle nouvellement créé.

TRAIN MODEL QuinielaModel

3. Validation du modèle créé sur la base du tableau d'entraînement utilisé.

VALIDATE MODEL QuinielaModel FROM QUINIELA_Object.MatchTrain

Avec ces trois étapes très simples, notre modèle est prêt à générer des prédictions. Examinons la qualité de notre modèle. Pour ce faire, exécutons la requête suivante :

SELECT * FROM INFORMATION_SCHEMA.ML_VALIDATION_METRICS

Avec cette requête, nous obtiendrons les mesures suivantes :

Eh bien, pas si mal, pour la victoire au niveau local, le taux de réussite est de 52%, pour la victoire au niveau des visiteurs de 41% et pour les matchs nuls de 37%, nous dépassons le taux de réussite de 33% dû au pur hasard !