584. 윈도우 함수 (Window Function) - ROWS BETWEEN과 파티션 연산

⚠️ 이 문서는 데이터베이스 쿼리를 짤 때 "각 부서별로 연봉 1등부터 3등까지 뽑아와!" 또는 "최근 3일간의 매출 이동 평균(Moving Average)을 구해!"처럼 복잡한 분석 쿼리를 요구받았을 때, 기존의 GROUP BY로 억지로 짜면 원본 데이터의 줄(Row)들이 다 뭉개져 사라지는 비극을 막기 위해, **원본 데이터의 형태를 100% 그대로 유지한 채 각 줄(Row) 옆에 '순위', '누적합', '이동 평균'이라는 마법의 창문(Window)을 덧대어 계산 결과를 보여주는 분석 함수의 끝판왕 '윈도우 함수'**를 다룹니다.

핵심 인사이트 (3줄 요약)

  1. 본질: GROUP BY의 한계(데이터를 하나로 뭉개버림)를 돌파한 고급 분석 함수다. 원본 데이터는 단 한 줄도 손상되지 않으면서, 그 옆에 새로운 계산 컬럼이 하나 찰칵 붙어서 결괏값을 보여준다.
  2. 가치: 서브 쿼리(Subquery)와 셀프 조인(Self Join)을 떡칠해서 100줄로 짜야 했던 '누적 매출액'이나 '이전 달 대비 성장률' 쿼리를 단 1줄의 OVER() 구문으로 압도적으로 빠르고 우아하게 해결해 낸다.
  3. 기술 체계: 데이터를 그룹으로 나누는 PARTITION BY, 그 그룹 안에서 줄을 세우는 ORDER BY, 그리고 "내 위로 2줄, 내 밑으로 1줄만 계산해!"라고 범위를 좁히는 ROWS BETWEEN 이라는 3대 뼈대로 완벽한 분석 프레임을 조립한다.

Ⅰ. GROUP BY의 붕괴와 OVER()의 기적

전체 평균을 알기 위해 내 월급 명세서를 찢어버릴 필요는 없다.

  1. GROUP BY의 치명적 단점 (데이터 압축):
    • "각 부서의 평균 연봉과, 직원 개개인의 연봉을 한 화면에 같이 보여줘!"
    • 초보자는 GROUP BY 부서를 친다. 그러면 부서 평균 연봉은 나오지만, 정작 직원 개개인의 이름과 연봉 데이터는 믹서기에 갈려버려 화면에서 사라진다(데이터 압축 현상).
    • 이걸 같이 보려면 GROUP BY로 만든 가상 테이블과 원본 테이블을 또 조인(JOIN)해야 하는 더러운 쿼리가 탄생한다.
  2. 윈도우 함수 (OVER)의 탄생:
    • SELECT 이름, 연봉, AVG(연봉) OVER(PARTITION BY 부서) AS 부서평균 FROM 직원;
    • 이 1줄을 치면 기적이 일어난다. 직원 100명의 원래 이름과 연봉 리스트(100줄)는 그대로 화면에 뜬다.
    • 그런데 그 옆에 [부서평균]이라는 새로운 칸(창문, Window)이 하나 더 생기더니, 자기가 속한 부서의 평균값이 각 직원의 옆구리에 예쁘게 복사되어 붙는다.
    • 원본 보존의 원칙: 윈도우 함수는 절대 데이터를 뭉개지 않고 원본의 줄(Row) 수를 100% 그대로 유지한다.

📢 섹션 요약 비유: GROUP BY는 과일 바구니(부서)를 통째로 믹서기에 넣고 갈아서 '과일 주스 1잔(평균)'만 내놓는 무식한 기계입니다. 원래 사과가 몇 개였는지 형체를 알 수 없습니다. 윈도우 함수(OVER)는 똑똑한 비서입니다. 바구니 안의 사과와 배를 믹서기에 갈지 않고 그대로 책상에 예쁘게 펼쳐둔 뒤, 각 사과 옆에 포스트잇을 붙여 "이 바구니의 과일은 총 10개임"이라고 전체 통계 정보를 귀띔(Window)해 주는, 형체 보존과 분석을 동시에 해내는 마법입니다.

Ⅱ. 파티션(PARTITION)과 순위(Rank) 함수

그룹 안에서 줄을 세우는 가장 우아한 방법.

  1. PARTITION BY (분석의 벽 치기):
    • 전체 직원을 대상으로 등수를 매기는 게 아니라, "부서 안에서만" 등수를 매기고 싶을 때 쓴다.
    • OVER (PARTITION BY 부서명)이라고 적으면, DB는 눈에 보이지 않는 투명한 벽을 쳐서 영업팀, 재무팀, 인사팀을 쪼개놓고 각 방 안에서만 따로따로 계산을 돌린다.
  2. ORDER BY (그룹 내 줄 세우기):
    • 파티션으로 방을 쪼갰으면, 그 안에서 누구부터 계산할지 순서를 정한다.
    • OVER (PARTITION BY 부서명 ORDER BY 연봉 DESC) $\rightarrow$ "부서별로 방을 쪼개고, 그 안에서 연봉 높은 순으로 줄을 세워라!"
  3. 순위 함수 삼형제 (ROW_NUMBER, RANK, DENSE_RANK):
    • ROW_NUMBER(): 자비 없이 무조건 1, 2, 3, 4등을 매긴다. 공동 2등? 그런 거 없다.
    • RANK(): 연봉이 똑같은 공동 2등이 2명 나오면 둘 다 2등으로 쳐준다. 단, 그다음 사람은 3등을 건너뛰고 4등이 된다. (1, 2, 2, 4등)
    • DENSE_RANK(): 공동 2등이 2명 나와도, 그다음 사람은 무조건 3등으로 촘촘하게(Dense) 등수를 매겨준다. (1, 2, 2, 3등)

📢 섹션 요약 비유: 전국 체육대회입니다. PARTITION BY는 선수들을 100m 달리기, 수영, 양궁 종목별로 대기실을 쪼개는 작업입니다. ORDER BY는 각 대기실 안에서 기록이 빠른 순서대로 한 줄로 세우는 작업입니다. RANK()는 올림픽 룰입니다. 은메달이 2명이면 동메달은 안 주고 바로 4등에게 철메달을 줍니다. DENSE_RANK()는 촘촘한 학교 룰입니다. 은메달이 2명이 나와도, 그다음으로 잘한 애한테 억울하지 않게 3등 동메달을 꼭 쥐여주는 방식입니다.

Ⅲ. ROWS BETWEEN: 이동 평균(Moving Average)과 누적합

주식 차트를 그리는 핵심 기술, 범위를 내 마음대로 고무줄처럼 조절한다.

  1. 기본 동작 (누적 합계 구하기):
    • SUM(매출액) OVER (ORDER BY 날짜)
    • 윈도우 함수에 ORDER BY만 쓰면 무서운 일이 벌어진다. "맨 첫째 날부터, 현재 줄(나)까지"의 데이터를 몽땅 더하는 **누적 합계(Running Total)**가 자동으로 계산된다.
    • 1일 차: 100원, 2일 차: 300원(100+200), 3일 차: 600원(100+200+300)... 이렇게 통장 잔고처럼 값이 불어난다.
  2. ROWS BETWEEN (프레임 찢기):
    • 근데 주식 차트를 그릴 때 "최근 3일간의 이동 평균선(Moving Average)"을 그려야 한다 치자. 1년 치를 누적하면 안 된다. 딱 "어제, 오늘, 내일" 3개만 더해서 나눠야 한다.
    • 이때 윈도우(창문)의 크기를 강제로 찢어버리는 전설의 템플릿이 들어간다.
    • AVG(주가) OVER (ORDER BY 날짜 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
    • 해석: 전체 데이터를 보지 마라! 내 창문은 오직 **"내 바로 위 1줄(1 PRECEDING)"**부터 "내 바로 아래 1줄(1 FOLLOWING)" 까지만 딱 3줄만 열린다. 이 3줄의 평균만 구해서 내 옆에 적어라!
  3. 무한대 프레임 (UNBOUNDED):
    • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    • "맨 처음 데이터(무한대 과거)부터 현재 내 줄(Current Row)까지 싹 다 더해라." (기본 누적합과 100% 동일한 작동)

📢 섹션 요약 비유: ROWS BETWEEN은 기차가 달릴 때 기관사가 창문 블라인드를 얼마나 올릴지 결정하는 것입니다. UNBOUNDED PRECEDING은 블라인드를 끝까지 올려서 출발역부터 지금까지 지나온 풍경(과거 전체 누적)을 몽땅 다 보는 것입니다. 1 PRECEDING AND 1 FOLLOWING은 블라인드를 좁게 닫아서 딱 내 눈앞 1미터 풍경(최근 3일 치)만 조그맣게 바라보는 것입니다. 주식 5일 이동 평균선(MA5) 같은 고급 차트 지표는 오직 이 좁은 블라인드 창문(윈도우 함수)을 통해서만 완벽하고 빠르게 그려낼 수 있습니다.