[PostgreSQL] 윈도우 last_value 프레임 함정 — "파티션 최종값"이 자기 값으로 나올 때

설치·접속: PostgreSQL 설치와 접속

부제: 부서별 최고 급여를 last_value로 뽑았더니 매 행이 자기 급여를 반환할 때

-- 부서별 급여를 오름차순 정렬하고, 그 파티션의 "마지막(=최고) 급여"를 붙이려는 의도
SELECT dept, emp, salary,
       last_value(salary) OVER (PARTITION BY dept ORDER BY salary) AS wrong_last
FROM sal ORDER BY dept, salary;

 dept  | emp | salary | wrong_last
-------+-----+--------+------------
 dev   | Fay |   4800 |       4800
 dev   | Dan |   5500 |       5500
 dev   | Eve |   7000 |       7000
 sales | Bob |   4200 |       4200
 sales | Ana |   5000 |       5000
 sales | Cai |   6100 |       6100
(6 rows)

wrong_last가 매 행 자기 salary를 그대로 낸다. 원인은 프레임(frame)이다. ORDER BY가 붙으면 윈도우의 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — "파티션 시작부터 현재 행까지(동점 포함)"다. last_value는 이 프레임의 마지막 행을 보는데, 그 마지막이 곧 현재 행이라 자기 값이 나온다. first_value가 멀쩡해 보이는 것과 대비돼 더 헷갈린다(프레임 시작은 항상 파티션 맨 앞이니까). 공식 문서가 명시적으로 경고하는 대표 함정이다.

교정은 프레임을 파티션 전체로 넓히는 것 하나뿐이다. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 명시하면 last_value가 진짜 마지막 행(정렬상 최댓값)을 본다.

SELECT dept, emp, salary,
       last_value(salary) OVER (PARTITION BY dept ORDER BY salary
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS real_last
FROM sal ORDER BY dept, salary;

 dept  | emp | salary | real_last
-------+-----+--------+-----------
 dev   | Fay |   4800 |      7000
 dev   | Dan |   5500 |      7000
 dev   | Eve |   7000 |      7000
 sales | Bob |   4200 |      6100
 sales | Ana |   5000 |      6100
 sales | Cai |   6100 |      6100
(6 rows)

같은 프레임 원리가 sum에서는 정반대로 유용하게 쓰인다. OVER()(프레임 없음)는 파티션 전체 합, OVER (ORDER BY ...)는 정렬 순서대로 시작~현재 행까지의 누적합(running total)이 된다 — ORDER BY 하나가 프레임을 "현재 행까지"로 바꾸기 때문이다.

SELECT emp, salary,
       sum(salary) OVER ()                AS total_all,
       sum(salary) OVER (ORDER BY salary) AS running
FROM sal WHERE dept='dev' ORDER BY salary;

 emp | salary | total_all | running
-----+--------+-----------+---------
 Fay |   4800 |     17300 |    4800
 Dan |   5500 |     17300 |   10300
 Eve |   7000 |     17300 |   17300
(3 rows)

total_all은 세 행 모두 17300(전체 합), running은 4800 → 10300 → 17300으로 쌓인다. 일자별 누적 매출 그래프를 그리려다 OVER()로 써서 "전부 같은 총합"이 나오는 실수가 여기서 나온다. last_value가 이상하면 프레임을 UNBOUNDED FOLLOWING까지 넓히고, 누적이 안 되면 ORDER BY를 넣어라 — 둘 다 같은 프레임 규칙의 양면이다.

이렇게도 쓴다

파티션 최댓값은 프레임 고민 없이 max() 윈도우가 더 명확하다. (조합: 집계 윈도우)

SELECT dept, emp, salary,
       max(salary) OVER (PARTITION BY dept) AS dept_max
FROM sal;

 

first_value는 기본 프레임에서도 잘 나온다(프레임 시작 = 파티션 맨 앞).

SELECT dept, emp,
       first_value(salary) OVER (PARTITION BY dept ORDER BY salary) AS min_in_dept
FROM sal;

 

프레임을 RANGE로 두면 동점(peer)까지 한 덩어리로 묶인다. 정확히 N행 창이 필요하면 ROWS.

SELECT emp, salary,
       avg(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ma3
FROM sal WHERE dept='dev';

 

전후 행 값은 lag/lead로. 전월 대비 증감 같은 패턴. (조합: lag)

SELECT emp, salary,
       salary - lag(salary) OVER (ORDER BY salary) AS diff_prev
FROM sal WHERE dept='dev';

 

누적 비율(구성비)은 running sum / total. (조합: OVER() + OVER(ORDER BY))

SELECT emp, salary,
       round(100.0 * sum(salary) OVER (ORDER BY salary)
                   / sum(salary) OVER (), 1) AS cum_pct
FROM sal WHERE dept='dev';

 

nth_value도 last_value와 같은 프레임 함정을 공유하니 프레임을 명시한다.

SELECT emp, nth_value(salary, 2) OVER (PARTITION BY dept ORDER BY salary
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM sal;