Création vue sur table calculée pour obtenir un ORDER BY

**Anne-LaureLbl** · 19/04/2017, 17h37

Bonjour,

Soit un item X pouvant enregistrer un certain nombre de votes pour différentes catégories. J’ai besoin de connaître :
A) le nombre de votes enregistrés dans chaque catégorie pour X
B) le pourcentage applicable à chaque catégorie pour X (et donc le nombre total de votes enregistrés pour X)
C) les catégories dont le pourcentage est le plus important pour X

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
/*pour A et B*/
create table inputs
  (item_id serial primary key,
  tribe1 numeric not null,
  tribe2 numeric not null,
  tribe3 numeric not null,
  tribe4 numeric not null,
  tribe5 numeric not null,
  tribe6 numeric not null,
  tribe7 numeric not null,
  tribe8 numeric not null,
  tribe9 numeric not null,
  tribe10 numeric not null,
  tribe11 numeric not null,
  tribe12 numeric not null,
  tribe13 numeric not null,
  tribe14 numeric not null,
  tribe15 numeric not null,
  tribe16 numeric not null,
  tribe17 numeric not null,
  tribe18 numeric not null,
  tribe19 numeric not null,
  tribe20 numeric not null,
  tribe21 numeric not null, 
  tribe22 numeric not null,
  tribe23 numeric not null,
  tribe24 numeric not null);
 
insert into inputs values 
(1, 5678543 , 18 , 1 , 667554 , 0 , 98 , 78 , 0 , 19 , 35785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 453 , 1),
(2, 12678543 , 18 , 1 , 37554 , 0 , 98 , 78 , 0 , 19 , 385 , 15 , 612 , 47543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 1),
(3, 678543 , 18 , 1 , 665654 , 0 , 98 , 78 , 0 , 19 , 35785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 10),
(4, 56543 , 18 , 1 , 467554 , 0 , 98 , 78 , 0 , 19 , 3785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 785325 , 9 , 3 , 245 , 51),
(5, 78543 , 18 , 1 , 64554 , 0 , 98 , 78 , 0 , 19 , 358 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 1),
(6, 6778543 , 18 , 1 , 97554 , 0 , 98 , 78 , 0 , 19 , 355785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 251),
(7, 9078543 , 18 , 1 , 554 , 0 , 98 , 78 , 0 , 19 , 5785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 555 , 9 , 3 , 45 , 1),
(8, 5679543 , 18 , 1 , 90554 , 0 , 98 , 78 , 0 , 19 , 3585 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 31);
 
create or replace view coefficient as select 
 item_id, (tribe1 +   tribe2 +   tribe3 +   tribe4 +   tribe5 +   tribe6 +   tribe7 +   tribe8 +   tribe9 +   tribe10 +   tribe11 +   
           tribe12 +   tribe13 +   tribe14 +   tribe15 +   tribe16 +   tribe17 +   tribe18 +   tribe19 +   tribe20 +   tribe21 +   tribe22 +   
           tribe23 +   tribe24)/100 as coefficient from inputs;
 
create or replace view tribe_coefficient as select 
	inputs.item_id as item_id, 
    tribe1 / v.coefficient as tribe1,  
    tribe2 / v.coefficient as tribe2,  
    tribe3 / v.coefficient as tribe3,  
    tribe4 / v.coefficient as tribe4,  
    tribe5 / v.coefficient as tribe5,  
    tribe6 / v.coefficient as tribe6,  
    tribe7 / v.coefficient as tribe7,  
    tribe8 / v.coefficient as tribe8,  
    tribe9 / v.coefficient as tribe9,  
    tribe10 / v.coefficient as tribe10,  
    tribe11 / v.coefficient as tribe11,  
    tribe12 / v.coefficient as tribe12,  
    tribe13 / v.coefficient as tribe13,  
    tribe14 / v.coefficient as tribe14,  
    tribe15 / v.coefficient as tribe15,  
    tribe16 / v.coefficient as tribe16,  
    tribe17 / v.coefficient as tribe17,  
    tribe18 / v.coefficient as tribe18,  
    tribe19 / v.coefficient as tribe19,  
    tribe20 / v.coefficient as tribe20,  
    tribe21 / v.coefficient as tribe21,  
    tribe22 / v.coefficient as tribe22,  
    tribe23 / v.coefficient as tribe23,  
    tribe24 / v.coefficient as tribe24, 
    v.coefficient as coefficient from inputs
		join coefficient as v on inputs.item_id = v.item_id;
 
/* pour C : select from ???? order by ???

Je ne vois pas du tout comment je peux obtenir un classement des valeurs indiquées dans les colonnes tribe1 à tribe24 sur un item donné... Qu'en pensez-vous ?
Un grand merci par avance pour vos réponses.

**SQLpro** · 20/04/2017, 16h22

Votre table violant les principes de modélisation et notamment la forme normale n°1, vous n'obtiendrez jamais rien de facile ni de performant pour faire ce que vous souhaitez.

Commencez par normaliser votre table sous forme de 2 relations et vous verrez que vos requêtes deviennent triviales et seront en sus performantes !

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE T_INPUT (
item_id serial primary key);
 
CREATE TABLE T_ALL_INPUT (
item_id FOREIGN KEY REFERENCES T_INPUT  (item_id)
item_num SMALLINT,
item_value numeric
PRIMARY KEY (item_id, item_num));

Apprenez la modélisation des données notamment avec notre livre :
Nom : Soutou Brouard Modélisation bases de données.jpg
Affichages : 181
Taille : 40,3 Ko

Nom : Soutou Brouard Modélisation bases de données.jpg
Affichages : 181
Taille : 40,3 Ko

A +

**Anne-LaureLbl** · 20/04/2017, 19h58

En partant sur une table TRIBES_VOTES (fk_item, fk_tribe, fk_type, vote) indexée sur les tables item, tribe, type.

1/pour trouver les tribes les plus populaires pour un item donné

Code :

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

1
2
3
Select t.tribe, t.vote, Rank() OVER (Order by t.vote desc) 
from tribes_votes as t 
where t.item= 4;

2/ pour sortir les items les plus populaires d'une catégorie donnée pour une tribe donnée

Code :

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

1
2
3
4
Select t.tribe, t.vote, Rank() OVER (Order by t.vote desc) 
from tribes_votes as t 
where t.type =  167 and t.tribe = 24
fetch first 4 rows only;

Et tant pis les pourcentages, tout le monde s'en fout.

**Anne-LaureLbl** · 20/04/2017, 20h03

Envoyé par SQLpro

Votre table violant les principes de modélisation et notamment la forme normale n°1, vous n'obtiendrez jamais rien de facile ni de performant pour faire ce que vous souhaitez.

Commencez par normaliser votre table sous forme de 2 relations et vous verrez que vos requêtes deviennent triviales et seront en sus performantes !

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE T_INPUT (
item_id serial primary key);
 
CREATE TABLE T_ALL_INPUT (
item_id FOREIGN KEY REFERENCES T_INPUT  (item_id)
item_num SMALLINT,
item_value numeric
PRIMARY KEY (item_id, item_num));

Apprenez la modélisation des données notamment avec notre livre :
Nom : Soutou Brouard Modélisation bases de données.jpg
Affichages : 181
Taille : 40,3 Ko

A +

Pour la 4e édition, mon projet ferait un excellent cas réel pour les exercices corrigés

**Anne-LaureLbl** · 22/04/2017, 21h29

Parce qu'en fait non, on ne s'en fout pas des pourcentages !

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
create schema s_test;
 
CREATE TABLE s_test.inputs
(
    item_id integer NOT NULL,
    tribe smallint NOT NULL,
    tribecpt numeric NOT NULL
);
 
insert into s_test.inputs (tribe, item_id, tribecpt)values 
(1, 1, 35),(1, 2, 16),(1, 3, 13),(1, 4, 245),(1, 5, 367),(1, 6, 4031),(1, 7, 4924),(1, 8, 5817),(2, 1, 671),(2, 2, 763),(2, 3, 846),(2, 4, 9389),(2, 5, 1022),(2, 6, 35),(2, 7, 16),(2, 8, 13),(3, 1, 245),(3, 2, 367),(3, 3, 4031),(3, 4, 494),(3, 5, 587),(3, 6, 61),(3, 7, 7603),(3, 8, 849),(4, 1, 9389),(4, 2, 1022),(4, 3, 35),(4, 4, 16),(4, 5, 13),(4, 6, 245),(4, 7, 367),(4, 8, 4031),(5, 1, 494),(5, 2, 5817),(5, 3, 671),(5, 4, 7603),(5, 5, 8496),(5, 6, 9389),(5, 7, 1022),(5, 8, 35),(6, 1, 16),(6, 2, 13),(6, 3, 245),(6, 4, 367),(6, 5, 4031),(6, 6, 494),(6, 7, 587),(6, 8, 671),(7, 1, 763),(7, 2, 8496),(7, 3, 939),(7, 4, 1022),(7, 5, 35),(7, 6, 16),(7, 7, 13),(7, 8, 245),(8, 1, 367),(8, 2, 401),(8, 3, 4924),
(8, 4, 5817),(8, 5, 671),(8, 6, 7603),(8, 7, 8496),(8, 8, 9389),(9, 1, 1022),(9, 2, 134),(9, 3, 35),(9, 4, 16),(9, 5, 13),(9, 6, 245),(9, 7, 367),(9, 8, 401),(10, 1, 494),(10, 2, 5817),(10, 3, 671),(10, 4, 7603),(10, 5, 8496),(10, 6, 9389),(10, 7, 1022),(10, 8, 35),(11, 1, 16),(11, 2, 13),(11, 3, 245),(11, 4, 367),(11, 5, 4031),(11, 6, 494),(11, 7, 587),(11, 8, 671),(12, 1, 7603),(12, 2, 8496),(12, 3, 9389),(12, 4, 1022),(12, 5, 35),(12, 6, 16),(12, 7, 13),(12, 8, 245),(13, 1, 367),(13, 2, 401),(13, 3, 494),(13, 4, 587),(13, 5, 671),(13, 6, 763),(13, 7, 8496),(13, 8, 9389),(14, 1, 1022),(14, 2, 35),(14, 3, 16),(14, 4, 13),(14, 5, 245),(14, 6, 367),(14, 7, 401),(14, 8, 4924),(15, 1, 5817),(15, 2, 671),(15, 3, 763),(15, 4, 846),(15, 5, 939),(15, 6, 1022),(15, 7, 35),(15, 8, 16),(16, 1, 13),(16, 2, 245),(16, 3, 367),
(16, 4, 401),(16, 5, 494),(16, 6, 587),(16, 7, 671),(16, 8, 7603),(17, 1, 846),(17, 2, 9389),(17, 3, 1022),(17, 4, 35),(17, 5, 16),(17, 6, 13),(17, 7, 245),(17, 8, 367),(18, 1, 401),(18, 2, 494),(18, 3, 581),(18, 4, 671),(18, 5, 703),(18, 6, 846),(18, 7, 9389),(18, 8, 10282),(19, 1, 35),(19, 2, 16),(19, 3, 13),(19, 4, 245),(19, 5, 367),(19, 6, 4031),(19, 7, 4924),(19, 8, 5817),(20, 1, 671),(20, 2, 35),(20, 3, 16),(20, 4, 13),(20, 5, 245),(20, 6, 367),(20, 7, 401),(20, 8, 494),(21, 1, 5817),(21, 2, 671),(21, 3, 763),(21, 4, 35),(21, 5, 16),(21, 6, 13),(21, 7, 245),(21, 8, 367),(22, 1, 403),(22, 2, 494),(22, 3, 587),(22, 4, 671),(22, 5, 763),(22, 6, 846),(22, 7, 939),(22, 8, 1022),(23, 1, 35),(23, 2, 16),(23, 3, 13),(23, 4, 245),(23, 5, 367),(23, 6, 4031),(23, 7, 4924),(23, 8, 587),(24, 1, 671),(24, 2, 763),(24, 3, 35),(24, 4, 16),(24, 5, 13),(24, 6, 245),(24, 7, 367),(24, 8, 401);  
 
create or replace view s_test.coeff as select item_id, sum(tribecpt) as item_input from s_test.inputs
group by item_id
order by item_id;
 
select *from s_test.inputs
select *from s_test.coeff
 
select s_test.inputs.item_id, s_test.inputs.tribe, to_char(case a.item_input when 0 then 0 else 100*s_test.inputs.tribecpt/a.item_input end, '999D99') as tribe_rank
 from  s_test.coeff a, s_test.inputs
where a.item_id = s_test.inputs.item_id
order by item_id, tribe_rank DESC

J'appréhende tout de même le temps de calcul des vues lorsqu'il y aura des item_id par millions...

**SQLpro** · 24/04/2017, 14h45

Envoyé par Anne-LaureLbl

J'appréhende tout de même le temps de calcul des vues lorsqu'il y aura des item_id par millions...

C'est pas le moment d'en discuter, d'autant qu'il manque un filtre sur le temps. peut être n'allez-vous pas conserver les infos sur 20 ans ou bine atteindre une telle profondeur de scrutation...
Il existe des solutions : indexation, voir vues indexées (c'est pas encore au point sous PG...mais bon!)

A +

Création vue sur table calculée pour obtenir un ORDER BY

Requêtes PostgreSQL

Discussions similaires

Partager

Partager