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 절로 한 번 계산해두고 공유하면 성능 저하를 줄이고 불필요한 계산도 방지할 수 있다.
'컴퓨터공학' 카테고리의 다른 글
아키텍처란 무엇인가? (0) | 2025.05.17 |
---|---|
복잡한 조건 분기 처리 전략: SQL로 비즈니스 로직을 설계하는 기술 (0) | 2025.05.16 |
실무에 강한 SQL 작성법: 비즈니스 로직을 쿼리로 설계하는 기술 (0) | 2025.05.16 |
SQL 실무 쿼리 패턴 모음: 가장 자주 쓰이는 구조 정리 (0) | 2025.05.15 |
SQL 트랜잭션과 롤백 처리 완전 정복: 데이터 신뢰성을 지키는 핵심 기술 (0) | 2025.05.15 |