Exemple général d'analyse de données sous Oracle
Modèle dimensionnel
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
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 _ ___
CUBE
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
RANK
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
RANK(N)
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
SUM(SUM(...))
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
Navigation :