Forum Programmation.autre Requêtes SQL

Posté par ilip le 21 novembre 2012 à 11:26.

Étiquettes : aucune

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 Frédéric Heulin le 21 novembre 2012 à 11:54. É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 ilip le 21 novembre 2012 à 12:00. É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 Flo le 21 novembre 2012 à 16:53. É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 Michaël (site web personnel) le 22 novembre 2012 à 07:30. É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 gaaaaaAab le 21 novembre 2012 à 11:56. É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 ilip le 21 novembre 2012 à 12:04. É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 gaaaaaAab le 22 novembre 2012 à 07:41. É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 lom (site web personnel) le 21 novembre 2012 à 12:05. É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 ilip le 21 novembre 2012 à 16:52. É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 Flo le 21 novembre 2012 à 17:04. É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 ilip le 21 novembre 2012 à 17:04. É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 eMerzh (site web personnel) le 22 novembre 2012 à 00:21. É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 eMerzh (site web personnel) le 22 novembre 2012 à 00:28. É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 ilip le 22 novembre 2012 à 11:58. É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 yohann (site web personnel) le 21 novembre 2012 à 16:29. É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 Flo le 21 novembre 2012 à 17:04. Évalué à 1.
  
  Non, ça ne marchera pas, ça va juste ajouter la liste des codes status, avec 0 comme compte.
# pourquoi pas ?

Posté par caboulot le 21 novembre 2012 à 23:16. É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.