Exemple général d'analyse de données sous Oracle
Modèle dimensionnel
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);
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);
ROLLUP
Exemple :
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);
__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 :
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);
__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 :
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;
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 :
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;
__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 :
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;
__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).