Annonce
L'appel à participation est ouvert jusqu'au 19 janvier 2025!
Pages: 1
- Sujet précédent - Je cherche à obtenir la surface fauchée par grille /année plus la moye - Sujet suivant
#1 Wed 12 September 2018 14:17
- shaie
- Juste Inscrit !
- 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 Wed 12 September 2018 15:04
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
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 1200La 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 (Wed 12 September 2018 16:03)
Hors ligne
#3 Wed 12 September 2018 15:53
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
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 1200Nicolas
Dernière modification par Nicolas Ribot (Wed 12 September 2018 16:10)
Hors ligne
#4 Thu 13 September 2018 12:47
- shaie
- Juste Inscrit !
- 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
Pages: 1
- Sujet précédent - Je cherche à obtenir la surface fauchée par grille /année plus la moye - Sujet suivant
