1. #1
    Membre à l'essai Avatar de Anne-LaureLbl
    Femme Profil pro
    Porteuse de projet
    Inscrit en
    février 2017
    Messages
    13
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Porteuse de projet
    Secteur : Tourisme - Loisirs

    Informations forums :
    Inscription : février 2017
    Messages : 13
    Points : 13
    Points
    13

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

    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.

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 013
    Points : 39 496
    Points
    39 496
    Billets dans le blog
    1

    Par défaut

    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 : 35
Taille : 40,3 Ko

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Membre à l'essai Avatar de Anne-LaureLbl
    Femme Profil pro
    Porteuse de projet
    Inscrit en
    février 2017
    Messages
    13
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Porteuse de projet
    Secteur : Tourisme - Loisirs

    Informations forums :
    Inscription : février 2017
    Messages : 13
    Points : 13
    Points
    13

    Par défaut 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 : 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.

  4. #4
    Membre à l'essai Avatar de Anne-LaureLbl
    Femme Profil pro
    Porteuse de projet
    Inscrit en
    février 2017
    Messages
    13
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Porteuse de projet
    Secteur : Tourisme - Loisirs

    Informations forums :
    Inscription : février 2017
    Messages : 13
    Points : 13
    Points
    13

    Par défaut Merci

    Citation Envoyé par SQLpro Voir le message
    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 : 35
Taille : 40,3 Ko

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

  5. #5
    Membre à l'essai Avatar de Anne-LaureLbl
    Femme Profil pro
    Porteuse de projet
    Inscrit en
    février 2017
    Messages
    13
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Porteuse de projet
    Secteur : Tourisme - Loisirs

    Informations forums :
    Inscription : février 2017
    Messages : 13
    Points : 13
    Points
    13

    Par défaut J'ai trouvé

    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...

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 013
    Points : 39 496
    Points
    39 496
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par Anne-LaureLbl Voir le message

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Requetes sur tables multiples ! Pour les 'gurus'
    Par bandicoq dans le forum Langage SQL
    Réponses: 5
    Dernier message: 07/08/2008, 11h39
  2. Réponses: 24
    Dernier message: 17/04/2008, 14h24
  3. Pb création vue sur Free
    Par PatatorMan dans le forum Installation
    Réponses: 12
    Dernier message: 16/10/2006, 09h38
  4. Réponses: 5
    Dernier message: 29/07/2006, 00h35
  5. Requete sur table récursive pour construire arbre
    Par dacid dans le forum Requêtes et SQL.
    Réponses: 3
    Dernier message: 13/06/2006, 17h17

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo