Фрейми

Підсумкова статистика та віконні функції в PostgreSQL

Michel Semaan

Data Scientist

Мотивація

LAST_VALUE

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

Фрейм: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Без фрейму LAST_VALUE поверне значення поточного рядка в стовпці City
Типово фрейм починається з початку таблиці або секції та закінчується на поточному рядку

ROWS BETWEEN

ROWS BETWEEN [START] AND [FINISH]
- n PRECEDING: n рядків до поточного
- CURRENT ROW: поточний рядок
- n FOLLOWING: n рядків після поточного

Приклади

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

Початкова таблиця

Запит

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

Результат

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

MAX без фрейму

Запит

WITH Russia_Medals AS (...)

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

Результат

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

MAX із фреймом

Запит

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;

Результат

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

Поточний і наступний рядки

Запит

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;

Результат

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

Давайте потренуємось!

Підсумкова статистика та віконні функції в PostgreSQL