Discussion :

[SylvainM]

Bonjour à tous,
J'ai hésité à poster dans la section PostGreSQL de Developpez, mais je me dis que mon problème est lié au SQL, et non forcément à PostGreSQL. Désolé si je me suis trompé de rubrique.
Je dispose d'une table d'observations d'espèces, avec (pour simplifier), un champ date_obs, un champ code_sps.
Chaque année, différentes espèces sont observées. En faisant un "count(distinct code_sps)", groupé par année ("extract(year from date_obs)"), j'arrive à avoir le nombre d'espèces observées par année.
Mais, je voudrais maintenant calculer le nombre d'espèces observées cumulées depuis le début, mais sans doublons. C'est à dire que pour chaque année, je calcule le nombre d'espèces observées durant l'ensemble des années précédentes.
Ma requête ci-dessous additionne le nombre d'espèces des années précédentes, mais du coup, il y a des doublons.

Code :

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

1
2
3
4
5
6
7
 
SELECT	extract(year from date_obs)::integer as annee,
	count(distinct code_sps)::integer as sps_annee,
	SUM(COUNT(distinct code_sps)) OVER(ORDER BY extract(year from date_obs)::integer)::integer as sps_cumul
FROM	obervations
GROUP BY annee
ORDER BY annee

L'exemple ci dessous montre le résultat faux, et un résultat fictif correct (qu'il n'est pas possible de déduire sans les données) :

Code :

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

1
2
3
4
5
annee   sps_annee   sps_cumul_faux   sps_cumul_ok
2000         20             20              20
2001         18             38              25
2002          5             43              28
2003         10             53              30

En espérant être clair, je vous remercie d'avance pour votre aide !

Sylvain M.

[al1_24]

Pourquoi pas tout simplement :

Code :

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

1
2
3
4
5
6
SELECT	extract(year from date_obs) as annee,
	count(distinct code_sps) as sps_annee,
	COUNT(distinct code_sps) OVER(ORDER BY extract(year from date_obs)) as sps_cumul
FROM	obervations
GROUP BY annee
ORDER BY annee

[SylvainM]

Merci Alain de te pencher sur mon problème
Sur le coup, je me suis dit "mais bien sûr ! pourquoi donc cette somme qui ne sert à rien !" (je l'avais oublié d'une précédente requête)
Mais en la retirant, j'ai le message d'erreur suivant :

Code :

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

1
2
3
4
5
6
7
8
9
ERREUR:  DISTINCT n'est pas implémenté pour des fonctions window
LINE 3:  COUNT(distinct code_sps) OVER(ORDER BY extract(year from date...
         ^
 
********** Erreur **********
 
ERREUR: DISTINCT n'est pas implémenté pour des fonctions window
État SQL :0A000
Caractère : 104

Une alternative ?

[al1_24]

Une alternative : identifier les valeurs distinctes et faire les regroupements ensuite.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH    cnt
    AS  (   SELECT  DISTINCT
                    EXTRACT(YEAR FROM date_obs) AS annee
                ,   code_sps
            FROM	obervations
        )
SELECT  annee
    ,   COUNT(code_sps)                         AS sps_annee
    ,   COUNT(code_sps) OVER (ORDER BY annee)   AS sps_cumul
FROM	cnt
GROUP BY annee
ORDER BY annee
;

[SylvainM]

Merci encore Alain,
Malheureusement, ça ne fonctionne pas :
- j'ai d'abord le message d'erreur "ERREUR: la colonne « cnt.code_sps » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat"
- et si j'ajoute un sum() ou un max(), les résultats sont faux...

Je vais essayer d'illustrer avec une table fictive, et des "code_sps" au format texte ([A,B,C,D]).

Code :

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

1
2
3
4
5
6
7
8
9
10
date_obs		code_sps
01/06/2010		A
01/06/2010		B
01/06/2010		A
01/06/2011		A
01/06/2012		C
01/06/2013		A
01/06/2013		B
01/06/2013		C
01/06/2014		D

Code :

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

1
2
3
4
5
6
annee	sps_annee	sps_cumul	liste_sps_annee		liste_sps_cumul
2010	2		2		[A,B]			[A,B]
2011	1		2		[A]			[A,B]
2012	1		3		[C]			[A,B,C]
2013	3		3		[A,B,C]			[A,B,C]
2014	1		4		[D]			[A,B,C,D]

J'ai rajouté 2 colonnes avec les listes d'espèces.
En bonus, j'aimerai bien ajouter une colonne avec "liste_sps_nouvelles" pour chaque année ! (mais ça, ce serait la cerise sur le gâteau ! )

[aieeeuuuuu]

Bonjour,

Je ne vois pas d'autres solution qu'avec une inéquijointure externe... avec une forte volumétrie, ça risque de piquer un peu, une indexation sera sans doute nécessaire.

Pour faire simple, je n'ai gardé que la composante année de la date, je vous laisse adapter avec les fonctions dates de PG pour extraire l'année de vos dates (attention a ce que les index restent utilisables...)

Je m'appuie sur une table calendrier, que je crée à la volée, mais une table en dur sera peut-être préférable, c'est là aussi pour l'exemple :

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
 
WITH observations(date_obs, code_sps) AS (
		  SELECT 2010			 , 'A'
    UNION ALL SELECT 2010		, 'B'
    UNION ALL SELECT 2010		, 'A'
    UNION ALL SELECT 2011		, 'A'
    UNION ALL SELECT 2012		, 'C'
    UNION ALL SELECT 2013		, 'A'
    UNION ALL SELECT 2013		, 'B'
    UNION ALL SELECT 2013		, 'C'
    UNION ALL SELECT 2014		, 'D'
),
Calendrier(annee) AS (
    SELECT 2010
    UNION ALL SELECT 2011
    UNION ALL SELECT 2012
    UNION ALL SELECT 2013
    UNION ALL SELECT 2014
)
SELECT 
	   C.annee
    ,   COUNT(DISTINCT O.code_sps) AS NbObsDistinctes
FROM Calendrier AS C
LEFT JOIN observations AS O
    ON O.date_obs <= C.annee
GROUP BY C.annee
;

Voilà pour le gâteau...

Pour la cerise, voici une piste toujours avec les même remarques
Dans l'exemple, je comptabilise les "nouvelles" espèces observées, il faudra sans doute utiliser array_agg pour obtenir le résultat souhaité, mais je n'ai pas PG sous la main
Avec le regroupement dans la sous requête, ça pourrait même être plus efficace que la requête précédente... à tester

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
29
30
31
32
33
 
WITH observations(date_obs, code_sps) AS (
		  SELECT 2010			 , 'A'
    UNION ALL SELECT 2010		, 'B'
    UNION ALL SELECT 2010		, 'A'
    UNION ALL SELECT 2011		, 'A'
    UNION ALL SELECT 2012		, 'C'
    UNION ALL SELECT 2013		, 'A'
    UNION ALL SELECT 2013		, 'B'
    UNION ALL SELECT 2013		, 'C'
    UNION ALL SELECT 2014		, 'D'
),
Calendrier(annee) AS (
    SELECT 2010
    UNION ALL SELECT 2011
    UNION ALL SELECT 2012
    UNION ALL SELECT 2013
    UNION ALL SELECT 2014
)
SELECT 
	   C.annee
    ,   COUNT(DISTINCT O.code_sps) AS NbObsDistinctes
    ,   COUNT(DISTINCT CASE WHEN O.date_obs = C.annee THEN code_sps ELSE NULL END) AS NbNouvelleObservations
FROM Calendrier AS C
LEFT JOIN (
	   SELECT code_sps, MIN(date_obs) AS date_obs 
	   FROM observations AS O
 
	   GROUP BY O.code_sps
    ) AS O
    ON O.date_obs <= C.annee
GROUP BY C.annee
;

[SylvainM]

Merci aieeeuuuuu,

Pour l'instant, je n'ai pas encore bien compris le fonctionnement de ta proposition de requête, mais je crois qu'il y a un malentendu : l'identifiant des espèces ("code_sps" dans l'exemple simplifié donné) correspond à une clé étrangère d'une table de plusieurs milliers d'espèces. Elle est au format numérique (integer), mais là pour plus de lisibilité, j'ai mis A,B,C,D...
De même, dans l'exemple, j'ai mis uniquement quelques années, mais la base de données remonte à plusieurs décennies (et même bien plus en fait, puisqu'il y a des données historiques).

Du coup, je crois pas (sauf incompréhension de ma part) que ta proposition puisse répondre à mon besoin, puisque la requête ferait du coup quelques milliers de lignes...

Si c'est impossible en SQL pur, je pense qu'il va falloir que je créé une fonction avec le langage "plpgsql" de PostGre. Ce sera l'occasion pour moi de le découvrir !

A+

Sylvain

[SylvainM]

Ah, désolé, je crois que je commence à comprendre : les tables "union all" sont juste des exemple pour créer les tables virtuelles dans la requête d'exemple !?
Je me penche donc de manière plus approfondie sur le code !!!

[Edition]
Et oui !!!! Ça marche !!!!
SUPER !!!
Pas encore tout compris, mais adapté avec mes champs et données, le résultat est correct !!!!
MERCI BEAUCOUP !!
[/Edition]