Načítání

PostgreSQL Summary Stats and Window Functions

Michel Semaan

Data Scientist

Čtyři funkce

Relativní

LAG(column, n) vrátí hodnotu sloupce column z řádku n řádků před aktuálním
LEAD(column, n) vrátí hodnotu sloupce column z řádku n řádků za aktuálním

Absolutní

FIRST_VALUE(column) vrátí první hodnotu v tabulce nebo oddílu
LAST_VALUE(column) vrátí poslední hodnotu v tabulce nebo oddílu

LEAD

Dotaz

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;

Výsledek

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

Dotaz

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;

Výsledek

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

Ve výchozím nastavení okno začíná na začátku tabulky nebo oddílu a končí na aktuálním řádku
Klauzule RANGE BETWEEN ... rozšíří okno na konec tabulky nebo oddílu

Rozdělení s 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) s 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          |

Rozdělení s 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) s 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            |

Pojďme si procvičit!

PostgreSQL Summary Stats and Window Functions