SQL Developpement: La clause MODEL

Post by: hbadir
16 août 2017
No Comment

La clause MODEL

Dans une feuille de calcul d’un tableur, on peut très simplement effectuer des calculs mettant en jeu les cellules de cette feuille de calcul. Ainsi vous pouvez ajouter la troisième colonne de la deuxième ligne à la cinquième colonne de la cinquième ligne ou ajouter une nouvelle ligne à partir de la somme des deux ou plusieurs autres existantes. Les tableurs vous permettent de traiter chaque ligne et colonne de données et l’utiliser dans une formule ce qui est très difficile voire impossible de faire en SQL classique.

A partir de la version Oracle 10g, une nouvelle fonctionnalité appelée la clause « MODEL » est introduite avec le niveau de performance d’un tableur classique.

La syntaxe de la clause « MODEL » est trop complexe pour être décrite dans son ensemble, et nous allons de ce fait commencer par la décrire partiellement comme suit :

SELECT * FROM …

MODEL [ RETURN { UPDATED | ALL } ROWS ]

[ MAIN nom_du_modèle ]

PARTITION BY ( expression[,…])

DIMENSION BY ( expression[,…])

MEASURES ( métrique[,…])

RULES ( métrique [expression[,…]][,…] ) ;

RETURN La description des enregistrements qui sont retournés ; « ALL » tous les enregistrements, « UPDATED » uniquement les enregistrements calculés.

PARTITION BY La définition des colonnes utilisées pour partitionner les lignes sélectionnées.

DIMENSION BY La définition des colonnes donnant le niveau de l’enregistrement pour le calcul à l’intérieur de la partition. Les valeurs des ces colonnes permettent d’identifier une celule dans le tableau.

MEASURES La définition des colonnes métriques qui sont utilisées pour les calculs dans le modèle.

RULES La définition des cellules qui seront affectées par le calcul et le mode d’application des règles. Chaque règle représente une affectation dans laquelle l’opérande gauche désigne les cellules à mettre à jour et l’opérande droit représente les valeurs à affecter à ces cellules. Si la cellule n’existe pas alors elle est créée.

métrique[val] La description d’une cellule du tableau. La valeur peut être une constante qui represente une valeur de la dimension déjà existante dans le tableau ou une nouvelle valeur pour le nouvel enregistrement.

SQL> WITH QM_2010 AS (

2 SELECT MOIS, SUM(QUANTITE) Q

3 FROM COMMANDES NATURAL JOIN DETAILS_COMMANDES

4 WHERE ANNEE = 2010 GROUP BY MOIS)

5 SELECT MOIS, Q FROM QM_2010

6 MODEL DIMENSION BY (MOIS) MEASURES (Q)

7 RULES ( Q[8.5] = Q[7]+Q[8], Q[12.5] = Q[11]+Q[12]) ORDER BY MOIS;

MOIS Q

———- ———–

1 2 586 939

2 2 422 312

3 2 562 429

4 2 413 987

5 2 662 399

6 2 538 585

7 2 568 649

8 2 722 756

8,5 5 291 405 <= 2 568 649 + 2 722 756

9 2 491 305

10 2 823 864

11 2 579 959

12 2 458 342

12,5 5 038 301 <= 2 579 959 + 2 458 342

Dans l’exemple précédent, la requête retourne les quantités mensuelles vendues pour l’année 2010. Il y a deux enregistrements supplémentaires : le premier est la somme des quantités vendues pour les mois de juillet et août et le deuxième représente la somme des quantités vendues pour les mois de novembre et décembre. Les deux enregistrements sont initialisés à l’aide de la syntaxe :

nom_métrique [ nouvelle valeur dimension ] = expression

Il faut être attentif car il n’est pas possible d’utiliser une constante de type différent du type de la dimension correspondante, sauf dans le cas des conversions implicites. Dans l’exemple précédent, pour les deux enregistrements, les valeurs attribuées pour la dimension M sont respectivement 8,5 et 12,5.

Il est possible de n’afficher que les enregistrements calculés à l’aide de la syntaxe « RETURN UPDATES ROWS » ; l’exécution de l’exemple précédent est alors comme dans l’exemple suivant.

SQL> WITH QM_2010 AS (

…

7 MODEL RETURN UPDATED ROWS

…

MOIS Q

———- ———–

8,5 5 291 405

12,5 5 038 301

Vous avez la possibilité d’utiliser et afficher toutes les colonnes qui sont déclarées dans la syntaxe de la clause « MODEL ». Qu’il s’agisse de « PARTITION BY », « DIMENSION BY » ou « MEASURES », toutes ces colonnes sont accessibles en calcul ou affichage.

Mais attention, aucune autre colonne n’est accessible pour l’affichage ou le calcul ; ainsi, si vous avez besoin d’afficher ou de traiter une colonne, il faut prévoir de l’inclure dans les déclarations de la clause « MODEL ».

SQL> SELECT ANNEE, MOIS, P FROM (

2 SELECT ANNEE, MOIS, SUM(PORT) P FROM COMMANDES

3 WHERE ANNEE = 2011 GROUP BY ANNEE, MOIS) SR

4 MODEL DIMENSION BY (MOIS) MEASURES (P) RULES();

SELECT ANNEE, MOIS, P FROM (

ERREUR à la ligne 1 :

ORA-32614: expression MODEL SELECT interdite

SQL> SELECT MOIS, P FROM (SELECT ANNEE, MOIS, SUM(PORT) P FROM COMMANDES

2 WHERE ANNEE = 2011 GROUP BY ANNEE, MOIS) SR

3 MODEL DIMENSION BY (MOIS) MEASURES (P) RULES();

MOIS P

———- ———-

2 66132,7

6 69621,7

1 65722,3

5 67417,3

3 73703,4

4 68543,4

Dans l’exemple précédent uniquement, la requête qui affiche les colonnes présentes dans la syntaxe de la clause « MODEL » peut être exécutée bien que la clause n’effectue aucune opération.

Dans la description des dimensions, vous pouvez utiliser une ou plusieurs colonnes pour déterminer le niveau d’agrégation, mais par la suite la liste entière des ces colonnes doit être utilisée pour identifier les cellules du tableau.

Dans l’exemple précédent, vous pouvez voir que la somme pour les catégories ‘Viandes’, ‘Viande en conserve’ et ‘Poissons et fruits de mer’ est effectuée uniquement pour les ventes qui on été faites en ‘France’.

SQL> WITH

2 PCQ_2010_01 AS (

3 SELECT PAYS, NOM_CATEGORIE CATEGORIE, SUM(DC.QUANTITE) Q

4 FROM CLIENTS CL NATURAL JOIN COMMANDES CO

5 NATURAL JOIN DETAILS_COMMANDES DC

6 JOIN PRODUITS USING(REF_PRODUIT)

7 JOIN CATEGORIES USING(CODE_CATEGORIE)

8 WHERE PAYS IN (‘Allemagne’,’France’)

9 AND ANNEE = 2011 AND MOIS = 1 AND CODE_CATEGORIE > 5

10 GROUP BY PAYS, NOM_CATEGORIE)

11 SELECT PAYS, CATEGORIE, Q FROM PCQ_2010_01

12 MODEL DIMENSION BY (PAYS,CATEGORIE) MEASURES (Q)

13 RULES ( Q[‘France’,’Viandes et Poissons’] =

14 Q[‘France’,’Viandes’]+ Q[‘France’,’Viande en conserve’] +

15 Q[‘France’,’Poissons et fruits de mer’])ORDER BY PAYS,CATEGORIE;

PAYS CATEGORIE Q

——— ————————- ——-

Allemagne Conserves 31 976

Allemagne Poissons et fruits de mer 41 059

Allemagne Produits secs 42 383

Allemagne Viande en conserve 10 323

Allemagne Viandes 21 255

France Conserves 32 195

France Poissons et fruits de mer 40 964

France Produits secs 39 467

France Viande en conserve 11 324

France Viandes 22 512

France Viandes et Poissons 74 800 <= 40964 + 11324 + 22512

A l’aide de la clause « MODEL » vous pouvez calculer des nouveaux enregistrements mais également modifier les valeurs des enregistrements existants. Il faut cependant être attentif car les calculs ultérieurs définis dans la clause « MODEL » sont effectués avec ces valeurs.

SQL> WITH PPA_AUT AS (

2 SELECT PAYS, ANNEE, SUM(PORT) P

3 FROM COMMANDES NATURAL JOIN CLIENTS

4 WHERE PAYS = ‘Autriche’

5 GROUP BY PAYS, ANNEE)

6 SELECT * FROM PPA_AUT

7 UNION ALL

8 SELECT * FROM PPA_AUT

9 MODEL DIMENSION BY (PAYS,ANNEE) MEASURES (P)

10 RULES ( P[‘Autriche’,2008] = P[‘Autriche’,2010],

11 P[‘Autriche’,2009] =

12 P[‘Autriche’,2010]+P[‘Autriche’,2011],

13 P[‘Autriche’,2010] = 0,

14 P[‘Autriche’,2012] =

15 P[‘Autriche’,2010]+P[‘Autriche’,2011]);

PAYS ANNEE P

———– —– ———

Autriche 2010 12 679,5

Autriche 2011 7 011,7 <= enregistrements initiaux

Autriche 2010 ,0

Autriche 2011 7 011,7

Autriche 2009 19 691,2

Autriche 2008 12 679,5

Autriche 2012 7 011,7

Dans l’exemple précédent, les deux premiers enregistrements sont les enregistrements initiaux. Il y a quatre calculs dans la clause « MODEL » pour les années 2008, 2009, 2010 et 2012. Le premier calcul affiché pour l’année 2008 est la valeur de frais de port de l’année 2010 initiale avant sa modification. Le deuxième calcul affiché pour l’année 2009 est la somme des frais de port des années 2010 et 2011 avant la modification de la valeur de l’année 2010. Le troisième calcul modifie la valeur des frais de port pour l’année 2010 en la mettant à 0. Le quatrième calcul affiché pour l’année 2012 est identique au deuxième mais il est exécuté après la modification des frais de port pour de l’année 2010, et alors la valeur trouvée est uniquement donnée par les frais de port de l’année 2011.

Vous pouvez utiliser plusieurs métriques à partir de la sous-requête principale, mais vous avez également la possibilité d’introduire des nouvelles directement dans la syntaxe de la clause « MODEL » comme suit :

MEASURES ( { métrique | expression } [AS alias] [,…] )

SQL> WITH QPPA_FS AS (

2 SELECT PAYS, ANNEE, SUM(PORT) P, SUM(QUANTITE) Q

3 FROM CLIENTS NATURAL JOIN COMMANDES

4 NATURAL JOIN DETAILS_COMMANDES

5 WHERE PAYS = ‘France’ AND REF_PRODUIT = 1

6 GROUP BY PAYS, ANNEE)

7 SELECT * FROM QPPA_FS

8 MODEL DIMENSION BY (PAYS, ANNEE) MEASURES (P,Q,0 C)

9 RULES ( C[‘France’,2010] = P[‘France’,2010]/Q[‘France’,2010],

10 C[‘France’,2011] = P[‘France’,2011]/Q[‘France’,2011],

11 P[‘France’,2012] = P[‘France’,2010]+P[‘France’,2011],

12 Q[‘France’,2012] = Q[‘France’,2010]+Q[‘France’,2011],

13 C[‘France’,2012] = P[‘France’,2012]/Q[‘France’,2012])

14 ORDER BY 2;

PAYS ANNEE P Q C

——- —– ——— ——— ——-

France 2010 19 878,9 29 972,0 0,6632

France 2011 14 779,2 21 466,0 0,6885

France 2012 34 658,1 51 438,0 0,6738

Dans l’exemple précédent, les deux métriques P et Q sont des champs de la sous-requête principale ; par contre C est une colonne décrite dans la clause « MODEL ». Il est impératif dans ce cas de fournir une constante ou une expression pour déterminer le type de la colonne. Attention, toutes les opérations futures avec cette colonne doivent respecter ce type comme pour toute colonne d’une table.

La condition « IS PRESENT » est une autre condition typique de la clause « MODEL » permettant de tester l’existence d’une cellule dans le tableau.

SQL> WITH VMPQ AS (

2 SELECT PAYS, DATE_COMMANDE D, SUM(PORT) P

3 FROM CLIENTS JOIN COMMANDES USING (CODE_CLIENT)

4 JOIN DETAILS_COMMANDES USING (NO_COMMANDE)

5 WHERE DATE_COMMANDE > ’20/06/2011′ AND PAYS IN (‘France’)

6 AND REF_PRODUIT = 110 GROUP BY PAYS, DATE_COMMANDE )

7 SELECT * FROM VMPQ

8 MODEL DIMENSION BY (PAYS,D) MEASURES (P,TO_NUMBER(NULL) T)

9 RULES ( T[‘France’,’22/06/2011′]=

10 CASE WHEN P[‘France’,’22/06/2011′] IS PRESENT

11 THEN P[‘France’,’22/06/2011′] ELSE NULL END,

12 T[‘France’,’25/06/2011′]=

13 CASE WHEN P[‘France’,’25/06/2011′] IS PRESENT

14 THEN P[‘France’,’25/06/2011′] ELSE NULL END

15 )ORDER BY 1,2,3;

PAYS D P T

———– ———- —— ——

France 21/06/2011 243,0 ——

France 22/06/2011 67,1 67,1

France 25/06/2011 —— —— <= nouvel enregistrement

France 28/06/2011 87,5 ——

France 29/06/2011 66,4 ——

France 30/06/2011 93,0 ——

La modification des cellules

La syntaxe pour décrire les colonnes de type métrique dans les règles est la suivante :

… RULES [{ UPDATE | UPSERT [ALL] }] …

UPDATE L’option force la base de données à appliquer les règles sur l’ensemble des cellules référencées dans l’opérande gauche qui existent dans le tableau multidimensionnel. Si la cellule n’existe pas, la règle est ignorée.

UPSERT L’option force la base de données à appliquer les règles sur l’ensemble des cellules référencées dans l’opérande gauche qui existent dans le tableau multidimensionnel et à créer des nouvelles lignes pour les cellules qui n’existent pas. Attention, si l’argument « ALL » n’est pas défini, la règle ne s’applique qu’avec un opérande gauche uni-colonne référencé par position, c’est l’option par défaut.

Dans l’exemple suivant vous pouvez remarquer que dans le cas d’utilisation de l’option « UPDATE » et de l’enregistrement qui n’existe pas alors, il n’y a aucune modification car l’affichage ne concerne que les enregistrements modifiés suite à l’option « RETURN UPDATED ROWS ».

SQL> SELECT * FROM (

2 SELECT DT.MOIS, SUM(PORT) P FROM COMMANDES JOIN DIM_TEMPS DT

3 ON( DATE_COMMANDE = JOUR) WHERE DT.ANNEE = 2011 GROUP BY DT.MOIS )

4 MODEL RETURN UPDATED ROWS DIMENSION BY (MOIS) MEASURES (P)

5 RULES UPDATE( P[‘Total’] = SUM(P)[MOIS LIKE ‘%’] );

aucune ligne sélectionnée

SQL> SELECT * FROM (

2 SELECT DT.MOIS, SUM(PORT) P FROM COMMANDES JOIN DIM_TEMPS DT

3 ON( DATE_COMMANDE = JOUR) WHERE DT.ANNEE = 2011 GROUP BY DT.MOIS )

4 MODEL RETURN UPDATED ROWS DIMENSION BY (MOIS) MEASURES (P)

5 RULES UPSERT ( P[‘Total’] = SUM(P)[MOIS LIKE ‘%’] );

MOIS P

—————— ———-

Total 411140,8

Les deux requêtes de l’exemple précédent utilisent dans les règles une fonction agrégat car il s’agit dans ce cas d’un calcul sur l’ensemble des enregistrements de la source de données. Le calcul est effectué uniquement sur les enregistrements qui vérifient la condition décrite entre les crochets, dans notre exemple, il s’agit des tous les enregistrements.

Vous pouvez utiliser la condition « ANY », valable pour effectuer des calculs pour tous les enregistrements d’une dimension de la clause « MODEL », à l’aide de la syntaxe suivante :

[nom dimension IS] ANY

En cas d’utilisation d’une condition à la place d’une constante il s’agit d’un calcul multi-cellules.

Attention, toute utilisation d’accès multi-cellule se solde avec une erreur si vous n’utilisez pas des fonctions d’agrégat.

Vous pouvez utiliser une fonction d’agrégat pour les valeurs d’un ensemble de cellules en utilisant la syntaxe suivante :

fonction_aggrégat( métrique )[ dimension[,…] ]

Dans l’exemple suivant, vous pouvez voir trois enregistrements calculés. Le premier calcul affiché pour la France est la somme de quantités vendues en France pour les mois de janvier et de février. Le deuxième affiché pour la Suisse est la somme des quantités vendues en Suisse pour les mois d’avril et mai. Le dernier calcul est affiché pour Italie, mais il somme les ventes dans tous les pays pour le mois de juin.

SQL> WITH VMPQ AS (SELECT DT.MOIS_N M, DT.MOIS, PAYS, SUM(DC.QUANTITE) Q

2 FROM CLIENTS JOIN COMMANDES CO USING (CODE_CLIENT)

3 JOIN DETAILS_COMMANDES DC USING (NO_COMMANDE)

4 JOIN PRODUITS USING (REF_PRODUIT)

5 JOIN DIM_TEMPS DT ON( DATE_COMMANDE = JOUR)

6 WHERE CO.ANNEE = 2011 AND REF_PRODUIT < 5

7 AND NO_EMPLOYE IN (45,47,73) GROUP BY DT.MOIS_N, DT.MOIS, PAYS )

8 SELECT * FROM VMPQ

9 MODEL DIMENSION BY (M,MOIS,PAYS) MEASURES (Q)

10 RULES UPSERT ALL

11 ( Q[7,’Total Jan Mar’,’France’] = SUM(Q)[M < 3,ANY,’France’] ,

12 Q[7,’Total Avr Mai’,’Suisse’] = SUM(Q)[M IN (4,5),ANY,’Suisse’],

13 Q[7,’Total Juin’ ,’Italie’] = SUM(Q)[M = 6,ANY,ANY])

14 ORDER BY 1,3;

M MOIS PAYS Q

— —————— ————– ——

1 Janvier France 4616

1 Janvier Italie 720

1 Janvier Suisse 271

2 Février France 3521

2 Février Italie 644

2 Février Suisse 288

3 Mars France 4725

3 Mars Italie 2874

3 Mars Suisse 354

4 Avril France 3391

4 Avril Italie 987

5 Mai France 4946

5 Mai Italie 789

5 Mai Suisse 690

6 Juin France 4746

6 Juin Italie 553

6 Juin Suisse 293

7 Total Jan Mar France 8137 <= 4616 + 3521

7 Total Juin Italie 5592 <= 4746 + 553 + 293

7 Total Avr Mai Suisse 690 <= 690

Vous pouvez utiliser la condition « ANY » pour effectuer le calcul pour toutes les valeurs de la dimension correspondante. Il ne faut pas oublier d’ajouter l’option « UPSERT ALL », sans quoi les enregistrements ne sont pas affichés.

SQL> SELECT * FROM

2 ( SELECT DT.ANNEE, DT.MOIS, SUM(PORT) P FROM COMMANDES JOIN

3 DIM_TEMPS DT ON( DATE_COMMANDE = JOUR ) GROUP BY DT.ANNEE, DT.MOIS)

4 MODEL RETURN UPDATED ROWS

5 DIMENSION BY (ANNEE,MOIS) MEASURES (P)

6 RULES UPSERT ( P[ANY ,’Total’] = SUM(P)[ANY,ANY] );

aucune ligne sélectionnée

SQL> SELECT * FROM

2 ( SELECT DT.ANNEE, DT.MOIS, SUM(PORT) P FROM COMMANDES JOIN

3 DIM_TEMPS DT ON( DATE_COMMANDE = JOUR ) GROUP BY DT.ANNEE, DT.MOIS)

4 MODEL RETURN UPDATED ROWS MAIN model_mp

5 DIMENSION BY (ANNEE,MOIS) MEASURES (P)

6 RULES UPSERT ALL ( P[ANY ,’Total’] = SUM(P)[ANY,ANY] );

ANNEE MOIS P

———- ——– ————

2010 Total 1 007 454

2011 Total 2 014 907

La fonction CV

La fonction « CURRENTV » ou son alias « CV » permettent de récupérer la valeur courante d’une dimension définie dans la règle et permet un indexage relatif. Les données renvoyées sont du type de la colonne de la dimension. Sans argument, la fonction utilise la dimension associée avec la position relative d’écriture dans la fonction.

SQL> WITH VMPQ AS (SELECT DT.MOIS_N M, DT.MOIS, PAYS, SUM(DC.QUANTITE) Q

2 FROM CLIENTS JOIN COMMANDES CO USING (CODE_CLIENT)

3 JOIN DETAILS_COMMANDES DC USING (NO_COMMANDE)

4 JOIN DIM_TEMPS DT ON( DATE_COMMANDE = JOUR)

5 WHERE CO.ANNEE = 2011 AND NO_EMPLOYE IN (45,47)

6 GROUP BY DT.MOIS_N, DT.MOIS, PAYS )

7 SELECT PAYS,MOIS,Q1,Q2,Q3 FROM VMPQ

8 MODEL DIMENSION BY (M,MOIS,PAYS) MEASURES (Q Q1,0 Q2,0 Q3)

9 RULES UPSERT ALL( Q1[7,’Total’,ANY] = SUM(Q1)[ANY,ANY,CV()],

10 Q2[ANY,ANY,ANY] = Q1[CV(),CV(),CV()],

11 Q3[ANY,ANY,ANY] = SUM(Q2)[CV(),CV(),ANY]) ORDER BY PAYS,M;

PAYS MOIS Q1 Q2 Q3

—— ——– ——– ——– ——–

France Janvier 108 331 108 331 118 108 <= 108 331 + 9 777

France Février 105 103 105 103 112 950

France Mars 110 334 110 334 135 474

France Avril 105 589 105 589 107 884

France Mai 141 985 141 985 160 145

France Juin 120 750 120 750 138 806

France Total 692 092 692 092 773 367

Suisse Janvier 9 777 9 777 118 108

Suisse Février 7 847 7 847 112 950

Suisse Mars 25 140 25 140 135 474

Suisse Avril 2 295 2 295 107 884

Suisse Mai 18 160 18 160 160 145

Suisse Juin 18 056 18 056 138 806

Suisse Total 81 275 81 275 773 367

L’exemple précédent permet d’afficher la colonne Q2 avec la même information que pour la colonne Q1 et calculer dans la colonne Q3 la somme de quantités vendues pour le même mois tous pays confondus. Un enregistrement de plus est affiché pour chaque pays et représente la somme des toutes les quantités vendues pour le pays.

SQL> SELECT * FROM ( SELECT ANNEE, MOIS, SUM(PORT) P1 FROM COMMANDES

2 WHERE NO_EMPLOYE = 12 AND MOIS BETWEEN 1 AND 3 GROUP BY ANNEE, MOIS)

3 MODEL DIMENSION BY(ANNEE,MOIS) MEASURES(P1,0 P2,0 P3,0 P4,0 P5)

4 RULES UPSERT ALL( P1[ANY,4] = SUM(P1)[CURRENTV(ANNEE),ANY] ,

5 P2[ANY,ANY ] = P1[CURRENTV(),CV(MOIS)],

6 P3[ANY,MOIS < 4] = P1[CV()+1,CV()],

7 P4[ANY,MOIS < 4] = SUM(P1)[ANY,CV()],

8 P5[ANY,MOIS < 4] = SUM(P1)[CV(),MOIS < 4])

9 ORDER BY ANNEE, MOIS;

ANNEE MOIS P P1 P2 P3 P4

—– —– ——– ——– ——– ——– ——–

2010 1 661,0 661,0 956,7 1 617,7 1 553,0

2010 2 424,4 424,4 744,4 1 168,8 1 553,0

2010 3 467,6 467,6 662,1 1 129,7 1 553,0

2010 4 1 553,0 1 553,0 ——– ——– ——–

2011 1 956,7 956,7 ——– 1 617,7 2 363,2

2011 2 744,4 744,4 ——– 1 168,8 2 363,2

2011 3 662,1 662,1 ——– 1 129,7 2 363,2

2011 4 2 363,2 2 363,2 ——– ——– ——–

La requête suivante effectue cinq calculs qui sont affichés dans les colonnes de métriques. Le premier calcul est la somme des frais de port pour l’année en cours affichée dans le mois 4, pour chaque année de la requête principale. La deuxième règle copie les valeurs de la colonne P1 pour chaque enregistrement et les affiche dans la colonne P2. La troisième règle de calcul copie dans la colonne P3 les valeurs de l’année suivante pour le même mois si le numéro du mois est < 4. La quatrième règle de calcul affiche dans la colonne P4, si le numéro du mois est < 4, la somme des frais de port pour le même mois dans toutes les années. La dernière règle permet de calculer la somme des frais de port de l’année courante.

Dans l’exemple suivant, la colonne Q2 affiche pour le même pays la différence entre les quantités vendues dans le mois précédent, si l’enregistrement existe, et le mois courant. La colonne Q3 représente le poids des quantités vendues pour le pays courant par rapport à la somme des ventes pour le mois courant.

SQL> WITH VMPQ AS ( SELECT MOIS, PAYS, SUM(DC.QUANTITE) Q

2 FROM CLIENTS JOIN COMMANDES CO USING (CODE_CLIENT)

3 JOIN DETAILS_COMMANDES DC USING (NO_COMMANDE)

4 WHERE CO.ANNEE = 2011 AND NO_EMPLOYE IN (45,47) GROUP BY MOIS, PAYS )

5 SELECT PAYS,MOIS,Q1,Q2,Q3 FROM VMPQ

6 MODEL DIMENSION BY (MOIS,PAYS) MEASURES (Q Q1,0 Q2,0 Q3)

7 RULES ( Q2[ANY,ANY] = Q1[CV(),CV()] –

8 CASE WHEN Q1[CV()-1,CV()] IS PRESENT

9 THEN Q1[CV()-1,CV()] ELSE NULL END,

10 Q3[ANY,ANY] = Q1[CV(),CV()] * 100 / SUM(Q1)[CV(),ANY])

11 ORDER BY PAYS,MOIS;

PAYS MOIS Q1 Q2 Q3

—— —- ——– ——– ——–

France 1 108 331 ——– 91,72

France 2 105 103 -3 228 93,05

France 3 110 334 5 231 81,44

France 4 105 589 -4 745 97,87

France 5 141 985 36 396 88,66

France 6 120 750 -21 235 86,99

Suisse 1 9 777 ——– 8,28

Suisse 2 7 847 -1 930 6,95

Suisse 3 25 140 17 293 18,56

Suisse 4 2 295 -22 845 2,13

Suisse 5 18 160 15 865 11,34

Suisse 6 18 056 -104 13,01

Les créations multiples

Si vous utilisez la condition « ANY » à gauche de l’égal de la règle pour effectuer le calcul de toutes les valeurs d’une dimension unique, il n’y a aucune contrainte. Dans le cas des dimensions multiples, l’utilisation de la condition « ANY » pour toutes les dimensions, le fonctionnement est celui décrit précédemment. Maintenant si vous avez plusieurs dimensions et que l’utilisation de la condition « ANY » se limite à un nombre inférieur au total des dimensions, le nombre d’enregistrements modifiés ou insérés est égal au résultat de la requête « SELECT DISTINCT » de la liste des colonnes qui n’ont pas la condition « ANY ».

STAG01@topaze>SELECT PAYS, ANNEE, MOIS, SUM(PORT) P

2 FROM COMMANDES JOIN CLIENTS USING(CODE_CLIENT)

3 WHERE (PAYS=’France’ AND MOIS<3 )OR(PAYS=’Finlande’ AND MOIS<5 )

4 GROUP BY PAYS, ANNEE, MOIS ORDER BY 1,2,3;

PAYS ANNEE MOIS P

——– ———- ———- —————

Finlande 2010 1 844,30

Finlande 2010 2 926,50

Finlande 2010 3 981,90

Finlande 2010 4 1 006,70

Finlande 2011 1 1 118,30

Finlande 2011 2 759,20

Finlande 2011 3 1 763,30

Finlande 2011 4 804,70

France 2010 1 7 079,20

France 2010 2 5 232,20

France 2011 1 7 698,20

France 2011 2 8 747,90

STAG01@topaze>SELECT * FROM (SELECT PAYS, ANNEE, MOIS, SUM(PORT) P

2 FROM COMMANDES JOIN CLIENTS USING(CODE_CLIENT)

3 WHERE (PAYS = ‘France’ AND MOIS < 3 ) OR

4 (PAYS = ‘Finlande’ AND MOIS < 5 )

5 GROUP BY PAYS, ANNEE, MOIS )

6 MODEL RETURN UPDATED ROWS

7 DIMENSION BY (PAYS, ANNEE, MOIS) MEASURES (P)

8 RULES UPSERT ALL (

9 P[‘Nouveau Pays’,ANY,ANY] = sum(P)[ANY,CV(),CV()] )

10 ORDER BY 1,2,3;

PAYS ANNEE MOIS P

—————- ———- ———- ————

Nouveau Pays 2010 1 7 924

Nouveau Pays 2010 2 6 159

Nouveau Pays 2010 3 982

Nouveau Pays 2010 4 1 007

Nouveau Pays 2011 1 8 817

Nouveau Pays 2011 2 9 507

Nouveau Pays 2011 3 1 763

Nouveau Pays 2011 4 805

STAG01@topaze>SELECT * FROM (SELECT PAYS, ANNEE, MOIS, SUM(PORT) P

2 FROM COMMANDES JOIN CLIENTS USING(CODE_CLIENT)

3 WHERE (PAYS = ‘France’ AND MOIS < 3 ) OR

4 (PAYS = ‘Finlande’ AND MOIS < 5 )

5 GROUP BY PAYS, ANNEE, MOIS )

6 MODEL RETURN UPDATED ROWS

7 DIMENSION BY (PAYS, ANNEE, MOIS) MEASURES (P)

8 RULES UPSERT ALL (

9 P[‘Nouveau Pays’,2011,ANY] = sum(P)[ANY,CV(),CV()] )

10 ORDER BY 1,2,3;

PAYS ANNEE MOIS P

—————- ———- ———- ————

Nouveau Pays 2011 1 8 817

Nouveau Pays 2011 2 9 507

Nouveau Pays 2011 3 1 763

Nouveau Pays 2011 4 805

STAG01@topaze>SELECT * FROM (SELECT PAYS, ANNEE, MOIS, SUM(PORT) P

2 FROM COMMANDES JOIN CLIENTS USING(CODE_CLIENT)

3 WHERE (PAYS = ‘France’ AND MOIS < 3 ) OR

4 (PAYS = ‘Finlande’ AND MOIS < 5 )

5 GROUP BY PAYS, ANNEE, MOIS )

6 MODEL RETURN UPDATED ROWS

7 DIMENSION BY (PAYS, ANNEE, MOIS) MEASURES (P)

8 RULES UPSERT ALL (

9 P[‘France’,2010,ANY] = P[CV(),CV(),CV()] )

10 ORDER BY 1,2,3;

PAYS ANNEE MOIS P

——– ———- ———- ————

France 2010 1 7 079

France 2010 2 5 232

France 2010 3

France 2010 4

STAG01@topaze>SELECT * FROM (SELECT PAYS, ANNEE, MOIS, SUM(PORT) P

2 FROM COMMANDES JOIN CLIENTS USING(CODE_CLIENT)

3 WHERE (PAYS = ‘France’ AND MOIS < 3 ) OR

4 (PAYS = ‘Finlande’ AND MOIS < 5 )

5 GROUP BY PAYS, ANNEE, MOIS )

6 MODEL RETURN UPDATED ROWS

7 DIMENSION BY (PAYS, ANNEE, MOIS) MEASURES (P)

8 RULES UPSERT ALL (

9 P[ANY,2010,ANY] = 1 )

10 ORDER BY 1,2,3;

PAYS ANNEE MOIS P

——– ———- ———- ————

Finlande 2010 1 1

Finlande 2010 2 1

Finlande 2010 3 1

Finlande 2010 4 1

France 2010 1 1

France 2010 2 1

Les conditions

Il existe plusieurs types de conditions pouvant être utilisées dans les règles pour identifier la dimension ou la liste des dimensions correspondantes. Les premières conditions utilisées jusqu’à présent sont les conditions logiques simples que vous avez déjà rencontrées pour la clause « WHERE ».

SQL> WITH VMPP AS (

2 SELECT NOM, PAYS, MOIS M, SUM(PORT) P

3 FROM EMPLOYES JOIN COMMANDES USING (NO_EMPLOYE)

4 WHERE ANNEE = 2011 AND NO_EMPLOYE IN (3,45,47,68)

5 GROUP BY NOM, PAYS, MOIS )

6 SELECT * FROM VMPP

7 MODEL DIMENSION BY (NOM,PAYS,M)

8 MEASURES (P P1,TO_NUMBER(NULL) P2,TO_NUMBER(NULL) P3,

9 TO_NUMBER(NULL) P4,TO_NUMBER(NULL) P5,TO_NUMBER(NULL) P6,

10 TO_NUMBER(NULL) P7)

11 RULES (P2[ANY,PAYS IN (‘France’,’Suisse’),M > 3]= P1[CV(),CV(),CV()],

12 P3[NOM LIKE ‘Le%’,ANY,M < 4]=P1[CV(),CV(),CV()],

13 P4[ANY,ANY,M BETWEEN 2 AND 4]=P1[CV(),CV(),CV()],

14 P5[ANY,ANY,ANY]=AVG(P1)[CV(),CV(),M BETWEEN 1 AND 4],

15 P6[ANY,ANY,ANY]=MIN(P1)[NOM LIKE ‘%re’,

16 PAYS NOT IN (‘France’,’Suisse’),ANY],

17 P7[ANY,PAYS > ‘Irlande’,ANY]=MAX(P1)[CV(),CV(),ANY])

18 ORDER BY NOM,PAYS,M;

NOM PAYS M P1 P2 P3 P4 P5 P6 P7

——– ——- — —— —— —— —— —— —— ——

Dupouey Irlande 1 71 —— —— —— 122 365 ——

Dupouey Irlande 2 191 —— —— 191 122 365 ——

Dupouey Irlande 3 58 —— —— 58 122 365 ——

Dupouey Irlande 4 167 —— —— 167 122 365 ——

Dupouey Irlande 5 262 —— —— —— 122 365 ——

Dupouey Irlande 6 316 —— —— —— 122 365 ——

Gregoire France 1 2 002 —— —— —— 2 120 365 ——

Gregoire France 2 2 062 —— —— 2 062 2 120 365 ——

Gregoire France 3 2 274 —— —— 2 274 2 120 365 ——

Gregoire France 4 2 142 2 142 —— 2 142 2 120 365 ——

Gregoire France 5 2 612 2 612 —— —— 2 120 365 ——

Gregoire France 6 2 391 2 391 —— —— 2 120 365 ——

Lefebvre Suisse 1 226 —— 226 —— 232 365 471

Lefebvre Suisse 2 158 —— 158 158 232 365 471

Lefebvre Suisse 3 471 —— 471 471 232 365 471

Lefebvre Suisse 4 72 72 —— 72 232 365 471

Lefebvre Suisse 5 321 321 —— —— 232 365 471

Lefebvre Suisse 6 347 347 —— —— 232 365 471

Letertre Espagne 1 899 —— 899 —— 793 365 ——

Letertre Espagne 2 784 —— 784 784 793 365 ——

Letertre Espagne 3 365 —— 365 365 793 365 ——

Letertre Espagne 4 1 124 —— —— 1 124 793 365 ——

Letertre Espagne 5 1 031 —— —— —— 793 365 ——

Letertre Espagne 6 850 —— —— —— 793 365 ——

Il est également possible d’utiliser la fonction « CV » dans les conditions pour avoir un contrôle relatif à l’enregistrement courant.

SQL> WITH VMPP AS ( SELECT PAYS, MOIS M, SUM(PORT) P

2 FROM EMPLOYES JOIN COMMANDES USING (NO_EMPLOYE)

3 WHERE ANNEE = 2011 AND NO_EMPLOYE IN (3,45,47,68)

4 GROUP BY PAYS, MOIS )

5 SELECT * FROM VMPP MODEL DIMENSION BY (PAYS,M)

6 MEASURES (P P1,TO_NUMBER(NULL) P2,TO_NUMBER(NULL) P3,

7 TO_NUMBER(NULL) P4,TO_NUMBER(NULL) P5) RULES (

8 P2[ANY,ANY]=MIN(P1)[CV(),M BETWEEN CV()-1 AND CV()+1],

9 P3[ANY,ANY]=MAX(P1)[CV(),M BETWEEN CV()-1 AND CV()+1],

10 P4[ANY,ANY]=P1[CV(),CV()]-P2[CV(),CV()],

11 P5[ANY,ANY]=P1[CV(),CV()]-P3[CV(),CV()]) ORDER BY PAYS,M;

PAYS M P1 P2 P3 P4 P5

——- — —— —— —— —— ——

Espagne 1 899 784 899 115 0

Espagne 2 784 365 899 419 -115

Espagne 3 365 365 1 124 0 -759

Espagne 4 1 124 365 1 124 759 0

Espagne 5 1 031 850 1 124 181 -93

Espagne 6 850 850 1 031 0 -181

France 1 2 002 2 002 2 062 0 -60

France 2 2 062 2 002 2 274 60 -212

France 3 2 274 2 062 2 274 212 0

France 4 2 142 2 142 2 612 0 -471

France 5 2 612 2 142 2 612 471 0

France 6 2 391 2 391 2 612 0 -221

Irlande 1 71 71 191 0 -120

Irlande 2 191 58 191 133 0

Irlande 3 58 58 191 0 -133

Irlande 4 167 58 262 110 -95

Irlande 5 262 167 316 95 -54

Irlande 6 316 262 316 54 0

Suisse 1 226 158 226 68 0

Suisse 2 158 158 471 0 -313

Suisse 3 471 72 471 399 0

Suisse 4 72 72 471 0 -399

Suisse 5 321 72 347 248 -26

Suisse 6 347 321 347 26 0

Une nouvelle condition « FOR … » est introduite uniquement dans la clause « MODEL » pour permettre de spécifier une liste des cellules à travers une dimension qui peuvent être modifiées ou ajoutées. Attention il n’est pas possible d’utiliser cette condition pour effectuer des calculs d’agrégats. La syntaxe est la suivante :

RULES (métrique[FOR dimension {IN({ expression[,…]|sous-requête})

| FROM expression TO expression INCREMENT expression } ] [,…] )

sous-requête La sous-requête ne peut pas être corrélée avec la requête principale. Vous ne pouvez pas utiliser une sous-requête déclarée dans une clause « WITH ». Elle ne doit pas retourner plus de 10 000 valeurs.

Voici dans l’exemple suivant l’utilisation de la condition « FOR » avec ses deux syntaxes : pour chacune des ces règles on affiche uniquement la valeur de la colonne P1 si la condition est validée ; dans la colonne P2 la condition n’est valable que pour les mois de janvier à mars mais uniquement pour les noms des pays > ‘Irlande’ ; la colonne P3 affiche les enregistrements uniquement tous les deux mois grâce à l’incrément ; la colonne P4 affiche uniquement les enregistrements pour les pays ‘France’ et ‘Suisse’ ; et finalement la colonne P5 affiche uniquement les enregistrements pour le pays du fournisseur du produit numéro 1.

SQL> WITH VMPP AS ( SELECT NOM, PAYS, MOIS M, SUM(PORT) P

2 FROM EMPLOYES JOIN COMMANDES USING (NO_EMPLOYE)

3 WHERE ANNEE = 2011 AND NO_EMPLOYE IN (1,45,47,68)

4 GROUP BY NOM, PAYS, MOIS )

5 SELECT * FROM VMPP

6 MODEL DIMENSION BY (NOM,PAYS,M)

7 MEASURES (P P1,TO_NUMBER(NULL) P2,TO_NUMBER(NULL) P3,

8 TO_NUMBER(NULL) P4,TO_NUMBER(NULL) P5)

9 RULES (P2[ANY,PAYS > ‘Irlande’,FOR M FROM 1 TO 3 INCREMENT 1]=

10 P1[CV(),CV(),CV()],

11 P3[ANY,ANY,FOR M FROM 1 TO 6 INCREMENT 2]=

12 P1[CV(),CV(),CV()],

13 P4[ANY,FOR PAYS IN (‘France’,’Suisse’),ANY]=

14 P1[CV(),CV(),CV()],

15 P5[ANY,FOR PAYS IN (SELECT PAYS FROM FOURNISSEURS

16 NATURAL JOIN PRODUITS WHERE REF_PRODUIT = 1),ANY]=

17 P1[CV(),CV(),CV()]) ORDER BY PAYS,M;

NOM PAYS M P1 P2 P3 P4 P5

——– ———– — —— —— —— —— ——

Gregoire France 1 2 002 —— 2 002 2 002 ——

Gregoire France 2 2 062 —— —— 2 062 ——

Gregoire France 3 2 274 —— 2 274 2 274 ——

Gregoire France 4 2 142 —— —— 2 142 ——

Gregoire France 5 2 612 —— 2 612 2 612 ——

Gregoire France 6 2 391 —— —— 2 391 ——

Dupouey Irlande 1 71 —— 71 —— ——

Dupouey Irlande 2 191 —— —— —— ——

Dupouey Irlande 3 58 —— 58 —— ——

Dupouey Irlande 4 167 —— —— —— ——

Dupouey Irlande 5 262 —— 262 —— ——

Dupouey Irlande 6 316 —— —— —— ——

Besse Royaume-Uni 1 1 152 1 152 1 152 —— 1 152

Besse Royaume-Uni 2 1 342 1 342 —— —— 1 342

Besse Royaume-Uni 3 708 708 708 —— 708

Besse Royaume-Uni 4 1 697 —— —— —— 1 697

Besse Royaume-Uni 5 827 —— 827 —— 827

Besse Royaume-Uni 6 1 251 —— —— —— 1 251

Lefebvre Suisse 1 226 226 226 226 ——

Lefebvre Suisse 2 158 158 —— 158 ——

Lefebvre Suisse 3 471 471 471 471 ——

Lefebvre Suisse 4 72 —— —— 72 ——

Lefebvre Suisse 5 321 —— 321 321 ——

Lefebvre Suisse 6 347 —— —— 347 ——

Les partitions

Vous pouvez également utiliser le partitionnement et alors les calculs suivant les dimensions et les métriques déclarés sont effectués uniquement dans une partition.

SQL> SELECT * FROM (SELECT ANNEE, MOIS, SUM(PORT) P FROM COMMANDES

2 WHERE MOIS < 5 GROUP BY ANNEE, MOIS) SR

3 MODEL PARTITION BY (ANNEE) DIMENSION BY (MOIS) MEASURES (P)

4 RULES(P[5]=P[2]+P[3]) ORDER BY ANNEE, MOIS;

ANNEE MOIS P

—– —– ———–

2010 1 49 332,80

2010 2 46 707,10

2010 3 49 768,40

2010 4 46 904,00

2010 5 96 475,50 <= 46 707,10 + 49 768,40

2011 1 65 722,30

2011 2 66 132,70

2011 3 73 703,40

2011 4 68 543,40

2011 5 139 836,10 <= 66 132,70 + 73 703,40

Attention, une fois que vous avez initialisé le partitionnement, les règles mises en place sont exécutées pour toutes les partitions car la syntaxe d’adressage des métriques ne comporte pas la possibilité de cibler une cellule du tableau dans une autre partition que celle courante.

Dans l’exemple suivant, pour le métrique T on contrôle dans chaque partition que l’enregistrement du ’25/06/2011′ existe. S’il existe, la valeur du champ T est égale au champ P du même jour, sinon elle est égale au champ P du ’30/06/2011′.

SQL> WITH VMPQ AS (SELECT PAYS, DATE_COMMANDE D, SUM(PORT) P

2 FROM CLIENTS JOIN COMMANDES USING (CODE_CLIENT)

3 JOIN DETAILS_COMMANDES USING (NO_COMMANDE)

4 WHERE DATE_COMMANDE >= ’25/06/2011′

5 AND PAYS IN (‘France’,’Brésil’,’Allemagne’)

6 AND REF_PRODUIT = 1 GROUP BY PAYS, DATE_COMMANDE )

7 SELECT * FROM VMPQ MODEL PARTITION BY (PAYS) DIMENSION BY (D)

8 MEASURES (P,TO_NUMBER(NULL) T, TO_NUMBER(NULL) C)

9 RULES ( T[’25/06/2011′]=

10 CASE WHEN P[’25/06/2011′] IS PRESENT

11 THEN P[’25/06/2011′] ELSE P[’30/06/2011′] END,

12 C[’30/06/2011′] = SUM(P)[ANY]) ORDER BY 1,2,3;

PAYS D P T C

———– ———- —— —— ——

Allemagne 25/06/2011 99,7 99,7 —— <= P[’25/06/2011′]

Allemagne 26/06/2011 93,4 —— ——

Allemagne 28/06/2011 165,7 —— ——

Allemagne 30/06/2011 119,5 —— 478,3

Brésil 25/06/2011 —— 149,4 —— <= P[’30/06/2011′]

Brésil 28/06/2011 91,5 —— ——

Brésil 29/06/2011 91,2 —— ——

Brésil 30/06/2011 149,4 —— 332,1

France 25/06/2011 —— 93,0 —— <= P[’30/06/2011′]

France 26/06/2011 76,1 —— ——

France 27/06/2011 57,9 —— ——

France 28/06/2011 178,8 —— ——

France 30/06/2011 93,0 —— 405,8

La requête suivante permet d’afficher la somme des frais de port par année, par mois et par pays qui est le critère de partitionnement. La première règle permet de calculer la somme des frais de port pour l’ensemble du pays et de l’afficher dans la colonne T pour un nouveau mois 4, inséré pour chaque année de la partition. La deuxième règle permet de calculer de nouveau la somme des frais de port pour l’ensemble de la partition et de l’afficher dans la colonne P pour une nouvelle année 2012 et un nouveau mois 4.

SQL> SELECT * FROM ( SELECT PAYS, ANNEE, MOIS, SUM(PORT) P

2 FROM CLIENTS JOIN COMMANDES USING (CODE_CLIENT)

3 JOIN DETAILS_COMMANDES USING (NO_COMMANDE)

4 WHERE PAYS IN (‘France’,’Brésil’) AND REF_PRODUIT = 1

5 AND MOIS BETWEEN 1 AND 3 GROUP BY PAYS, ANNEE, MOIS)

6 MODEL PARTITION BY (PAYS) DIMENSION BY (ANNEE,MOIS)

7 MEASURES (P,TO_NUMBER(NULL) T) RULES UPSERT ALL

8 ( T[ANY ,4] = SUM(P)[ANY,ANY], P[2012,4] = SUM(P)[ANY,ANY])

9 ORDER BY PAYS, ANNEE, MOIS;

PAYS ANNEE MOIS P T

———– —– ———- ——- ——-

Brésil 2010 1 1 740 ——-

Brésil 2010 2 730 ——-

Brésil 2010 3 1 363 ——-

Brésil 2010 4 ——- 9 378 <= T[ANY ,4]

Brésil 2011 1 1 328 ——-

Brésil 2011 2 2 077 ——-

Brésil 2011 3 2 140 ——-

Brésil 2011 4 ——- 9 378 <= T[ANY ,4]

Brésil 2012 4 9 378 ——- <= P[ANY ,4]

France 2010 1 2 145 ——-

France 2010 2 1 385 ——-

France 2010 3 1 459 ——-

France 2010 4 ——- 10 963 <= T[ANY ,4]

France 2011 1 1 709 ——-

France 2011 2 1 823 ——-

France 2011 3 2 443 ——-

France 2011 4 ——- 10 963 <= T[ANY ,4]

France 2012 4 10 963 ——- <= P[ANY ,4]

Dans l’exemple suivant la colonne comporte deux règles. La première permet de calculer la somme des quantités vendues de la catégorie du produit pour l’année en cours affichée pour le trimestre 5. La deuxième est d’insérer un enregistrement pour chaque trimestre dans une nouvelle année 2012 affichant la somme du même trimestre des années 2010 et 2011.

SQL> WITH CATQ AS ( SELECT NOM_CATEGORIE CATEGORIE, ANNEE A,

2 TRIMESTRE T,SUM(DC.QUANTITE) Q

3 FROM CLIENTS CL NATURAL JOIN COMMANDES CO

4 NATURAL JOIN DETAILS_COMMANDES DC JOIN PRODUITS

5 USING(REF_PRODUIT) JOIN CATEGORIES USING(CODE_CATEGORIE)

6 WHERE NO_EMPLOYE IN (45) AND CODE_CATEGORIE < 4

7 GROUP BY NOM_CATEGORIE, ANNEE, TRIMESTRE)

8 SELECT * FROM CATQ MODEL PARTITION BY (CATEGORIE)

9 DIMENSION BY (A, T) MEASURES (Q) RULES UPSERT ALL

10 ( Q[ANY,5] = SUM(Q)[CV(),ANY], Q[2012,ANY] = SUM(Q)[ANY,CV()])

11 ORDER BY CATEGORIE, A, T;

CATEGORIE A T Q

———— —– — ——–

Boissons 2010 1 30 323

Boissons 2010 2 32 897

Boissons 2010 3 31 088

Boissons 2010 4 28 987

Boissons 2010 5 123 295 <= 30 323 + 32 897 + 31 088 + 28 987

Boissons 2011 1 47 250

Boissons 2011 2 51 112

Boissons 2011 5 98 362

Boissons 2012 1 77 573 <= 30 323 + 47 250

Boissons 2012 2 84 009

Boissons 2012 3 31 088

Boissons 2012 4 28 987

Boissons 2012 5 221 657

Condiments 2010 1 37 368

Condiments 2010 2 42 000

Condiments 2010 4 40 009

Condiments 2010 5 159 369

…

Les valeurs NULL

Vous pouvez configurer la gestion de la cohérence des valeurs traitées par la clause à l’aide de la syntaxe suivante : MODEL [{ IGNORE | KEEP } NAV ] …

IGNORE NAV L’option permet de remplacer toutes les valeurs NULL ou manquantes et renvoyer une valeur suivant le type de la colonne : « 0 » pour les données de type numérique, « 01/01/2000 » pour les données de type date, une chaîne vide pour les données de type caractère et NULL pour les autres.

KEEP NAV L’option ne traite pas les valeurs NULL, c’est option par défaut.

SQL> WITH

2 VENTES AS ( SELECT DATE_COMMANDE, SUM(PORT) SP FROM COMMANDES

3 WHERE DATE_COMMANDE BETWEEN ’07/05/2011′ AND ’08/05/2011′

4 AND CODE_CLIENT IN (‘CHOPS’) GROUP BY DATE_COMMANDE),

5 VF AS ( SELECT JOUR, SP FROM VENTES V RIGHT OUTER JOIN

6 ( SELECT JOUR FROM DIM_TEMPS

7 WHERE JOUR BETWEEN ’07/05/2011′ AND ’08/05/2011′) T

8 ON ( V.DATE_COMMANDE = T.JOUR)),

9 M01 AS ( SELECT * FROM VF

10 MODEL IGNORE NAV DIMENSION BY (JOUR) MEASURES (SP)

11 RULES( SP[’09/05/2011′]= SP[’07/05/2011′]+SP[’08/05/2011′])),

12 M02 AS ( SELECT * FROM VF

13 MODEL KEEP NAV RETURN UPDATED ROWS

14 DIMENSION BY (JOUR) MEASURES (SP)

15 RULES( SP[’09/05/2011′]= SP[’07/05/2011′]+SP[’08/05/2011′])),

16 M03 AS ( SELECT * FROM VF

17 MODEL RETURN UPDATED ROWS

18 DIMENSION BY (JOUR) MEASURES (SP)

19 RULES( SP[’09/05/2011′]= NVL(SP[’07/05/2011′],0)+

20 NVL(SP[’08/05/2011′],0)))

21 SELECT ‘MODEL IGNORE NAV’, M01.* FROM M01 UNION ALL

22 SELECT ‘MODEL KEEP NAV ‘, M02.* FROM M02 UNION ALL

23 SELECT ‘MODEL DEFAUT+NVL’, M03.* FROM M03;

‘MODELIGNORENAV’ JOUR SP

—————- ———- ———-

MODEL IGNORE NAV 08/05/2011 54,5

MODEL IGNORE NAV 07/05/2011 ———-

MODEL IGNORE NAV 09/05/2011 54,5

MODEL KEEP NAV 09/05/2011 ———-

MODEL DEFAUT+NVL 09/05/2011 54,5

Dans l’exemple précédent, il y a trois sous-requêtes : la première affiche tous les enregistrements et les deux dernières uniquement les enregistrements modifiés. La première sous-requête utilise l’option « IGNORE NAV », ainsi la somme des deux valeurs numériques n’est pas NULL comme pour la deuxième sous-requête. La dernière traite la valeur NULL avec la fonction « NVL ».

PRESENTV

La fonction renvoie la première expression si la cellule existe avant l’exécution de la clause « MODEL », sinon elle renvoie la deuxième expression. La syntaxe de la fonction est :

PRESENTV ( référence cellule, expression, expression )

PRESENTNNV

La fonction renvoie la première expression si la cellule existe avant l’exécution de la clause « MODEL » et si sa valeur n’est pas NULL, sinon elle renvoie la deuxième expression. La syntaxe de la fonction est :

PRESENTNNV ( référence cellule, expression, expression )

SQL> WITH VENTES AS ( SELECT CL.PAYS, CO.DATE_COMMANDE, SUM(CO.PORT) P

2 FROM CLIENTS CL JOIN COMMANDES CO

3 ON ( CL.CODE_CLIENT = CO.CODE_CLIENT

4 AND CO.DATE_COMMANDE BETWEEN ’05/05/2011′ AND ’10/05/2011′

5 AND CL.CODE_CLIENT IN (‘FOLIG’,’ROMEY’, ‘CHOPS’))

6 GROUP BY CL.PAYS, CO.DATE_COMMANDE),

7 VF AS ( SELECT PAYS, JOUR, P FROM VENTES V

8 PARTITION BY (PAYS) RIGHT OUTER JOIN( SELECT JOUR FROM DIM_TEMPS

9 WHERE JOUR BETWEEN ’05/05/2011′ AND ’10/05/2011′) T

10 ON ( V.DATE_COMMANDE = T.JOUR))

11 SELECT * FROM VF MODEL PARTITION BY (PAYS) DIMENSION BY (JOUR)

12 MEASURES ( P, 0 PV, 0 PNNV, 0 PNVL)

13 RULES ( PV [ANY]=PRESENTV(P[CV()],P[CV()]*1.1,0),

14 PNNV[ANY]=PRESENTNNV(P[CV()],P[CV()]*1.1,0),

15 PNVL[ANY]=NVL(PRESENTV(P[CV()],P[CV()]*1.1,0),0))ORDER BY 1,2;

PAYS JOUR P PV PNNV PNVL

——- ———- ——- ——- ——- ——-

Espagne 05/05/2011 62,30 68,53 68,53 68,53

Espagne 06/05/2011 69,90 76,89 76,89 76,89

Espagne 07/05/2011 59,00 64,90 64,90 64,90

Espagne 08/05/2011 ——- ——- ,00 ,00

Espagne 09/05/2011 ——- ——- ,00 ,00

Espagne 10/05/2011 ——- ——- ,00 ,00

France 05/05/2011 60,10 66,11 66,11 66,11

France 06/05/2011 66,60 73,26 73,26 73,26

…

L’exécution des règles

Les règles peuvent être exécutées séquentiellement, l’option par défaut, ou dans l’ordre de leurs dépendances suivant les calculs effectués. La syntaxe qui permet de préciser le mode d’interprétation est la suivante :

RULES [{ AUTOMATIC | SEQUENTIAL } ORDER ] …

Dans le cas de l’exécution des calculs séquentiellement, vous devez, pour la cohérence, tenir compte de l’antériorité des calculs. Dans l’exemple suivant, pour la colonne P1 trois règles de calcul sont décrites pour trois nouveaux enregistrements pour chaque année.

SQL> SELECT * FROM ( SELECT ANNEE, MOIS, SUM(PORT) P1

2 FROM COMMANDES WHERE NO_EMPLOYE = 12 GROUP BY ANNEE, MOIS)

3 MODEL DIMENSION BY (ANNEE,MOIS)

4 MEASURES(P1) RULES UPSERT ALL SEQUENTIAL ORDER (

5 P1[ANY,13] =SUM(P1)[CV(),ANY],

La première calcule la somme des frais de port pour la même année et l’affiche dans un nouveau mois 13.

6 P1[ANY,0] =SUM(P1)[CV(),MOIS BETWEEN 1 AND 6],

La deuxième calcule la somme des frais de port uniquement pour les premiers six mois de la même année et l’affiche dans un nouveau mois 0.

7 P1[2011,13]=P1[CV()-1,CV()])ORDER BY ANNEE, MOIS;

La troisième règle affiche dans un nouvel enregistrement pour l’année 2011 et le mois 13 la valeur du même mois de l’année précédente.

ANNEE MOIS P1

—– —- ———

2010 0 4 146,90

2010 1 661,00

2010 2 424,40

2010 3 467,60

2010 4 677,50

2010 5 742,80

2010 6 1 173,60

2010 7 676,30

2010 8 416,70

2010 9 811,30

2010 10 543,70

2010 11 1 410,60

2010 12 877,90

2010 13 8 883,40

2011 0 4 794,70

2011 1 956,70

2011 2 744,40

2011 3 662,10

2011 4 877,40

2011 5 912,60

2011 6 641,50

2011 13 8 883,40

Dans cet exemple, l’ordre séquentiel est salutaire car il nous permet de calculer le mois 0 la somme des frais de port pour les premiers six mois, mais cette somme n’est pas prise en compte pour la somme des frais de port de l’année.

Dans le cas ou vous utilisez « AUTOMATIC ORDER », l’interprétation est effectuée tenant compte des dépendances des cellules, ainsi il calcule d’abord toutes les cellules indépendantes et ensuite tous les calculs qui utilisent ces cellules. Attention, dans ce cas une cellule ne peut être mise à jour qu’une seule fois.

SQL> SELECT * FROM ( SELECT ANNEE, MOIS, SUM(PORT) P1

2 FROM COMMANDES GROUP BY ANNEE, MOIS)

3 MODEL DIMENSION BY (ANNEE,MOIS) MEASURES(P1)

4 RULES UPSERT ALL AUTOMATIC ORDER (

5 P1[ANY,13] =SUM(P1)[CV(),ANY],P1[2011,13]=P1[CV()-1,CV()]);

P1[ANY,13] =SUM(P1)[CV(),ANY],P1[2011,13]=P1[CV()-1,CV()])

ERREUR à la ligne 5 :

ORA-32630: plusieurs affectations en ordre automatique MODEL

Dans l’exemple suivant, le même ensemble de règles est exécuté deux fois avec les deux options « AUTOMATIC » et « SEQUENTIAL » pour voir la différence de calcul des ces deux options.

SQL> WITH

2 CATQ AS(SELECT NOM_CATEGORIE C, TRIMESTRE T, SUM(DC.QUANTITE) Q

3 FROM CLIENTS CL NATURAL JOIN COMMANDES CO

4 NATURAL JOIN DETAILS_COMMANDES DC JOIN PRODUITS

5 USING(REF_PRODUIT) JOIN CATEGORIES USING(CODE_CATEGORIE)

6 WHERE NO_EMPLOYE = 1 AND ANNEE = 2010

7 AND CODE_CATEGORIE IN (6,8,10) GROUP BY NOM_CATEGORIE, TRIMESTRE)

8 SELECT ‘A’,C CATEGORIE,T,Q0,Q FROM CATQ MODEL

9 DIMENSION BY (C, T) MEASURES (Q Q0,Q)

10 RULES UPSERT ALL AUTOMATIC ORDER (

11 Q[‘x–Toutes Viandes–x’,ANY] =SUM(Q)[C LIKE ‘Viande%’,CV()], <- 6

12 Q[‘x–Toutes–x’,ANY] =AVG(Q)[ANY,CV()], <- 7

13 Q[‘Viande en conserve’,4] =Q[CV(),1]+Q[CV(),3], <- 3

14 Q[‘Viandes’,4] =Q[CV(),2]+Q[CV(),3], <- 5

15 Q[‘Viande en conserve’,1] =AVG(Q)[CV(),T IN (2,3)], <- 2

16 Q[‘Viandes’,2] =AVG(Q)[CV(),T IN (1,3)], <- 4

17 Q[‘Poissons et fruits de mer’,3]=Q[CV(),1]+Q[CV(),2]) <- 1

18 UNION ALL

19 SELECT ‘S’,C CATEGORIE,T,Q0,Q FROM CATQ MODEL

20 DIMENSION BY (C, T) MEASURES (Q Q0,Q)

21 RULES UPSERT ALL SEQUENTIAL ORDER (

22 Q[‘x–Toutes Viandes–x’,ANY] =SUM(Q)[C LIKE ‘Viande%’,CV()],

23 Q[‘x–Toutes–x’,ANY] =AVG(Q)[ANY,CV()],

24 Q[‘Viande en conserve’,4] =Q[CV(),1]+Q[CV(),3],

25 Q[‘Viandes’,4] =Q[CV(),2]+Q[CV(),3],

26 Q[‘Viande en conserve’,1] =AVG(Q)[CV(),T IN (2,3)],

27 Q[‘Viandes’,2] =AVG(Q)[CV(),T IN (1,3)],

28 Q[‘Poissons et fruits de mer’,3]=Q[CV(),1]+Q[CV(),2])

29 ORDER BY 1 DESC,2,3;

‘ CATEGORIE T Q0 Q

– ————————- — ——– ——–

S Poissons et fruits de mer 1 17 384 17 384

S Poissons et fruits de mer 2 8 421 8 421

S Poissons et fruits de mer 3 11 641 25 805 <= 17 384 + 8 421

S Poissons et fruits de mer 4 15 161 15 161

S Viande en conserve 1 4 237 2 560

S Viande en conserve 2 2 590 2 590

S Viande en conserve 3 2 530 2 530

S Viande en conserve 4 3 325 6 767 <= 4 237 + 2 530

S Viandes 1 8 673 8 673

S Viandes 2 5 616 7 478

S Viandes 3 6 283 6 283

S Viandes 4 6 253 11 899 <= 5 616 + 6 283

S x–Toutes Viandes–x 1 ——– 12 910

S x–Toutes Viandes–x 2 ——– 8 206

S x–Toutes Viandes–x 3 ——– 8 813

S x–Toutes Viandes–x 4 ——– 9 578 <= 6 253 + 3 325

S x–Toutes–x 1 ——– 10 801

S x–Toutes–x 2 ——– 6 208

S x–Toutes–x 3 ——– 7 317

S x–Toutes–x 4 ——– 8 579

A Poissons et fruits de mer 1 17 384 17 384

A Poissons et fruits de mer 2 8 421 8 421

A Poissons et fruits de mer 3 11 641 25 805

A Poissons et fruits de mer 4 15 161 15 161

A Viande en conserve 1 4 237 2 560

A Viande en conserve 2 2 590 2 590

A Viande en conserve 3 2 530 2 530

A Viande en conserve 4 3 325 5 090 <= 2 560 + 2 530

A Viandes 1 8 673 8 673

A Viandes 2 5 616 7 478

A Viandes 3 6 283 6 283

A Viandes 4 6 253 13 761 <= 7 478 + 6 283

A x–Toutes Viandes–x 1 ——– 11 233

A x–Toutes Viandes–x 2 ——– 10 068

A x–Toutes Viandes–x 3 ——– 8 813

A x–Toutes Viandes–x 4 ——– 18 851 <= 13 761 + 5 090

A x–Toutes–x 1 ——– 9 963

A x–Toutes–x 2 ——– 7 139

A x–Toutes–x 3 ——– 10 858

A x–Toutes–x 4 ——– 13 216

Une autre erreur que vous pouvez avoir avec l’option « AUTOMATIC ORDER », est de calculer plusieurs cellules avec une formule récursive qui fait appel aux mêmes cellules.

SQL> SELECT * FROM ( SELECT ANNEE, MOIS, SUM(PORT) P1

2 FROM COMMANDES GROUP BY ANNEE, MOIS)

3 MODEL DIMENSION BY (ANNEE,MOIS) MEASURES(P1)

4 RULES UPSERT ALL AUTOMATIC ORDER(P1[ANY,12]=SUM(P1)[CV(),ANY]);

FROM COMMANDES GROUP BY ANNEE, MOIS)

ERREUR à la ligne 2 :

ORA-32634: l’évaluation MODEL avec ordre automatique ne converge pas

L’itération des règles

Les règles peuvent être exécutées une seule fois ou plusieurs fois à l’aide de l’opération « ITERATE ». La mise en œuvre de cette opération est possible à l’aide de la syntaxe :

RULES ITERATE ( valeur numérique ) [ UNTIL condition de sortie ]

Dans la clause « MODEL » vous bénéficiez de la possibilité de connaître le niveau de l’itération à l’aide de la pseudo-colonne « ITERATION_NUMBER » qui renvoie 0 à la première itération puis, pour les itérations suivantes, un entier qui représente le nombre d’itérations.

L’exemple suivant permet d’ajouter les prévisions des sommes de frais de port en augmentant de 10% chaque année la valeur de l’année précédente. La colonne I représente le niveau de l’itération retournée par la pseudo-colonne « ITERATION_NUMBER ».

SQL> WITH

2 VENTES AS (

3 SELECT PAYS, ANNEE, SUM(PORT) P

4 FROM CLIENTS CL NATURAL JOIN COMMANDES

5 WHERE PAYS IN (‘Espagne’,’France’)

6 AND ANNEE = 2011 GROUP BY PAYS, ANNEE)

7 SELECT * FROM VENTES

8 MODEL PARTITION BY (PAYS) DIMENSION BY (ANNEE)

9 MEASURES (P,TO_NUMBER(NULL) I)

10 RULES SEQUENTIAL ORDER ITERATE (3)

11 ( P[2011+ITERATION_NUMBER+1]=P[2011+ITERATION_NUMBER]*1.1,

12 I[2011+ITERATION_NUMBER+1]=ITERATION_NUMBER)

13 ORDER BY 1,2;

PAYS ANNEE P I

————— ———- ———– ———-

Espagne 2011 23 539,00 ———-

Espagne 2012 25 892,90 0

Espagne 2013 28 482,19 1

Espagne 2014 31 330,41 2

France 2011 52 583,90 ———-

France 2012 57 842,29 0

France 2013 63 626,52 1

France 2014 69 989,17 2

Une nouvelle fonction « PREVIOUS » permet d’écrire des conditions plus cohérentes dans l’option « UNTIL ». L’utilisation de cette fonction est admise uniquement à cet emplacement avec la syntaxe suivante :

UNTIL ( PREVIOUS ( référence_cellule ) … )

SQL> WITH

2 VENTES AS (

3 SELECT PAYS, ANNEE, SUM(PORT) P

4 FROM CLIENTS CL NATURAL JOIN COMMANDES

5 WHERE PAYS IN (‘Espagne’,’France’)

6 AND ANNEE = 2011 GROUP BY PAYS, ANNEE)

7 SELECT * FROM VENTES

8 MODEL PARTITION BY (PAYS) DIMENSION BY (ANNEE)

9 MEASURES (P,TO_NUMBER(NULL) I)

10 RULES ITERATE (1000)

11 UNTIL( PREVIOUS(P[2011+ITERATION_NUMBER+1])-P[2011]> 20000

12 OR 2011+ITERATION_NUMBER+1 >= 2017)

13 ( P[2011+ITERATION_NUMBER+1]=P[2011+ITERATION_NUMBER]*1.1,

14 I[2011+ITERATION_NUMBER+1]=ITERATION_NUMBER)

15 ORDER BY 1,2;

PAYS ANNEE P I

——- —– ———– —-

Espagne 2011 23 539,00 —-

Espagne 2012 25 892,90 0

Espagne 2013 28 482,19 1

Espagne 2014 31 330,41 2

Espagne 2015 34 463,45 3

Espagne 2016 37 909,79 4

Espagne 2017 41 700,77 5 <= 41 700,77 – 23 539,00 < 20 000

France 2011 52 583,90 —- ANNEE = 2017

France 2012 57 842,29 0

France 2013 63 626,52 1

France 2014 69 989,17 2 <= 69 989,17 – 52 583,90 < 20 000

France 2015 76 988,09 3 <= 76 988,09 – 52 583,90 > 20 000

Dans l’exemple suivant, l’itération est exécutée tant que la limite maximum n’est pas atteinte où le prédicat déclaré dans l’option « UNTIL » est VRAI. Le prédicat contrôle que la valeur de la différence de l’année précédente et de la première occurrence est inférieure à 20 000 ou l’année en cours est au maximum 2017.

Vous pouvez utiliser les itérations pour effectuer le calcul et l’afficher dans la même cellule.

SQL> SELECT * FROM (SELECT 1 DIM,1000 M FROM DUAL)

2 MODEL DIMENSION BY (DIM) MEASURES ( M, M1, 0 I)

3 RULES SEQUENTIAL ORDER ITERATE (1000)

4 UNTIL ( PREVIOUS(M[1]) – M[1] < 100)

5 (M[1] = M[1]/4,

6 I[1+ITERATION_NUMBER+1]=ITERATION_NUMBER+1,

7 M1[1+ITERATION_NUMBER+1]=M1[1+ITERATION_NUMBER]/4);

DIM M M1 I

— ——— ——— —–

1 15,63 1 000,00 0

2 ——— 250,00 1

3 ——— 62,50 2

4 ——— 15,63 3

L’assemblage des modèles

Il est possible de combiner plusieurs modèles références dans un modèle principal. Dans la syntaxe, on commence par déclarer les modèles références et ensuite le modèle principal. Il est obligatoire que chaque modèle référence soit utilisé au moins dans une des règles. Attention, au niveau d’utilisation de la fonction « CV » pour qu’elle trouvé la valeur de la dimension elle à impérativement besoin du nom de la dimension. La syntaxe de déclarations des modèles références est :

SELECT … MODEL

[ REFERENCE nom_du_modèle on (sous-requête) [,…]]

[ MAIN nom_du_modèle ]…

STAG01@topaze> WITH

2 VENTES AS (

3 SELECT PAYS PP,ANNEE AP,TRIMESTRE TP,SUM(PRIX_UNITAIRE*QUANTITE) CA,

4 SUM(QUANTITE) Q

5 FROM CLIENTS NATURAL JOIN COMMANDES

6 NATURAL JOIN DETAILS_COMMANDES

7 WHERE ANNEE = 2010 AND PAYS IN (‘Italie’,’France’,’Suisse’)

8 GROUP BY PAYS,ANNEE,TRIMESTRE),

9 FRAIS AS (

10 SELECT PAYS PR,ANNEE AR,TRIMESTRE TR,SUM(PORT) SP

11 FROM CLIENTS NATURAL JOIN COMMANDES

12 WHERE ANNEE = 2010 AND PAYS IN (‘Italie’,’France’,’Suisse’)

13 GROUP BY PAYS,ANNEE,TRIMESTRE)

14 SELECT * FROM VENTES

15 MODEL

16 REFERENCE frais

17 ON (SELECT * FROM FRAIS)

18 DIMENSION BY (PR,AR,TR) MEASURES (SP) IGNORE NAV

19 MAIN ventes

20 DIMENSION BY (PP,AP,TP) MEASURES (CA,Q,0 SP1,CA NCA)

21 RULES(

22 SP1[ANY,ANY,ANY] =

23 frais.SP[CV(PP),CV(AP),CV(TP)],

24 NCA[ANY,ANY,ANY]=CA[CV(),CV(),CV()] –

25 frais.SP[CV(PP),CV(AP),CV(TP)])

26 ORDER BY 1,2,3;

PP AP TP CA Q SP1 NCA

——– —– — ————– ——– ————- ————–

France 2010 1 60 767 879,64 928 929 17 814,70 60 750 064,94

France 2010 2 61 776 341,04 940 449 18 714,90 61 757 626,14

France 2010 3 61 218 497,16 933 988 18 285,00 61 200 212,16

France 2010 4 60 711 312,36 927 487 17 989,60 60 693 322,76

Italie 2010 1 14 415 191,28 220 434 4 316,90 14 410 874,38

Italie 2010 2 13 984 922,88 211 007 4 196,20 13 980 726,68

Italie 2010 3 14 995 751,40 226 536 4 287,70 14 991 463,70

Italie 2010 4 15 223 098,36 233 658 4 697,10 15 218 401,26

Suisse 2010 1 9 525 672,48 145 357 2 824,60 9 522 847,88

Suisse 2010 2 12 891 916,68 196 279 3 659,80 12 888 256,88

Suisse 2010 3 13 508 505,72 205 786 4 106,10 13 504 399,62

Suisse 2010 4 9 081 987,72 139 178 2 672,30 9 079 315,42

Références: http://db-experts.com

Categories: Base de données

Hassan BADIR

SQL Developpement: La clause MODEL

SQL Developpement: La clause MODEL

La clause MODEL

La modification des cellules

La fonction CV

Les créations multiples

Les conditions

Les partitions

Les valeurs NULL

PRESENTV

PRESENTNNV

L’exécution des règles

L’itération des règles

L’assemblage des modèles

Laisser un commentaire Annuler la réponse