Cas Fantastic : Data mart pour l'analyse de ticket de caisse

[2h]

Afin d'analyser la structure des tickets de caisse, l'on va créer un data mart dédié.

Question

Réaliser un data mart pour l'analyse de structure de tickets de caisse. On se contentera d'une analyse en quantité (sans intégrer le chiffre d'affaire donc).

Solution

Exemple
1
CREATE TABLE f_dm1_ventes (
2
num char(10),
3
mag char(4),
4
dat date,
5
qte number(2)
6
);
1
ALTER TABLE f_dm1_ventes
2
ADD CONSTRAINT f_dm1_ventes_fkdat
3
FOREIGN KEY (dat) REFERENCES f_dw_date(dat);
4
ALTER TABLE f_dm1_ventes
5
ADD CONSTRAINT f_dm1_ventes_fkmag
6
FOREIGN KEY (mag) REFERENCES f_dw_mag(mag);
1
-- TODO constraints desactivation
2
INSERT INTO f_dm1_ventes (num, mag, dat, qte)
3
SELECT num, mag, dat, count(*) as q
4
FROM f_dw_ventes
5
GROUP BY num, mag, dat, num;
6
-- TODO constraints reactivation

Question

Exploiter le data mart pour analyser globalement les comportements d'achats multiples en fonction des rayonnages, des dates...

Solution

Exemple
1
SELECT m.ray, avg(qte)
2
FROM f_dm1_ventes v JOIN f_dw_mag m
3
ON v.mag=m.mag
4
GROUP BY m.ray;

  • Y : 1.69

  • A : 1.86

  • E : 1.69

1
SELECT m.bs, avg(qte)
2
FROM f_dm1_ventes v JOIN f_dw_mag m
3
ON v.mag=m.mag
4
GROUP BY m.bs;

  • 1 : 1.83

  • 0 : 1.38

Question

Enrichissez le data mart avec les indicateurs adéquats pour analyser si le rayonnage influe sur la structure du ticket de caisse.

Indice

Typiquement on va chercher à étudier si :

  • les rayonnages de type A ou E induisent des tickets de caisse avec plusieurs livres du même auteur ou du même éditeur ;

  • les rayons BS induisent des tickets avec plusieurs livres de type BS

Solution

Exemple
1
ALTER TABLE f_dm1_ventes
2
ADD ind_aut1 NUMBER(2);
3
ALTER TABLE f_dm1_ventes
4
ADD ind_aut2 NUMBER(1);
1
DELETE FROM f_dm1_ventes;
1
INSERT INTO f_dm1_ventes (num, mag, dat, qte, ind_aut1, ind_aut2)
2
SELECT r1.num, r1.mag, r1.dat, r1.q, round(max(r2.a),2), case when max(r2.a)>1 then 1 else 0 end
3
FROM
4
(SELECT num, mag, dat, count(*) as q
5
FROM f_dw_ventes
6
GROUP BY num, mag, dat, num
7
) r1,
8
(SELECT num, p.auteur, count(*) as a
9
FROM f_dw_ventes v JOIN f_dw_produit p
10
ON v.pro=p.isbn
11
GROUP BY num, p.auteur
12
) r2
13
WHERE r1.num = r2.num
14
GROUP BY r1.num, r1.mag, r1.dat, r1.q;

On obtient ici :

  • un indice entier ind_aut1 qui contient le nombre maximum de livres du même auteur dans un ticket (il vaut 1 si tous les auteurs sont différents ou si le ticket ne contient qu'un seul produit) ;

  • un indice booléen ind_aut2 qui vaut 1 s'il y a au moins deux livre d'un même auteur sur le ticket, et 0 sinon.

1
SELECT m.ray, avg(v.ind_aut1)
2
FROM f_dm1_ventes v JOIN f_dw_mag m
3
ON v.mag=m.mag
4
GROUP BY m.ray

Y : 1.07

A : 1.21

E : 1.05

On observe que les magasins de type A favorisent la vente de plusieurs livres de même auteur.

1
SELECT m.ray, round(avg(v.ind_aut2),2)
2
FROM f_dm1_ventes v JOIN f_dw_mag m
3
ON v.mag=m.mag
4
WHERE v.qte>1
5
GROUP BY m.ray

  • Y : 0.16

  • A : 0.37

  • E : 0.14

Ici on a considéré uniquement les tickets pour lesquels il y a au moins deux livres de vendus (qte > 1).

On observe que, pour les magasins de type A, pour 37% des tickets comprenant au moins deux livres, il y a au moins deux livres du même auteur (contre 15% environ pour les autres magasins) ; on en conclut que les magasins de type A permettent de vendre deux fois plus souvent des livres du même auteur que les autres.