Abrufen

PostgreSQL: Zusammenfassende Statistiken und Fensterfunktionen

Michel Semaan

Data Scientist

Die vier Funktionen

Relativ

LAG(column, n) gibt Wert von column in Zeile n Zeilen vor aktueller Zeile zurück
LEAD(column, n) gibt Wert von column in Zeile n Zeilen nach aktueller Zeile zurück

Absolut

FIRST_VALUE(column) gibt ersten Wert in Tabelle oder Partition zurück
LAST_VALUE(column) gibt letzten Wert in Tabelle oder Partition zurück

LEAD

Abfrage

WITH Hosts AS (
  SELECT DISTINCT Year, City
  FROM Summer_Medals)

SELECT
  Year, City,
  LEAD(City, 1) OVER (ORDER BY Year ASC)
    AS Next_City,
  LEAD(City, 2) OVER (ORDER BY Year ASC)
    AS After_Next_City
FROM Hosts
ORDER BY Year ASC;

Ergebnis

| Year | City      | Next_City | After_Next_City |
|------|-----------|-----------|-----------------|
| 1896 | Athens    | Paris     | St Louis        |
| 1900 | Paris     | St Louis  | London          |
| 1904 | St Louis  | London    | Stockholm       |
| 1908 | London    | Stockholm | Antwerp         |
| 1912 | Stockholm | Antwerp   | Paris           |
| ...  | ...       | ...       | ...             |

FIRST_VALUE und LAST_VALUE

Abfrage

SELECT
  Year, City,
  FIRST_VALUE(City) OVER
    (ORDER BY Year ASC) AS First_City,
  LAST_VALUE(City) OVER (
   ORDER BY Year ASC
   RANGE BETWEEN
     UNBOUNDED PRECEDING AND
     UNBOUNDED FOLLOWING
  ) AS Last_City
FROM Hosts
ORDER BY Year ASC;

Ergebnis

| Year | City      | First_City | Last_City       |
|------|-----------|------------|-----------------|
| 1896 | Athens    | Athens     | London          |
| 1900 | Paris     | Athens     | London          |
| 1904 | St Louis  | Athens     | London          |
| 1908 | London    | Athens     | London          |
| 1912 | Stockholm | Athens     | London          |

Standardmäßig beginnt Fenster am Anfang der Tabelle oder Partition und endet bei aktueller Zeile
RANGE BETWEEN ... erweitert Fenster bis zum Ende der Tabelle oder Partition

Partitionierung mit LEAD

LEAD(Champion, 1) ohne PARTITION BY

| Year | Event        | Champion | Next_Champion |
|------|--------------|----------|---------------|
| 2004 | Discus Throw | LTU      | EST           |
| 2008 | Discus Throw | EST      | GER           |
| 2012 | Discus Throw | GER      | SWE           |
| 2004 | Triple Jump  | SWE      | POR           |
| 2008 | Triple Jump  | POR      | USA           |
| 2012 | Triple Jump  | USA      | null          |

LEAD(Champion, 1) mit PARTITION BY Event

| Year | Event        | Champion | Next_Champion |
|------|--------------|----------|---------------|
| 2004 | Discus Throw | LTU      | EST           |
| 2008 | Discus Throw | EST      | GER           |
| 2012 | Discus Throw | GER      | null          |
| 2004 | Triple Jump  | SWE      | POR           |
| 2008 | Triple Jump  | POR      | USA           |
| 2012 | Triple Jump  | USA      | null          |

Partitionierung mit FIRST_VALUE

FIRST_VALUE(Champion) ohne PARTITION BY Event

| Year | Event        | Champion | First_Champion |
|------|--------------|----------|----------------|
| 2004 | Discus Throw | LTU      | LTU            |
| 2008 | Discus Throw | EST      | LTU            |
| 2012 | Discus Throw | GER      | LTU            |
| 2004 | Triple Jump  | SWE      | LTU            |
| 2008 | Triple Jump  | POR      | LTU            |
| 2012 | Triple Jump  | USA      | LTU            |

FIRST_VALUE(Champion) mit PARTITION BY Event

| Year | Event        | Champion | First_Champion |
|------|--------------|----------|----------------|
| 2004 | Discus Throw | LTU      | LTU            |
| 2008 | Discus Throw | EST      | LTU            |
| 2012 | Discus Throw | GER      | LTU            |
| 2004 | Triple Jump  | SWE      | SWE            |
| 2008 | Triple Jump  | POR      | SWE            |
| 2012 | Triple Jump  | USA      | SWE            |

Lass uns üben!

PostgreSQL: Zusammenfassende Statistiken und Fensterfunktionen