Ifnull / Coalesce 함수, 윈도우 함수

 

- SQL 5회차 세션 

(코드: https://github.com/rladbstj56/2025_main_bootcamp/blob/main/SQL_session/251230_5th)

 

- ifnull / coalesce 함수

• 윈도우 함수를 쓸 때 null이 자주 발생하므로 ifnull 함수와 coalesce 함수 자주 함께 사용
• ifnull : 값 1개만 넣고 그 값이 null일 경우 치환할 다른 한 값 1개
• coalesce : 값 여러개 넣을 수 있고 한 값이 null일 경우 다음 값 확인, 마지막까지 null인 경우 치환할 다른 한 값 1개
  • 만약 모든 인자가 다 NULL일 경우 NULL 반환

select 
	enrollment_id, 
	coupon_code, 
	course_id,
	ifnull(coupon_code,'no_coupon') as coupon_label,
	coalesce(coupon_code, course_id, 'no_coupon') as coupon_filled
from basic.enrollments 
order by enrollment_id;

 

 

 

- 윈도우 함수

MySQL에서 윈도우함수는 select 리스트 / order by 딱 이렇게 두가지 경우에서만 사용 가능함!

 

- 윈도우 함수 vs GROUP BY 

• 핵심 차이점: 행(row)의 개수를 줄이는가?
• GROUP BY: 지정한 컬럼을 기준으로 행들을 그룹화하여 하나의 요약된 행으로 합침 (Aggregating)
• 윈도우 함수(OVER): 각 행의 디테일은 그대로 유지하면서, 옆에 계산 결과만 추가함 (Non-aggregating)

 

- 윈도우 함수 패턴 1번

• count over
  • group by 와 뜻은 같지만 group by는 행이 단위행의 개수로 줄어들지만
  • count over 은 파티션으로 나누어서 보되, 행의 개수가 그대로 보존됨

select 
	student_id,
	count(*) as enroll_cnt
from basic.enrollments 
group by student_id;

select 
	student_id,
	course_id, 
	enroll_date,
	count(*) over (partition by student_id) as enroll_cnt_in_student
from basic.enrollments 
order by 1;

• sum / avg 도 마찬가지로 사용가능

select 
	enrollment_id, 
	student_id, 
	final_price,  
	count(*) over (partition by student_id) as cnt,
	sum(final_price) over (partition by student_id) 
	as student_total_spend, 
	avg(final_price) over (partition by student_id) 
	as student_avg_price 
from basic.enrollments 
order by 2, 1;

• count over 미니 실습

# 미니 실습 1
select
	enrollment_id, student_id, course_id, 
	count(*) over (partition by student_id) as stu_enroll_cnt, 
	count(*) over (partition by course_id) as crs_enroll_cnt 
from basic.enrollments 
order by enrollment_id;

 

- 윈도우 함수 패턴 2번 

• row_number(): 파티션 내에서 행에 번호 붙임 / 동점이어도 무조건 1,2,3...
• rank(): 동점이면 같은 순위 / 다음 순위는 건너뜀 # 1,1,1,4,4,6,6,6
• dense_rank(): 동점이면 같은 순위 / 다음 순위 안건너뜀  # 1,1,1,2,2,3,3,3

select 
	course_id, 
	course_name, 
	list_price, 
	row_number() over (order by list_price desc) as row_num,
	rank() over (order by list_price desc) as rnk,
	dense_rank() over (order by list_price desc) as dense_rnk
from basic.courses 
order by list_price desc, course_id;

• 그룹별 Top N 뽑기 
  • MySQL에서는 윈도우 함수 결과를 WHERE에서 직접 못 씀
  • → CTE로 감싼 뒤 바깥에서 필터해야함

# 학생별 가장 비싼 강의
with ranked as ( 
	select 
		enrollment_id, student_id, course_id, final_price,
		rank() over (partition by student_id order by final_price desc) as rnk 
	from basic.enrollments  
) 
select 
	s.student_id, 
	s.student_name, 
	ifnull(r.course_id,'-'), 
	ifnull(r.final_price,'-'), 
	ifnull(r.rnk,'-')
from basic.students s 
left join ranked r 
on s.student_id = r.student_id  
and r.rnk = 1
order by s.student_id

# 강좌별로 final_price가 높은 신청 Top 2
with ranked as ( 
	select course_id, enrollment_id, student_id, final_price,
		   row_number() over (partition by course_id  order by final_price desc, enrollment_id)
		   	as rnk
	from basic.enrollments 
) 
select * 
from ranked 
where rnk <= 2 
order by course_id, rnk

 

 

- SQL 세션 6회차

(코드: https://github.com/rladbstj56/2025_main_bootcamp/blob/main/SQL_session/251231_6th)

 

- 윈도우 함수 패턴 3번

•  누적합
  • rows between unbounded preceding and current row 

# 학생별 누적 지출
select 
	student_id, 
	enrollment_id, 
	final_price, 
	sum(final_price) over (   
		partition by student_id 
		order by enroll_date, enrollment_id 
		rows between unbounded preceding and current row 
	) as cum_spend 
from basic.enrollments 
order by student_id, enroll_date, enrollment_id;

# 결제 시간 순 누적 결제금액
select payment_id, paid_at, amount, 
	   sum(amount) over (order by paid_at, payment_id
	   		rows between unbounded preceding and current row 
	   ) as cum_amount
from basic.payments 
where payment_status = 'paid'
order by paid_at, payment_id

• 각 행의 비율 

# 각 행의 비율 (개별 값 / 그룹 합계)
# 학생 총 지출의 몇 %인가?
select 
	student_id, final_price, 
	sum(final_price) over (partition by student_id) as sum_per_student, 
	round(final_price / sum(final_price) over (partition by student_id) * 100,2)
		as pct_of_sum_
from basic.enrollments 
order by student_id;

 

- 윈도우 함수 패턴 4번 

• LAG / LEAD 
  • 시계열/세션/로그 데이터에서 자주 씀
    • LAG(expr, N, default) over (partition by ... order by ...) 
    • LEAD(expr, N, default) over (partition by ... order by ...)

# window로 이름 붙이기 to remove the duplication of OVER stmt
select enrollment_id, student_id, enroll_date, final_price, 
	   lag(final_price, 1, final_price) over w as prev_price, 
	   lead(final_price, 1, final_price) over w as next_price 
from basic.enrollments 
window w as ( 
	partition by student_id 
	order by enroll_date, enrollment_id 
)
order by student_id, enroll_date, enrollment_id;

# 미니 실습 4 
select enrollment_id, student_id, enroll_date, final_price, 
	   coalesce(lag(final_price) over w ,final_price) as prev_price, 
	   coalesce(lead(final_price) over w ,final_price)as next_price, 
	   final_price - coalesce(lag(final_price) over w ,final_price)
	   	as diff_from_prev, 
	   coalesce(lead(final_price) over w ,final_price) - final_price 
	   	as diff_to_next
from basic.enrollments 
window w as ( 
	partition by student_id 
	order by enroll_date, enrollment_id
)
order by student_id, enroll_date, enrollment_id;

 

- 윈도우 함수는 MySQL에서 SELECT / ORDER BY 에서만 사용 가능!!!

 

- 윈도우 함수 정리

분류	대표 함수	설명	GROUP BY 병행 여부
집계 함수	SUM(), MAX(), MIN(), AVG(), COUNT()	윈도우 범위 내 합계, 최대/최소, 평균, 개수 계산	가능
순위 함수	RANK(), DENSE_RANK(), ROW_NUMBER()	행에 순위를 매기거나 행 번호 부여	불가능
순서 함수	FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()	파티션 내 첫 값/마지막 값, 이전/다음 행 값 참조	불가능
비율/분석 함수	RATION_TO_REPORT(), PERCENT_RANK(), CUME_DIST(), NTILE(n)	비율, 누적 백분율, 분위수 계산	불가능

 

- STRING 함수

 

- MATH 함수

 

- 날짜 함수 DATE FUNCTION