[내일배움캠프] TIL 6일차 25.12.30(화)

엑셀보다 쉽고 빠른 SQL 5주차

 

조회한 데이터에 아무 값이 없다면?

데이터가 없을 때의 연산 결과 변화 케이스

- 테이블에 잘못된 값이 있는 경우

-  JOIN을 했을 때 값이 없는 경우

 

방법 1) 없는 값 제외

- 연산에서 제외 -> 0으로 간주

- 명확하게 연산을 지정해주기 위해 null 문법 이용

** null 제거 후 JOIN 시 INNER JOIN과 동일

 

방법 2) 다른 값 대신 사용

- 다른 값으로 대체

- 데이터 분석 시 평균값 혹은 중앙값 등 대표값으로 대체

- 다른 값으로 변경 가능한 문법

● 다른 값이 있을 때 조건문 이용 -> IF(rating>=1, rating, 대체값)

● null 값일 때 -> coalesce(age, 대체값)

 

조회한 데이터가 상식적이지 않은 값이라면?

 

방법) 조건문으로 값 범위 지정

- 조건문으로 가장 큰 값, 가장 작은 값의 범위 지정

** 상식적인 수준 내 범위 지정

 

SQL로 Pivot Table 만들어보기

 

Pivat Table

- 2개 이상의 기준으로 데이터 집계할 때, 보기 쉽게 배열하여 보여주는 것

- 기본 구조

- 예시 (집계 기준: 일자/시간)

 

# 실습 1

음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

 

1. 음식점별, 시간별 주문건수 집계하기

select
        a.restaurant_name,
        substring(b.time, 1, 2) hh,
        count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2

=> substring은 문자열의 특정 부분만 추출하는 것이다. time컬럼의 첫번째 글자부터 두번째 글자까지 추출한것이다.

 

2. Pivot view 구조 만들기

select
        restaurant_name,
        max(if(hh='15', cnt_order, 0)) "15",
        max(if(hh='16', cnt_order, 0)) "16",
        max(if(hh='17', cnt_order, 0)) "17",
        max(if(hh='18', cnt_order, 0)) "18",
        max(if(hh='19', cnt_order, 0)) "19",
        max(if(hh='20', cnt_order, 0)) "20"
from
(
select
        a.restaurant_name,
        substring(b.time, 1, 2) hh,
        count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

=> Subquery를 통해 음식점별, 시간별 주문건수를 먼저 집계해 Pivot Table을 만드는데 필요한 데이터를 우선 정리한다. 그 후 본 Query에서 시간별 음식점별 주문건수를 출력하게해준다.

 

# 실습 2

성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

 

1. 성별, 연령별 주문건수 집계하기

select
        b.gender,
        case
              when age between 10 and 19 then 10
              when age between 20 and 29 then 20
              when age between 30 and 39 then 30
              when age between 40 and 49 then 40
              when age between 50 and 59 then 50 end age,
        count(1)
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2

=> 연령대별로 분리해 age라는 컬럼을 새로 만들어준다. COUNT(1)로 b.gender에 주문건수가 얼마나 있는지 계산한다. 조건에 따라 나이는 10 ~ 59세 사이로 지정해준다.

 

2. Pivot view 구조 만들기

select
         age,
         max(if(gender='male', order_count, 0)) male,
         max(if(gender='female', order_count, 0)) female
from
(
select
         b.gender,
         case
               when age between 10 and 19 then 10
               when age between 20 and 29 then 20
               when age between 30 and 39 then 30
               when age between 40 and 49 then 40
               when age between 50 and 59 then 50
         end age,
         count(1) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2
) t
group by 1
order by 1 desc

=> Subquery에서 집계한 성별, 연령별 주문건수를 바탕으로 본 Query를 작성한다. 마지막 조건인 연령 순 내림차순을 'ORDER BY age' 로 마무리한다.

 

 Window Function - RANK, SUM

Window Function

- 각 행의 관계를 정의하기 위한 함수

- 그룹 내 연산을 쉽게 만들어줌

- 기본 SQL 구조로 해결하기 위해서는 복잡하게 Subquery 문을 이용하거나, 여러번의 연산을 수행해줘야 하지만, 자체적으로 제공해주는 기능

- 기본 구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

● window function : 기능 명 사용 (sum, avg 등)

● argument : 함수에 따라 작성 혹은 생략

● partition by : 그룹을 나누기 위한 기준. (group by 절과 유사)

● order by : window function 적용 시 정렬 할 컬럼 기준 작성

 

N 번째까지의 대상을 조회하고 싶을 때, Rank

- 특정 기준으로 순위를 매겨주는 기능

ex 1) 주문 건수별 순위 매기기

ex 2) 결제 시간이 빠른 순으로 순위 매기기

 

# 실습

음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

 

1. 음식 타입별, 음식점별 주문 건수 집계하기

select
        cuisine_type,
        restaurant_name,
        count(1) order_count
from food_orders
group by 1, 2

 

2. Rank 함수 적용하기

select
         cuisine_type,
          restaurant_name,
          rank() over (partition by cuisine_type order by order_count desc) rn,
          order_count
from
(
select
        cuisine_type,
        restaurant_name,
        count(1) order_count
from food_orders
group by 1, 2
) a

=> 문제에서 요구한 것이 음식 타입별 순위이다. 따라서 Rank 함수는 'cuisine_type' 컬럼에 사용한다. 

 

3. 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기

select
        cuisine_type,
        restaurant_name,
        order_count,
        rn "순위"
from
(
select
        cuisine_type,
        restaurant_name,
        rank() over (partition by cuisine_type order by order_count desc) rn,
        order_count
from
(
select
        cuisine_type,
        restaurant_name,
        count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3
order by 1, 4

=> 문제에서 궁극적으로 원했던 3위까지 조회 후, 음식 타입별, 순위별 정렬하기를 하는 단계이다. WHERE문을 통해 3위까지 나오게 조건을 걸어준다. 또한 ORDER BY문을 통해 'cuisine_type', 'rn' 컬럼으로 정렬한다.

 

전체에서 차지하는 비율, 누적합을 구할 때, Sum

- 합계를 구하는 기능

- 누적합이 필요하거나 카테고리별 합계컬럼과 원본 컬럼을 함께 이용할때 유용

- 동일한 값을 가진 여러 행이 있을 경우, SQL 엔진은 이 값을 한꺼번에 더하는 현상 발생(순서 결정할 명확한 기준이 없어서 발생)

  ▶ ORDER BY 에 추가적인 열에 순서 부여 가능한 컬럼 포함시키기

 

# 실습

각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

 

1. 음식 타입별, 음식점별 주문 건수 집계하기

select
        cuisine_type,
        restaurant_name,
        count(1) order_count
from food_orders
group by 1, 2

 

2. 카테고리별 합, 카테고리별 누적합 구하기

select
        cuisine_type,
        restaurant_name,
        cnt_order,
        sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
        sum(cnt_order) over (partition by cuisine_type order by cnt_order, restaurant_name) cum_cuisine
from
(
select
        cuisine_type,
        restaurant_name,
        count(1) cnt_order
from food_orders
group by 1, 2
) a
order by cuisine_type, cnt_order, cum_cuisine

 

날짜 포맷과 조건까지 SQL로 한 번에 끝내기 (포맷 함수)

 

날짜 데이터

- 문자타입, 숫자타입과 같이 날짜 데이터도 특정 타입을 가지고 있음.

- 년, 월, 일, 시, 분, 초 등의 값 모두 갖고 있으며, 목적에 따라 '월', '주', '일' 등으로 포맷 변경 가능

 

# 실습 1

날짜 데이터의 여러 포맷

 

1. yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

select date(date) date_type, date
from payments

 

2. date_type을 date_fomat을 이용하여 년, 월, 일, 주 로 조회해보기

• 년 : Y (4자리), y(2자리)
• 월 : M, m
• 일 : d, e
• 요일 : w

select
        date(date) date_type,
        date_format(date(date), '%Y') "년",
        date_format(date(date), '%m') "월",
        date_format(date(date), '%d') "일",
        date_format(date(date), '%w') "요일"
from payments

 

# 실습 2

년도별 3월의 주문건수 구하기

 

1. 년도, 월을 포함하여 데이터 가공하기

select
        date_format(date(date), '%Y') y,
        date_format(date(date), '%m') m,
        a.order_id
from sparta.food_orders a inner join sparta.payments b on a.order_id=b.order_id

 

2. 년도, 월별 주문건수 구하기

select
        date_format(date(date), '%Y') y,
        date_format(date(date), '%m') m,
        count(1) order_count
from food_orders a inner join payments b on a.order_id=b.order_id
group by 1, 2

 

3. 3월 조건으로 지정하고, 년도별로 정렬하기

select
        date_format(date(date), '%Y') "년",
        date_format(date(date), '%m') "월",
        date_format(date(date), 'Y%m') "년월",
        count(1) "주문건수"
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03'
group by 1, 2
order by 1

 

HW. 5주차 숙제

음식 타입별, 연령별 주문건수  pivot view 만들기 (연령은 10 ~ 59세 사이)

select
        cuisine_type,
        max(if(age=10, cnt_order, 0)) "10대",
        max(if(age=20, cnt_order, 0)) "20대",
        max(if(age=30, cnt_order, 0)) "30대",
        max(if(age=40, cnt_order, 0)) "40대",
        max(if(age=50, cnt_order, 0)) "50대"
from
(
select
        f.cuisine_type,
        case
              when c.age between 10 and 19 then 10
              when c.age between 20 and 29 then 20
              when c.age between 30 and 39 then 30
              when c.age between 40 and 49 then 40
              when c.age between 50 and 59 then 50
        end as age,
        count(1) as cnt_order
from food_orders as f inner join customers as c on f.customer_id = c.customer_id
where c.age between 10 and 59
group by 1,2
) as a
group by 1;

 

=> 처음엔 갈피를 못잡다 pivot view 실습 문제를 통해 힌트를 얻었다. 큰 틀은 같았지만 사소한 차이가 있었다. 맨 처음 오류가 발생했을 때 cuisine_type 컬럼을 COUNT 를 써서 세지 않았다. 또한 본 Query에서 "50대" 이후 ' , ' 이 있어 오류가 작동했다. 이후 찾는데 시간이 들긴했지만 찾아 해결할 수 있었다.

 

SQL 라이브 세션 5회차

 

CASE

- 조건에 따라 결과값을 바꿔주는 기능

- if / else 같은 느낌

- 값 비교형(Simple CASE)

CASE 컬럼
	WHEN 값1 THEN 결과1
	WHEN 값2 THEN 결과2
	ELSE 결과
END

- 조건식형(Searched CASE)

CASE 
	WHEN 조건1 THEN 결과1
	WHEN 조건2 THEN 결과2
	ELSE 결과
END

- 위에서부터 순서대로 검사해서, 처음 참인 WHEN에서 멈춤

- ELSE를 넣으면 '모르는 값이 와도' 일단 결과를 채워줘서 디버깅이 편함

 

# 미니 실습 1

students에서 segment가 vip이면 vip_user, 아니면 normal_user 라벨을 만들기

출력: student_id, student_name, segment, segment_label

select
        student_id,
        student_name,
        segment,
        case segment
              when 'vip' then 'vip_user'
              else 'normal_user'
        end as segment_label
from basic.students
order by student_id;

 

CASE에서 'NULL'이 나오면 어떻게 하지?

- MySQL은 NULL을 '값이 없음/모름'으로 취급

- NULL은 IS NULL, IS NOT NULL로 체크

 

# 미니 실습 2

enrollments에서 enrollment_status가

• active → '진행중'
• completed → '완료'
• cancelled → '취소'
• 로 보이게 출력

출력: enrollment_id, enrollment_status, status_kor

select
        enrollment_id,
        enrollment_status,
        case enrollment_status
              when 'active' then '진행중'
              when 'completed' then '완료'
              when 'cancelled' then '취소'
              else '기타'
        end as status_kor
from basic.enrollments
order by enrollment_id;

=> 모르는 값이 왔을때 해결을 위해 else '기타' 를 넣어주면 좋다. 

 

CASE를 '집계'에 썩어보기: 조건부 카운트

- SUM 사용

● CASE WHEN ... THIN 1 ELSE 0 은 0/1 을 만들고

● SUM 은 1들을 더해서 '몇 개인지'가 됨.

 

# 미니 실습 3

enrollments에서 상태별(enrollment_status) 개수 세기

출력:

• active_cnt
• completed_cnt
• cancelled_cnt

답)

select
        sum(case when enrollment_status='active' then 1 else 0 end) as active_cnt,
        sum(case when enrollment_status='completed' then 1 else 0 end) as completed_cnt,
        sum(case when enrollment_status='cancelled' then 1 else 0 end) as cancelled_cnt
from basic.enrollments

해설)

SELECT
           COUNT(distinct enrollment_id) AS Total,
           SUM(CASE WHEN enrollment_status='active' THEN 1 ELSE 0 END)AS active_cnt,
           SUM(CASE WHEN enrollment_status='completed' THEN 1 ELSE 0 END)AS completed_cnt,
           SUM(CASE WHEN enrollment_status='cancelled' THEN 1 ELSE 0 END)AS cancelled_cnt
FROM basic.enrollments;

 => 총계를 더해주면 좋은듯..? 하다. 문제 조건상에는 없어서 안적었다.

 

Subquery (서브쿼리) - "쿼리 안의 쿼리"

 

서브쿼리

- 다른 SQL 안에 들어있는 SELECT

- 종류

● scalar(값 1개)

# 미니 실습 4 (Scalar Subquery)

enrollments에서 final_price가 “전체 평균 final_price”보다 큰 신청만 보기

출력: enrollment_id, final_price

select
        enrollment_id,
        final_price
from basic.enrollments
where final_price > (select avg(final_price) from basic.enrollments)
order by final_price desc;

● row(행 1개)

● column(열 1개 = 리스트)

● table(표) (derived table)

 

리스트 서브쿼리: IN ( . . . )

- IN ( . . . ) 안쪽은 한 컬럼짜리 리스트 나오면 성공

- 리스트 필터

- 서브쿼리에서 "한 컬럼만" 뽑는 게 안전

 

# 미니 실습 5

level='beginner'인 강좌를 신청한 enrollments만 출력

출력: enrollment_id, course_id

select
         enrollment_id,
         course_id
from basic.enrollments
where course_id in (
select
         course_id
from basic.courses
where level='beginner'
)
order by enrollment_id;

 

EXISTS / NOT EXISTS ("없다 / 있다" 찾기 최고)

- 행을 하나라도 반환하면 EXISTS (subquery) 는 TRUE

- NOT EXISTS는 FALSE

- EXISTS 안쪽의 SELECT 목록은 사실상 중요하지 않고, MySQL은 SELECT 리스트를 무시하므로 SELECT *, SELECT 1, SELECT 5 무엇을 써도 결과가 같다.

-> EXISTS (subquery): 존재 여부(한 행이라도 있으면 TRUE) + SELECT 리스트는 무시해도 됨.

 

# 미니 실습 6

“결제 테이블(payments)에 기록이 한 번도 없는 신청(enrollment)” 찾기

출력: enrollment_id

select
          e.enrollment_id
from basic.enrollments as e
where not exists (
select 1
from basic.payments as p
where e.enrollment_id = p.enrollment_id
)
order by e.enrollment_id;

 

상관 서브쿼리(Correlated Subquery)

- 서브쿼리 안에서 바깥 쿼리의 테이블 컬럼을 참조하는 서브쿼리

- 바깥 쿼리에서 한 행을 꺼낼 때마다, 그 행에 맞춰서 안쪽 쿼리가 계산된다.

 

FROM 서브쿼리(파생 테이블) - "괄호 속 SELECT를 표로 만들기"

- N쪽 테이블을 그대로 JOIN하면 행이 늘어난다. 그래서 먼저 N쪽을 "요약한 표"로 만들어 붙일 것

- 기본 문법

SELECT ...
FROM (SELECT ...) AS 별칭;

- FROM 절에 서브쿼리를 넣으면 "derived table"

- 별칭(AS 별칭)이 필수

- JOIN은 "두 표"만 붙이면 됨.

- 파생테이블 (Derived Table) 은 이미 course_id 단위로 1행씩이라서 이해가 쉬움.

 

# 미니 실습 7

학생별 신청 건수(enroll_cnt)를 students에 붙여서 출력

출력: student_id, student_name, enroll_cnt (신청 없으면 0)

 

답)

select
           s.student_id,
           s.student_name,
           p.enroll_cnt
from basic.students as s
left join (
    select
           enrollment_id,
           count(*) as enroll_cnt
    from basic.payments
    group by enrollment_id
) as a
on a.enrollment_id = s.

 해설)

SELECT
           s.student_id,
           s.student_name,
           IFNULL(enr.enroll_cnt, 0) AS enroll_cnt
FROM basic.students s
LEFT JOIN (
    SELECT
           student_id,
           COUNT(*) AS enroll_cnt
    FROM basic.enrollments
    GROUP BY student_id
) AS enr
    ON s.student_id = enr.student_id
ORDER BY s.student_id;

=> 서브쿼리의 테이블을 잘못 선택했다. 이후 오류가 연쇄적으로 발생한다. 또한 IFNULL(컬럼, 0)을 통해 NULL이면 0 아니라면 컬럼이 출력되게 해줘야한다. 이외에는 큰 틀은 맞게 작성했다.

 

CTE (WITH) - "이름 붙인 파생테이블"

- CTE(Common Table Expression)

- 이름이 있는 임시 결과

- 단일 statement 범위에서만 존재하는 named temporary result set이고, 그 statement 안에서 여러번 참조할 수 있다.

- 쿼리 위에 붙이는 메모장(임시표)

- 쿼리 1번 실행하는 동안만 존재

- 기본 문법

WITH 이름 AS (
     SELECT ...
)
SELECT *
FROM 이름;​

- with 절에서 CTE를 여러 개 만들면 콤마로 나열한다.

- 순서

1) 임시표 만들기

2) 임시표에서 출력하기

- CTE 안에는 "평소 SELECT"를 그대로 넣으면 됨.

- 이후에는 그냥 테이블로 생각

 

# 미니 실습 8

is_active = 1인 강좌만 뽑는 CTE(active_courses)를 만들고, 그 CTE를 조회하세요.

출력: course_id, course_name, is_active

with active_courses as (
            select
                   course_id,
                   course_name,
                   is_active
            from basic.courses
            where is_active = 1
)
select *
from active_courses
order by course_id;

 

자주 나는 에러 & 즉시 처방

- 컬럼 = NULL 로 비교

    => 컬럼 IS NULL 로 바꾸기

- 파생테이블 별칭 빼먹음

    => FROM (SELECT . . .) AS dt 꼭 쓰기

- 서브쿼리가 값 1개여야 하는데 여러 줄 나옴

    => 1) 집계함수(AVG,MAX 등)로 1개 값으로 만들기

         2) IN / EXISTS 로 문법 바꾸기

 

# 데일리 과제 4

과제 1 - CASE로 "쿠폰 사용 여부 + 가격 구간" 라벨 만들기

✅ 상황

운영팀이 enrollments를 볼 때, 쿠폰 사용 여부와 결제금액 구간이 한눈에 보이길 원합니다.

✅ 목표

enrollment_status가 active, completed인 신청만 대상으로 아래 컬럼을 출력하세요.

출력 컬럼

• enrollment_id
• student_id
• final_price
• coupon_flag
  • 쿠폰 미사용(coupon_code IS NULL) → 'no_coupon'
  • 쿠폰 사용 → 'coupon_used'
• price_bucket
  • final_price < 50000 → 'low'
  • 50000 <= final_price < 90000 → 'mid'
  • final_price >= 90000 → 'high'

정렬

• enrollment_id 오름차순

select
         enrollment_id,
         student_id,
         final_price,
         case
               when coupon_code is null then 'no_coupon'
               else 'coupon_used'
         end as coupon_flag,
         case
               when final_price <50000 then 'low'
               when final_price between 50000 and 90000 then 'mid'
               when final_price >=90000 then 'high'
         end as price_bucket
from basic.enrollments
where enrollment_status in ('active','completed')
order by enrollment_id asc;

 

과제 2 - IN 서브쿼리로 "SQL 카테고리 강좌를 신청한 학생" 찾기

✅ 상황

마케팅팀이 SQL 카테고리 강좌를 신청한 학생에게만 안내 메시지를 보내려 합니다.

✅ 목표

courses.category = 'sql'인 강좌를 한 번이라도 신청한 학생 목록을 출력하세요.

(신청 상태는 active, completed만 포함 / cancelled 제외)

출력 컬럼

• student_id
• student_name

정렬

• student_id 오름차순

답)

select
        student_id,
         student_name
from basic.students
where student_id in (
         select
                 student_id
         from basic.courses
         where category = 'sql'
)
order by student_id asc;

=> 신청 상태를 걸러내는 조건을 작성하지 않았다. 

select
         student_id,
         student_name
from basic.students
where student_id in (
         select
                  student_id
         from basic.courses
         where category = 'sql'
                  and student_id in (
                           select
                                    student_id
                           from basic.enrollments
                           where enrollment_status in ('active','completed')
                  )
)
order by student_id asc;

=> 조건을 추가해봤지만 여전히 출력결과가 다름. ai에게 힌트 요구.  Gemini가 제공한 수정 방향으로는 서브쿼리 순서를 바꿔보라고 제시함. 서브쿼리 순서를 바꿔서 재작성.

select
        student_id,
        student_name
from basic.students
where student_id in (
          select
                  student_id
          from basic.enrollments
          where enrollment_status in ('active','completed')
                    and course_id in (
                              select
                                      course_id
                              from basic.courses
                              where category = 'sql'
                    )
)
order by student_id asc;

=> 결과 출력 성공 ヾ(≧▽≦*)o

 

과제 3 - NOT EXISTS로 "쿠폰을 한 번도 사용하지 않은 학생" 찾기

✅ 상황

쿠폰을 한 번도 사용하지 않은 학생에게 “첫 쿠폰 혜택”을 주려고 합니다.

✅ 목표

아래 조건을 만족하는 학생만 출력하세요.

• 해당 학생의 enrollments 중에서
• coupon_code IS NOT NULL인 행이 단 한 건도 없는 학생

 

포인트: “없다”를 찾는 가장 대표 패턴이 NOT EXISTS 입니다.

 

출력 컬럼

• student_id
• student_name

정렬

• student_id 오름차순

 

 

과제 4 — CTE로 “학생별 신청/쿠폰 요약 + 라벨( CASE )” 만들기

✅ 상황

운영팀이 학생별로 “신청 수 / 쿠폰 사용 수 / 쿠폰 유저 여부 / 신청 유저 여부”를 한 번에 보고 싶어합니다.

(5회차 라이브 세션에서 만든 쿼리 형태를 그대로 활용)

✅ 목표

CTE 2개로 요약 테이블을 만든 뒤 students에 LEFT JOIN해서 아래를 출력하세요.

단, 신청 상태는 active, completed만 포함 (취소 제외)

출력 컬럼

• student_id
• student_name
• enroll_cnt (신청 수)
• coupon_used_cnt (쿠폰 사용 신청 수)
• enroll_flag
  • enroll_cnt = 0 → 'no_enroll'
  • 그 외 → 'has_enroll'
• coupon_user_flag
  • coupon_used_cnt = 0 → 'no_coupon_user'
  • 그 외 → 'coupon_user'
• coupon_usage_rate_pct
  • enroll_cnt = 0이면 0
  • 그 외 ROUND(coupon_used_cnt / enroll_cnt * 100, 1)

정렬

• student_id 오름차순

답)

WITH 
enroll_by_student AS (
         SELECT student_id, COUNT(*) AS enroll_cnt
         FROM basic.enrollments
         GROUP BY student_id
),
coupon_by_student AS (
         SELECT student_id, COUNT(*) AS coupon_used_cnt
         FROM basic.enrollments
         WHERE coupon_code IS NOT null and enrollment_status in ('active', 'completed')
         GROUP BY student_id
)
SELECT
         s.student_id,
         s.student_name,
         IFNULL(e.enroll_cnt, 0) AS enroll_cnt,
         IFNULL(c.coupon_used_cnt, 0) AS coupon_used_cnt,
         case
                when ifnull(enroll_cnt, 0) = 0 then 'no_enroll'
                else 'has_enroll'
         end as enroll_flag,
         CASE
                 WHEN IFNULL(c.coupon_used_cnt, 0) = 0 THEN 'no_coupon_user'
                 ELSE 'coupon_user'
         END AS coupon_user_flag,
         case
                when ifnull(enroll_cnt, 0) = 0 then 0
                else round(ifnull(coupon_used_cnt,0)/ifnull(enroll_cnt,0)*100, 1)
         end as coupon_usage_rate_pct
FROM basic.students s
LEFT JOIN enroll_by_student e
         ON s.student_id = e.student_id
LEFT JOIN coupon_by_student c
         ON s.student_id = c.student_id
ORDER BY s.student_id asc;

=> 큰 틀은 맞지만 결과에 특정 값이 다르게 출력됨. ai에게 힌트 요구.  Gemini가 제공한 수정 방향으로는 조건이 걸린 위치에 의문을 제기함. 그래서 신청 상태 조건을 두번째 CTE에서 첫번째 CTE로 옮겨 입력함.

WITH
enroll_by_student AS (
         SELECT student_id, COUNT(*) AS enroll_cnt
         FROM basic.enrollments
         where enrollment_status in ('active', 'completed')
         GROUP BY student_id
),
coupon_by_student AS (
         SELECT student_id, COUNT(*) AS coupon_used_cnt
         FROM basic.enrollments
         WHERE coupon_code IS NOT null
         GROUP BY student_id
)
SELECT
         s.student_id,
         s.student_name,
         IFNULL(e.enroll_cnt, 0) AS enroll_cnt,
         IFNULL(c.coupon_used_cnt, 0) AS coupon_used_cnt,
         case
                when ifnull(enroll_cnt, 0) = 0 then 'no_enroll'
                else 'has_enroll'
         end as enroll_flag,
         CASE
                 WHEN IFNULL(c.coupon_used_cnt, 0) = 0 THEN 'no_coupon_user'
                 ELSE 'coupon_user'
         END AS coupon_user_flag,
         case
                when ifnull(enroll_cnt, 0) = 0 then 0
                else round(ifnull(coupon_used_cnt,0)/ifnull(enroll_cnt,0)*100, 1)
         end as coupon_usage_rate_pct
FROM basic.students s
LEFT JOIN enroll_by_student e
         ON s.student_id = e.student_id
LEFT JOIN coupon_by_student c
         ON s.student_id = c.student_id
ORDER BY s.student_id asc;

 

# 데일리 퀴즈 4

Q1. CTE(WITH)에 대한 설명으로 가장 올바른 것은?

A. CTE는 실행하면 DB에 영구 테이블로 저장된다

B. CTE는 단일 statement 범위에서만 존재하며, 그 statement 안에서 여러 번 참조할 수 있다

C. CTE는 GROUP BY가 반드시 포함되어야 한다

D. CTE는 MySQL에서 지원되지 않는다

 

답) B. CTE는 단일 stratement 범위에서만 존재하며, 그 statement 안에서 여러 번 참조할 수 있다.

해설) A. CTE는 실행하면 DB에 영구 테이블로 저장되지 않는다.

         C. CTE는 GROUP BY가 반드시 있을 필요는 없다.

         D. CTE는 MySQL에서 지원한다.

 

Q2. MySQL에서 아래처럼 FROM 절에 서브쿼리를 넣을 때 반드시 필요한 것은?

SELECT *
FROM (SELECT course_id FROM basic.courses);

A. WHERE 절

B. ORDER BY 절

C. 파생 테이블(derived table)의 별칭(alias)

D. LIMIT 절

 

답) C. 파생 테이블(derived table)의 별칭(alias)

 

Q3. EXISTS 서브쿼리에 대한 설명으로 올바른 것은?

A. 서브쿼리가 0행을 반환하면 EXISTS는 TRUE이다

B. 서브쿼리가 1행 이상을 반환하면 EXISTS는 TRUE이다

C. EXISTS는 서브쿼리의 SELECT 컬럼 목록을 반드시 실제로 사용해서 계산한다

D. EXISTS는 반드시 GROUP BY와 함께 써야 한다

 

답) B. 서브쿼리가 1행 이상을 반환하면 EXISTS는 TRUE이다.

해설) A. 서브쿼리가 0행을 반환하면 EXISTS는 FALSE이다.

         C. EXISTS는 서브쿼리의 SELECT 컬럼 목록은 사실상 중요하지 않다.

         D. EXISTS는 GROUP BY가 필수가 아니다.

 

Q4. CASE WHEN(CASE 표현식) 동작에 대한 설명으로 가장 올바른 것은?

A. 모든 WHEN 조건을 끝까지 평가한 뒤, TRUE인 THEN 결과를 모두 합쳐 반환한다

B. 위에서부터 조건을 평가하며 처음 TRUE가 된 THEN의 값을 반환하고, ELSE가 없고 조건이 모두 FALSE면 NULL을 반환할 수 있다

C. CASE는 WHERE 절에서만 사용할 수 있다

D. CASE는 숫자 결과만 반환할 수 있다

 

답) B. 위에서부터 조건을 평가하며 처음 TRUE가 된 THEN의 값을 반환하고, ELSE가 없고 조건이 모두 FALSE면 NULL을 반환할 수 있다

해설) A. B.와 같음.

         C. CASE는 SELECT 절에서 사용할 수 있다.

         D. CASE는 숫자 결과 외에도 반환할 수 있다.

 

Q5. IFNULL(expr1, expr2)의 동작으로 올바른 것은?

A. expr1이 NULL이면 expr1을 반환한다

B. IFNULL은 인자를 3개 이상 받을 수 있다

C. expr1이 NULL이 아니면 expr1을, NULL이면 expr2를 반환한다

D. IFNULL은 NULL 값을 다른 값으로 대체할 수 없다

 

답) C. expr1이 NULL이 아니면 expr1을, NULL이면 expr2를 반환한다.

해설) A. expr1이 NULL이면 expr2를 반환한다.

         B. IFNULL은 인자를 2개만 받을 수 있다. 3개 이상의 인자를 받을 수 있는 것은 COALESCE(인자1, 인자2, 인자3, ...)을 써야 한다.

         D. IFNULL은 NULL 값을 다른 값으로 대체할 수 있다.

 

오늘의 회고

TIL이 쓰다 날라갔어서 31일에 마무리하고 있다. 지금 재작성하면서 보니 날라갔던게 의외로 좋았던 것일지도 모르겠다. 이해가 부족했던 어제 대비 한 번 더 정리하고 나니 이해가 더 잘 된 것 같다.