Discussion :

[LudoSIGMA]

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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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)

[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.

[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 ?

[lola06]

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

Code :

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

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
;

[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 :

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

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 :

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

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 :

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

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 ?

[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 :

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

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.

[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

[LudoSIGMA]

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 :

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

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 :

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

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 ?

[punkoff]

Votre group by n'est pas bon.

Vous utilisez MySql je suppose ?

Code :

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

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