CHAPITRE 2: Modélisation prédictive
Dans le précédent article, on a pu voir que la clause MODEL d’Oracle permettait de faire des calculs un peu comme on les ferait avec un tableur en utilisant le résultat de certaines cellules pour en calculer d’autres.
La modélisation permet aussi de créer des lignes comlpètes de toute pièce en s’appuiant sur les précédentes. Ces types de modélisations sont généralement utilisés pour se projeter dans l’avenir et donc intéressant pour les modèles décisionnels
Je vais construire ici un modèle fictif, pour montrer la méthode, qui va donc se baser sur un historique et extrapoler un avenir.
Le modèle à utiliser sera celui d’un ingénieur commercial qui voudrait prédire l’évolution de ses tarifs. Rien que du très classique en fin de compte. Les règles de calcul vont être lesquelles ?
- Les tarifs sont révisés annuellement
- Les tarifs sont révisés à date de renouvellement de contrat
- La révision des tarifs aura pour objectif d’être égale à l’évolution annuelle de l’indice syntec 2 mois avant la date anniversaire du contrat.
- Il y a deux indices syntec, on se base sur celui qui correspond à la date d’origine du contrat. L’ancienne valeur a été diffusée jusqu’à la date du 1er juillet 2022 et qu’à partir de là il se calcul en multipliant le nouvel indice par une constante égale à 0,97975.
Je vais donc
- Extrapoler l’indice syntec jusqu’en juin 2025
- Extrapoler l’ancienne indice par rapport au nouveau à partir d’aout 2022
- Calculer les variations annuelles entre juillet 2023 et juin 2025
je prendrai comme hypothèse, qu’au pire des cas, l’indice n’évoluera pas plus qu’il n’a évolué l’une de ces deux dernières années.
Je crée la table de référence contenant les données connues à date de sortie de l’article (donc la variation des index anciens et nouveaux entre décembre 2019 et juin 2023). Je la nomme SYNTEC
create table syntec as
select to_date('01/12/2019','DD/MM/YYYY') mois,274.7 ancien, 274.7 nouveau from dual
select to_date('01/01/2020','DD/MM/YYYY') mois,274.9 ancien, 274.7 nouveau from dual UNION ALL
select to_date('01/02/2020','DD/MM/YYYY') mois,274.7 ancien, 274.4 nouveau from dual UNION ALL
select to_date('01/03/2020','DD/MM/YYYY') mois,275.3 ancien, 275.3 nouveau from dual UNION ALL
select to_date('01/04/2020','DD/MM/YYYY') mois,275.1 ancien, 275.1 nouveau from dual UNION ALL
select to_date('01/05/2020','DD/MM/YYYY') mois,275.2 ancien, 275.6 nouveau from dual UNION ALL
select to_date('01/06/2020','DD/MM/YYYY') mois,274.9 ancien, 275.6 nouveau from dual UNION ALL
select to_date('01/07/2020','DD/MM/YYYY') mois,274.9 ancien, 275.6 nouveau from dual UNION ALL
select to_date('01/08/2020','DD/MM/YYYY') mois,274.4 ancien, 275.2 nouveau from dual UNION ALL
select to_date('01/09/2020','DD/MM/YYYY') mois,275.2 ancien, 275.2 nouveau from dual UNION ALL
select to_date('01/10/2020','DD/MM/YYYY') mois,274.4 ancien, 274.8 nouveau from dual UNION ALL
select to_date('01/11/2020','DD/MM/YYYY') mois,274.8 ancien, 275.1 nouveau from dual UNION ALL
select to_date('01/12/2020','DD/MM/YYYY') mois,275 ancien, 275.9 nouveau from dual UNION ALL
select to_date('01/01/2021','DD/MM/YYYY') mois,275 ancien, 275.9 nouveau from dual UNION ALL
select to_date('01/02/2021','DD/MM/YYYY') mois,275.1 ancien, 276.6 nouveau from dual UNION ALL
select to_date('01/03/2021','DD/MM/YYYY') mois,274.6 ancien, 275.9 nouveau from dual UNION ALL
select to_date('01/04/2021','DD/MM/YYYY') mois,275.1 ancien, 276.9 nouveau from dual UNION ALL
select to_date('01/05/2021','DD/MM/YYYY') mois,275.3 ancien, 277.1 nouveau from dual UNION ALL
select to_date('01/06/2021','DD/MM/YYYY') mois,275.7 ancien, 278.1 nouveau from dual UNION ALL
select to_date('01/07/2021','DD/MM/YYYY') mois,275.8 ancien, 279.3 nouveau from dual UNION ALL
select to_date('01/08/2021','DD/MM/YYYY') mois,276.5 ancien, 279.8 nouveau from dual UNION ALL
select to_date('01/09/2021','DD/MM/YYYY') mois,276.5 ancien, 280 nouveau from dual UNION ALL
select to_date('01/10/2021','DD/MM/YYYY') mois,276.7 ancien, 280 nouveau from dual UNION ALL
select to_date('01/11/2021','DD/MM/YYYY') mois,276.9 ancien, 280.3 nouveau from dual UNION ALL
select to_date('01/12/2021','DD/MM/YYYY') mois,277.3 ancien, 280.7 nouveau from dual UNION ALL
select to_date('01/01/2022','DD/MM/YYYY') mois,277.5 ancien, 281.8 nouveau from dual UNION ALL
select to_date('01/02/2022','DD/MM/YYYY') mois,278.2 ancien, 282.8 nouveau from dual UNION ALL
select to_date('01/03/2022','DD/MM/YYYY') mois,279.9 ancien, 284.6 nouveau from dual UNION ALL
select to_date('01/04/2022','DD/MM/YYYY') mois,279.8 ancien, 283.8 nouveau from dual UNION ALL
select to_date('01/05/2022','DD/MM/YYYY') mois,280 ancien, 284.6 nouveau from dual UNION ALL
select to_date('01/06/2022','DD/MM/YYYY') mois,280 ancien, 284.7 nouveau from dual UNION ALL
select to_date('01/07/2022','DD/MM/YYYY') mois,280.4 ancien, 285.8 nouveau from dual UNION ALL
select to_date('01/08/2022','DD/MM/YYYY') mois,null ancien, 286.4 nouveau from dual UNION ALL
select to_date('01/09/2022','DD/MM/YYYY') mois,null ancien, 287.2 nouveau from dual UNION ALL
select to_date('01/10/2022','DD/MM/YYYY') mois,null ancien, 289.4 nouveau from dual UNION ALL
select to_date('01/11/2022','DD/MM/YYYY') mois,null ancien, 289.9 nouveau from dual UNION ALL
select to_date('01/12/2022','DD/MM/YYYY') mois,null ancien, 292.6 nouveau from dual UNION ALL
select to_date('01/01/2023','DD/MM/YYYY') mois,null ancien, 293.9 nouveau from dual UNION ALL
select to_date('01/02/2023','DD/MM/YYYY') mois,null ancien, 298.5 nouveau from dual UNION ALL
select to_date('01/03/2023','DD/MM/YYYY') mois,null ancien, 300.7 nouveau from dual UNION ALL
select to_date('01/04/2023','DD/MM/YYYY') mois,null ancien, 301.9 nouveau from dual UNION ALL
select to_date('01/05/2023','DD/MM/YYYY') mois,null ancien, 302.7 nouveau from dual UNION ALL
select to_date('01/06/2023','DD/MM/YYYY') mois,null ancien, 304 nouveau from dual
/
Je vais m’intenant interroger la table SYNTEC avec une contrainte particulière: return uodated rows et aucune règle.
select *
from SYNTEC
model return updated rows
dimension by (MOIS)
measures ( ancien, nouveau)
rules()
;
La requête ne retourne aucune ligne, ce qui est normal puisqu’aucune règle n’a été appliquée et qu’en conséquence, aucune ligne, aucune cellule n’a été calculée.
Calcul de la progression des valeurs de l’indice au delà des dates pour lesquelles on l’a.
A compter du 1er juillet 2023 et jusqu’au 1er juin 2025, je vais calculer un ratio qui donnera la pire (plus importante) évolution mensuelle de l’indice syntec ces deux dernières années à la même période.
La colonne de dimension, MOIS, est de type date, les indices dans les règles devront eux aussi être de trype date et exister dans la colonne. S’ils n’existent pas ils seront générés.
ratio [ for mois from to_date('01/07/2023','DD/MM/YYYY')
to to_date('01/06/2025','DD/MM/YYYY')
increment interval '1' MONTH ]
= greatest( nouveau[add_months(cv(),-12)] / nouveau[add_months(cv(),-13)]
, nouveau[add_months(cv(),-24)] / nouveau[add_months(cv(),-25)] )
Il n’y a donc pas besoin de générateur de type select level from dual connect by level … Ce sont les indices imposés pour la dimension qui génèrent les dates futures souhaitées.
Résultat :
MOIS ANCIEN NOUVEAU RATIO -------- ---------- ---------- ---------- 01/08/22 280,6 286,4 01/09/22 281,4 287,2 01/10/22 283,5 289,4 01/11/22 284 289,9 01/12/22 286,7 292,6 01/01/23 287,9 293,9 01/02/23 292,5 298,5 01/03/23 294,6 300,7 01/04/23 295,8 301,9 01/05/23 296,6 302,7 01/06/23 297,8 304 01/07/23 299,1 305,3 1,00431499 01/08/23 299,7 305,9 1,00209937 01/09/23 300,6 306,8 1,0027933 01/10/23 302,9 309,2 1,00766017 01/11/23 303,4 309,7 1,00172771 01/12/23 306,3 312,6 1,00931356 01/01/24 307,6 314 1,00444293 01/02/24 312,4 318,9 1,01565158 01/03/24 314,8 321,3 1,00737018 01/04/24 316,1 322,6 1,00399069 01/05/24 316,9 323,5 1,00281889 01/06/24 318,3 324,9 1,00429468 01/07/24 01/08/24 01/09/24 01/10/24 01/11/24 01/12/24 01/01/25 01/02/25 01/03/25 01/04/25 01/05/25 01/06/25
La plage de date souhaitée a bien été générée. Cependant si Oracle a bien calculé les valeurs pour 2023 et début 2024, il ne l’a pas fait juillet pour 2024 et les dates suivantes. La raison est qu’il passe les règles dans l’ordre où elles sont écrites une fois et une seule sur la table cournate. On a besoin du résultat de la règle nouveau sur 2024 pour calculer les dates manquantes sur 2025. Et on aura pas besoin de plus. J’aimerais bien qu’il fasse deux fois le passage des règles pour calculer toutes les cases qui nous intéressent.
La clause iterate
La réponse à la problématique évoquée au paragraphe précédent réside dans la clause iterate qui indique à oracle combien de fois de suite il doit appliquer les règles séquentiellement et dans l’ordre où ells sont écrites.
select *
from SYNTEC
model return updated rows
dimension by (MOIS)
measures ( ancien
, nouveau
, cast(null as integer) as ratio
)
rules iterate(2) (
ratio [ for mois from to_date('01/07/2023','DD/MM/YYYY')
to to_date('01/06/2025','DD/MM/YYYY')
increment interval '1' MONTH ] = greatest( nouveau[add_months(cv(),-12)] / nouveau[add_months(cv(),-13)]
, nouveau[add_months(cv(),-24)] / nouveau[add_months(cv(),-25)] )
, nouveau[ mois > to_date('30/06/2023','DD/MM/YYYY') ] = round( nouveau[add_months(cv(),-1)] * ratio[cv()], 1 )
, ancien[ mois > to_date('31/07/2022','DD/MM/YYYY') ] = round( nouveau[cv()] * 0.97975, 1 )
)
;
Résultat
MOIS ANCIEN NOUVEAU RATIO -------- ---------- ---------- ---------- 01/08/22 280,6 286,4 01/09/22 281,4 287,2 01/10/22 283,5 289,4 01/11/22 284 289,9 01/12/22 286,7 292,6 01/01/23 287,9 293,9 01/02/23 292,5 298,5 01/03/23 294,6 300,7 01/04/23 295,8 301,9 01/05/23 296,6 302,7 01/06/23 297,8 304 01/07/23 299,1 305,3 1,00431499 01/08/23 299,7 305,9 1,00209937 01/09/23 300,6 306,8 1,0027933 01/10/23 302,9 309,2 1,00766017 01/11/23 303,4 309,7 1,00172771 01/12/23 306,3 312,6 1,00931356 01/01/24 307,6 314 1,00444293 01/02/24 312,4 318,9 1,01565158 01/03/24 314,8 321,3 1,00737018 01/04/24 316,1 322,6 1,00399069 01/05/24 316,9 323,5 1,00281889 01/06/24 318,3 324,9 1,00429468 01/07/24 319,7 326,3 1,00427632 01/08/24 320,4 327 1,00209937 01/09/24 321,4 328 1,00294214 01/10/24 323,9 330,6 1,00782269 01/11/24 324,5 331,2 1,00172771 01/12/24 327,5 334,3 1,0093639 01/01/25 329 335,8 1,00447857 01/02/25 334,2 341,1 1,01565158 01/03/25 336,7 343,7 1,00752587 01/04/25 338,1 345,1 1,00404606 01/05/25 339,1 346,1 1,00278983 01/06/25 340,6 347,6 1,00432767
On a bien toutes nos valeurs d’indices syntec prospectives. Le reste n’est que calcul du type de ceusx qui ont été faits au chapitre précédent, rien de nouveau coté clause MODEL.
Requête définitive
La requête définitive est donc la suivante:
select mois
, ancien
, txaug_a
, nouveau
, txaug_n
from syntec
model return updated rows
dimension by (MOIS)
measures ( ancien
, nouveau
, cast(null as integer) as ratio
, cast(null as integer) as txaug_a
, cast(null as integer) as txaug_n
)
rules iterate(2) (
ratio [ for mois from to_date('01/07/2022','DD/MM/YYYY')
to to_date('01/06/2025','DD/MM/YYYY')
increment interval '1' MONTH ] = greatest( nouveau[add_months(cv(),-12)] / nouveau[add_months(cv(),-13)]
, nouveau[add_months(cv(),-24)] / nouveau[add_months(cv(),-25)] )
, nouveau[ mois > to_date('30/06/2023','DD/MM/YYYY') ] = round( nouveau[add_months(cv(),-1)] * ratio[cv()], 1 )
, ancien[ mois > to_date('30/06/2022','DD/MM/YYYY') ] = round( nouveau[cv()] * 0.97975, 1 )
, txaug_a[ mois > to_date('30/06/2022','DD/MM/YYYY') ] = round( ancien[add_months(cv(),-2)] / ancien[add_months(cv(),-14)], 4)
, txaug_n[ mois > to_date('30/06/2022','DD/MM/YYYY') ] = round( nouveau[add_months(cv(),-2)] / nouveau[add_months(cv(),-14)], 4)
)
;
Son résultat est le suivant:
MOIS ANCIEN TXAUG_A NOUVEAU TXAUG_N -------- ---------- ---------- ---------- ---------- 01/07/22 280 1,0171 285,8 1,0271 01/08/22 280,6 1,0156 286,4 1,0237 01/09/22 281,4 1,0152 287,2 1,0233 01/10/22 283,5 1,0148 289,4 1,0236 01/11/22 284 1,0177 289,9 1,0257 01/12/22 286,7 1,0246 292,6 1,0336 01/01/23 287,9 1,0256 293,9 1,0342 01/02/23 292,5 1,0339 298,5 1,0424 01/03/23 294,6 1,0375 300,7 1,0429 01/04/23 295,8 1,0514 301,9 1,0555 01/05/23 296,6 1,0525 302,7 1,0566 01/06/23 297,8 1,0572 304 1,0638 01/07/23 299,1 1,0593 305,3 1,0636 01/08/23 299,7 1,0636 305,9 1,0678 01/09/23 300,6 1,0682 306,8 1,0682 01/10/23 302,9 1,0681 309,2 1,0681 01/11/23 303,4 1,0682 309,7 1,0682 01/12/23 306,3 1,0684 312,6 1,0684 01/01/24 307,6 1,0683 314 1,0683 01/02/24 312,4 1,0684 318,9 1,0684 01/03/24 314,8 1,0684 321,3 1,0684 01/04/24 316,1 1,068 322,6 1,0683 01/05/24 316,9 1,0686 323,5 1,0685 01/06/24 318,3 1,0686 324,9 1,0686 01/07/24 319,7 1,0684 326,3 1,0687 01/08/24 320,4 1,0688 327 1,0688 01/09/24 321,4 1,0689 328 1,0688 01/10/24 323,9 1,0691 330,6 1,069 01/11/24 324,5 1,0692 331,2 1,0691 01/12/24 327,5 1,0693 334,3 1,0692 01/01/25 329 1,0695 335,8 1,0694 01/02/25 334,2 1,0692 341,1 1,0694 01/03/25 336,7 1,0696 343,7 1,0694 01/04/25 338,1 1,0698 345,1 1,0696 01/05/25 339,1 1,0696 346,1 1,0697 01/06/25 340,6 1,0696 347,6 1,0697