Ramar

PostgreSQL – Sammanfattande statistik och fönsterfunktioner

Michel Semaan

Data Scientist

Motivation

LAST_VALUE

LAST_VALUE(City) OVER (
 ORDER BY Year ASC
 RANGE BETWEEN
   UNBOUNDED PRECEDING AND
   UNBOUNDED FOLLOWING
) AS Last_City

Ram: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Utan ramen skulle LAST_VALUE returnera radens eget värde i kolumnen City
Som standard börjar en ram vid tabellens eller partitionens början och slutar vid aktuell rad

ROWS BETWEEN

ROWS BETWEEN [START] AND [FINISH]
- n PRECEDING: n rader före aktuell rad
- CURRENT ROW: aktuell rad
- n FOLLOWING: n rader efter aktuell rad

Exempel

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING

Källtabell

Fråga

SELECT
  Year, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
  Country = 'RUS'
  AND Medal = 'Gold'
GROUP BY Year
ORDER BY Year ASC;

Resultat

| Year | Medals |
|------|--------|
| 1996 | 36     |
| 2000 | 66     |
| 2004 | 47     |
| 2008 | 43     |
| 2012 | 47     |

MAX utan ram

Fråga

WITH Russia_Medals AS (...)

SELECT
  Year, Medals,
  MAX(Medals)
    OVER (ORDER BY Year ASC) AS Max_Medals
FROM Russia_Medals
ORDER BY Year ASC;

Resultat

| Year | Medals | Max_Medals |
|------|--------|------------|
| 1996 | 36     | 36         |
| 2000 | 66     | 66         |
| 2004 | 47     | 66         |
| 2008 | 43     | 66         |
| 2012 | 47     | 66         |

MAX med ram

Fråga

WITH Russia_Medals AS (...)

SELECT
  Year, Medals,
  MAX(Medals)
    OVER (ORDER BY Year ASC) AS Max_Medals,
  MAX(Medals)
    OVER (ORDER BY Year ASC
          ROWS BETWEEN
          1 PRECEDING AND CURRENT ROW)
    AS Max_Medals_Last
FROM Russia_Medals
ORDER BY Year ASC;

Resultat

| Year | Medals | Max_Medals | Max_Medals_Last |
|------|--------|------------|-----------------|
| 1996 | 36     | 36         | 36              |
| 2000 | 66     | 66         | 66              |
| 2004 | 47     | 66         | 66              |
| 2008 | 43     | 66         | 47              |
| 2012 | 47     | 66         | 47              |

Aktuell och efterföljande rader

Fråga

WITH Russia_Medals AS (...)

SELECT
  Year, Medals,
  MAX(Medals)
    OVER (ORDER BY Year ASC
          ROWS BETWEEN
          CURRENT ROW AND 1 FOLLOWING)
    AS Max_Medals_Next
FROM Russia_Medals
ORDER BY Year ASC;

Resultat

| Year | Medals | Max_Medals_Next |
|------|--------|-----------------|
| 1996 | 36     | 66              |
| 2000 | 66     | 66              |
| 2004 | 47     | 47              |
| 2008 | 43     | 47              |
| 2012 | 47     | 47              |

Nu kör vi en övning!

PostgreSQL – Sammanfattande statistik och fönsterfunktioner