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
; |
Partager