Discussion :

[roblucas]

Bonjour,

Je souhaite diviser le résultat de deux requêtes pour obtenir une moyenne, et je ne comprends pas ou ça pêche dans la syntaxe. Idéalement, j'aimerais diviser tous les enregistrements des deux tables entières, mais je ne vois pas trop comment faire. Pour tester sur une colonne j'ai écrit la requête suivante, mais elle n'est pas fonctionnelle non plus :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH 
surface_totale AS (
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 GROUP BY annee ORDER BY annee'
)
AS ct("nomzone" varchar, "2006" decimal, "2007" decimal, "2008" decimal, "2009" decimal, "2010" decimal, "2011" decimal, "2012" decimal, "2013" decimal, "2014" decimal, "2015" decimal, "2016" decimal)
),
nombre_parcelle AS (
SELECT * FROM CROSSTAB(
'SELECT nomzone, annee, COUNT(*) FROM parcellaire_total_test WHERE type = ''abattis frais'' GROUP BY nomzone, annee ORDER BY 1,2',
'SELECT annee FROM parcellaire_total_test GROUP BY annee ORDER BY annee'
)
AS ct("nomzone" varchar, "2006" decimal, "2007" decimal, "2008" decimal, "2009" decimal, "2010" decimal, "2011" decimal, "2012" decimal, "2013" decimal, "2014" decimal, "2015" decimal, "2016" decimal)
)
SELECT nomzone, 2006.surface_totale/2006.nombre_parcelle FROM surface_totale, nombre_parcelle

Je précise qu'il y a des valeurs NULL dans le résultat des CROSSTABLE.
Merci d'avance pour vos avis éclairés !

[roblucas]

Bon après moultes recherches, j'ai trouvé ce qui bloquait :
1. Bien définir les types des colonnes (décimal pour une division me parait bien), en sortie mais aussi dans les CTE : ROUND(SUM(ST_AREA(geom))::numeric
Le ::type permet de définir le type de donnée en sortie de manière abrégée.
2. Utiliser la fonction ROUND est très important, sans elle les décimales vont bloquer le calcul (ou son affichage).
3. Utiliser les "doubles guillemets" pour définir les champs de sortie.
4. La jointure naturelle avec la clé d'équivalence.
Il reste juste à me trouver une manière de faire le même script avec des colonnes dynamiques...

Le code solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
WITH 
surface_totale AS (
SELECT * FROM CROSSTAB(
'SELECT nomzone, annee, ROUND(SUM(ST_AREA(geom))::numeric, 3) FROM parcellaire_total_test WHERE type = ''abattis frais'' GROUP BY nomzone, annee ORDER BY 1,2',
'SELECT annee FROM parcellaire_total_test GROUP BY annee ORDER BY annee'
)
AS ct("nomzone" varchar, "2006" decimal, "2007" decimal, "2008" decimal, "2009" decimal, "2010" decimal, "2011" decimal, "2012" decimal, "2013" decimal, "2014" decimal, "2015" decimal, "2016" decimal)
),
nombre_parcelle AS (
SELECT * FROM CROSSTAB(
'SELECT nomzone, annee, COUNT(*) FROM parcellaire_total_test WHERE type = ''abattis frais'' GROUP BY nomzone, annee ORDER BY 1,2',
'SELECT annee FROM parcellaire_total_test GROUP BY annee ORDER BY annee'
)
AS ct("nomzone" varchar, "2006" decimal, "2007" decimal, "2008" decimal, "2009" decimal, "2010" decimal, "2011" decimal, "2012" decimal, "2013" decimal, "2014" decimal, "2015" decimal, "2016" decimal)
)
SELECT surface_totale.nomzone, 
ROUND(((surface_totale."2006" / nombre_parcelle."2006")/10000)::numeric, 2) AS "2006",
ROUND(((surface_totale."2007" / nombre_parcelle."2007")/10000)::numeric, 2) AS "2007", 
ROUND(((surface_totale."2008" / nombre_parcelle."2008")/10000)::numeric, 2) AS "2008", 
ROUND(((surface_totale."2009" / nombre_parcelle."2009")/10000)::numeric, 2) AS "2009", 
ROUND(((surface_totale."2010" / nombre_parcelle."2010")/10000)::numeric, 2) AS "2010", 
ROUND(((surface_totale."2011" / nombre_parcelle."2011")/10000)::numeric, 2) AS "2011", 
ROUND(((surface_totale."2012" / nombre_parcelle."2012")/10000)::numeric, 2) AS "2012", 
ROUND(((surface_totale."2013" / nombre_parcelle."2013")/10000)::numeric, 2) AS "2013", 
ROUND(((surface_totale."2014" / nombre_parcelle."2014")/10000)::numeric, 2) AS "2014", 
ROUND(((surface_totale."2015" / nombre_parcelle."2015")/10000)::numeric, 2) AS "2015", 
ROUND(((surface_totale."2016" / nombre_parcelle."2016")/10000)::numeric, 2) AS "2016"
FROM surface_totale JOIN nombre_parcelle ON surface_totale.nomzone = nombre_parcelle.nomzone