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

Version imprimable

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:

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.

1 pièce(s) jointe(s)

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:

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 :
Pièce jointe 266530

A +

20/04/2017, 19h58
Anne-LaureLbl

J'ai tout refait...
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:

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:

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.

Merci

Citation:
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:

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 :
Pièce jointe 266530

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

J'ai trouvé

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

Code:

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

:lol:

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

24/04/2017, 14h45
SQLpro

Citation:

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 +