Pobieranie wartości

PostgreSQL – statystyki podsumowujące i funkcje okna

Michel Semaan

Data Scientist

Cztery funkcje

Względne

LAG(column, n) zwraca wartość column z wiersza n wierszy przed bieżącym
LEAD(column, n) zwraca wartość column z wiersza n wierszy po bieżącym

Bezwzględne

FIRST_VALUE(column) zwraca pierwszą wartość w tabeli lub partycji
LAST_VALUE(column) zwraca ostatnią wartość w tabeli lub partycji

LEAD

Zapytanie

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;

Wynik

| 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 i LAST_VALUE

Zapytanie

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;

Wynik

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

Domyślnie okno zaczyna się na początku tabeli lub partycji i kończy na bieżącym wierszu
Klauzula RANGE BETWEEN ... rozszerza okno do końca tabeli lub partycji

Partycjonowanie z LEAD

LEAD(Champion, 1) bez 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) z 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          |

Partycjonowanie z FIRST_VALUE

FIRST_VALUE(Champion) bez 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) z 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            |

Czas na ćwiczenia!

PostgreSQL – statystyki podsumowujące i funkcje okna