Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 mer. 12 septembre 2018 14:17

shaie
Membre
Date d'inscription: 12 nov. 2016
Messages: 5

Je cherche à obtenir la surface fauchée par grille /année plus la moye

bonjour,

la suite le démontre.... mais peu expérimente en sql, je cherche à passer le résultat de cette rqte en colonne

SELECT
  grille_50_secteur_baie.id,
  cast (geom_de_gestion.annee_geom_gest as integer),
  geom_de_gestion.id_geom_gest,
ST_area(st_intersection(grille_50_secteur_baie.geom,geom_de_gestion.geom_pol_geom_gest))as surf
 
FROM
  gestion_agricole.grille_50_secteur_baie,
  gestion_agricole.geom_de_gestion

order by surf asc

J'ai bien essayé de faire un case when cast(geom_de_gestion.annee_geom_gest as integer)=2000 then sum(surf) end as "2000" même si ce n'est pas vraiment une somme,  mais ce n'est pas concluant et  pour plusieurs années impossible.

pour essayer d'etre le plus clair possible :

j'ai avec ma rqte de base :

ID         AN             ID_GE            SURF       
205      2000            4                      2400
205      2001            4                      1650
710      2000            5                        750
710      2002            5                      1200

et J’aimerai

ID         2000             2001          2002           
205      2400             1650             0
710        750                0              1200

si une âme charitable souhaite me dépanner.

merci d'avance

Hors ligne

 

#2 mer. 12 septembre 2018 15:04

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: Je cherche à obtenir la surface fauchée par grille /année plus la moye

Bonjour,

C'est une transposition de lignes en colonnes que vous voulez.
Il y a une extension pour cela dans PG: tablefunc (https://www.postgresql.org/docs/10/stat … efunc.html)

Ex avec la table bidon suivante:

Code:

create extension tablefunc;

drop table if exists test;
create table test (
  id int,
  an int,
  id_ge int,
  surf int
);

insert into test
values (205, 2000, 4, 2400),
       (205, 2001, 4, 1650),
       (710, 2000, 5, 750),
       (710, 2002, 5, 1200);


select * from crosstab(
    'select  id, an, surf from test ORDER BY id',
    'select distinct an from test order by 1') as ct (id int, _2000 int, _2001 int, _2002 int);

-- id     _2000    _2001  _2002
-- 205    2400    1650    null    
-- 710    750     null    1200

La premiere requete de crosstab doit renvoyer les catégories (les nouvelles colonnes) et les valeurs (surf ici). Il faut penser a la clause ORDER BY dans cette requête, c'est très important.
La deuxième requete renvoie la liste ordonnées des catégories, histoire que tablefunc sache quelles colonnes sont a générer

Nicolas

Dernière modification par Nicolas Ribot (mer. 12 septembre 2018 16:03)

Hors ligne

 

#3 mer. 12 septembre 2018 15:53

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: Je cherche à obtenir la surface fauchée par grille /année plus la moye

Sans tablefunc, on peut s'en sortir en SQL en utilisant des tableaux (array)
Ca permet de regrouper surfaces dans un tableau, ordonné par année.
On a alors dans chaque cellule du tableau les valeurs de surface pour les n années.
On peut alors construire n colonnes, correspondant aux n années.

Le trick, c'est de générer les valeurs manquantes de surface pour chaque année, si jamais il manque une année indiquant une surface:

Ce sont les deux premières CTE qui font ca: liste des années dispo par id, puis années manquantes.
On fait ensuite une UNION entre les valeurs manquantes, avec surf forcée a null, et les vraies données.
On peut alors regrouper dans un tableau, ordonné par année et sortir les colonnes:

* order by an ajouté*

Code:

with tmp as (
     select id, array_agg(an) as ans
     from test
     group by id
), tmp1 as (
  select distinct an from test
), tmp2 as (
    select t.id, t1.an, null as surf
    from tmp t
           join tmp1 t1 on not (t1.an = any(t.ans))
  UNION ALL
    select id, an, surf
  from test
), tmp3 as (
    select id, array_agg(surf order by an) as vals
    from tmp2
    group by 1
) select id, vals[1] as an1, vals[2] as an2, vals[3] as an3
from tmp3
order by id;

id  an1   an2   an3
205 2400  1650  null
710 750   null  1200

Nicolas

Dernière modification par Nicolas Ribot (mer. 12 septembre 2018 16:10)

Hors ligne

 

#4 jeu. 13 septembre 2018 12:47

shaie
Membre
Date d'inscription: 12 nov. 2016
Messages: 5

Re: Je cherche à obtenir la surface fauchée par grille /année plus la moye

Super ,

merci pour la réponse et la rapidité.

je teste tout ca

CDT

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |