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


-- 
Sent via pgsql-de-allgemein mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein