Intégrez un modèle de calcul à vos requêtes SQL : la clause MODEL d'Oracle


précédentsommaire

II. Statistiques et simulation

II-A. Références multi-cellules

Il est possible de traiter plusieurs cellules avec une seule règle, en utilisant une référence multi-cellules. Pour illustrer cette notion, je vais introduire une nouvelle table de démonstration avec une clé primaire composée, afin de pouvoir travailler sur des dimensions multiples. Cette table contient les ventes mensuelles de deux livres en 2005 et 2006. Voici la requête de création :

 
Sélectionnez

SQL> create table sales
  2  as
  3  select 'The Da Vinci Code' book, date '2005-03-01' month, 'Netherlands' country, 5 amount from dual union all
  4  select 'The Da Vinci Code', date '2005-04-01', 'Netherlands', 8 from dual union all
  5  select 'The Da Vinci Code', date '2005-05-01', 'Netherlands', 3 from dual union all
  6  select 'The Da Vinci Code', date '2005-07-01', 'Netherlands', 2 from dual union all
  7  select 'The Da Vinci Code', date '2005-10-01', 'Netherlands', 1 from dual union all
  8  select 'The Da Vinci Code', date '2005-02-01', 'United Kingdom', 15 from dual union all
  9  select 'The Da Vinci Code', date '2005-03-01', 'United Kingdom', 33 from dual union all
 10  select 'The Da Vinci Code', date '2005-04-01', 'United Kingdom', 47 from dual union all
 11  select 'The Da Vinci Code', date '2005-05-01', 'United Kingdom', 44 from dual union all
 12  select 'The Da Vinci Code', date '2005-06-01', 'United Kingdom', 11 from dual union all
 13  select 'The Da Vinci Code', date '2005-08-01', 'United Kingdom', 2 from dual union all
 14  select 'The Da Vinci Code', date '2005-05-01', 'France', 2 from dual union all
 15  select 'The Da Vinci Code', date '2005-08-01', 'France', 3 from dual union all
 16  select 'The Da Vinci Code', date '2006-01-01', 'France', 4 from dual union all
 17  select 'Bosatlas', date '2005-01-01', 'Netherlands', 102 from dual union all
 18  select 'Bosatlas', date '2005-02-01', 'Netherlands', 55 from dual union all
 19  select 'Bosatlas', date '2005-03-01', 'Netherlands', 68 from dual union all
 20  select 'Bosatlas', date '2005-04-01', 'Netherlands', 42 from dual union all
 21  select 'Bosatlas', date '2005-05-01', 'Netherlands', 87 from dual union all
 22  select 'Bosatlas', date '2005-06-01', 'Netherlands', 40 from dual union all
 23  select 'Bosatlas', date '2005-07-01', 'Netherlands', 31 from dual union all
 24  select 'Bosatlas', date '2005-08-01', 'Netherlands', 26 from dual union all
 25  select 'Bosatlas', date '2005-09-01', 'Netherlands', 22 from dual union all
 26  select 'Bosatlas', date '2005-10-01', 'Netherlands', 23 from dual union all
 27  select 'Bosatlas', date '2005-11-01', 'Netherlands', 88 from dual union all
 28  select 'Bosatlas', date '2005-12-01', 'Netherlands', 143 from dual union all
 29  select 'Bosatlas', date '2006-01-01', 'Netherlands', 31 from dual union all
 30  select 'Bosatlas', date '2006-02-01', 'Netherlands', 18 from dual union all
 31  select 'Bosatlas', date '2006-03-01', 'Netherlands', 15 from dual union all
 32  select 'Bosatlas', date '2006-04-01', 'Netherlands', 11 from dual union all
 33  select 'Bosatlas', date '2006-05-01', 'Netherlands', 17 from dual union all
 34  select 'Bosatlas', date '2006-06-01', 'Netherlands', 9 from dual union all
 35  select 'Bosatlas', date '2006-07-01', 'Netherlands', 12 from dual union all
 36  select 'Bosatlas', date '2006-08-01', 'Netherlands', 20 from dual union all
 37  select 'Bosatlas', date '2006-09-01', 'Netherlands', 4 from dual union all
 38  select 'Bosatlas', date '2006-10-01', 'Netherlands', 5 from dual union all
 39  select 'Bosatlas', date '2006-11-01', 'Netherlands', 1 from dual union all
 40  select 'Bosatlas', date '2006-12-01', 'Netherlands', 1 from dual
 41  /
 
Table créée

Le livre titré Bosatlas a une ligne chaque mois, mais seulement aux Pays-Bas (Netherlands). Le Da Vinci Code a été vendu dans les trois pays, mais pas tous les mois. Les colonnes book (livre), month (mois) et country (pays) forment la clé primaire de cette table. Pour que les mois apparaissent plus clairement, je modifie ainsi le format de date :

 
Sélectionnez

 SQL> alter session set nls_date_format = 'fmmonth yyyy'
   2 /

L'exemple suivant présente comment doubler les ventes de Bosatlas à partir de juin 2006 :

 
Sélectionnez

SQL> select book
  2       , month
  3       , country
  4       , amount
  5   from sales
  6   model
  7         return updated rows
  8         partition by (country)
  9         dimension by (book,month)
 10         measures (amount)
 11         rules
 12         ( amount['Bosatlas',month > date '2006-06-01'] =
 13             amount['Bosatlas',cv(month)] * 2
 14         )
 15  /
 
BOOK              MONTH          COUNTRY            AMOUNT
----------------- -------------- -------------- ----------
Bosatlas          juillet 2006   Netherlands            24
Bosatlas          août 2006      Netherlands            40
Bosatlas          août 2006      Netherlands             8
Bosatlas          octobre 2006   Netherlands            10
Bosatlas          novembre 2006  Netherlands             2
Bosatlas          décembre 2006  Netherlands             2
 
6 lignes sélectionnées

L'indicateur amount a maintenant deux dimensions. Pour nous référer à une cellule du modèle, nous devons donc spécifier à la fois un livre et un mois. Avec une seule règle, je modifie six cellules, grâce à l'expression month > date '2006-06-01' à gauche du = de la règle. A droite, la fonction cv est utilisée pour se référer à la valeur correspondante côté gauche, cv indiquant la valeur courante. A la place de cv(month), j'aurais pu aussi utiliser cv(), car il n'y a qu'une référence multi-cellules. Même avec plus de références multi-cellules, cv() reste utilisable, tant que la dimension concernée est évidente. Si ce n'est pas le cas, vous aurez une ORA-32611 :

 
Sélectionnez

ERREUR à la ligne N :
ORA-32611: utilisation incorrecte de l'opérateur MODEL CV

Pour indiquer l'ensemble des valeurs d'une dimension, plutôt qu'un sous-ensemble, utilisez le mot-clé ANY :

 
Sélectionnez

SQL> select book
  2       , month
  3       , country
  4       , amount
  5    from sales
  6   model
  7         return updated rows
  8         partition by (country)
  9         dimension by (book, month)
 10         measures (amount)
 11         rules
 12         ( amount[any, date '2005-08-01'] = 200
 13         )
 14   order by book, month
 15  /
 
BOOK              MONTH          COUNTRY            AMOUNT
----------------- -------------- -------------- ----------
Bosatlas          août 2005  Netherlands           200
The Da Vinci Code août 2005  United Kingdom        200
The Da Vinci Code août 2005  France                200
 
3 lignes sélectionnées

Dans cette règle, le mot-clé ANY est utilisé pour indiquer tous les livres qui ont eu des ventes en août 2005. Les références multi-cellules sont également possibles avec le mot-clé FOR (par exemple : amount['Bosatlas', for month from date '2005-03-01' to date '2005-08-01' increment 1]), avec BETWEEN (par exemple: amount['Bosatlas'], month between date '2005-03-01' and date '2005-08-01']), et avec tous les autres opérateurs de comparaison.

II-B. Modèles de référence

Les modèles de référence sont des sous-modèles dans un modèle. Le modèle principal peut utiliser toutes les valeurs du modèle de référence, mais ces valeurs ne peuvent pas directement être placées dans le SELECT. Nous parlons ici de données auxiliaires, qui ne sont lisibles que par le modèle principal. Pour illustrer cette idée, voici une table supplémentaire contenant les prix des deux livres :

 
Sélectionnez

SQL> create table prices
  2  as
  3  select 'Bosatlas' book, 42.95 price from dual union all
  4  select 'The Da Vinci Code', 19.95 from dual
  5  /
 
Table créée

Les deux lignes de la table des prix sont fournies au modèle principal par le modèle de référence :

 
Sélectionnez

SQL> select book
  2       , month
  3       , country
  4       , amount
  5       , to_char(turnover,'99G990D00') turnover
  6   from sales
  7   where month between date '2005-07-01' and date '2005-12-31'
  8   model
  9         reference prices on (select book, price from prices)
 10           dimension by (book)
 11           measures (price)
 12         main result
 13           partition by (country)
 14           dimension by (book, month)
 15           measures (0 as turnover, amount)
 16           rules
 17           ( turnover[any,any] = amount[cv(),cv()] * price[cv(book)]
 18           )
 19   order by book
 20       , month
 21  /
 
BOOK              MONTH          COUNTRY            AMOUNT TURNOVER
----------------- -------------- -------------- ---------- ----------
Bosatlas          juilet 2005    Netherlands            31   1.331,45
Bosatlas          août 2005      Netherlands            26   1.116,70
Bosatlas          septembre 2005 Netherlands            22     944,90
Bosatlas          octobre 2005   Netherlands            23     987,85
Bosatlas          november 2005  Netherlands            88   3.779,60
Bosatlas          décembre 2005  Netherlands           143   6.141,85
The Da Vinci Code juilet 2005    Netherlands             2      39,90
The Da Vinci Code août 2005      United Kingdom          2      39,90
The Da Vinci Code août 2005      France                  3      59,85
The Da Vinci Code octobre 2005   Netherlands             1      19,95
 
10 lignes sélectionnées

Le modèle de référence contient donc lui aussi des dimensions et des indicateurs, mais il ne peut avoir ni partition, ni règle propre. Notez également que les deux modèles sont maintenant nommés : le modèle de référence est nommé prices (introduit par le mot-clé REFERENCE) et le modèle principal result (préfixé par MAIN). L'indicateur price du modèle de référence est utilisé dans le modèle principal, au moyen de l'expression price[cv(book)]. Enfin, nous avons créé un nouvel indicateur nommé turnover (chiffre d'affaires), initialisé à zéro pour toutes les cellules existantes.

II-C. Itération

L'itération permet de répéter la même règle plusieurs fois sur le même modèle. Dans l'exemple ci-dessous, les ventes du Da Vinci Code sont élevées à la puissance quatre, sans utiliser la fonction POWER, juste pour vous montrer le fonctionnement de l'itération. Pour cela, il nous fait multiplier le nombre de ventes trois fois par le nombre original, comme ceci :

 
Sélectionnez

SQL> select book
  2       , month
  3       , country
  4       , a1 amount
  5       , a2 amount_to_the_fourth
  6   from sales
  7   where book = 'The Da Vinci Code'
  8     and country = 'Netherlands'
  9   model
 10         partition by (country)
 11         dimension by (book,month)
 12         measures (amount a1, amount a2)
 13         rules iterate (3)
 14         ( a2[any,any] = a2[cv(),cv()] * a1[cv(),cv()]
 15         )
 16   order by month
 17  /
 
BOOK              MONTH          COUNTRY            AMOUNT AMOUNT_TO_THE_FOURTH
----------------- -------------- -------------- ---------- --------------------
The Da Vinci Code mars 2005      Netherlands             5                  625
The Da Vinci Code avril 2005     Netherlands             8                 4096
The Da Vinci Code mai 2005       Netherlands             3                   81
The Da Vinci Code juilet 2005    Netherlands             2                   16
The Da Vinci Code octobre 2005   Netherlands             1                    1
 
5 lignes renvoyées.

Nous plaçons le mot-clé ITERATE, suivi du nombre d'itérations, directement après RULES. L'indicateur a1 contient le nombre de ventes original, inchangé. L'indicateur a2 contient initialement les nombres 5, 8, 3, 2 et 1, comme a1. A chaque itération, ces nombres sont multipliés par a1. Après la première itération, a2 contient 25, 64, 9, 4 et 1, et après la deuxième 125, 512, 8 et 1. Les résultats finaux sont présentés dans le jeu de données ci-dessus.

Pour travailler avec les itérations, il est très pratique d'utiliser le mot-clé UNTIL, suivi d'une expression booléenne permettant d'éviter les itération inutiles. Le terme ITERATION_NUMBER, qui donne le numéro de l'itération en cours, peut être utilisé dans la règle. Attention, il commence à 0. Dans l'exemple ci-dessus, il irait donc de 0 à 2.

Pour vous montrer comment UNTIL et ITERATION_NUMBER fonctionnent, voici un exemple où l'on génère une prévision des ventes futures de chaque livre en supposant qu'elles baisseront de 75% par an. Chaque année, nous vendrons donc seulement 25% des ventes du même mois de l'année précédente (en arrondissant vers le bas). L'itération doit se répéter jusqu'à ce qu'on ne vende plus un seul livre de toute l'année. Voici la requête pour ce faire :

 
Sélectionnez

SQL> select book
  2       , country
  3       , to_date(to_char(y) || to_char(m),'yyyymm') month
  4       , amount
  5   from sales
  6   where book = 'Bosatlas'
  7       and extract (year from month) = 2006
  8   model
  9         partition by ( book, country)
 10         dimension by ( extract(year from month) y, extract(month from month) m)
 11         measures (amount, 0 max_monthly_amount)
 12         rules upsert all
 13         iterate (100) until (max_monthly_amount[2007+iteration_number,1] < 4)
 14         ( amount[2007+iteration_number,any]
 15           = trunc(amount[2006+iteration_number,cv()]/4)
 16         , max_monthly_amount[2007+iteration_number,1]
 17           = max(amount)[2007+iteration_number,m between 1 and 12]
 18         )
 19   order by y, m
 20  /

Cet exemple est beaucoup plus complexe que ceux que nous avons vus jusqu'à maintenant. Comme vous pouvez le constater dans la clause WHERE, nous commençons par les 12 lignes du livre Bosatlas en 2006. La colonne month est séparée en deux dimensions, l'année (year) et le numéro du mois (month), ce qui permet de traiter en une seule itération les douze mois d'une année complète.

Pour empêcher l'apparition de la dernière année avec des ventes à zéro tous les mois, j'ai créé un indicateur auxiliaire appellé max_monthly_amount. Cet indicateur est alimenté par une fonction analytique, max(amount)[2007+iteration_number, month_number between 1 and 12] et donne le plus haut nombre de ventes mensuelles pour l'année courante. Cet indicateur est utilisé dans la clause UNTIL. Les itérations doivent s'arrêter quand ce maximum est inférieur à 4. En effet, tant qu'il y a au moins 4 ventes, cela donnera des ventes supérieures à 0 l'année suivante. Avec les données courantes, seules deux des 100 itérations prévues sont exécutées, renvoyant ce résultat :

 
Sélectionnez

BOOK              COUNTRY        MONTH              AMOUNT
----------------- -------------- -------------- ----------
Bosatlas          Netherlands    janvier 2006           31
Bosatlas          Netherlands    février 2006           18
Bosatlas          Netherlands    mars 2006              15
Bosatlas          Netherlands    avril 2006             11
Bosatlas          Netherlands    mai 2006               17
Bosatlas          Netherlands    juin 2006               9
Bosatlas          Netherlands    juillet 2006           12
Bosatlas          Netherlands    août 2006              20
Bosatlas          Netherlands    septembre 2006          4
Bosatlas          Netherlands    octobre 2006            5
Bosatlas          Netherlands    november 2006           1
Bosatlas          Netherlands    décembre 2006           1
Bosatlas          Netherlands    janvier 2007            7
Bosatlas          Netherlands    février 2007            4
Bosatlas          Netherlands    mars 2007               3
Bosatlas          Netherlands    avril 2007              2
Bosatlas          Netherlands    mai 2007                4
Bosatlas          Netherlands    juin 2007               2
Bosatlas          Netherlands    juillet 2007            3
Bosatlas          Netherlands    août 2007               5
Bosatlas          Netherlands    septembre 2007          1
Bosatlas          Netherlands    octobre 2007            1
Bosatlas          Netherlands    november 2007           0
Bosatlas          Netherlands    décembre 2007           0
Bosatlas          Netherlands    janvier 2008            1
Bosatlas          Netherlands    février 2008            1
Bosatlas          Netherlands    mars 2008               0
Bosatlas          Netherlands    avril 2008              0
Bosatlas          Netherlands    mai 2008                1
Bosatlas          Netherlands    juin 2008               0
Bosatlas          Netherlands    juillet 2008            0
Bosatlas          Netherlands    août 2008               1
Bosatlas          Netherlands    septembre 2008          0
Bosatlas          Netherlands    octobre 2008            0
Bosatlas          Netherlands    novembre 2008           0
Bosatlas          Netherlands    décembre 2008           0
 
36 lignes sélectionnées

Dans cette requête, le mode utilisé est UPSERT ALL. C'est l'un des trois modes possibles, les deux autres étant UPDATE et UPSERT. UPSERT est le mode par défaut (nous l'avions donc implicitement utilisé dans tous les exemples précédents). Le mode UPDATE interdit la création de nouvelles lignes, contrairement à l'UPSERT. Enfin, la différence entre UPSERT et UPSERT ALL est que ce dernier crée de nouvelles cellules même si la partie gauche de la règle contient des références sur des variables (dites "références non-positionnelles"). Par exemple, si la partie gauche de la règle contient le mot-clé ANY, le mode UPSERT ne rechercherait les correspondances que dans les cellules existantes. UPSERT ALL crée de nouvelles cellules, une pour chaque correspondance avec une valeur de dimension non-positionnelle.

La notion de référence non-positionnelle et le fonctionnement exact du UPSERT ALL par rapport à l'UPSERT font l'objet d'un autre billet de Rob Van Wijk, non encore traduit, mais disponible ici en anglais : About Oracle: UPSERT ALL

D'autres fonctions et possibilités sont disponibles avec la clause MODEL, qui valent vraiment la peine d'être étudiées, mais je vais me contenter de les citer rapidement. Tout d'abord, les fonctions PRESENTV et PRESENTNNV, le prédicat IS PRESENT et les mots-clés KEEP NAV et IGNORE NAV. En bref, ils vous permettent de distinguer entre cellules manquantes et cellules existantes contenant un NULL. Ensuite, il y a la fonction PREVIOUS, pour faire référence à la valeur de l'itération précédente dans une clause UNTIL. Enfin, les clauses AUTOMATIC ORDER et SEQUENTIAL ORDER, qui permettent à Oracle de décider lui-même dans quel ordre les règles doivent être évaluées. Toutes ces clauses et fonctions sont assez clairement expliquées dans la documentation.

Dans la dernière partie, je tenterai d'expliquer les utilisations pratiques de la clause MODEL. Je présenterai quelques solutions à des problèmes auparavant insolubles, ainsi que des solutions plus efficaces à des problèmes courants (les habitués des OTN Forums verront sans doute à quoi je fais allusion). Cette troisième partie est en cours de publication (en néerlandais) dans la revue OGh Visie. Elle sera prochainement disponible (en anglais) sur mon blog.

Script SQL contenant toutes les requêtes utilisées


précédentsommaire

  

Copyright © 2007 Rob van Wijk. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.