Forums PostgreSQL.fr

evaporite

Membre

requete crosstab

Bonjour à tous
Je suis sous 9.4.
J'ai une table voirie avec 3 champs
INSEE (text)    nature (text)     longeur (integre)
1                       A                            5
2                       B                            2
1                       C                            3
1                       A                            6

je voudrais le faire évoluer en
INSEE      TOTAL (sum de longueur),    nature_A          nature_B       nature_C
1                   14                                      11                      0              3
2                    2                                        0                  2                  0

DOnc j'ai pris la fonction crosstab (c'est faire un tableau croisé dynamique de excel )
avec deux requetes  :   crosstab(text source_sql, text category_sql)

SELECT *
     FROM crosstab('
        SELECT INSEE,nature,sum(longeur_ml)
                FROM voirie
                group by INSEE,nature
                order by nature',

         'SELECT nature
                FROM voirie
                group by nature
                order by nature')

Les deux requetes fonctionnent individuellement, c'est pour la suite que j'ai un souci ...

option 1 (ce qui m'arrangerait)
La liste des colonnes seraient variables, les valeurs différentes de "nature" aujourd'hui sont de 44 différentes mais demain ça sera peut être 30 ou 50, donc je souhaiterais que les entetes de champ soit automatiques en fonction des variables du champ nature ...

option 2 je défini les noms de champs
Et comme j'ai pas trouvé comment faire, j'ai fait avec la définition des champs mais ca ne marche pas non plus                
En fait après avoir recommencé ma requete je fais tombé le serveur  ... server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Pour la suite de la requete voici ce que j'ai préparé
AS
(
"enrobé" numeric(10,2),
"bicouche" numeric(10,2),
"graviers" numeric(10,2),
"sablé" numeric(10,2)
))
et c'est là ou ça fait tombé postgresql ... ma base est trop importante ? J'ai 44 natures différentes ...

Alors si quelqu'un a des conseils, une idée des corrections je suis preneuse, je débute ...

Merci
Aurélie

dverite

Membre

Re : requete crosstab

Malheureusement, il faut obligatoirement 3 étapes avec crosstab lorsque la liste des colonnes est variable.

- 1: récupérer la liste des colonnes via une requête SQL, du style

select distinct nature from voirie;

- 2: injecter cette liste dans une requête SQL crosstab à 2 paramètres, plus une déclaration de type de retour

Cette requête va ressembler à ce modèle (que je reprends de la question, je ne sais pas si elle est juste)

SELECT *
     FROM crosstab('
        SELECT INSEE,nature,sum(longeur_ml)
                FROM voirie
                group by INSEE,nature
                order by nature',

         'SELECT nature
                FROM voirie
                group by nature
                order by nature') 
AS ct
(
"enrobé" numeric(10,2),
"bicouche" numeric(10,2),
"graviers" numeric(10,2),
"sablé" numeric(10,2)
.... etc ...
)

(encore que généralement on trie par la 1ere colonne qui serait ici INSEE, et non la 2eme, à vérifier).

- 3: exécuter cette requête.

Il n'est pas possible d'encapsuler ces étapes en une seule fonction sur le serveur, sinon crosstab le ferait.

En ce qui concerne le plantage de postgresql, ce n'est biensûr pas normal, même s'il y a beaucoup de colonnes.
Ca pourrait donner lieu à un rapport de bug surtout si c'est reproductible, et si les versions de postgres et crosstab sont à jour et bien identifiées.

Une autre option est de pivoter le résultat côté client au lieu de le faire côté serveur. Mais il faut écrire un bout de programme client.

Dernière modification par dverite (24/08/2015 16:38:40)

---

@DanielVerite
http://blog-postgresql.verite.pro/