Exemple général d'analyse de données sous Oracle

Modèle dimensionnel

Modèle dimensionnel
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
CREATE TABLE t_produit (
2
 pk_num number,
3
 a_designation varchar(50),
4
 a_type char(3)
5
);
6
CREATE UNIQUE INDEX idx_produit_num
7
 ON t_produit (pk_num);
8
ALTER TABLE t_produit
9
 ADD CONSTRAINT cstr_produit_num PRIMARY KEY (pk_num)
10
 ADD CONSTRAINT cstr_produit_type CHECK (a_type in ('CD', 'DVD'));
11
CREATE TABLE t_client (
12
 pk_num number,
13
 a_anneenaiss number,
14
 a_sexe char(1)
15
);
16
CREATE UNIQUE INDEX idx_client_num
17
 ON t_client (pk_num);
18
ALTER TABLE t_client
19
 ADD CONSTRAINT cstr_client_num PRIMARY KEY (pk_num)
20
 ADD CONSTRAINT cstr_client_sexe CHECK (a_sexe in ('M', 'F'));
21
CREATE TABLE t_ventes (
22
 pk_num number,
23
 a_prix number,
24
 a_qte number,
25
 fk_produit number,
26
 fk_client number
27
);
28
CREATE UNIQUE INDEX idx_ventes_num
29
 ON t_ventes (pk_num);
30
ALTER TABLE t_ventes
31
 ADD CONSTRAINT cstr_ventes_num PRIMARY KEY (pk_num)
32
 ADD CONSTRAINT cstr_ventes_produit FOREIGN KEY (fk_produit) REFERENCES t_produit(pk_num)
33
 ADD CONSTRAINT cstr_ventes_client FOREIGN KEY (fk_client) REFERENCES t_client(pk_num);
CREATE TABLE t_produit (
 pk_num number,
 a_designation varchar(50),
 a_type char(3)
);
CREATE UNIQUE INDEX idx_produit_num 
 ON t_produit (pk_num);
ALTER TABLE t_produit 
 ADD CONSTRAINT cstr_produit_num PRIMARY KEY (pk_num)  
 ADD CONSTRAINT cstr_produit_type CHECK (a_type in ('CD', 'DVD'));
CREATE TABLE t_client (
 pk_num number,
 a_anneenaiss number,
 a_sexe char(1)
);
CREATE UNIQUE INDEX idx_client_num 
 ON t_client (pk_num);
ALTER TABLE t_client 
 ADD CONSTRAINT cstr_client_num PRIMARY KEY (pk_num)
 ADD CONSTRAINT cstr_client_sexe CHECK (a_sexe in ('M', 'F'));
CREATE TABLE t_ventes (
 pk_num number,
 a_prix number,
 a_qte number,
 fk_produit number,
 fk_client number
);  
CREATE UNIQUE INDEX idx_ventes_num 
 ON t_ventes (pk_num);
ALTER TABLE t_ventes
 ADD CONSTRAINT cstr_ventes_num PRIMARY KEY (pk_num)
 ADD CONSTRAINT cstr_ventes_produit FOREIGN KEY (fk_produit) REFERENCES t_produit(pk_num)
 ADD CONSTRAINT cstr_ventes_client FOREIGN KEY (fk_client) REFERENCES t_client(pk_num);
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
INSERT INTO t_produit VALUES (1, 'Pink Martini', 'CD');
2
INSERT INTO t_produit VALUES (2, 'Souad Massi', 'CD');
3
INSERT INTO t_produit VALUES (3, 'Souad Massi', 'DVD');
4
INSERT INTO t_produit VALUES (4, 'Raul Paz', 'CD');
5
INSERT INTO t_produit VALUES (5, 'Star wars', 'DVD');
6
INSERT INTO t_produit VALUES (6, 'Star wars BO', 'CD');
7
8
INSERT INTO t_client VALUES (1, 1980, 'M');
9
INSERT INTO t_client VALUES (2, 1980, 'M');
10
INSERT INTO t_client VALUES (3, 1970, 'F');
11
INSERT INTO t_client VALUES (4, 1970, 'M');
12
INSERT INTO t_client VALUES (5, 1985, 'F');
13
14
INSERT INTO t_ventes VALUES (1, 15, 1, 1, 1);
15
INSERT INTO t_ventes VALUES (2, 20, 3, 1, 2);
16
INSERT INTO t_ventes VALUES (3, 30, 1, 1, 3);
17
INSERT INTO t_ventes VALUES (4, 10, 2, 2, 1);
18
INSERT INTO t_ventes VALUES (5, 2, 5, 2, 1);
19
INSERT INTO t_ventes VALUES (6, 10, 1, 3, 1);
20
INSERT INTO t_ventes VALUES (7, 20, 1, 4, 2);
21
INSERT INTO t_ventes VALUES (8, 30, 1, 4, 3);
22
INSERT INTO t_ventes VALUES (9, 10, 1, 5, 3);
23
INSERT INTO t_ventes VALUES (10, 40, 4, 6, 4);
24
INSERT INTO t_ventes VALUES (11, 30, 1, 6, 5);
25
INSERT INTO t_ventes VALUES (12, 10, 100, 6, 5);
INSERT INTO t_produit VALUES (1, 'Pink Martini', 'CD');
INSERT INTO t_produit VALUES (2, 'Souad Massi', 'CD');
INSERT INTO t_produit VALUES (3, 'Souad Massi', 'DVD');
INSERT INTO t_produit VALUES (4, 'Raul Paz', 'CD');
INSERT INTO t_produit VALUES (5, 'Star wars', 'DVD');
INSERT INTO t_produit VALUES (6, 'Star wars BO', 'CD');

INSERT INTO t_client VALUES (1, 1980, 'M');
INSERT INTO t_client VALUES (2, 1980, 'M');
INSERT INTO t_client VALUES (3, 1970, 'F');
INSERT INTO t_client VALUES (4, 1970, 'M');
INSERT INTO t_client VALUES (5, 1985, 'F');

INSERT INTO t_ventes VALUES (1, 15, 1, 1, 1);
INSERT INTO t_ventes VALUES (2, 20, 3, 1, 2);
INSERT INTO t_ventes VALUES (3, 30, 1, 1, 3);
INSERT INTO t_ventes VALUES (4, 10, 2, 2, 1);
INSERT INTO t_ventes VALUES (5, 2, 5, 2, 1);
INSERT INTO t_ventes VALUES (6, 10, 1, 3, 1);
INSERT INTO t_ventes VALUES (7, 20, 1, 4, 2);
INSERT INTO t_ventes VALUES (8, 30, 1, 4, 3);
INSERT INTO t_ventes VALUES (9, 10, 1, 5, 3);
INSERT INTO t_ventes VALUES (10, 40, 4, 6, 4);
INSERT INTO t_ventes VALUES (11, 30, 1, 6, 5);
INSERT INTO t_ventes VALUES (12, 10, 100, 6, 5);
T_PRODUIT
T_CLIENT
T_VENTES
Jointure des 3 tables et CA
ROLLUP
Exemple
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
SELECT SUM(v.a_prix * v.a_qte) as CA, p.pk_num as P, p.a_type as T
2
FROM t_ventes v, t_produit p
3
WHERE v.fk_produit=p.pk_num
4
GROUP BY ROLLUP (p.a_type, p.pk_num);
SELECT SUM(v.a_prix * v.a_qte) as CA, p.pk_num as P, p.a_type as T
FROM t_ventes v, t_produit p
WHERE v.fk_produit=p.pk_num
GROUP BY ROLLUP (p.a_type, p.pk_num);
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
        __CA          P __T
2
       _105          1 _CD
3
        __30          2 _CD
4
        __50          4 _CD
5
      1190          6 _CD
6
      1375 _           _CD
7
       __10          3 DVD
8
        __10          5 DVD
9
        __20     _       DVD
10
      1395 _  ___
        __CA          P __T
       _105          1 _CD
        __30          2 _CD
        __50          4 _CD
      1190          6 _CD
      1375 _           _CD
       __10          3 DVD
        __10          5 DVD
        __20     _       DVD
      1395 _  ___
La requête permet de calculer les chiffres d'affaire (quantité multipliée par le prix de vente) selon la dimension "produit", c'est à dire pour chaque produit, mais aussi pour chaque type de produit (granularité plus grossière dans la hiérarchie de la dimension produit).
Comme pour un GROUP BY classique, la première ligne nous donne le chiffre d'affaire du produit 1, la seconde celui du produit 2, etc. Mais la cinquième ligne (une fois tous les produits de type CD traités) propose une consolidation et donne le chiffre d'affaire pour tous les produits de type CD. De même la huitième ligne donne le chiffre d'affaire pour tous les produits de type DVD et enfin la dernière ligne donne le chiffre d'affaire global.
CUBE
Exemple
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
SELECT SUM(v.a_prix * v.a_qte) as CA, p.a_type as T, c.a_sexe as S
2
FROM t_ventes v, t_produit p, t_client c
3
WHERE v.fk_produit=p.pk_num AND v.fk_client=c.pk_num
4
GROUP BY CUBE (p.a_type, c.a_sexe);
SELECT SUM(v.a_prix * v.a_qte) as CA, p.a_type as T, c.a_sexe as S
FROM t_ventes v, t_produit p, t_client c
WHERE v.fk_produit=p.pk_num AND v.fk_client=c.pk_num
GROUP BY CUBE (p.a_type, c.a_sexe);
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
__CA __T   S
2
      1395 ___ _
3
      1100   ___  F
4
       _295   ___  M
5
      1375 _CD _
6
      1090 _CD  F
7
       _285 _CD  M
8
        __20 DVD _
9
        __10 DVD F
10
        __10 DVD M
__CA __T   S
      1395 ___ _
      1100   ___  F
       _295   ___  M
      1375 _CD _
      1090 _CD  F
       _285 _CD  M
        __20 DVD _
        __10 DVD F
        __10 DVD M
La requête permet de calculer le "cube" des chiffres d'affaire selon les types de produit et le sexe des clients. 
La première ligne renvoie le chiffre d'affaire global, la seconde celui pour les sexe=F (donc les femmes) seulement, la troisième pour les sexe=M seulement, la quatrième pour les type=CD, la cinquième pour les type=CD et sexe=F (CD achetés par des femmes), etc.
RANK
Exemple
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
SELECT * FROM (
2
SELECT 
3
 RANK() OVER (
4
  PARTITION  BY p.a_type 
5
  ORDER by sum(v.a_qte) DESC
6
 ) as R,
7
 SUM(v.a_qte) as V, 
8
 p.a_type as T,
9
 p.a_designation as P
10
FROM t_ventes v, t_produit p
11
WHERE v.fk_produit=p.pk_num
12
GROUP BY p.a_type, p.a_designation
13
)
14
WHERE R<=3;
SELECT * FROM (
SELECT 
 RANK() OVER (
  PARTITION  BY p.a_type 
  ORDER by sum(v.a_qte) DESC
 ) as R,
 SUM(v.a_qte) as V, 
 p.a_type as T,
 p.a_designation as P
FROM t_ventes v, t_produit p
WHERE v.fk_produit=p.pk_num
GROUP BY p.a_type, p.a_designation
)
WHERE R<=3;
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
      R          __V __T   P
2
          1        105 _CD  Star wars BO
3
          2          __7 _CD  Souad Massi
4
          3          __5 _CD  Pink Martini
5
          1          __1 DVD Star wars
6
          1          __1 DVD Souad Massi
      R          __V __T   P
          1        105 _CD  Star wars BO
          2          __7 _CD  Souad Massi
          3          __5 _CD  Pink Martini
          1          __1 DVD Star wars
          1          __1 DVD Souad Massi
Cette requête renvoie le "top 3" des produits vendus (en quantité) pour les CD et pour les DVD. On notera que pour les DVD, étant donné qu'il n'y a eu que  deux ventes et chacune d'une unité, ces deux ventes apparaissent premières ex-aequo et il n'y a pas de troisième vente.
RANK(N)
Exemple
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
SELECT
2
 p.a_type as P,
3
 RANK(3) WITHIN GROUP
4
  (ORDER BY v.a_qte DESC) as HypotheticalRank
5
FROM t_ventes v, t_produit p
6
WHERE v.fk_produit=p.pk_num
7
GROUP BY p.a_type;
SELECT
 p.a_type as P,
 RANK(3) WITHIN GROUP
  (ORDER BY v.a_qte DESC) as HypotheticalRank
FROM t_ventes v, t_produit p
WHERE v.fk_produit=p.pk_num
GROUP BY p.a_type;
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
__P   HYPOTHETICALRANK
2
_CD                 4
3
DVD                1
__P   HYPOTHETICALRANK
_CD                 4
DVD                1
Cette requête permet de se demander combien serait classée une vente de trois unités dans le domaine des CD ainsi que dans celui des DVD. On voit qu'une telle vente serait la quatrième meilleure vente pour des CD et la première pour des DVD.
SUM(SUM(...))
Exemple
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
SELECT
2
 sum(v.a_prix * v.a_qte) as CA,
3
 c.a_anneenaiss as D,
4
 sum(sum(v.a_prix * v.a_qte)) 
5
  OVER 
6
  (ORDER BY c.a_anneenaiss ROWS UNBOUNDED PRECEDING) 
7
  as CA_cumul
8
FROM t_ventes v, t_client c
9
WHERE v.fk_client=c.pk_num
10
GROUP BY c.a_anneenaiss;
SELECT
 sum(v.a_prix * v.a_qte) as CA,
 c.a_anneenaiss as D,
 sum(sum(v.a_prix * v.a_qte)) 
  OVER 
  (ORDER BY c.a_anneenaiss ROWS UNBOUNDED PRECEDING) 
  as CA_cumul
FROM t_ventes v, t_client c
WHERE v.fk_client=c.pk_num
GROUP BY c.a_anneenaiss;
Ligne | num. | copie | 
CTRL+C pour copier, CTRL+V pour coller
1
        __CA          ___D   CA_CUMUL
2
               _230       1970        _230
3
               _135       1980        _365
4
              1030       1985       1395
        __CA          ___D   CA_CUMUL
               _230       1970        _230
               _135       1980        _365
              1030       1985       1395
Cette requête permet de faire le calcul cumulé des chiffres d'affaire sur les années de naissance des clients. Le résultat montre ainsi que les clients nés en 1970 ont permis un chiffre d'affaire de 230, ceux de 1980 et moins de 365 et ceux de 1985 et moins de 1395 (le total).