Discussion :

[StringBuilder]

Bonjour à tous,

Histoire de m'amuser, je suis en train de tenter de répondre à une problématique simple :

On a des livraisons qui sont regroupées dans des voyages (correspond à un chargement sur un navire par exemple, ou sur un camion)
Un voyage comporte un nombre de kilomètres, à partir duquel on va calculer un prix.
Chaque livraison comporte des produits.
Ce produits sont regroupés par familles.

Le but du jeu, c'est de ventiler, en pourcentage, le poids des produits par rapport à la distance du voyage, afin d'obtenir le pourcentage du poids de chaque famille par voyage, afin de savoir comment répartir le coût du voyage sur les produits.

J'ai donc le modèle et le jeu de données suivant (désolé, il contient quelques éléments de plus que l'énoncé) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
drop table livraison_poste;
drop table conversion;
drop table produit;
drop table unite;
drop table famille;
drop table livraison;
drop table client;
drop table voyage;
 
create table voyage
(
	voy_id int identity(1, 1) not null primary key,
	voy_numero int not null,
	voy_port varchar(30) not null,
	voy_distance int not null
);
 
create table client
(
	cli_id int identity(1, 1) not null primary key,
	cli_sigle varchar(6) not null,
	cli_nom varchar(30) not null
);
 
create table livraison
(
	liv_id int identity(1, 1) not null primary key,
	liv_numero int not null,
	cli_id int not null references client(cli_id),
	voy_id int not null references voyage(voy_id)
);
 
create table famille
(
	fam_id int identity(1, 1) not null primary key,
	fam_nom varchar(30) not null
)
 
create table unite
(
	uni_id int identity(1, 1) not null primary key,
	uni_code varchar(2) not null,
	uni_nom varchar(10) not null
);
 
create table produit
(
	pro_id int identity(1, 1) not null primary key,
	pro_nom varchar(30) not null,
	fam_id int not null references famille(fam_id),
	pro_unidef_id int references unite(uni_id)
)
 
create table conversion
(
	con_id int identity(1, 1) not null primary key,
	pro_id int not null references produit(pro_id),
	uni_id int not null references unite(uni_id),
	con_conversion float not null
);
 
create table livraison_poste
(
	lip_id int identity(1, 1) not null primary key,
	liv_id int not null references livraison(liv_id),
	liv_poste int not null,
	liv_quantite int not null,
	con_id int not null references conversion(con_id)
);
 
insert into unite (uni_code, uni_nom) values ('PI', 'Pièce');
insert into unite (uni_code, uni_nom) values ('LT', 'Litre');
insert into unite (uni_code, uni_nom) values ('KG', 'Kilogramme');
insert into unite (uni_code, uni_nom) values ('PA', 'Palette');
 
insert into famille (fam_nom) values ('Bricolage');
insert into famille (fam_nom) values ('Jardinage');
 
insert into client (cli_sigle, cli_nom) values ('000001', 'Client 1');
insert into client (cli_sigle, cli_nom) values ('000002', 'Client 2');
insert into client (cli_sigle, cli_nom) values ('000003', 'Client 3');
 
insert into produit (pro_nom, fam_id, pro_unidef_id) values ('Marteau', 1, 1);
insert into produit (pro_nom, fam_id, pro_unidef_id) values ('Rateau', 2, 1);
insert into produit (pro_nom, fam_id, pro_unidef_id) values ('Engrais', 2, 2);
insert into produit (pro_nom, fam_id, pro_unidef_id) values ('Terreau', 2, 2);
insert into produit (pro_nom, fam_id, pro_unidef_id) values ('Tournevis', 1, 1);
insert into produit (pro_nom, fam_id, pro_unidef_id) values ('Perceuse', 1, 1);
 
insert into conversion (pro_id, uni_id, con_conversion) values (1, 1, 1);
insert into conversion (pro_id, uni_id, con_conversion) values (1, 3, 0.5);
insert into conversion (pro_id, uni_id, con_conversion) values (1, 4, 1200);
insert into conversion (pro_id, uni_id, con_conversion) values (2, 1, 1);
insert into conversion (pro_id, uni_id, con_conversion) values (2, 3, 2);
insert into conversion (pro_id, uni_id, con_conversion) values (2, 4, 40);
insert into conversion (pro_id, uni_id, con_conversion) values (3, 1, 1);
insert into conversion (pro_id, uni_id, con_conversion) values (3, 2, 0.75);
insert into conversion (pro_id, uni_id, con_conversion) values (3, 3, 0.8);
insert into conversion (pro_id, uni_id, con_conversion) values (3, 4, 90);
insert into conversion (pro_id, uni_id, con_conversion) values (4, 1, 1);
insert into conversion (pro_id, uni_id, con_conversion) values (4, 2, 16);
insert into conversion (pro_id, uni_id, con_conversion) values (4, 3, 20);
insert into conversion (pro_id, uni_id, con_conversion) values (4, 4, 60);
insert into conversion (pro_id, uni_id, con_conversion) values (5, 1, 1);
insert into conversion (pro_id, uni_id, con_conversion) values (5, 3, 0.1);
insert into conversion (pro_id, uni_id, con_conversion) values (5, 4, 1200);
insert into conversion (pro_id, uni_id, con_conversion) values (6, 1, 1);
insert into conversion (pro_id, uni_id, con_conversion) values (6, 3, 0.6);
insert into conversion (pro_id, uni_id, con_conversion) values (6, 4, 200);
 
insert into voyage (voy_numero, voy_port, voy_distance) values (1, 'Xingang', 17453);
insert into voyage (voy_numero, voy_port, voy_distance) values (2, 'Miami', 6310);
 
insert into livraison (liv_numero, cli_id, voy_id) values (1, 2, 1);
insert into livraison (liv_numero, cli_id, voy_id) values (2, 3, 2);
insert into livraison (liv_numero, cli_id, voy_id) values (3, 3, 1);
 
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (1, 1, 60, 3);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (1, 2, 80, 6);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (1, 3, 70, 10);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (1, 4, 40, 14);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (2, 1, 80, 6);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (2, 2, 40, 14);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (2, 3, 80, 17);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (2, 4, 50, 20);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (3, 1, 45, 10);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (3, 2, 29, 3);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (3, 3, 178, 6);
insert into livraison_poste (liv_id, liv_poste, liv_quantite, con_id) values (3, 4, 150, 17);

Voici la requête que j'ai écrit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select voy_numero, voy_port, fam_nom, voy_distance, SUM(pct)
from (
  select voy.voy_numero, voy.voy_port, fam.fam_nom, voy.voy_distance, (con1.con_conversion * lip.liv_quantite * con2.con_conversion) / SUM(con1.con_conversion * lip.liv_quantite * con2.con_conversion) over(partition by voy.voy_id) pct
  from voyage voy
  inner join livraison liv on liv.voy_id = voy.voy_id
  inner join livraison_poste lip on lip.liv_id = liv.liv_id
  inner join conversion con1 on con1.con_id = lip.con_id
  inner join produit pro on pro.pro_id = con1.pro_id
  inner join conversion con2 on con2.pro_id = pro.pro_id
  inner join unite uni on uni.uni_id = con2.uni_id and uni.uni_code = 'KG'
  inner join famille fam on fam.fam_id = pro.fam_id
) tmp
group by voy_numero, voy_port, fam_nom, voy_distance;

Je pensais, avec la fonction de fenêtrage, pouvoir calculer le pourcentage non pas par produit, mais par famille, mais les fonctions de fenêtrage semblent incompatibles avec group by. Comment puis-je me passer de la sous-requête ?

[Waldar]

Les fonctions de fenêtrages ne sont pas incompatibles avec le GROUP BY, mais elles sont évaluées après.
Dès lors, la bonne syntaxe est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
    SELECT voy.voy_numero, voy.voy_port, fam.fam_nom, voy.voy_distance
         ,     sum(co1.con_conversion * lip.liv_quantite * co2.con_conversion)
         / sum(sum(co1.con_conversion * lip.liv_quantite * co2.con_conversion)) over(partition BY voy.voy_id) as pct
      FROM voyage          voy
INNER JOIN livraison       liv ON liv.voy_id = voy.voy_id
INNER JOIN livraison_poste lip ON lip.liv_id = liv.liv_id
INNER JOIN conversion      co1 ON co1.con_id = lip.con_id
INNER JOIN produit         pro ON pro.pro_id = co1.pro_id
INNER JOIN conversion      co2 ON co2.pro_id = pro.pro_id
INNER JOIN unite           uni ON uni.uni_id = co2.uni_id AND uni.uni_code = 'KG'
INNER JOIN famille         fam ON fam.fam_id = pro.fam_id
  group by voy.voy_id, voy.voy_numero, voy.voy_port, fam.fam_nom, voy.voy_distance;

À noter qu'Oracle propose cette fonction en natif :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
    SELECT voy.voy_numero, voy.voy_port, fam.fam_nom, voy.voy_distance
         , ratio_to_report(sum(co1.con_conversion * lip.liv_quantite * co2.con_conversion)) over(partition BY voy.voy_id) as pct
      FROM voyage          voy
INNER JOIN livraison       liv ON liv.voy_id = voy.voy_id
INNER JOIN livraison_poste lip ON lip.liv_id = liv.liv_id
INNER JOIN conversion      co1 ON co1.con_id = lip.con_id
INNER JOIN produit         pro ON pro.pro_id = co1.pro_id
INNER JOIN conversion      co2 ON co2.pro_id = pro.pro_id
INNER JOIN unite           uni ON uni.uni_id = co2.uni_id AND uni.uni_code = 'KG'
INNER JOIN famille         fam ON fam.fam_id = pro.fam_id
  group by voy.voy_id, voy.voy_numero, voy.voy_port, fam.fam_nom, voy.voy_distance;

[StringBuilder]

Hmmm, ok, j'essayais de faire un sum() sur l'ensemble du calcul de la colonne pct et ça me disait "Windowed functions cannot be used in the context of another windowed function or aggregate."

Merci pour ta syntaxe, elle marche parfaitement !