Discussion :

[SylvainM]

Salut à vous,

Je bloque sur une requête SQL qui de premier abord me semblait "basique".
Je travaille sur une base SQLite/Spatialite, mais je peux aussi la faire sur une base PostGreSQL/PostGis, si jamais c'était plus simple.

Voici la table d'origine très simplifiée : une table d'observations d'espèces, avec notamment un champ date d'observation (dateobs), et un champ espèce (espece) :

dateobs	espece
01/01/2024	Oiseau bleu
01/01/2024	Aigle noir
02/01/2024	Oiseau bleu
02/01/2024	Flamand rose
03/01/2024	Rougegorge

Ce que je souhaiterais, c'est avoir le nombre cumulé d'espèces différentes, observées à chaque date, sous cette forme :

dateobs	cumul_nb_especes	Liste_especes
01/01/2024	2	Oiseau bleu + Aigle noir
02/01/2024	3	Oiseau bleu + Aigle noir + Flamand rose
03/01/2024	4	Oiseau bleu + Aigle noir + Flamand rose + Rougegorge

(j'ai ajouté un champ "liste Espèce", juste pour illustrer, car il ne sera pas calculé, la table ayant plusieurs milliers d'espèces)

Est-ce que quelqu'un ici arriverait à écrire la requête SQL qui ferait cette synthèse ?

J'ai tenté de demander à un célèbre agent conversationnel, et sa proposition n'a pas fonctionné (mais j'ai peut-être mal formulé la question).
Voici sa proposition :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH CumulativeSpecies AS (
    SELECT
        dateobs,
        espece,
        ROW_NUMBER() OVER (PARTITION BY espece ORDER BY dateobs) AS species_first_seen
    FROM matable)
 
SELECT
    datebobs,
    COUNT(DISTINCT espece) AS cumulative_species_count
FROM CumulativeSpecies
WHERE species_first_seen = 1
GROUP BY dateobs
ORDER BY dateobs;

Un grand merci d'avance !!!

Sylvain M.

[Séb.]

Il y a plusieurs approches possibles.

Avec une fonction fenêtrée et en passant par un résultat intermédiaire (les COUNT(DISTINCT) fenêtrés n'étant pas supportés) :

with
	dataset (dateobs, espece) as (
		values
			('01/01/2024', 'Oiseau bleu'),
			('01/01/2024', 'Aigle noir'),
			('02/01/2024', 'Oiseau bleu'),
			('02/01/2024', 'Flamand rose'),
			('03/01/2024', 'Rougegorge')
	),
	rapport (espece, premiere_dateobs) as (
		select all espece, min(dateobs)
		from dataset
		group by espece
	)
select all 
	premiere_dateobs as dateobs, 
	sum(count(*)) over (order by premiere_dateobs asc) as cumul_nb_especes
from rapport
group by 1
order by 1 asc;

Avec une sous-requête corrélée dans le SELECT :

with dataset (dateobs, espece) as (
	values
		('01/01/2024', 'Oiseau bleu'),
		('01/01/2024', 'Aigle noir'),
		('02/01/2024', 'Oiseau bleu'),
		('02/01/2024', 'Flamand rose'),
		('03/01/2024', 'Rougegorge')
)
select distinct
	dateobs,
	(select count(distinct espece) from dataset where dateobs <= d.dateobs) as cumul_nb_especes
from dataset as d
order by 1 asc;

Tu dois aussi pouvoir le faire avec une auto-jointure.

Testées OK sur SQLite et PostgreSQL.

Bien sûr dans la pratique il faudra que la colonne dateobs soit bien du type DATE. J'ai laissé les JJ/MM/AAAA par pure flemme

[SylvainM]

Super : merci beaucoup Séb.

Les 2 requêtes ont fonctionné sur ma base de données réelle, mais la 1ere s’exécute quasi instantanément, alors que la 2e met près de 20 secondes (sur un petit jeu de données de 2500 observations).
Du coup, je vais me pencher sur la 1ere pour essayer de la comprendre.

Même si ça fait des années que je pratique le SQL, je reste débutant, et il y a 2 notions que je ne comprends pas ici. Je vais faire des recherches, mais au cas où, si tu veux m'expliquer les principes du select all et du group by 1 order by 1 asc; ?

[SylvainM]

Bon, je me suis fait aider (toujours par le célèbre "agent"), et j'ai compris les notions.
Du coup, j'aurais écris comme ceci :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
with
	rapport (espece, premiere_dateobs) as (
		select espece, min(dateobs)
		from matable
		group by espece
	)
select
	premiere_dateobs as dateobs, 
	sum(count(*)) over (order by premiere_dateobs asc) as cumul_nb_especes
from rapport
group by dateobs
order by dateobs asc;

Merci encore !

[Séb.]

si tu veux m'expliquer les principes du select all

SELECT ALL est le SELECT par défaut => Donne toutes les lignes, doublons compris
L'autre est SELECT DISTINCT => Donne les lignes dédoublonnées

et du group by 1 order by 1 asc; ?

C'est l'indicateur ordinal de colonne, ça évite de mentionner les colonnes en toutes lettres
=> Ici on groupe sur la 1re colonne du SELECT
=> Ici on trie sur la 1re colonne du SELECT
Je crois que cette syntaxe est obsolète selon ANSI-SQL

[SQLpro]

GROUP BY 1 n'existe pas en SQL... Et c'est très dangereux...

ORDER BY 1 existe bien et c'est tout aussi dangereux...

A +

[SylvainM]

Merci @SQLpro pour ces précisions/avertissements 😃