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

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

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

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 ?

J'obtiens
annee;surface
"1930";0.0808

Auriez vous des explications ?

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

[Scriuiw]

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

[LudoSIGMA]

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

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.

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

[punkoff]

oki,
donc

Code :

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

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.

[LudoSIGMA]

Merci, je regarde cette nouvelle requête

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

Ah oui effectivement

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 :

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
;

Me donne bien le résultat attendu
Si je fais la requête

Code :

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

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 :

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,'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

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

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

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.

[LudoSIGMA]

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 :

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

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,'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 :

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

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

[punkoff]

Code :

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

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

Code :

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

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.

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