윈도우 함수 심화 정리: 실무에서 꼭 써먹는 핵심 기술

윈도우 함수 심화 정리

1. 윈도우 함수란 무엇인가?

1-1. 윈도우 함수의 핵심 철학

 윈도우 함수(Window Function)는 SQL이 가진 기존의 집계 기능을 한 단계 확장시킨 개념이다. 보통 집계 함수(SUM, AVG 등)는 GROUP BY를 통해 그룹 단위로 데이터를 요약하지만, 윈도우 함수는 행 단위 결과를 유지하면서 그 옆에 추가적인 계산 결과를 붙여주는 방식이다. 이로써 하나의 행은 여전히 고유한 정보로 존재하지만, 그 옆에 ‘그룹 안에서의 순위’, ‘누적 합계’, ‘직전 또는 다음 행의 정보’, ‘부분 평균’ 등 “해당 행이 전체 맥락에서 어디에 위치하는가”를 알려주는 정보가 더해진다.

1-2. 실무에서 필요한 이유

 실무에서 데이터는 단순히 조회하는 것에 그치지 않는다. 다음과 같은 요구사항이 실제로 자주 발생한다.

• “이 사용자는 전체 중 몇 위인가요?”
• “이 고객은 3회 이상 재방문했나요?”
• “이 글은 조회수가 증가하고 있는 패턴인가요?”
• “최근 일주일 평균 주문 금액을 기준으로 분석해줘요.”
• “직전 상태와 현재 상태를 비교해 이상치를 잡아야 해요.”

 이 모든 것들을 처리하려면 기존 GROUP BY만으로는 불가능하고, 윈도우 함수가 필요하다. 그만큼 윈도우 함수는 단순 SELECT를 넘어, 데이터 분석과 UX를 위한 실시간 처리에 매우 핵심적인 기술이다.

1-3. 문법 구조에 대한 깊이 있는 이해

 윈도우 함수는 다음과 같은 구성으로 되어 있다.

함수명() OVER (
  PARTITION BY 기준컬럼
  ORDER BY 정렬기준
  ROWS BETWEEN n PRECEDING AND CURRENT ROW
)

 

 각 항목의 의미는 다음과 같다:

• 함수명(): SUM, COUNT, RANK, ROW_NUMBER 등
• PARTITION BY: 계산을 끊어주는 그룹 기준. 없으면 전체 데이터가 하나의 창으로 간주됨
• ORDER BY: 정렬 기준. 대부분 이 기준으로 누적, 순위가 계산됨
• ROWS BETWEEN: 윈도우 크기 설정 (선택사항)

 이 구조 덕분에 우리는 그룹화와 정렬이 혼합된 형태의 유연한 계산을 할 수 있다. 즉, 기존 SQL이 제공하지 않던 ‘행 단위 + 그룹 단위’ 분석이 가능해진 것이다.

---

2. ROW_NUMBER, RANK, DENSE_RANK의 차이

2-1. ROW_NUMBER()

• 고유한 순번을 매긴다. 동일 값도 무조건 순서를 나눔
• 실무에서 각 사용자별 가장 최근 활동 1건 뽑기 등에 자주 쓰인다.

SELECT *,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date DESC) AS rn
FROM logins;

2-2. RANK()

• 동일 값이 있을 경우 같은 순위를 주고, 다음 순위를 건너뜀
• 1, 1, 3, 4 순으로 매겨짐

2-3. DENSE_RANK()

• 동일 순위 다음 순번을 바로 이어감
• 1, 1, 2, 3 순

* 실무 팁
 RANK는 랭킹 UI에서 공동 순위 처리가 필요한 경우, ROW_NUMBER는 TOP N만 추출할 때, DENSE_RANK는 등수 집계가 필요한 통계 대시보드에서 자주 사용된다.

---

3. SUM, AVG, COUNT + OVER(): 누적 통계 처리

3-1. 전체 누적합 구하기

SELECT *,
       SUM(sales) OVER (ORDER BY date) AS cumulative_sales
FROM daily_sales;

→ 날짜 순으로 매출이 얼마나 누적되어왔는지 확인 가능

3-2. 사용자별 누적 통계

SELECT user_id, login_date,
       COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date) AS session_count
FROM logins;

→ 사용자별 몇 번째 로그인인지 추적 가능.
→ PARTITION BY를 사용하면 그룹별 누적 처리가 가능하다.

 

실무 활용:

• 유입 사용자 수 추적
• 반복 구매 행동 확인
• 캠페인 효과 누적 분석 등

---

4. LEAD, LAG: 전후 데이터 비교

4-1. LAG()

현재 행의 기준에서 이전 행의 값을 가져온다.

SELECT user_id, login_date,
       LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login
FROM logins;

→ 직전 로그인과의 차이를 구하거나, 간격 분석에 사용

4-2. LEAD()

현재 행에서 다음 행의 값을 가져온다. → 다음 행동 예측이나 순서 비교에 사용됨

 

실무 활용 예시:

• 같은 유저가 이탈하기까지 걸린 기간
• 이전 결제 이후 다음 결제까지 걸린 시간
• 직전 상태와 현재 상태 비교

---

5. NTILE: 그룹 나누기

 NTILE(n) 함수는 전체 결과를 n개의 그룹으로 등분해준다.

예: 판매액 기준으로 상위 25%, 중위 25%, 하위 25% 등 구간을 나눌 수 있다.

SELECT product_id, sales,
       NTILE(4) OVER (ORDER BY sales DESC) AS rank_quartile
FROM products;

 

실무 활용:

• 구매 등급 분류
• 학생 성적 분포 구간 나누기
• 사용자의 참여 수준에 따라 A/B/C 구간 나누기

---

6. ROWS BETWEEN: 구간 설정

 윈도우 함수는 단순 누적뿐 아니라, ROWS BETWEEN 절을 활용해 지정된 범위 내에서만 집계 처리할 수 있다.

6-1. 최근 3일간 평균값

SELECT *,
       AVG(sales) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS avg_last_3_days
FROM daily_sales;

→ 현재 행 기준으로 자기 자신 + 직전 2일간의 평균을 계산한다.

 

실무 응용:

• 7일 평균 체온 계산
• 최근 5일간 거래량 비교
• 이동평균선 분석 (주식, 트렌드 데이터)

---

7. 윈도우 함수 + 조건식 조합

 윈도우 함수 내부에서는 조건을 직접 걸 수 없지만, CASE WHEN 구문과 결합해서 특정 조건에 해당하는 값만 누적할 수 있다.

SELECT *,
       SUM(CASE WHEN type = 'A' THEN amount ELSE 0 END)
         OVER (PARTITION BY user_id ORDER BY date) AS sum_type_A
FROM transactions;

 

이 방식은 특히 다음 용도로 자주 쓰인다:

• 특정 이벤트 참여 여부만 누적
• 특정 조건을 만족한 행동만 카운팅
• 상태별, 유형별 누적 지표 구성

---

8. 윈도우 함수 실무 설계 전략

 윈도우 함수는 편리하지만 다음 기준을 기억하고 써야 한다.

8-1. 반드시 정렬 기준이 있어야 한다

 윈도우 함수에서 ORDER BY는 계산 순서를 결정하는 핵심이다. 정렬 기준이 없으면 예측 불가능한 결과가 나올 수 있다.

→ 가능한 고유한 정렬 기준을 지정하는 습관을 들여야 한다. (ex. created_at DESC, id DESC)

8-2. OVER()는 필터링이 아니라 계산이다

 윈도우 함수는 행을 줄이지 않기 때문에, 필터링 조건은 반드시 WHERE 또는 서브쿼리에서 미리 걸어야 한다. 예를 들어 “상위 1건만 가져오고 싶다”면 다음과 같이 필터링해야 한다.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
  FROM purchases
) AS ranked
WHERE rn = 1;

→ 윈도우 함수는 결과를 보조로 붙이는 도구일 뿐, 최종 결과를 가공하기 위해선 서브쿼리나 WITH 절과 함께 사용하는 설계 패턴이 필요하다.

---

9. 윈도우 함수로 이상 행동 감지하기

9-1. 이상 값 탐지 구조

 데이터 분석의 한 축은 이상 행동 감지다. 예를 들어 평소 주문이 하루에 2건이던 사용자가 어느 날 갑자기 50건을 주문한다면, 이건 마케팅 이벤트 효과일 수도 있고, 시스템 오류일 수도 있다. 이때 다음과 같은 쿼리를 활용할 수 있다.

SELECT *,
  AVG(order_count) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days,
  order_count - AVG(order_count) OVER (...) AS deviation
FROM daily_orders;

→ 이전 7일 평균과 오늘의 주문 수 차이를 계산하여 이상 패턴을 식별할 수 있다.

9-2. 실무 예시

• 비정상 클릭률 탐지 (CTR 급등 사용자)
• 알림 수신 후 반응률 급증 사용자
• 마켓 거래량 급등 계정 탐지

 이렇게 윈도우 함수는 단순 통계뿐 아니라 데이터 기반의 문제 탐지와 알림 로직 설계에도 활용된다.

---

10. 윈도우 함수와 VIEW, CTE의 조합 설계

10-1. VIEW 또는 WITH 절로 분리하기

 윈도우 함수가 포함된 쿼리는 대부분 복잡하고 가독성이 떨어지기 쉽다. 이때 VIEW나 WITH 절(Common Table Expression)을 활용해 1단계로 쿼리를 분리하면 재사용성과 유지보수성이 크게 향상된다.

 

예:

WITH ranked_logs AS (
  SELECT user_id, login_at,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_at DESC) AS rn
  FROM login_history
)
SELECT * FROM ranked_logs WHERE rn = 1;

이런 방식은 다음과 같은 상황에서 매우 유용하다:

• 사용자별 최근 행동만 추출할 때
• 날짜별 순위를 기준으로 필터링할 때
• 대시보드에서 상태값별 최신 통계만 볼 때

10-2. 성능 최적화에도 효과적

 단일 쿼리로 윈도우 함수가 여러 번 호출되면 중복 연산이 발생한다. 이럴 땐 중간 결과를 뷰나 WITH 절로 한 번 계산해두고 공유하면 성능 저하를 줄이고 불필요한 계산도 방지할 수 있다.