Fensterfunktionen

Dummy-Tabelle - WWWW

Aggregationen 1

SELECT
  yyyy,
  mm,
  v1,
  sum(v1) OVER (PARTITION BY yyyy ORDER BY mm) AS kumuliert,
  sum(v1) OVER (PARTITION BY yyyy) AS jahr,
  sum(v1) OVER (ORDER BY yyyy, mm) AS alles_kumuliert,
  sum(v1) OVER () AS alles
FROM WWWW
WHERE yyyy in ('2020', '2021') AND mm in ('01', '02', '03', '04')
ORDER BY yyyy, mm;

Fensterumfang

PARTITION BY:
gesamte Partition
ORDER BY:
von Beginn bis zur aktuellen Zeile (bis zum letzten Peer)
PARTITION BY ORDER BY:
von Beginn der Partition bis zur aktuellen Zeile in der Partition (bis zum letzten Peer)

Aggregationen 2

Window-Funktionen in Berechnungen,
hier prozentualer Anteil

SELECT
  yyyy,
  mm,
  v1,
  sum(v1) OVER (PARTITION BY yyyy) AS jahr,
  round((v1 / sum(v1) OVER (PARTITION BY yyyy)) * 100, 2) 
    AS prozent
FROM WWWW
WHERE yyyy in ('2021')
ORDER BY yyyy, mm;

Aggregationen 3

Sortierung über v2

SELECT
  yyyy,
  mm,
  v2,
  sum(v2) OVER (ORDER BY v2) AS vv2
FROM WWWW
WHERE yyyy in ('2021')
ORDER BY v2;

Rangbildung 1

SELECT
  yyyy,
  mm,
  v1,
  rank() OVER (ORDER BY v1 desc) AS rang,
  dense_rank() OVER (ORDER BY v1 desc) AS dichter_rang,
  round(percent_rank() OVER (ORDER BY v1 desc), 2) AS prozent_rang,
  row_number() OVER (ORDER BY v1 desc) AS zeilennummer
FROM WWWW
WHERE yyyy in ('2021');

Rangbildung 2

SELECT
  yyyy,
  mm,
  v1,
  rank() OVER (ORDER BY v1 desc) AS rang,
  dense_rank() OVER (ORDER BY v1 desc) AS dichter_rang,
  round(percent_rank() OVER (ORDER BY v1 desc), 2) AS prozent_rang,
  row_number() OVER (ORDER BY v1 desc) AS zeilennummer
FROM WWWW
WHERE yyyy in ('2021')
ORDER BY yyyy, mm;

Andere Sortierung in der Ausgabe als in der Rangbildung

Rangbildung 3

SELECT
  yyyy,
  mm,
  v1,
  rank() OVER (PARTITION BY yyyy ORDER BY v1 desc) AS rang,
  dense_rank() OVER (PARTITION BY yyyy ORDER BY v1 desc) AS dichter_rang,
  row_number() OVER (PARTITION BY yyyy ORDER BY v1 desc) AS zeilennummer
FROM WWWW
WHERE yyyy in ('2020', '2021');

Rangbildung pro Partition

Gleitende Durchschnitte

SELECT
  yyyy,
  mm,
  v1,
  round(avg(v1) OVER (
    ORDER BY mm ROWS BETWEEN 
      1 preceding AND 
      1 following), 
    2)
  AS jahr
FROM WWWW
WHERE yyyy in ('2019')
ORDER BY yyyy, mm;

Einschränkung des Fensters

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW

offset FOLLOWING
UNBOUNDED FOLLOWING

Positionierung 1

SELECT
  mm,
  v1,
  lag(mm) OVER (ORDER BY mm) AS eins_vorher,
  lag(mm, 4) OVER (ORDER BY mm) AS vier_vorher,
  first_value(mm) OVER (ORDER BY mm) AS erster,
  last_value(mm) OVER (ORDER BY mm ROWS BETWEEN unbounded preceding AND unbounded following) AS letzter,
  nth_value(mm, 3) OVER (ORDER BY mm ROWS BETWEEN unbounded preceding AND unbounded following) AS dritter
FROM WWWW
WHERE yyyy in ('2021')
ORDER BY mm;

Positionierung 2

SELECT
  mm,
  v1,
  lag(v1) OVER (ORDER BY mm) AS eins_vorher,
  lag(v1, 4) OVER (ORDER BY mm) AS vier_vorher,
  first_value(v1) OVER (ORDER BY mm) AS erster,
  last_value(v1) OVER (ORDER BY mm ROWS BETWEEN unbounded preceding AND unbounded following) AS letzter,
  nth_value(v1, 3) OVER (ORDER BY mm ROWS BETWEEN unbounded preceding AND unbounded following) AS dritter
FROM WWWW
WHERE yyyy in ('2021')
ORDER BY mm;

Spalte v1 in der Ausgabe

Positionierung 3

SELECT
  yyyy,
  mm,
  lag(yyyy || '-' || mm) OVER (PARTITION BY mm ORDER BY yyyy, mm) AS ein_jahr_vorher,
  lag(yyyy || '-' || mm, 2) OVER (PARTITION BY mm ORDER BY yyyy, mm) AS zwei_jahre_vorher
FROM WWWW
WHERE mm in ('01', '02', '08')
ORDER BY yyyy, mm;

Year over Year

Verteilungen 1

SELECT
  yyyy,
  mm,
  v3,
  row_number() OVER (ORDER BY v3) AS nr,
  ntile(6) OVER (ORDER BY v3) AS bucket,
  round(cume_dist() OVER (ORDER BY v3), 2) 
    AS verteilung
FROM WWWW
WHERE yyyy in ('2020', '2021')
ORDER BY v3;

Verteilungen 2

Verteilungen pro Partition (Jahr)

SELECT
  yyyy,
  mm,
  v3,
  row_number() OVER (PARTITION BY yyyy ORDER BY v3) 
    AS nr,
  ntile(6) OVER (PARTITION BY yyyy ORDER BY v3) 
    AS bucket,
  round(
    cume_dist() OVER (PARTITION BY yyyy ORDER BY v3),
    2
    ) AS verteilung
FROM WWWW
WHERE yyyy in ('2020', '2021')
ORDER BY yyyy, v3;