Création d'un tableau croisé

Tipie · 15/06/2016 13:40:48

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 · 15/06/2016 13:48:30

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);

Tipie · 15/06/2016 15:05:14

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 · 15/06/2016 15:06:27

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.

Tipie · 15/06/2016 15:44:41

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 · 16/06/2016 09:23:51

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.

Antosansh · 20/10/2016 10:29:08

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 · 20/10/2016 12:32:21

Antosansh a écrit :

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)

Antosansh · 20/10/2016 13:04:05

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 · 25/10/2016 20:58:34

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

rjuju · 25/10/2016 22:50:13

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

Antosansh · 26/10/2016 00:02:46

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 · 26/10/2016 00:09:46

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.

Forums PostgreSQL.fr

#1 15/06/2016 13:40:48

Création d'un tableau croisé

#2 15/06/2016 13:48:30

Re : Création d'un tableau croisé

#3 15/06/2016 15:05:14

Re : Création d'un tableau croisé

#4 15/06/2016 15:06:27

Re : Création d'un tableau croisé

#5 15/06/2016 15:44:41

Re : Création d'un tableau croisé

#6 16/06/2016 09:23:51

Re : Création d'un tableau croisé

#7 20/10/2016 10:29:08

Re : Création d'un tableau croisé

#8 20/10/2016 12:32:21

Re : Création d'un tableau croisé

#9 20/10/2016 13:04:05

Re : Création d'un tableau croisé

#10 25/10/2016 20:58:34

Re : Création d'un tableau croisé

#11 25/10/2016 22:50:13

Re : Création d'un tableau croisé

#12 26/10/2016 00:02:46

Re : Création d'un tableau croisé

#13 26/10/2016 00:09:46

Re : Création d'un tableau croisé

Pied de page des forums