SQL 윈도우 함수

한 줄 정의

윈도우 함수는 행을 한 줄로 합치지 않고, 관련 행의 순위·이전 값·누적값 같은 계산 결과를 각 행 옆에 붙인다.

GROUP BY가 시즌별 평균처럼 여러 행을 한 행으로 접는다면, 윈도우 함수는 플레이어 행을 유지한 채 시즌 내 순위를 붙인다. 기본 모양은 함수 OVER (PARTITION BY 그룹 ORDER BY 순서 ROWS 프레임)다.

실습 데이터: 시즌 점수와 일별 접속

DROP TABLE IF EXISTS daily_logins, season_scores;

CREATE TABLE season_scores (
  season_id integer NOT NULL,
  player_id integer NOT NULL,
  nickname text NOT NULL,
  score integer NOT NULL CHECK (score >= 0),
  PRIMARY KEY (season_id, player_id)
);
INSERT INTO season_scores VALUES
 (1,1,'루나',1200),(1,2,'카이',1000),(1,3,'솔',1000),(1,4,'미르',800),
 (2,1,'루나',900),(2,2,'카이',1300),(2,3,'솔',1100);

CREATE TABLE daily_logins (
  cohort text NOT NULL,
  login_date date NOT NULL,
  dau integer NOT NULL CHECK (dau >= 0),
  PRIMARY KEY (cohort, login_date)
);
INSERT INTO daily_logins VALUES
 ('신규','2026-08-01',100),('신규','2026-08-02',140),
 ('신규','2026-08-03',90), ('신규','2026-08-04',170),
 ('신규','2026-08-05',130),('신규','2026-08-06',160),
 ('신규','2026-08-07',150),
 ('복귀','2026-08-01',60), ('복귀','2026-08-02',75),
 ('복귀','2026-08-03',70);

PARTITION BY는 계산 세계를 나눈다

PARTITION BY season_id는 시즌마다 독립된 윈도우를 만든다. SQL 결과 전체를 나누어 출력하는 GROUP BY와 달리 행 수는 그대로다. PARTITION BY가 없으면 모든 시즌이 하나의 랭킹이 된다. 윈도우 안 ORDER BY score DESC는 계산 순서이며, 최종 출력 순서를 보장하려면 SELECT 바깥 ORDER BY도 따로 써야 한다.

ROW_NUMBER·RANK·DENSE_RANK의 동점 처리

SELECT season_id, nickname, score,
  ROW_NUMBER() OVER (PARTITION BY season_id ORDER BY score DESC, player_id) AS row_no,
  RANK()       OVER (PARTITION BY season_id ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY season_id ORDER BY score DESC) AS dense_rank
FROM season_scores
ORDER BY season_id, row_no;

 season_id | nickname | score | row_no | rank | dense_rank
-----------+----------+-------+--------+------+-----------
         1 | 루나     |  1200 |      1 |    1 |          1
         1 | 카이     |  1000 |      2 |    2 |          2
         1 | 솔       |  1000 |      3 |    2 |          2
         1 | 미르     |   800 |      4 |    4 |          3
         2 | 카이     |  1300 |      1 |    1 |          1
         2 | 솔       |  1100 |      2 |    2 |          2
         2 | 루나     |   900 |      3 |    3 |          3

• ROW_NUMBER는 무조건 1, 2, 3처럼 고유 번호를 준다. 페이지네이션·대표 1행 선택에는 동점을 가르는 player_id 같은 안정적 기준을 넣는다.

• RANK는 동점에 같은 순위를 주고 다음 순위를 건너뛴다. 공동 2위 다음은 4위다.

• DENSE_RANK는 동점 다음 순위를 건너뛰지 않는다. 점수 티어 번호처럼 연속 등급이 필요할 때 맞다.

ORDER BY가 동점을 완전히 해소하지 않으면 ROW_NUMBER의 동점 내부 순서는 보장되지 않는다. 결과를 캐시하거나 보상 대상자를 자를 때는 반드시 고유한 최종 정렬 키를 추가한다.

LAG와 LEAD로 이전·다음 행을 본다

LAG는 현재 행보다 앞선 행, LEAD는 뒤의 행 값을 가져온다. 자기 조인 없이 전일 증감과 다음 접속일까지의 간격을 계산할 수 있다. 코호트별로 나누지 않으면 신규 코호트 끝과 복귀 코호트 시작이 잘못 연결된다.

SELECT cohort, login_date, dau,
  LAG(dau) OVER w AS prev_dau,
  dau - LAG(dau) OVER w AS day_change,
  LEAD(login_date) OVER w - login_date AS days_to_next
FROM daily_logins
WINDOW w AS (PARTITION BY cohort ORDER BY login_date)
ORDER BY cohort, login_date;

 cohort | login_date | dau | prev_dau | day_change | days_to_next
--------+------------+-----+----------+------------+-------------
 복귀   | 2026-08-01 |  60 | NULL     | NULL       | 1
 복귀   | 2026-08-02 |  75 | 60       | 15         | 1
 복귀   | 2026-08-03 |  70 | 75       | -5         | NULL
 신규   | 2026-08-01 | 100 | NULL     | NULL       | 1
 ...

프레임은 현재 행 주변의 계산 범위다

PARTITION이 코호트 전체라면 frame은 그 안에서 현재 계산에 포함할 행 범위다. 누적합은 첫 행부터 현재 행, 3일 이동평균은 현재 행과 앞의 두 행을 쓴다. ROWS는 물리적 행 개수를 세므로 날짜당 한 행이라는 기본 키와 잘 맞는다.

SELECT cohort, login_date, dau,
  SUM(dau) OVER (
    PARTITION BY cohort ORDER BY login_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROUND(AVG(dau) OVER (
    PARTITION BY cohort ORDER BY login_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS ma_3d
FROM daily_logins
WHERE cohort = '신규'
ORDER BY login_date;

 login_date | dau | running_total | ma_3d
------------+-----+---------------+-------
 2026-08-01 | 100 |           100 | 100.00
 2026-08-02 | 140 |           240 | 120.00
 2026-08-03 |  90 |           330 | 110.00
 2026-08-04 | 170 |           500 | 133.33
 2026-08-05 | 130 |           630 | 130.00
 2026-08-06 | 160 |           790 | 153.33
 2026-08-07 | 150 |           940 | 146.67

폭 k의 후행 이동평균은 현재를 포함해 최대 k개 관측값을 평균낸다. 시작 구간은 아직 k개가 없으므로 PostgreSQL AVG는 존재하는 행만 사용한다.

$8/1$$8/2$$8/3$$8/4$$8/5$$8/6$$8/7$$100.00$$120.00$$110.00$$133.33$$130.00$$153.33$$146.67$$이동평균은 일별 급등락을 완화$$일별 DAU$$3일 이동평균$

주석

ROWS BETWEEN 2 PRECEDING은 “3일”이 아니라 “현재 포함 3행”이다. 날짜가 빠져 있으면 실제 3일 범위가 아니다. 달력 테이블로 빈 날짜를 채우거나 날짜 간격을 뜻하는 RANGE 프레임을 신중히 사용한다.

기본 프레임의 함정

ORDER BY가 있는 집계 윈도우의 기본 프레임은 보통 파티션 전체가 아니라 현재 행의 동점 peer까지 포함하는 시작→현재 범위다. 같은 점수 행이 한꺼번에 누적되는 것이 의도와 다를 수 있다. 누적합에서는 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 명시해 리뷰어에게 행 단위 의도를 보여 주는 편이 안전하다.

반대로 LAST_VALUE는 기본 프레임에서 파티션의 마지막 값이 아니라 현재 peer의 마지막 값이 될 수 있다. 시즌 최종 값을 원하면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING처럼 프레임 끝을 명시한다.

상위 N명과 코호트 분석 패턴

윈도우 결과는 같은 SELECT의 WHERE에서 바로 필터링할 수 없으므로 서브쿼리나 CTE에서 계산한 다음 바깥에서 고른다. 다음은 시즌마다 정확히 두 행을 뽑는다. 공동 2위를 모두 포함하려면 ROW_NUMBER 대신 RANK를 사용하며 결과 행 수가 둘보다 많을 수 있음을 받아들여야 한다.

WITH ranked AS (
  SELECT season_id, player_id, nickname, score,
    ROW_NUMBER() OVER (
      PARTITION BY season_id ORDER BY score DESC, player_id
    ) AS rn
  FROM season_scores
)
SELECT season_id, nickname, score
FROM ranked
WHERE rn <= 2
ORDER BY season_id, rn;

코호트 분석에서도 PARTITION BY cohort를 사용하면 신규·복귀 집단의 규모가 달라도 각 집단 안에서 전일 변화와 이동평균을 계산한다. 다만 이 예시의 cohort는 접속 행에 이미 붙은 분류다. 실제 가입 코호트 리텐션은 사용자별 최초 가입일과 활동일을 먼저 계산한 뒤 날짜 차이별 고유 사용자를 집계해야 한다.

성능과 정확성 체크리스트

• PARTITION BY가 분석 집단을, 윈도우 ORDER BY가 계산 순서를 정확히 표현하는지 확인한다.

• ROW_NUMBER에는 고유 tie-breaker를 넣고, 공동 순위 정책에 따라 RANK와 DENSE_RANK를 구분한다.

• ROWS·RANGE·GROUPS 중 프레임 단위를 선택하고 시작·끝을 명시한다.

• LAG·LEAD의 첫·마지막 NULL과 날짜 누락을 업무 규칙에 맞게 처리한다.

• 큰 파티션의 정렬은 메모리와 임시 파일을 쓸 수 있으므로 EXPLAIN (ANALYZE, BUFFERS)로 Sort와 디스크 사용을 측정한다.

참고 자료

• PostgreSQL 공식 문서 — Window Functions Tutorial (2026-08-11 확인)

• PostgreSQL 공식 문서 — Window Function 목록 (2026-08-11 확인)

• PostgreSQL 공식 문서 — Window Function Calls와 Frame 문법 (2026-08-11 확인)

• PostgreSQL 공식 문서 — Using EXPLAIN (2026-08-11 확인)