프레임

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              |

¡Vamos a practicar!

PostgreSQL 요약 통계와 윈도 함수