[PostgreSQL] 윈도우 프레임 ROWS/RANGE BETWEEN으로 이동평균·누적합을 낸다

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

부제: 일별 매출에서 "최근 3일 이동평균"과 "그날까지 누적합"을 GROUP BY 없이 각 행 옆에 나란히 붙이고 싶을 때

-- ROWS 프레임으로 "현재 행 기준 앞뒤 몇 행"을 집계 범위로 지정한다
SELECT day, amount,
  round(avg(amount) OVER (ORDER BY day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1)        AS ma3,
  sum(amount) OVER (ORDER BY day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)    AS running
FROM m10_sales ORDER BY day LIMIT 8;

    day     | amount |  ma3  | running 
------------+--------+-------+---------
 2026-03-01 |    100 | 100.0 |     100
 2026-03-02 |    114 | 107.0 |     214
 2026-03-03 |    125 | 113.0 |     339
 2026-03-04 |    130 | 123.0 |     469
 2026-03-05 |    127 | 127.3 |     596
 2026-03-06 |    118 | 125.0 |     714
 2026-03-07 |    104 | 116.3 |     818
 2026-03-08 |     89 | 103.7 |     907
(8 rows)

윈도우 함수의 OVER 절 안에서 프레임(ROWS/RANGE BETWEEN ...)은 "현재 행에서 어디까지를 계산 범위로 볼지"를 정한다. 2 PRECEDING AND CURRENT ROW는 나 포함 앞 3개라서 3일 이동평균이 되고, UNBOUNDED PRECEDING AND CURRENT ROW는 맨 처음부터 지금까지라서 누적합이 된다. ROWS는 물리적 행 개수로 세고, RANGE는 정렬 값(날짜·숫자) 자체로 범위를 잡는다는 게 핵심 차이다. GROUP BY와 달리 원본 행을 그대로 두고 계산 결과만 옆에 붙이므로, 리포트·차트용 파생 컬럼을 한 번에 만든다.

이렇게도 쓴다

앞뒤로 걸쳐 중심 이동평균을 낸다. (조합: PRECEDING AND FOLLOWING)

SELECT day, amount,
  round(avg(amount) OVER (ORDER BY day
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 1) AS center3
FROM m10_sales ORDER BY day;

 

행 개수가 아니라 날짜 간격으로 "최근 7일"을 잡는다. (조합: RANGE + interval)

SELECT day,
  sum(amount) OVER (ORDER BY day
        RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW) AS last7
FROM m10_sales ORDER BY day;

 

프레임을 생략하면 파티션 전체가 대상이 된다(전체 대비 비율). (조합: OVER ())

SELECT day, amount,
  round(100.0 * amount / sum(amount) OVER (), 1) AS pct
FROM m10_sales ORDER BY day;

 

동점(같은 정렬키)에서 ROWS와 RANGE는 다르게 동작한다.

WITH t(k,v) AS (VALUES (1,10),(1,20),(2,30))
SELECT k, v,
  sum(v) OVER (ORDER BY k ROWS  UNBOUNDED PRECEDING) AS by_rows,
  sum(v) OVER (ORDER BY k RANGE UNBOUNDED PRECEDING) AS by_range
FROM t;
--  k=1 두 행: by_rows=10,30(한 행씩)  by_range=30,30(동점 묶어 합산)

 

그룹별로 프레임을 리셋한다. (조합: PARTITION BY)

SELECT user_id, id,
  sum(qty) OVER (PARTITION BY user_id ORDER BY id
        ROWS UNBOUNDED PRECEDING) AS user_running
FROM orders ORDER BY user_id, id;

 

직전 값과 비교해 증감을 낸다. (조합: lag)

SELECT day, amount, amount - lag(amount) OVER (ORDER BY day) AS diff
FROM m10_sales ORDER BY day;