Forums PostgreSQL.fr

robinson

Membre

Syntaxe/sous-requête WHERE dans un CROSSTAB

Bonjour,

Je fais appel une nouvelle fois à vos lumières pour résoudre le problème suivant.
J'ai écrit ce code pour réaliser un CROSSTAB sur une table décrivant des polygones portant les informations (nomzone, annee, geom, type), afin de réaliser des calculs de surface de polygones en fonction des annees et des zones.

SELECT * FROM CROSSTAB(
'SELECT nomzone, annee, SUM(ST_AREA(geom) FROM parcellaire_total_test GROUP BY nomzone, annee ORDER BY 1,2',
'SELECT annee FROM parcellaire_total_test WHERE type = 'abattis frais' GROUP BY annee ORDER BY annee'
)
AS ct("nomzone" varchar, "2006" varchar, "2007" varchar, "2008" varchar, "2009" varchar, "2010" varchar, "2011" varchar, "2012" varchar, "2013" varchar, "2014" varchar, "2015" varchar, "2016" varchar)

Ce code est fonctionnel (à priori, je n'ai pas vérifié la cohérence des données).
Néanmoins, je souhaite rajouter une clause WHERE afin de séléctionner les données de sortie en fonction du type. J'ai donc écrit :

SELECT * FROM CROSSTAB(
'SELECT nomzone, annee, SUM(ST_AREA(geom) FROM parcellaire_total_test WHERE type = 'abattis frais'' GROUP BY nomzone, annee ORDER BY 1,2',
'SELECT annee FROM parcellaire_total_test WHERE type = 'abattis frais' GROUP BY annee ORDER BY annee'
)
AS ct("nomzone" varchar, "2006" varchar, "2007" varchar, "2008" varchar, "2009" varchar, "2010" varchar, "2011" varchar, "2012" varchar, "2013" varchar, "2014" varchar, "2015" varchar, "2016" varchar)

Mais après plusieurs essais de placement de la clause WHERE, aucune disposition ne marche. L'utilisation de WHERE est-elle possible? Y a t'il une autre fonction afin d'arriver à mon résultat? Et dernière questions pouvez vous m'expliquer la fonction du deuxième 'SELECT'? Je n'ai pas bien compris pourquoi elle était nécessaire (j'ai bêtement recopié des scripts sur des forums).

Merci d'avance!

gleu

Administrateur

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

Merci de toujours indiquer le message d'erreur quand une requête ne fonctionne pas ou d'indiquer qu'il n'y a pas de message d'erreur. En l'occurence, si j'essaie de jouer aux devinettes, je dirais que vous avez oublié de doubler les guillemets dans les deux clauses WHERE.

---

Guillaume.

robinson

Membre

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

Bonjour,

Désolé si mon post n'était pas complet. Le message d'erreur renvoie une erreur de syntaxe :
ERROR:  syntax error at or near "abattis"
LINE 2: ...A(geom) FROM parcellaire_total_test WHERE type = 'abattis fr...
                                                                                          ^
SQL state: 42601
Character: 109

J'avais déjà testé les doubles guillemets, qui renvoient l'erreur suivante (mais j'utilise la clause WHERE dans un SELECT avec des simples guillemets, donc j'imagine que ça doit être la même chose ici).

ERROR:  column "abattis frais" does not exist
LINE 1: ...CT annee FROM parcellaire_total_test WHERE type = "abattis f...
                                                             ^
QUERY:  SELECT annee FROM parcellaire_total_test WHERE type = "abattis frais" GROUP BY annee ORDER BY annee
SQL state: 42703

robinson

Membre

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

Problème résolu!
Il fallait tout simplement supprimer les espaces (ce qui est bizarre car dans les requêtes sans CROSSTAB fonctionnent avec les espaces) comme suit (et aussi pour précision, parce que j'avais pas compris au début, il faut bien doubler les guillemets simple et non pas mettre des doubles guillemets) :

SELECT * FROM CROSSTAB(
'SELECT nomzone, annee, SUM(ST_AREA(geom) FROM parcellaire_total_test WHERE type=''abattis frais'' GROUP BY nomzone, annee ORDER BY 1,2',
'SELECT annee FROM parcellaire_total_test WHERE type=''abattis frais'' GROUP BY annee ORDER BY annee'
)
AS ct("nomzone" varchar, "2006" varchar, "2007" varchar, "2008" varchar, "2009" varchar, "2010" varchar, "2011" varchar, "2012" varchar, "2013" varchar, "2014" varchar, "2015" varchar, "2016" varchar)

Par contre je ne comprends toujours pas l'utilité de "doubler la requête" par :

'SELECT annee FROM parcellaire_total_test WHERE type=''abattis frais'' GROUP BY annee ORDER BY annee'

Si je l'enlève, j'obtiens une erreur de type :

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

Mais je ne vois pas à quoi cela correspond.

dverite

Membre

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

Pour les espaces, ce n'est pas le problème: d'ailleurs maintenant que vous avez une version qui marche, vous pouvez remettre les espaces, vous devriez observer que ça ne change rien. En tout cas pour les espaces qui servent juste à aérer le code biensûr, pas ceux qui ont une valeur sémantique.

Pour la question de doubler les guillemets simples ou apostrophes, comme c'est pénible on peut  utiliser à la place  la syntaxe dite "guillemet dollar" comme expliqué dans la doc ici: https://docs.postgresql.fr/10/sql-syntax.html
Cette syntaxe est très souvent utilisée pour encadrer blocs de code dans les CREATE FUNCTION, mais elle peut être utilisée pour toute chaîne de caractères.

Pour l'utilité de la deuxième requête c'est plus compliqué. L'algo utilisé par la fonction crosstab(text), c.a.d la fonction  qui prend une seule requête en paramètre, n'est pas le même que celui de la fonction qui prend deux requêtes: crosstab(text, text) En particulier, la première version est incapable de produire des "cases vides" dans le résultat s'il se trouve que certains couples (nomzone,annee) sont absents. Dans ce cas la grille de résultats a des valeurs décalées et apparait fausse.

Ne pas oublier ces 2 remarques importantes de la doc de crosstab(text):
1. "Les noms des colonnes en sortie n'ont pas d'importance en soi"
2. "Notez que crosstab ne fait pas attention à la deuxième colonne du résultat de la requête"

En général on déconseille d'utiliser la 1ere version crosstab(text), trop dangereuse quand on n'est pas bien familier avec son fonctionnement.

Indépendamment de ça l'erreur "return and sql tuple descriptions are incompatible" paraît logique puisque SUM(ST_AREA(geom) doit être de type float (je pense?) alors que les colonnes 2006, 2007 etc.. sont déclarées "varchar".

Dernière modification par dverite (06/06/2018 18:39:02)

---

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

robinson

Membre

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

Ah... Je n'avais pas bien compris la différence entre les deux versions de CROSSTAB. Effectivement j'ai des valeurs NULL, ce qui explique le message d'erreur. Merci beaucoup pour vos explications! Je veillerai par ailleurs à bien définir le type de mes colonnes, c'est plus sûr.

Par ailleurs, j'ai une question plus globale :
j'ai l'impression que postgresql ne semble pas fait pour traiter les données d'une base, mais plus pour l'organiser et les afficher suivant des critères.
En effet, il n'est pas possible de réaliser des chaînes de traitements statistiques complexes (comme AVG(SUM(ST_AREA()) -> aggregate function calls cannot be nested) et la constitution de tableaux croisés dynamiques (fonction CROSSTAB) nécessite d'expliciter les types des colonnes de sortie (donc pas pratique si la table d'entrée est amenée à s'incrémenter).

Globalement, à l'inverse de Sotos sur ce sujet (https://forums.postgresql.fr/viewtopic.php?id=4478) dans son avant dernier post, je me demande si il est pertinent d'utiliser postgreSQL pour réaliser des opérations de traitements statistiques sur des tables plutôt que d'utiliser une autre solution comme Rstat liée à une base postgreSQL (surtout pour un novice comme moi qui suis incapable d'écrire une procédure telle que celle de Sotos)?

Dernière modification par robinson (06/06/2018 22:18:55)

robinson

Membre

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

@ dverite : Par curiosité, je suis allé voir sur votre blog, et votre dernier post traite exactement de ce dont je veux parler! Merci beaucoup pour cette documentation sur les pivots dynamiques, c'est bien mieux que CROSSTAB!

dverite

Membre

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

Merci pour le retour positif sur les pivots dynamiques!

Sur ce problème

comme AVG(SUM(ST_AREA()) -> aggregate function calls cannot be nested

typiquement il faut imbriquer les calculs en sous-requête, du style:

 SELECT avg(s.v) FROM (select sum(valeur) AS v FROM table GROUP BY qqch) AS s;

Les requêtes les plus complexes peuvent avoir beaucoup de niveaux d'imbrication, et mentalement on conçoit ces niveaux d'imbrication séparément pour arriver à comprendre ou produire ces requêtes.

Pour la question sur choisir Rstat ou des solutions équivalentes ou SQL, personnellement n'y connaissant rien en statistiques, je ne me rends pas vraiment compte de ce qu'on fait avec et si c'est mieux ou non.

En regardant la procédure de Sotos sur  https://forums.postgresql.fr/viewtopic.php?id=4478 ce qui me paraît notable c'est que pour son traitement toutes les colonnes numériques d'une table se valent. C'est-à-dire que sa procédure va calculer un paquet de statistiques sur les colonnes sans s'occuper de savoir si c'est un nombre de personnes, une durée en secondes, une taille, une surface... Cette manière d'aborder les données est plutôt étrange du point de vue du mode de pensée relationnel, dans lequel chaque colonne a un sens individuellement, et donc l'idée de considérer les colonnes de manière générique sort du cadre naturel.

---

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

robinson

Membre

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

Effectivement j'avais repéré des syntaxes comme celle que vous proposez sur des forums pour passer outre les limites des fonctions AGGREGATE, mais ça ne permet par de l'insérer dans un SELECT comme :
SELECT AVG(SUM(ST_AREA(geom)) FROM ...
Ce qui est limitant lorsque l'on veut opérer des transformations de données sur des données déjà sélectionnées avec une procédure "complexe" (comme un CROSSTAB par exemple). Globalement je trouve que postgreSQL n'est pas pratique pour "produire de la donnée", c-a-d produire de nouvelles tables à partir de données transformées provenant d'autres tables. J'ai le sentiment que le SQL (et probablement le système relationnel) est plus conçu pour afficher et concaténer des données que pour les transformer, d’où l'approche atypique de Sotos dans sa procédure... Ou alors c'est que je ne suis pas suffisamment bon en SQL!

robinson

Membre

Re : Syntaxe/sous-requête WHERE dans un CROSSTAB

Je viens de découvrir l'utilisation des CTE (Common Table Expression) avec WITH (l'équivalent de votre syntaxe pour les requêtes imbriquées) qui rendent la construction de requêtes beaucoup plus aisées. Je vais persévérer encore un peu dans le postgreSQL...

https://www.postgresql.org/docs/9.1/sta … -with.html
http://www.portailsig.org/content/postg … recursives
http://www.craigkerstiens.com/2013/11/1 … not-using/

Dernière modification par robinson (07/06/2018 20:04:51)