Forum Programmation.autre Requêtes SQL

Posté par  .
Étiquettes :
0
21
nov.
2012

Bonjour,

Je travaille sous PostgreSQL, avec une quantitée de données importante dans une table consituée des colonnes suivantes :

id : clé primaire
timestamp : timestamp d'enregistrement de la valeur
status : un code erreur, qui varie de 0 à 10
valeur : une valeur qui varie de 0 à 100

La première requête concerne la colonne status.
Il s'agit de renvoyer le nombre d'occurence trouvé dans la table pour chaque status. Exemple :

[ n° du status ] [ nombre d'occurence ]
0 - 66
1 - 12
2 - 0
3 - 0
4 - 5
5 - 0
6 - 6
7 - 44
8 - 23
9 - 0

J'aimerai savoir si ceci peut être réalisé en une seule requête (pour les performances).

On pourrait utiliser la fonction PostgreSQL generate_series(0, 10, 1) qui génère les chiffres de 0 à 10, mais je ne sais pas comment l'utiliser ensuite pour le comptage pour chaque status.

La deuxième requête est plus complexe, je ne sais pas si c'est possible en une seule requête,
il s'agit de retourner le nombre de valeurs comprises dans chaque intervals [0 - 10], [10 - 20], [20 - 30] … [90 - 100]

Sinon on peut faire ce genre de chose en programmation PL/pgSQL, avec une boucle FOR mais après tests, cela ne semble pas performant :

    FOR row_table_donnees IN SELECT * 
        FROM table_donnees 
        WHERE "timestamp" >= timestamp_debut
        AND "timestamp" < timestamp_fin
    LOOP
        CASE 
            -- de 0 à 10
            WHEN row_table_donnees.valeur < 10 THEN
                nbr_echelon_1 := nbr_echelon_1 + 1;
            -- de 10 à 20
            WHEN row_table_donnees.valeur < 20 THEN
                nbr_echelon_2 := nbr_echelon_2 + 1;
            -- de 20 à 30
            WHEN row_table_donnees.valeur < 30 THEN
                nbr_echelon_3 := nbr_echelon_3 + 1;
            -- suppérieur à 30
            ELSE
                nbr_echelon_4 := nbr_echelon_4 + 1;
        END CASE
    END LOOP;

    RETURN NEXT;

  • # select count distinct

    Posté par  . Évalué à -1.

    Ce n'est peut-être pas performant mais est-ce que des requêtes du style :

    SELECT COUNT(DISTINCT(status)) FROM table_donnees";
    SELECT COUNT(*) FROM (SELECT DISTINCT status FROM table_donnees) AS result";

    ne répondent pas plus simplement à la question ?

    • [^] # Re: select count distinct

      Posté par  . Évalué à -1.

      Bonjour,

      Ca ne correspond pas à ce que je veux.

      Lorsque j'exécute ces requêtes, le résultat est '10'

      Ca me compte combien de status différents j'ai dans ma table.

      Or ce que je veux c'est le nombre d'occurence pour chaque status différent dans la table.

      • [^] # Re: select count distinct

        Posté par  . Évalué à 0. Dernière modification le 21 novembre 2012 à 16:54.

        Ce n'est vraiment pas clair. Pourrais-tu donner des exemples ?

        • [^] # Re: select count distinct

          Posté par  (site web personnel) . Évalué à 1.

          Chaque enregistrement est décoré avec une catégorie et il veut répondre à la question «pour chaque catégorie, combien de membres dans cette catégorie?»

  • # GROUP BY

    Posté par  . Évalué à 8.

    la clause GROUP BY sert exactement à faire ce genre de trucs. Je n'ai pas de postgres sous la main, mais en Oracle, ça pourrait s'écrire:

    SELECT   status, count(*)
    FROM     ta_table
    GROUP BY status;
    
    
    • [^] # Re: GROUP BY

      Posté par  . Évalué à 0.

      Bonjour

      Cette requête fonctionne, j'obtiens :

      0;421
      3;4
      5;2
      6;2
      7;3
      8;1

      Mais il me faut une ligne pour chaque status :

      0;421
      1;0
      2;0
      3;4
      4;0
      5;2
      6;2
      7;3
      8;1
      9;0

      Et je pense qu'il y'a une solution avec generate_series(0, 10, 1)

      • [^] # Re: GROUP BY

        Posté par  . Évalué à 4.

        ah, j'avais raté un aspect du problème !

        à mon avis, il faut rentrer plus finement dans la compréhension de ce que représente ce status pour ne pas se tromper de solutions. De deux choses l'une:

        • soit la liste des status possibles est définie au niveau de la DB. Du coup, le status mériterait d'être modélisé par sa propre entité, et une clause "group by" et une jointure externe devrait permettre de produire le résultat que tu attends,

        • soit (le plus probable) la liste des valeurs possibles pour le statut est définie par l'application qui alimente la DB, et dans ce cas, il va falloir s'assurer qu'il n'y a jamais de divergence entre la liste des status définis par l'application et ce que produit generate_series. En plus, si, au fil du temps, certains status sont rendus obsolètes, et qu'il se met à y avoir des trous dans la séquence des différents status possibles, ça va pas être simple à reproduire via generate_series.

        pour conclure, à long terme, je pense que generate_series pose un sérieux problème de maintenance. En fonction de ce que représente précisément ce status, soit je l'ajouterais explicitement en DB et je m'en servirais en jointure externe, soit je laisserais la couche applicative ajouter la série de couple (valeur_status, 0) nécessaire pour avoir la liste complète.

  • # GROUP BY

    Posté par  (site web personnel) . Évalué à 5.

    Hello,

    Pour ta première requête:

    SELECT status, count(*) FROM table_donnees GROUP BY status;
    
    

    Ne retournera pas de 0 en effet. Dans ce cas, le plus simple est de créer toi même une table statuses(INT status) dans laquelle tu auras ta liste de status. Il te suffit ensuite de joindre:

    SELECT
        s.status
      , COUNT(*)
    FROM
        statuses s
    LEFT JOIN
        table_donnees d ON d.status=s.status
    GROUP BY 
        s.status
    ;
    
    

    En cas de nouveau status, il te suffit de rajouter une ligne dans statuses.

    Ta seconde est un chouia plus complexe, et peut être gérée de plusieurs manières. Ce que tu veux au final c'est une valeur à utiliser dans le GROUP BY.

    Version simple:

    SELECT 
        CASE valeur 
            WHEN BETWEEN 0  AND 10 THEN '0-10'
            WHEN BETWEEN 11 AND 20 THEN '11-20'
            ...
        END as echelon
      , count(*)
    FROM
        table_donnees 
    GROUP BY 
        echelon
    ;
    
    

    Si encore une fois tu veux pouvoir voir les échelons avec 0 rows, il te suffit de créer une table echelons (echelon, valeur_debut, valeur_fin), de la joindre à table_donnees et de grouper sur echelons.echelon.

    • [^] # Re: GROUP BY

      Posté par  . Évalué à 1.

      Bonjour,

      Pour la première requête, ça ne marche pas.

      Création des tables avec les valeurs :

      CREATE TABLE donnees
      (
        id bigserial NOT NULL,
        status smallint,
        valeur integer,
        CONSTRAINT donnees_pkey PRIMARY KEY (id )
      )
      
      INSERT INTO donnees (status, valeur) VALUES ('0', '30');
      INSERT INTO donnees (status, valeur) VALUES ('0', '65');
      INSERT INTO donnees (status, valeur) VALUES ('0', '20');
      INSERT INTO donnees (status, valeur) VALUES ('7', '25');
      INSERT INTO donnees (status, valeur) VALUES ('0', '25');
      
      CREATE TABLE status_list
      (
        id bigserial NOT NULL,
        status smallint,
        libelle text,
        CONSTRAINT status_list_pkey PRIMARY KEY (id )
      )
      
      INSERT INTO status_list (status, libelle) VALUES ('0', '--');
      INSERT INTO status_list (status, libelle) VALUES ('1', '--');
      INSERT INTO status_list (status, libelle) VALUES ('2', '--');
      INSERT INTO status_list (status, libelle) VALUES ('3', '--');
      INSERT INTO status_list (status, libelle) VALUES ('4', '--');
      INSERT INTO status_list (status, libelle) VALUES ('5', '--');
      INSERT INTO status_list (status, libelle) VALUES ('6', '--');
      INSERT INTO status_list (status, libelle) VALUES ('7', '--');
      INSERT INTO status_list (status, libelle) VALUES ('8', '--');
      INSERT INTO status_list (status, libelle) VALUES ('9', '--');
      
      

      La requête :

      SELECT
          s.status
        , COUNT(*)
      FROM
          status_list s
      LEFT JOIN
          donnees d ON d.status=s.status
      GROUP BY 
          s.status
      ;
      
      

      me renvoi :

      0;4
      1;1
      2;1
      3;1
      4;1
      5;1
      6;1
      7;1
      8;1
      9;1

      • [^] # Re: GROUP BY

        Posté par  . Évalué à 0.

        C'est plutôt quelque chose comme ça :

        SELECT status, COALESCE (compte, '0')
        FROM
        (
        SELECT
        s.status
        , COUNT(*) as compte
        FROM
        status_list s
        GROUP BY
        s.status
        ) as compte_status
        LEFT JOIN donnees d ON d.status=compte_status.status;

        (en fait il faut faire la jointure hors du GROUP BY)

      • [^] # Re: GROUP BY

        Posté par  . Évalué à 1.

        L'équivalent de la requête avec la fonction generate_series est :

        SELECT
            s.status
          , COUNT(*)
        FROM
            (SELECT status FROM generate_series(0, 9, 1) AS status) s
        LEFT JOIN
             donnees d ON d.status=s.status
        GROUP BY 
            s.status
        ;
        
        
        • [^] # Re: GROUP BY

          Posté par  (site web personnel) . Évalué à 2.

          En effet,
          pour le 1 , je backup le generate series : http://sqlfiddle.com/#!1/92d26/18

          et pour la deuxième même combat … si j'ai bien compris : http://sqlfiddle.com/#!1/92d26/19/0

          d'ailleur merci pour la découverte du sqlfiddle… super outil!

          • [^] # Re: GROUP BY

            Posté par  (site web personnel) . Évalué à 1.

            sinon … je pense pas que ça s'applique ici … mais les windowing function sont vraiment super pour éviter des boucles en pgplsql…
            un aperçu ici : http://www.depesz.com/2012/11/20/window-window-on-the-wall/

          • [^] # Re: GROUP BY

            Posté par  . Évalué à 1.

            Bonjour,

            C'est bien ce que je cherchai a faire.

            Pour la première requête ça donne :

            SELECT s, COUNT(status) 
            FROM generate_series(0, 20, 1) AS s
            LEFT JOIN (
                SELECT status 
                FROM donnees
                WHERE timestamp >= 1104537600 
                AND timestamp < 1104624000
                ) d ON s=d.status
            GROUP BY s
            ORDER BY s;
            
            

            La requête sur 229398 lignes me renvoit bien les cumuls :

            0;218584
            1;527
            2;577
            3;557
            4;528
            5;552
            6;544
            7;549
            8;549
            9;566
            10;548
            11;528
            12;578
            13;506
            14;530
            15;540
            16;499
            17;540
            18;528
            19;532
            20;536

            La requête prend 6812 msec ce qui est assez performant.

            Avant j'obtenai le même résultat en faisant une requête COUNT pour chaque valeur de status :

            SELECT COUNT(status) 
                FROM donnees
                WHERE timestamp >= 1104537600 
                AND timestamp < 1104624000
                AND status=0;
            
            SELECT COUNT(status) 
                FROM donnees
                WHERE timestamp >= 1104537600 
                AND timestamp < 1104624000
                AND status=1;
            
            ...
            
            

            Ce qui me prenait 51802 msec

            Pour la deuxième requête par contre c'est moins bon au niveau performances et je ne comprend pas pourquoi :

            Je veux répartir la colonne "valeur" dans 15 intervalles différents :

            -∞ à -120
            -120 à -100
            -100 à -80
            -80 à -60
            -60 à -40
            -40 à -20
            -20 à 0
            0 à 20
            20 à 40
            40 à 60
            60 à 80
            80 à 100
            100 à 120
            120 à +∞

            Ma requête est:

            SELECT (s.values*20), COUNT(valeur)
            FROM (SELECT generate_series(-7, 7, 1) AS values) as s
            LEFT JOIN (
                SELECT valeur 
                FROM donnees_brutes 
                WHERE timestamp >= 1104537600 
                AND timestamp < 1104624000
                ) t ON ((t.valeur >= s.values*20) OR (s.values = -7)) AND ((t.valeur < ((s.values*20)+20)) OR (s.values = 7))
            GROUP BY s.values
            ORDER BY s.values;
            
            

            Comme la précédente requête, il y'a 229398 lignes, cela me renvoit :

            -∞;1090
            -120;2325
            -100;1169
            -80;3521
            -60;3437
            -40;17349
            -20;17269
            0;137523
            20;17308
            40;16870
            60;3466
            80;3397
            100;1166
            120;2311
            +∞;1197

            Mais j'ai ce résultat en 43875 msec.

            Alors que si je fais des requêtes COUNT pour chaque intervalles séparément, j'ai le résultat 37738 msec, ce qui est meilleur que la requête groupée.

  • # Group by union distinct

    Posté par  (site web personnel) . Évalué à 0.

    Pas testé, mais avec un peu de chance:

    SELECT   status, count(*)
    FROM     ta_table
    GROUP BY status
    UNION
    SELECT DISINCT status, 0
    FROM     ta_table;
    
    
    • [^] # Re: Group by union distinct

      Posté par  . Évalué à 1.

      Non, ça ne marchera pas, ça va juste ajouter la liste des codes status, avec 0 comme compte.

  • # pourquoi pas ?

    Posté par  . Évalué à 1.

    Je ne connais pas les spécificités de PostreSQL comme ça à brûle-pourpoint mais je veux bien contribuer quand même : http://sqlfiddle.com/#!2/e3a4a/22

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.