Frames

PostgreSQL Summary Stats and Window Functions

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

Frame: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Without the frame, LAST_VALUE would return the row's value in the City column
By default, a frame starts at the beginning of a table or partition and ends at the current row

ROWS BETWEEN

ROWS BETWEEN [START] AND [FINISH]
- n PRECEDING: n rows before the current row
- CURRENT ROW: the current row
- n FOLLOWING: n rows after the current row

Examples

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

Source table

Query

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

Result

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

MAX without a frame

Query

WITH Russia_Medals AS (...)

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

Result

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

MAX with a frame

Query

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;

Result

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

Current and following rows

Query

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;

Result

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

Let's practice!

PostgreSQL Summary Stats and Window Functions