Forums PostgreSQL.fr

Tipie

Membre

Création d'un tableau croisé

Bonjour à tous,

Je dispose d'une table appelée "dep1"

ID                               |dT |plage horaire |départ | arrivé
"IND250140000000000"|1   |0                  |739771|739782
"IND250140000000001"|1   |0                  |739772|739782
"IND250140000000002"|1   |0                  |721373|721347
"IND250140000000003"|1   |0                  |739771|721347

Que je souhaite transformer en  tableau croisé dynamique comme ceci :

|739782 |739532|721347  | 739782
|739771   |2          |           |            |
|739772   |0          |1         |            |
|739773   |            |           |1          |
|739774   |1          |           |            |

En regardant la documentation de postgres je suis tombée sur la fonction crosstab, mais je crois que je me suis emmêlée les pinceau car lorsque je lance la requête suivante:

SELECT *
FROM crosstab(
  'select départ
   arrivé
   FROM dep1')
AS ct(départ, arrivé);

J'obtiens le message d'erreur suivant:

ERREUR:  une liste de définition de colonnes est requise pour les fonctions renvoyant
un « record »
LINE 2: FROM crosstab(
             ^
********** Erreur **********

ERREUR: une liste de définition de colonnes est requise pour les fonctions renvoyant
un « record »
État SQL :42601
Caractère : 15

Je voulais donc savoir si quelqu'un sait la marche à suive afin que je puisse obtenir le tableau que je cherche à créer

Merci d'avance

Marc Cousin

Membre

Re : Création d'un tableau croisé

Bonjour,

C'est le SELECT * le problème. Il faut que vous lui donniez la liste des colonnes. Si vous reprenez l'exemple de la doc, on rajoute un AS à la fin, pour justement faire cette déclaration

SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

---

Marc.

Tipie

Membre

Re : Création d'un tableau croisé

Bonjour et merci de votre réponse,
Cependant vu que je débute avec Postgresql et le langage sql, je ne vois pas ce que vous voulez dire par "Il faut que vous lui donniez la liste des colonnes" : je dois mettre le nom des colonnes (à savoir départ et arrivé) à la place du '*' ?

Marc Cousin

Membre

Re : Création d'un tableau croisé

Non, dans le AS à la fin… la fonction pouvant retourner un peu tout et n'importe quoi, il faut que vous déclariez le type de retour de la fonction de cette façon.

---

Marc.

Tipie

Membre

Re : Création d'un tableau croisé

Qu'entendez-vous par "type de retour de la fonction" ?
J'ai essayer avec la requête suivante:

SELECT *
FROM crosstab(
  'select maille_dep
   maille_t
   FROM dep25')
AS ct(maille_dep numeric, maille_t numeric);

Et j'ai obtenu ceci:

ERREUR:  invalid source data SQL statement
DETAIL:  The provided SQL must return 3 columns: rowid, category, and values.
********** Erreur **********

ERREUR: invalid source data SQL statement
État SQL :22023
Détail :The provided SQL must return 3 columns: rowid, category, and values.

Marc Cousin

Membre

Re : Création d'un tableau croisé

Comme indiqué dans le message, l'ordre SQL fourni doit fournir 3 colonnes. Il a besoin de 3 colonnes dans la requête interne, pour pouvoir générer le tableau croisé: une qui indique la ligne (rowid), une qui indique la catégorie (colonne), et la valeur à afficher. Votre ordre SQL "select maille_dep    maille_t    FROM dep25» n'a que deux colonnes.

À priori, votre rowid correspond à la colonne départ, votre category à votre colonne arrivée, et value à ce que vous voulez afficher dans la colonne… il ne vous reste plus qu'à écrire la requête qui produit ça.

---

Marc.

Antosansh

Membre

Re : Création d'un tableau croisé

Bonjour

Je me permets de relancer ce post. Je suis comme Tipie, novice et cherchant à faire un tableau croisé, mais pour ma part, j'ai le message d'erreur suivant:

ERREUR: return and sql tuple descriptions are incompatible
État SQL :42601

Apparemment, c'est la définition de "numeric" qui pourrait poser problème.

Pour remettre les choses dans leur contexte, j'ai une table avec des identifiants d'entreprise (id_entreprise), situées dans différentes région (region), produisant différents produits (prod). Je cherche à sortir un tableau avec en ligne les produits, en colonne les régions et dans le tableau le nombre d'entreprise qui produisent les produits en question dans chaque région.

SELECT *
FROM crosstab(
  'select prod,region, count(distinct id_entreprise) as nb from enq_region group by prod, region')
AS ct(prod text, region text, nb numeric);

Où est l'erreur??

rjuju

Administrateur

Re : Création d'un tableau croisé

ERREUR: return and sql tuple descriptions are incompatible
État SQL :42601

Apparemment, c'est la définition de "numeric" qui pourrait poser problème.

[...]

SELECT *
FROM crosstab(
  'select prod,region, count(distinct id_entreprise) as nb from enq_region group by prod, region')
AS ct(prod text, region text, nb numeric);

Tout à fait, un count() va renvoyer un type bigint et non numeric. Pour le vérifier :

rjuju=# select pg_typeof(count(*));
 pg_typeof 
-----------
 bigint
(1 row)

---

Julien.
https://rjuju.github.io/

Antosansh

Membre

Re : Création d'un tableau croisé

Effectivement, il me renvoit un bigint mais même si je mets ça

AS ct(prod text, region text, nb bigint);

J'ai ça comme résultat:

ERREUR: return and sql tuple descriptions are incompatible
État SQL :42601

Antosansh

Membre

Re : Création d'un tableau croisé

Personne ne peut m'éclairer?? Je suis vraiment coincé là...

rjuju

Administrateur

Re : Création d'un tableau croisé

En fait les 2ème et 3ème colonnes (et autant de colonnes que vous avez de « region » différentes) doivent être déclarées de type bigint, vu que c'est ce qui sera pris en compte.

Votre requête devrait plutôt être quelque chose comme :

SELECT *
FROM crosstab(
  'select prod,region, count(distinct id_entreprise) as nb from enq_region group by prod, region'
) AS ct(prod text, region_1 bigint, region_2 bgint);

Vous pouvez consulter la documentation de crosstab pour plus de détail : http://docs.postgresql.fr/9.6/tablefunc.html

---

Julien.
https://rjuju.github.io/

Antosansh

Membre

Re : Création d'un tableau croisé

Ok, merci!

Alors ça marche mais ça ne sort pas ce que j'espérais...

Comme je l'ai dit, suis assez novice. J'ai jamais eu de formation dans ce domaine. J'avais déjà vu la page http://docs.postgresql.fr/9.6/tablefunc.html mais c'est du chinois pour ça

Voici ma requête:

select distinct nom from enq_zone;

SELECT *
     FROM crosstab('
        SELECT prod,region,count(distinct id_entreprise) as nb
                FROM enq_region
                group by prod,region
                order by prod',

         'SELECT region
                FROM enq_region
                group by region
                order by region')
AS ct
(
"Prod" text,
"region_1" bigint,
"region_2" bigint,
"region_3" bigint,
"region_4" bigint,
"region_5" bigint);

Le soucis c'est que cela ne m'associe pas les bonnes régions aux bons décompte (count(distinct id_entreprise))...

Une idée??

Antosansh

Membre

Re : Création d'un tableau croisé

Bon, en fait, j'ai corrigé ma requête mais je ne comprends pas pourquoi.

J'ai fait ma première requête:

select distinct nom from enq_region;

Ca m'a sorti ma liste de région: région_1, region_2, etc...

Puis je fais le cross tab. Et dans le as ct(...) j'ai mis les régions dans l'ordre de sorti de la requête précédente. A savoir region_1, 2, 3, etc... Or, en fait, ça marche si je les mets dans l'ordre inverse: region_5, region_4, region_3, etc...

Je ne sais pas si je suis clair.

Mais je me demande pourquoi. Qu'est ce qui détermine l'ordre des colonnes? Car du coup je dois le connaître pour réaliser mon ct(...), non?

Est-ce qu'il n'est pas possible d'automatiser la chose? Pour l'instant je fais avec 5 régions mais je pourrai aussi le faire pour 4 ou 15.