[TIL] SQL 라이브세션 6회차

🎯 오늘 수업 목표

 

1. 수강생이 세션 종료 후 할 수 있어야 하는 것:

2. 윈도우 함수가 “행을 유지하면서(행 수 그대로) 계산 결과를 옆에 붙이는 기능”임을 이해한다.

3. OVER() 안의 PARTITION BY(그룹 나누기) / ORDER BY(그룹 안에서 줄 세우기)를 쓸 수 있다.

• 실무에서 가장 많이 쓰는 패턴 4개를 익힌다:
• 그룹 합/개수/평균을 각 행에 붙이기: SUM/COUNT/AVG OVER
• 그룹별 순번/순위 만들기: ROW_NUMBER, RANK, DENSE_RANK
• 누적합(러닝 토탈): SUM OVER (… ORDER BY … ROWS …)
• 이전/다음 행 비교: LAG, LEAD

4. IFNULL, COALESCE로 NULL을 안정적으로 처리한다.

5. 윈도우 함수 결과를 WHERE에서 바로 못 쓸 때 CTE로 감싸서 필터할 수 있다.

 

 

Part 0. NULL 처리: IFNULL / COALESCE (윈도우 함수랑 같이 자주 씀)

IFNULL

expr1...

NULL이 아니면 expr1 반환

NULL이면 expr2 반환

IFNULL(expr1, expr2)

 

COALESCE

var1→var2→var3 .... 를 거쳐가며 처음으로 NULL이 아닌 값을 반환 

COALESCE COALESCE(val1, val2, val3, ...)

 

IFNULL vs COALESCE

IFNULL → 단일 처리 (NULL이 무엇이 되야할지 정해질 경우 좋음 )

COALESCE → 다중처리 (표준 + 여러 후보 가능)

---

Part 1. 윈도우 함수 “제일 많이 쓰는 1번 패턴”

 

그룹 합 / 개수 / 평균을 행을 유지한 채로 붙이기

• COUNT/SUM/AVG OVER
• 집계 결과를 만들되, 행을 줄이지 않는다

함수(...) OVER ( PARTITION BY ... ORDER BY ... )

PARTITION BY → 어떤 기준으로 “그룹을 나눌지”
ORDER BY → 파티션 안에서의 순서 (선택)

 

⭐ MySQL에서 윈도우 함수는 SELECT/ORDER BY에서만 사용 가능
`WHERE`, `GROUP BY`, `HAVING`에서는 사용 불가.

→집계 이후 단계에서 작동한다는 의미

 

GROUP BY vs COUNT OVER

GROUP BY

역할: 행을 묶어서 결과를 줄임

결과:

• 그룹당 1행

• 원본 행 정보는 사라짐

목적:

• 요약 테이블

• 리포트용 집계

→ 결과의 단위(grain)를 바꾸는 문법

 

COUNT OVER:

역할: 행을 유지한 채, 집계 값을 계산해서 붙임

결과:

• 원본 행 수 그대로

• 같은 파티션 안에서는 집계 값이 반복 표시됨

목적:

• 비율 계산

• 비교 기준 값 붙이기

• “이 행이 전체 중 어디쯤인지” 보기

→  결과의 단위를 유지한 채, 정보만 추가

 

요약

GROUP BY → “요약해서 새 테이블을 만든다”
COUNT OVER → “요약값을 컬럼처럼 덧붙인다”

 

# SUM / AVG도 동일한 논리 적용 가능

SUM OVER → 같은 그룹의 총합을 각 행에 붙임

AVG OVER → 같은 그룹의 평균을 각 행에 붙임

⭐ COUNT만의 문제가 아니라 모든 집계 함수에 동일하게 적용되는 개념

---

Part 2. 윈도우 함수 “제일 많이 쓰는 2번 패턴”

 

그룹별 순번/순위 매기기

• ROW_NUMBER / RANK / DENSE_RANK

 

2-1) ROW_NUMBER()

무조건 1,2,3… 번호 (동점이어도 나눠짐)

ROW_NUMBER() OVER ( PARTITION BY ... ORDER BY ... )

개념

• 파티션 내에서 각 행에 고유한 번호를 부여

• 동점이 있어도 번호는 무조건 나뉜다

• 결과는 항상: 1, 2, 3, 4, …

→순위 보단 순번에 가까움

 

⭐ 포인트

• ORDER BY가 없으면 결과가 비결정적 (실행할 때마다 달라질 수 있음)

• “가장 최신 1건”, “각 그룹의 대표 행 1개” 뽑을 때 자주 사용

 

2-2) RANK vs DENSE_RANK (동점 처리)

RANK() 동점이면 같은 순위, 다음 순위는 건너뜀(갭)

RANK() OVER ( PARTITION BY ... ORDER BY ... )

개념적으로 “등수 개념에 가까움” (1등이 2명이면 다음은 3등)

 

DENSE_RANK() : 동점이면 같은 순위, 다음 순위는 안 건너뜀(촘촘)

DENSE_RANK() OVER ( PARTITION BY ... ORDER BY ... )

개념적으로 “그룹 내 순서 레벨” (1 → 2 → 3 처럼 촘촘하게 증가)

 

2-3) RANK() vs DENSE_RANK()

동점이 있을 때의 처리 방식 차이

둘의 공통점:

• 값이 같으면 같은 순위

• ORDER BY 기준으로 순위 계산

• 행은 줄어들지 않음

→차이는 다음 순위를 어떻게 매기느냐

---

Part3. 윈도우 함수 “제일 많이 쓰는 3번 패턴”

 

누적합/비율/최근 N개 평균

• SUM/AVG OVER + ORDER BY + Frame
• 행의 위치에 따라 계산에 포함되는 범위가 달라진다

 

3-1) 누적합에서 꼭 알아야 하는 1가지: 프레임(Frame)

ORDER BY가 있는 윈도우 집계에는 기본 Frame이 자동 적용된다

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

개념

• 파티션의 첫 행부터 현재 행까지
• 값 기준(RANGE)으로 묶어서 누적

→ 즉, ORDER BY만 써도 암묵적으로 누적 계산이 된다

 

🚨문제 발생

RANGE는 값 기준 프레임, 정렬 기준 컬럼 값이 같으면 여러 행이 한 번에 포함될 수 있다

정확한 "행 단위 누적"을 원할 때, 날짜/시간이 아닌 단순 순번 기준일 때...

→ 결과가 의도와 어긋날 수 있다

때문에...
누적합은 Frame을 명시한다

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

개념

• 물리적인 행 기준
• "앞에서부터 지금 이 행까지"를 명확히 보장

→ 누적합 = ROWS 프레임을 떠올리자!

 

3-2 각 행의 비율 계산 (리포트 · KPI · 대시보드에서 자주 사용)

개별 값 / 그룹 합계

개념

• 한 행의 값은 그대로 둔다
• 같은 그룹의 전체 합계를 윈도우 함수로 붙인다
• 둘을 나눈다

🚨 GROUP BY는 행이 줄어들기 떄문에 불가능

value / SUM(value) OVER (PARTITION BY group_key)

이 행의 값 = value
이 행이 속한 그룹의 전체합= SUM(value) OVER (PARTITION BY group_key)

 

이 구조만 기억해도

• 매출 비중

• 사용자별 사용량 비율

• 카테고리 점유율

이 가능해짐

 

Part4. 윈도우 함수 “제일 많이 쓰는 4번 패턴”

“이전/다음 행 비교” (LAG / LEAD) + NULL 처리(IFNULL/COALESCE)

→ “행과 행 사이의 관계를 비교한다”

 

집계(SUM/COUNT)나 순위와 달리,
LAG / LEAD는 행 자체를 이동해서 참조하는 함수

 

4-1) LAG / LEAD의 개념 졉근

LAG(값)

• 현재 행 기준 → 이전 행의 값을 가져옴
• “어제 값”, “이전 상태”, “직전 이벤트”를 볼 때 사용

 

LEAD(값)

• 현재 행 기준 → 다음 행의 값을 가져옴
• “다음 값”, “다음 상태”, “이후 이벤트”를 볼 때 사용

 

LAG(값) : 내 바로 “이전 행”의 값을 가져온다
LEAD(값) : 내 바로 “다음 행”의 값을 가져온다

LAG(expr, N, default) OVER (PARTITION BY ... ORDER BY ...) LEAD(expr, N, default) OVER (PARTITION BY ... ORDER BY ...)

exper

→ 가져오고 싶은 컬럼 값

N

→  기본값 = 1 (한 칸 전/후) → 몇 행 전 / 몇 행 후를 볼지

default (기본값 = NULL)

→ 참조할 행이 없을 때 반환할 값

ORDER BY

→ 는 사실상 필수(“전/후” 기준이 필요)

 

⭐ORDER BY는 사실상 필수

LAG / LEAD에서 ORDER BY는 선택이 아니라 의미를 결정하는 기준
ORDER BY가 없을 경우:
“이전/다음”의 기준이 없다
결과는 비결정적 (매 실행마다 달라질 수 있음)

 

⭐ NULL이 생기는 이유 (중요)

LAG / LEAD는 구조적으로 NULL을 만들 수밖에 없다.

• 파티션의 첫 행 → LAG = NULL

• 파티션의 마지막 행 → LEAD = NULL

이 NULL은 “데이터가 없는 것”이 아닌

“참조할 이전/다음 행이 구조적으로 존재하지 않음” 을 의미

→반환값(default)를 설정하거나 IFNULL, COALESCE을 사용하자

 

4-2) “WINDOW로 이름 붙이기” (OVER 중복 줄이기)

윈도우 정의가 반복될 때는 WINDOW 절을 쓰면 좋다.

• OVER(...)에 붙일 설정을 이름으로 분리
• 쿼리가 짧아지고 의미가 명확해짐

WINDOW w AS ( PARTITION BY ... ORDER BY ... ) LAG(value) OVER w LEAD(value) OVER w

→ LAG / LEAD 같이 OVER가 반복되는 패턴에서 특히 효과적

---