Re: Abfrage nach einer Reihe von Jahren

So,

ich habs rausbekommen, danke für eure Hinweise, hat auf jeden Fall geholfen:

1. Die Abfrage wie oben:

SELECT
mzb.land AS land,
mzb.samplsite_id AS ss_id,
EXTRACT (YEAR FROM date) as Probejahr
FROM
public.mzb
WHERE EXTRACT (YEAR FROM mzb.date) BETWEEN 1999 AND 2002
GROUP BY mzb.samplsite_id, mzb.land, Probejahr
ORDER BY ss_id
LIMIT 10

land | ss_id | probejahr
--------+--------+-----------
Bayern | 200005 | 2000
Bayern | 200005 | 2001
Bayern | 200010 | 2001
Bayern | 200010 | 1999
Bayern | 200020 | 2001
Bayern | 200020 | 1999
Bayern | 200020 | 2000
Bayern | 200027 | 1999
Bayern | 200036 | 2001
Bayern | 200036 | 2000
(10 rows)

Wir sehen, eine Probestelle, mehrere Jahre (200005) oder nur ein Jahr
(200027).

Diese Abfrage wird als Subquery benutzt, ich zähle die Jahre pro
Probestelle und filtere mit HAVING auf Zeilen welche nur 4 und somit
alle Probejahre enthalten:

Select Subtable.land,
Subtable.ss_id,
count ( distinct ( Subtable.Probejahr)) as AnzahlJahre
FROM
( SELECT
mzb.land AS land,
mzb.samplsite_id AS ss_id,
EXTRACT (YEAR FROM date) as Probejahr
FROM
public.mzb
WHERE EXTRACT (YEAR FROM mzb.date) BETWEEN 1999 AND 2002
GROUP BY mzb.samplsite_id, mzb.land, Probejahr ) AS Subtable

GROUP BY Subtable.ss_id, Subtable.land
HAVING count ( distinct ( Subtable.Probejahr)) = 4
ORDER BY AnzahlJahre DESC
LIMIT 10;

land | ss_id | anzahljahre
--------+--------+-------------
Bayern | 200071 | 4
Bayern | 200078 | 4
Bayern | 200088 | 4
Bayern | 200112 | 4
Bayern | 200323 | 4
Bayern | 200333 | 4
Bayern | 200334 | 4
Bayern | 200336 | 4
Bayern | 200337 | 4
Bayern | 200050 | 4
(10 rows)

@ Andreas, deine Variante ist interessant und funktioniert auch.

SELECT mzb.land AS land,
mzb.samplsite_id AS ss_id,
array_agg ( distinct (EXTRACT (YEAR FROM date))) as Probejahre
FROM
public.mzb
WHERE EXTRACT (YEAR FROM mzb.date) BETWEEN 1999 AND 2002
GROUP BY mzb.samplsite_id, mzb.land
HAVING array[1999, 2000, 2001, 2002]::float8[] = array_agg ( distinct
(EXTRACT (YEAR FROM date)))
LIMIT 10

@> contains zu verwenden ist in dem Fall nicht sinnvoll, da lediglich
getestet wird ob aus array[1999, 2000, 2001, 2002] Elemente in array_agg
( distinct (EXTRACT (YEAR FROM date))) entahlten sind, was ja für jede
Zeile stimmt, also nochmal das BETWEEN wiederholen.

Obige Abfrage mit:

HAVING array[1999, 2000, 2001, 2002]::float8[] @> array_agg ( distinct
(EXTRACT (YEAR FROM date)))

land | ss_id | probejahre
--------+--------+-----------------------
Bayern | 200005 | {2000,2001}
Bayern | 200010 | {1999,2001}
Bayern | 200020 | {1999,2000,2001}
Bayern | 200027 | {1999}
Bayern | 200036 | {1999,2000,2001}
Bayern | 200050 | {1999,2000,2001,2002}
Bayern | 200068 | {1999,2001}
Bayern | 200071 | {1999,2000,2001,2002}
Bayern | 200078 | {1999,2000,2001,2002}
Bayern | 200088 | {1999,2000,2001,2002}
(10 rows)

Jo, herzlichen Dank euch und beste Grüße,

Gunnar

--
Gunnar Oehmichen
Quantitative Landscape Ecology
Institute for Environmental Sciences
University Koblenz-Landau
Fortstrasse 7
76829 Landau
Germany
http://www.uni-koblenz-landau.de/landau/fb7/umweltwissenschaften/landscape-ecology/Staff/gunnaroehmichen