Évolution de surfaces

Version imprimable

Bonjour à tous
Je vous expose mon problème, j'ai 3 tables :
une table parcelle (qui à priori n'est pas nécessaire dans mon problème)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE parcelle ( idparcelle character(20) NOT NULL, numeropar character(10), numerosection character(5), surface numeric(15,4), codeinsee character(5) NOT NULL, idclimat integer NOT NULL, the_geomparcelle geometry, idqgis serial NOT NULL, CONSTRAINT idqgis_pkey PRIMARY KEY (idqgis ), CONSTRAINT enforce_dims_the_geomparcelle CHECK (st_ndims(the_geomparcelle) = 2), CONSTRAINT enforce_geotype_the_geomparcelle CHECK (geometrytype(the_geomparcelle) = 'MULTIPOLYGON'::text OR the_geomparcelle IS NULL), CONSTRAINT enforce_srid_the_geomparcelle CHECK (st_srid(the_geomparcelle) = 2154) )
une table exploitation
Code:

1 2 3 4 5 6 CREATE TABLE exploitation ( idexploit bigint NOT NULL, nomexploit character(100), CONSTRAINT exploitation_pkey PRIMARY KEY (idexploit ) )
et une table concerneexploit qui fait le lien entre ces deux tables
Code:

1 2 3 4 5 6 7 8 CREATE TABLE concerneexploit ( idexploit bigint NOT NULL, idparcelle character(20) NOT NULL, dateexploit date NOT NULL, surfaceexploit numeric(15,4), CONSTRAINT concerneexploit_pkey PRIMARY KEY (idexploit , idparcelle , dateexploit ) )
Pour obtenir les intervalles de date j'ai utilisé une vue :
Code:

1 2 3 4 5 CREATE OR REPLACE VIEW intervalle_exploit_surf AS SELECT t1.idexploit, t1.idparcelle, t1.dateexploit AS datedebut, COALESCE(min(t2.dateexploit) - 1, '9999-12-31'::date) AS datefin, sum(t1.surfaceexploit) AS surfaceexploit FROM concerneexploit t1 LEFT JOIN concerneexploit t2 ON t1.idparcelle = t2.idparcelle AND t1.idexploit = t2.idexploit AND t2.dateexploit > t1.dateexploit GROUP BY t1.idparcelle, t1.idexploit, t1.dateexploit;
Je cherche à récupérer l'évolution (par exemple annuelle) des surfaces d'exploitation pour un type d'exploitation donné.

J'arrive à obtenir la surface pour un type d'exploitation donné et à une date donnée à l'aide de la requête :
Code:

1 2 3 4 5 6 7 SELECT SUM(c.surfaceexploit) FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit WHERE e.nomexploit = 'Vigne' AND '1952-01-01' BETWEEN c.datedebut AND c.datefin ;
Mais est il possible d'obtenir un résultat du type

Année Surface
1950 100
1951 200
1952 200
1953 124
etc

Je me dis que je pourrais toujours le faire par programmation en lançant des requêtes successives mais j'aurai voulu savoir si c'était possible directement en SQL, ou si mon modèle de données ne me permet pas de la faire.

J'espère que mon explication est compréhensible, n'hésitez pas à me demander des précisions

En vous remerciant

Ludovic

PS : j'utilise postgresql (pour le cas où cela ait une importance)

24/05/2012, 14h50
punkoff

bonjour,

pour celà il faudra utiliser :
- un group by http://sqlpro.developpez.com/cours/sqlaz/ensembles/
- recouper votre requête avec une table (ou vu, fonction...) contenant les années sur lesquelles vous voullez faire votre regroupement.
24/05/2012, 15h54
LudoSIGMA

Merci pour votre réponse
Je suis désolé, je n'arrive pas à comprendre comment je vais pouvoir inclure mes dates en tant que critère du coup ?

Il faut rajouter l'année dans le SELECT et le GROUP BY.
Imaginons que ta colonne s'appelle année, cela donnera :

Code:

1
2
3
4
5
6
7
8
SELECT année, SUM(c.surfaceexploit)
FROM intervalle_exploit_surf c
    JOIN exploitation e
        ON c.idexploit = e.idexploit
WHERE e.nomexploit = 'Vigne'
    AND '1952-01-01' BETWEEN c.datedebut AND c.datefin
GROUP BY année
;

24/05/2012, 16h23
LudoSIGMA
Merci lola06

En fait ce que je ne comprends pas c'est si comme le propose punkoff, je crée une table date en plus.
Code:

1 2 3 4 CREATE TABLE test_date ( date date NOT NULL, CONSTRAINT primary_key PRIMARY KEY (date )
Je ne comprends pas comment l'inclure dans ma requête, je ne peux pas faire de jointure avec cette table et de plus il faudrait que les valeurs de cette table se retrouvent comme critère dans ma requête à la place de la date manuelle ? dans

Code:

AND '1952-01-01' BETWEEN c.datedebut AND c.datefin

En fait je vois assez bien comment utiliser un group by, par exemple si dans ma requête je souhaitais avoir la surface par type d'exploitation, je ferai
Code:

1 2 3 4 5 6 SELECT e.nomexploit, SUM(c.surfaceexploit) FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit GROUP BY e.nomexploit ;
(ou quelque chose dans le genre)

Mais là je ne vois vraiment pas comment je peux faire, je ne dois pas saisir un truc.

lola06, quand tu parles du champs année, d'où provient il ?
24/05/2012, 16h36
lola06
Pour la colonne année je me suis appuyée sur ton exemple. Tu peux formater ta colonne datedebut ou datefin pour qu'elle renvoi l'année contenue. A toi de définir correctement tes critères.
Code:

1 2 3 4 5 SELECT to_char(datedebut,'RRRR'), SUM(c.surfaceexploit) FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit GROUP BY to_char(datedebut,'RRRR')
Pour la table supplémentaire, c'est comme une table calendrier qui te permet de répertorier toutes les années existantes (et pas seulement celle présente dans ta base de données). Il faut que tu fasses la jointure avec ta datedebut ou datefin.
24/05/2012, 16h41
punkoff

L'utilisation d'une table calendrier (ou émulation de celle-ci via des fonctions de votre SGBD) ne sera obligatoire que si vous voullez avoir un résultat pour une année non présente dans vos données.

Sinon une simple extraction de l'année de votre colonne date sera suffisant

Merci à tous les deux
Rien de tel qu'une nuit de sommeil pour mieux comprendre, à priori ça semble fonctionner, voici la requête qui me donne mes résultats :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT to_char(t.date,'YYYY') as annee,sum(c.surfaceexploit) as surface_vigne FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit FULL OUTER JOIN test_date t ON to_char(t.date,'RRRR')=to_char(c.datedebut,'RRRR') WHERE e.nomexploit = 'Vigne' AND t.date BETWEEN c.datedebut AND c.datefin GROUP BY t.date ORDER BY t.date ;
Et j'obtiens
année;surface
"1925";40.3114
"1930";40.7597
"1935";40.7597
"1940";40.7597
"1945";40.2282
"1950";40.2462
"1955";40.7499
"1960";40.9604
"1965";46.3609
"1970";47.5234

qui semble pouvoir correspondre au résultat attendu

Cependant, je dois avouer que je ne comprends pas très bien l'usage de to_char(t.date,'RRRR') et de ce que fait 'RRRR'
Mais si je remplace par
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT to_char(t.date,'YYYY') as annee,sum(c.surfaceexploit) as surface_vigne FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit FULL OUTER JOIN test_date t ON to_char(t.date,'YYYY')=to_char(c.datedebut,'YYYY') WHERE e.nomexploit = 'Vigne' AND t.date BETWEEN c.datedebut AND c.datefin GROUP BY t.date ORDER BY t.date ;
J'obtiens
annee;surface
"1930";0.0808

Auriez vous des explications ?

Votre group by n'est pas bon.

Vous utilisez MySql je suppose ?

Code:

1
2
3
4
5
6
7
8
9
10
11
 
SELECT to_char(t.date,'YYYY') AS annee, sum(c.surfaceexploit) AS surface_vigne
FROM intervalle_exploit_surf c
	JOIN exploitation e
		ON c.idexploit = e.idexploit
	FULL OUTER JOIN test_date t
		ON to_char(t.date,'RRRR')=to_char(c.datedebut,'RRRR')
WHERE e.nomexploit = 'Vigne'
	AND t.date BETWEEN c.datedebut AND c.datefin
GROUP BY to_char(t.date,'YYYY')
ORDER BY annnee

25/05/2012, 10h16
LudoSIGMA

Non, j'utilise postgresql
La modification du group by me donne des résultats identiques (par rapport à ceux que j'avais précédemment), ce qui me semble normal, puisque mon champ date ne contient que des dates uniques pour chaque année (mais que vous ne pouvez pas devinez effectivement)
25/05/2012, 10h30
punkoff

RRRR car lola est habituée à travailler avec Oracle.
PostgreSql le permet, mais il vaut mieux utiliser ce que la doc propose (YYYY en l'occurence).

Votre requête est simplifiable.

Sinon,

Citation:

L'utilisation d'une table calendrier (ou émulation de celle-ci via des fonctions de votre SGBD) ne sera obligatoire que si vous voullez avoir un résultat pour une année non présente dans vos données.

Est-ce le cas ou non ?

Citation:

J'obtiens
annee;surface
"1930";0.0808

Auriez vous des explications ?

Sans un jeu de donnée on ne peut pas deviner non.
25/05/2012, 10h31
Scriuiw

Citation:

Cependant, je dois avouer que je ne comprends pas très bien l'usage de to_char(t.date,'RRRR') et de ce que fait 'RRRR'

Le masque YYYY attend impérativement 4 chiffres pour désigner l'année tandis que le masque RRRR fonctionne aussi bien avec 2 ou 4 chiffres.

Par exemple, 04 donnera 2004 avec le masque RR et donnera l'an 4 avec YYYY

Sachant qu'avec le masque RR on a :
De 0 à 49 correspond de 2000 à 2049
De 50 à 99 correspond de 1950 à 1999
25/05/2012, 10h37
LudoSIGMA

Citation:

Envoyé par punkoff

RRRR car lola est habituée à travailler avec Oracle.
PostgreSql le permet, mais il vaut mieux utiliser ce que la doc propose (YYYY en l'occurence).

OK

Citation:

Envoyé par punkoff

Est-ce le cas ou non ?

Oui c'est bien le cas, puisque nous enregistrons finalement des intervalles et que nous voulons le résultat pour une date donnée lorsqu'elle est inclue dans cet intervalle.

Citation:

Envoyé par punkoff

Sans un jeu de donnée on ne peut pas deviner non.

Quel est le meilleur moyen pour faire passer un jeu de données ? Est ce qu'une instruction type COPY ... FROM stdin; pourrait convenir ?

Merci

oki,
donc
Code:

1 2 3 4 5 6 7 8 9 10 SELECT to_char(t.date,'YYYY') AS annee, sum(c.surfaceexploit) AS surface_vigne FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit inner JOIN test_date t ON t.date BETWEEN c.datedebut AND c.datefin WHERE e.nomexploit = 'Vigne' GROUP BY to_char(t.date,'YYYY') ORDER BY annnee
(A voir pour l'inner join dur votre table calendrier...)

Je ne sais pas ce qui est mieux, utilisez le to_char ou extract(year from t.date)

Sinon évitez d'appeller une colonne avec un mot réservé SQL (Date en l'occurence)

Pour les données, vous pourriez déjà commencer à bosser de votre côté.

Faites une extraction des lignes qui se réfèrent à l'année 1930 pour voir si vous avez le même résultat.

Merci, je regarde cette nouvelle requête

Citation:

Envoyé par punkoff

Sinon évitez d'appeller une colonne avec un mot réservé SQL (Date en l'occurence)

Ah oui effectivement

Citation:

Envoyé par punkoff

Pour les données, vous pourriez déjà commencer à bosser de votre côté.

Euh oui ça je l'avais bien fait, mais je n'ai sans doute pas été suffisamment clair, je vais me réexpliquer

La requête
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT to_char(t.date,'YYYY') AS annee,sum(c.surfaceexploit) AS surface_vigne FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit FULL OUTER JOIN test_date t ON to_char(t.date,'RRRR')=to_char(c.datedebut,'RRRR') WHERE e.nomexploit = 'Vigne' AND t.date BETWEEN c.datedebut AND c.datefin GROUP BY t.date ORDER BY t.date ;
Me donne bien le résultat attendu
Si je fais la requête
Code:

1 2 3 4 5 6 7 SELECT sum(c.surfaceexploit) AS surface_vigne FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit WHERE e.nomexploit = 'Vigne' AND '1930-01-01' BETWEEN c.datedebut AND c.datefin ;
J'obtiens bien exactement le même résultat pour l'année en question
C'est ce que je disais préalablement ça semble parfaitement fonctionner

L'interrogation que j'avais c'est pourquoi la requête ci dessus (la première) fonctionne bien, alors que la même mais en remplaçant RRRR par YYYY dans la jointure ne fonctionne plus
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT to_char(t.date,'YYYY') AS annee,sum(c.surfaceexploit) AS surface_vigne FROM intervalle_exploit_surf c JOIN exploitation e ON c.idexploit = e.idexploit FULL OUTER JOIN test_date t ON to_char(t.date,'YYYY')=to_char(c.datedebut,'YYYY') WHERE e.nomexploit = 'Vigne' AND t.date BETWEEN c.datedebut AND c.datefin GROUP BY t.date ORDER BY t.date ;
Me donne comme résultat
annee;surface
"1930";0.0808

et le résultat pour l'année 1930 n'est pas le bon mais en plus il ne me liste que l'année 1930 et pas les autres années présentes dans ma table.

Sinon je viens de tester votre requête et elle fonctionne parfaitement, je ne savais pas que je pouvais mettre
ON t.date BETWEEN c.datedebut AND c.datefin
comme critère de la jointure

25/05/2012, 13h28
punkoff
les deux requêtes sont différentes.

dans la dernière vous restreignez le résultat comme ceci :
- année de dateDebut = année de dateIntervale

C'est un outer join, du coup si il n'y a pas correspondance on aura dateIntervale à null.

Ensuite vient s'appliquer la condition de jointure qui transformera votre jointure externe en jointure interne :
Code:

1 2 3 WHERE e.nomexploit = 'Vigne' AND t.date BETWEEN c.datedebut AND c.datefin
Ici vous forcez le SGBD à ne sélectionner que les lignes qui ont dateIntervale comprise entre la date de début et de fin.

Mais on a vu plus haut que la jointure externe pourra vous renvoyer null pour certains cas.

Citation:

Envoyé par punkoff

les deux requêtes sont différentes.

Désolé de vous embêter avec ça mais c'est juste pour bien comprendre, on parle bien de ces deux requêtes :

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT to_char(t.date,'YYYY') AS annee,sum(c.surfaceexploit) AS surface_vigne
FROM intervalle_exploit_surf c
	JOIN exploitation e
		ON c.idexploit = e.idexploit
	FULL OUTER JOIN test_date t
		ON to_char(t.date,'RRRR')=to_char(c.datedebut,'RRRR')
WHERE e.nomexploit = 'Vigne'
	AND t.date BETWEEN c.datedebut AND c.datefin
GROUP BY t.date
ORDER BY t.date
;

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT to_char(t.date,'YYYY') AS annee,sum(c.surfaceexploit) AS surface_vigne
FROM intervalle_exploit_surf c
	JOIN exploitation e
		ON c.idexploit = e.idexploit
	FULL OUTER JOIN test_date t
		ON to_char(t.date,'YYYY')=to_char(c.datedebut,'YYYY')
WHERE e.nomexploit = 'Vigne'
	AND t.date BETWEEN c.datedebut AND c.datefin
GROUP BY t.date
ORDER BY t.date
;

Comme j'avais cru comprendre que YYYY était équivalent à RRRR, je ne comprends pas pourquoi j'obtiens un résultat différent

Ceci dit j'ai bien compris l'intérêt de la solution que vous me proposez et c'est celle ci que j'utiliserai au final, à savoir :

Code:

1
2
3
4
5
6
7
8
9
10
 
SELECT to_char(t.date,'YYYY') AS annee, sum(c.surfaceexploit) AS surface_vigne
FROM intervalle_exploit_surf c
	JOIN exploitation e
		ON c.idexploit = e.idexploit
	INNER JOIN test_date t
		ON t.date BETWEEN c.datedebut AND c.datefin
WHERE e.nomexploit = 'Vigne'
GROUP BY to_char(t.date,'YYYY')
ORDER BY annee

25/05/2012, 14h00
punkoff
Code:

1 2 3 4 5 6 7 select to_char('2002-05-20'::date, 'RRRR'); Résultat : -------------------- "RRRR"
Oué donc non postgresql n'accepte pas cette syntaxe :aie:
Code:

1 2 3 4 5 6 select to_char('2002-05-20'::date, 'YYYY'); Résultat : -------------------- "2002"
Sinon, pour mon poste plus haut, je comparais la requête avec la jointure sur l'année et sans.
25/05/2012, 14h06
LudoSIGMA

Ok
Bon ben c'est parfait je crois que j'ai tout ce dont j'avais besoin
Un grand merci à tous, cela va bien m'avancer.