[내일배움캠프] TIL 7일차 25.12.31(수)

SQL 라이브세션 6회차

 

NULL 처리: IFNULL / COALESCE (윈도우 함수량 같이 자주 씀)

 

IFNULL / COALESCE 같이 배우는 이유

- 윈도우 함수에서 특히 NULL이 자주 생기는 순간 존재

● LAG() 의 첫 행 -> "이전 행이 없음"이라 NULL

● LEAD() 의 마지막 행 -> "다음 행이 없음"이라 NULL

● LEFT JOIN 후 매칭이 없으면 컬럼이 NULL

● 조건부 합계( SUM ( CASE WHEN . . . THEN amount END ) 에서 조건을 만족하는 값이 없으면 NULL

=> 이럴 때 IFNULL / COALESCE로 결과를 보기 좋게 바꿈.

 

IFNULL문법 (MySQL)

IFNULL(expr1, expr2)

- expr1이 NULL이 아니면 expr1

- NULL이면 expr2 반환

 

COALESCE 문법

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

- 처음으로 NULL이 아닌 값 반환

 

IFNULL  vs COALESCE

- 2개 중 하나만 고르면 COALESCE 추천 (표준 + 여러 후보 가능)

- "NULL이면 이것" 만 필요하면 IFNULL도 깔끔

 

# 미니 실습 0 (NULL 처리)

✅ 아래 쿼리 먼저 실행 후 수행

insert into basic.students (student_id, student_name, signup_date, region, segment)
values ('st_0019', '천준석', '2025-12-29', null, 'new'),
	('st_0020', '설무아', '2025-12-30', null, 'returning'),
	('st_0021', '문길래', '2025-12-31', null, 'vip');

문제: students에서 region이 NULL이면 'UNKNOWN'으로 보이게 해보세요.

출력: student_id, student_name, region_clean

select
        student_id,
        student_name,
        ifnull(region,'unknown') as region_clean
from basic.students
order by student_id;

=> IFNULL() 외에도 COALESCE() 로도 동일한 결과 출력 가능

select
        student_id,
        student_name,
        COALESCE(region,'unknown') as region_clean
from basic.students
order by student_id;

 

그룹 합 / 개수 / 평균을 행 유지한 채로 붙이기 (COUNT / SUM / AVG OVER)

 

윈도우 함수 기본 문법

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

- 행을 줄이지 않고(=원본 행 유지), 계산 결과를 컬럼으로 붙임

- MySQL에서는 윈도우 함수는 SELECT 리스트 / ORDER BY에서만 사용 가능 -> 필터는 CTE로

- WHERE, GROUP BY, HAVING 에서는 직접 못 씀.

 

GROUP BY vs COUNT OVER (행이 줄어드냐 유지되냐)

- GROUP BY : 행이 줄어듦

   => 집계 목적

- COUNT(*) OVER (PARTITION BY . . . ) 원래 행 수 유지 + 컬럼 추가

   => 원본 행 + 맥락(집계값) 유지 목적

   ** SUM / AVG 도 COUNT와 동일하게 이용 가능

 

# 미니 실습 1 (COUNT / SUM OVER)

enrollments에서 각 행마다

• enrollment_id
• student_id
• course_id
• student_enroll_cnt (학생 기준 신청 건수)
• course_enroll_cnt (강좌 기준 신청 건수)

를 같이 붙이세요.

select
       enrollment_id,
       student_id,
       course_id,
       count(*) over (partition by student_id) as student_enroll_cnt,
       count(*) over (partition by course_id) as course_enroll_cnt
from basic.enrollments
order by enrollment_id;

 

그룹별 순번 / 순위 ( ROW_NUMBER / RANK / DENSE_RANK )

- 대표적 "경쟁 계열" 윈도우 함수

 

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

- 파티션 내에서 행에 번호를 붙임

- ORDER BY가 없으면 결과가 비결정적 (매번 달라질 수 있음)

 

RANK vs DENSE_RANK (동점 처리)

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

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

 

그룹별 Top N 뽑기 (실무 최빈도)

- MySQL에서는 윈도우 함수 결과를 WHERE에서 직접 못 씀

   => CTE로 감싼 뒤 바깥에서 필터해야 함.

 

# 미니 실습 2 (Top N)

강좌별(course_id)로 final_price가 높은 신청 Top 2를 뽑아보세요.

출력: course_id, enrollment_id, student_id, final_price

정렬 : ORDER BY course_id, rn

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 rn
     from basic.enrollments
)
select
        course_id,
        enrollment_id,
        student_id,
        final_price
from ranked
where rn<=2
order by course_id, rn;

 

누적합 / 비율 / 최근 N개 평균 ( SUM / AVG OVER + ORDER BY + Frame)

 

프레임(Frame)

- MySQL은 ORDER BY가 있는 윈도우 집계에서 기본 프레임 존재

- ORDER BY가 있으면 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW임.

   => "그룹의 첫 행 ~ 현재 행까지 누적" 이라는 뜻

** 누적합은 ROWS 프레임 명시

    => ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

 

# 미니 실습 3 (누적합)

payments에서 payment_status='paid'만 대상으로, 결제 시간순 누적 결제금액을 구하세요.

출력: payment_id, paid_at, amount, cum_amount

select
        payment_id,
        paid_at,
        amount,
        sum(amount) over (
                order by payment_id
                rows between unbounded preceding and current row
        ) as cum_amount
from basic.payments
where payment_status = 'paid'
order by payment_id;

=> ORDER BY에 paid_at도 추가해야 한다.

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;

 

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

 

LAG, LEAD

- 시계열 / 세션 / 로그 데이터에서 자주 쓰임

- LAG(값) : 내 바로 "이전 행"의 값을 가져온다.

● 기본 문법

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

● LAG(expr, N, default) OVER ( . . .)

    => N과 default를 생략하면 기본값은 N=1, default=NULL

- LEAD(값) : 내 바로 "다음 행"의 값을 가져온다.

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

● N 기본값 = 1 (한 칸 전 / 후)

● default 없을 때 대처 값 (기본값 = NULL)

● ORDER BY는 사실상 필수("전/후" 기준이 필요)

 

** 윈도우 함수는 MySQL에서 SELECT 리스트 / ORDER BY에서만 쓸 수 있음.

     => 보통 CTE로 감싸서 필터

 

"WINDOW로 이름 붙이기"도 쉬운 형태로 (중복 OVER 줄이기)

- 같은 OVER( . . . ) 여러 번 쓰면 보기 싫음

    => WINDOW w AS ( . . . )로 창 정의를 한 번 만 작성 가능

        ** w라는 "줄세우는 규칙"을 한 번만 적고 재사용하는 것

 

# 미니 실습 4 - 학생별 "이전/다음 신청 금액" + "변화량" 만들기 (LAG/LEAD + COALESCE)

basic.enrollments에서 **학생별(student_id)**로 신청을 시간순으로 정렬했을 때, 각 신청 행에 아래 컬럼을 만들어 출력하세요.

정렬 기준(줄 세우기 기준):

• enroll_date 오름차순
• 같은 날짜면 enrollment_id 오름차순

출력 컬럼:

• enrollment_id, student_id, enroll_date, final_price
• prev_price : 바로 이전 신청의 final_price
  • 이전 신청이 없으면(첫 행) → 현재값(final_price) 으로 대체
• next_price : 바로 다음 신청의 final_price
  • 다음 신청이 없으면(마지막 행) → 현재값(final_price) 으로 대체
• diff_from_prev : final_price - prev_price (첫 행은 0이 되게)
• diff_to_next : next_price - final_price (마지막 행은 0이 되게)

힌트:

• LAG(final_price) / LEAD(final_price)는 기본적으로 “한 칸 전/후”를 보며, 값이 없으면 NULL이 나옵니다.
• NULL은 COALESCE(x, final_price)로 현재값으로 바꿔주세요.

with w as (
   select
           enrollment_id,
           student_id,
           enroll_date,
           final_price,
           lag(final_price) over (
                partition by student_id
                order by enroll_date, enrollment_id
           ) as prev_price,
           lead(final_price) over (
                partition by student_id
                order by enroll_date, enrollment_id
           ) as next_price
   from basic.enrollments
)
select
        enrollment_id,
        student_id,
        enroll_date,
        final_price,
        prev_price,
        next_price,
        final_price - coalesce(prev_price, final_price) as diff_from_prev,
        coalesce(next_price, final_price) - final_price as diff_to_next
from w
order by enroll_date asc, enrollment_id asc

=> NULL이 나오지 않게 prev_price, next_price에도 coalesce()를 적용시켜줘야한다.

WITH w AS (
     SELECT
                enrollment_id,
                student_id,
                enroll_date,
                final_price,
                LAG(final_price) OVER (
                     PARTITION BY student_id
                     ORDER BY enroll_date, enrollment_id
                ) AS prev_raw,
                LEAD(final_price) OVER (
                     PARTITION BY student_id
                     ORDER BY enroll_date, enrollment_id
                 ) AS next_raw
      FROM basic.enrollments
)
SELECT
           enrollment_id,
           student_id,
           enroll_date,
           final_price,
           COALESCE(prev_raw, final_price) AS prev_price,
           COALESCE(next_raw, final_price) AS next_price,
           final_price - COALESCE(prev_raw, final_price) AS diff_from_prev,
           COALESCE(next_raw, final_price) - final_price AS diff_to_next
FROM w
ORDER BY student_id, enroll_date, enrollment_id;

 => 출력오류가 많았던 전 Query 대비 출력 결과가 분명하게 나온다.

 

# 데일리 과제 5

 

과제 1 - NULL 처리 + JOIN 1번 ( COALESCE / IFNULL 연습 )

✅ 상황

운영팀이 신청 내역을 볼 때,

• 학생 region이 비어있으면 "UNKNOWN"으로 보이고
• coupon_code가 비어있으면 "no_coupon"으로 보이길 원합니다.

✅ 목표

enrollment_status IN ('active','completed')인 신청만 대상으로 아래를 출력하세요.

출력 컬럼

• enrollment_id
• student_id
• student_name
• region_clean : COALESCE(s.region, 'UNKNOWN')
• coupon_label : IFNULL(e.coupon_code, 'no_coupon')

정렬

• enrollment_id 오름차순

select
       e.enrollment_id,
       e.student_id,
       s.student_name,
       coalesce(s.region, 'UNKNOWN') as region_clean,
       ifnull(e.coupon_code, 'no_coupon') as coupon_label
from basic.enrollments as e
inner join basic.students as s
on e.student_id = s.student_id
where enrollment_status in ('active','completed')
order by enrollment_id asc;

 

과제 2 - "행 유지" 윈도우 집계 (COUNT / SUM / AVG OVER)

✅ 상황

분석팀이 enrollments(신청 1건=1행)를 유지한 채로, 학생별 통계를 “옆에 붙여서” 보고 싶어합니다.

✅ 목표

enrollments에서 각 행마다 아래 컬럼을 붙여 출력하세요.

출력 컬럼

• enrollment_id
• student_id
• final_price
• student_enroll_cnt : 학생 기준 신청 건수
• student_total_spend : 학생 기준 총 결제(신청금액 합)
• student_avg_price : 학생 기준 평균 신청금액

정렬

• student_id, enrollment_id 오름차순

select
        enrollment_id,
        student_id,
        final_price,
        count(*) over (partition by student_id) as student_enroll_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 student_id, enrollment_id asc;

 

과제 3 - 학생별 "가장 비싼 신청 1건" 만 뽑기 (ROW_NUMBER + CTE)

✅ 상황

마케팅팀이 학생별로 “가장 고가 강좌 신청 1건”만 뽑아 VIP 안내를 보내려 합니다.

✅ 목표

enrollment_status IN ('active','completed')만 대상으로,

학생별로 final_price가 가장 큰 신청 1건만 남기세요.

 

동점이면 enrollment_id가 더 작은 것을 1건으로 선택하세요.

 

출력 컬럼

• student_id
• enrollment_id
• final_price

정렬

• student_id 오름차순

with ranked as (
     select
             student_id,
             enrollment_id,
             final_price,
             row_number() over (
                    partition by student_id
                    order by final_price desc, enrollment_id
             ) as rn
     from basic.enrollments
     where enrollment_status in ('active','completed')
)
select
        student_id,
        enrollment_id,
        final_price
from ranked
where rn=1
order by student_id asc, rn

 

과제 4 - 결제 로그 "이전 값 + 변화량 + 누적합" 만들기 (LAG + SUM OVER)

✅ 상황

재무팀이 paid 결제 로그를 시간순으로 보면서,

• 바로 이전 결제 금액
• 이전 대비 증감
• 지금까지 누적 결제액
• 을 한 번에 보고 싶어합니다.

✅ 목표

payment_status='paid'인 결제만 대상으로 아래를 출력하세요.

출력 컬럼

• payment_id
• paid_at
• amount
• prev_amount : 이전 결제의 amount (없으면 0)
• diff_from_prev : amount - prev_amount
• cum_amount : 시간순 누적 결제액

정렬

• paid_at, payment_id 오름차순

with w as(
     select
             payment_id,
             paid_at,
             amount,
             lag(amount) over (order by payment_id) as pre
      from basic.payments
)
select
        payment_id,
        paid_at,
        amount,
        coalesce(pre, 0) as prev_amount,
        amount - coalesce('prev_amount', amount) as diff_from_prev,
        sum(amount) over (
               order by payment_id, paid_at
               rows between unbounded preceding and current row
        ) as cum_amount
from w
order by paid_at, payment_id;

=> 조건으로 만든 컬럼들이 제대로 출력되지 않음. ai에게 조언 부탁.  CTE 속 조건을 추가하고, 정렬을 일치하며, 식을 수정하라는 힌트를 받음. 그러나 적요한 뒤에도 잘못된 결과 출력되는 문제 지속적 발생. 한 번 더 조언을 구해본 결과 ORDER BY의 순서를 바꿔보라는 조언을 얻음. 동일한 결과를 출력함. 이의 코드는 아래와 같음.

with w as(
     select
             payment_id,
             paid_at,
             amount,
             lag(amount) over (order by paid_at, payment_id) as pre
      from basic.payments
      where payment_status = 'paid'
)
select
        payment_id,
        paid_at,
        amount,
        coalesce(pre, 0) as prev_amount,
        amount - coalesce(coalesce(pre, 0), amount) as diff_from_prev,
        sum(amount) over (
                order by paid_at, payment_id
                rows between unbounded preceding and current row
        ) as cum_amount
from w
order by paid_at, payment_id;

 

# 데일리 퀴즈 5

 

Q1. MySQL에서 윈도우 함수 사용 위치로 올바른 것은?

A. WHERE 절에서만 사용 가능하다

B. GROUP BY 절에서만 사용 가능하다

C. SELECT 리스트와 ORDER BY 절에서만 사용 가능하다

D. JOIN ON 절에서만 사용 가능하다

 

답) C. SELECT 리스트와 ORDER BY 절에서만 사용 가능하다

 

Q2. LAG(expr, N, default)에 대한 설명으로 올바른 것은?

A. N의 기본값은 0이다

B. 이전 행이 없으면 default를 반환한다

C. default의 기본값은 0이다

D. ORDER BY 없이도 항상 같은 결과가 보장된다

 

답) B. 이전 행이 없으면 default를 반환한다  

 

Q3. COALESCE(a, b, c)에 대한 설명으로 올바른 것은?

A. 세 값이 모두 NULL이면 0을 반환한다

B. 첫 번째 인자만 검사하고 NULL이면 항상 b를 반환한다

C. 왼쪽부터 보면서 처음으로 NULL이 아닌 값을 반환한다

D. NULL을 검사할 수 없는 함수다

 

답) C. 왼쪽부터 보면서 처음으로 NULL이 아닌 값을 반환한다

해설) A. 세 값이 모두 NULL이면 NULL을 반환한다.

         B. 첫 번째 인자만 검사하고 NULL이면 두 번째 인자를 검사한다.

         D. NULL을 검사하는 함수다.

 

Q4. ROW_NUMBER()와 RANK()의 차이로 가장 올바른 것은?

A. 둘 다 동점이면 같은 순위를 준다

B. ROW_NUMBER는 동점이어도 서로 다른 번호를 준다

C. RANK는 ORDER BY 없이도 항상 안정적이다

D. ROW_NUMBER는 GROUP BY가 있어야만 사용 가능하다

 

답) B. ROW_NUMBER는 동점이어도 서로 다른 번호를 준다

해설) A. RANK()는 동점이면 같은 순위를 주지만(1,2,2), ROW_NUMBER()는 동점이어도 순위를 매긴다.(1,2,3) 

         C. RANK는 ORDER BY가 필수이다.

         D. ROW_NUMBER는 GROUP BY가 없어도 사용 가능하다.

 

Q5. 아래 누적합 문법에서 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW의 의미로 가장 올바른 것은?

A. 현재 행 한 줄만 합산한다

B. 파티션의 마지막 행부터 현재 행까지 합산한다

C. 파티션의 첫 행부터 현재 행까지 누적 합산한다

D. 파티션 전체(모든 행)를 항상 합산한다

 

답) C. 파티션의 첫 행부터 현재 행까지 누적 합산한다