스파르타 내일배움캠프_본캠프_data11기 김선영_TIL_Day 5

<오늘의 학습>

• 10시 SQL 라이브 세션 (Union[All] / Join) + 데일리 퀘스트

---

<학습내용 정리>

# SQL 라이브 세션

1. Union / Union All

: SELECT 결과를 세로로 붙여서 하나로 만듭니다.

ex. 1월 신청자 목록이 있고, 12월 신청자 목록이 따로 있을 때

      웹 유입 로그와 앱 유입 로그가 따로 있고 “전체 유입”을 보고 싶을 때

      “학생 목록”과 “강사 목록”을 합쳐서 “전체 사람 목록”을 만들고 싶을 때

1. 각 SELECT는 같은 개수의 컬럼을 반환해야 함 (컬럼명이 같지 않아도 됨.)
2. 같은 위치의 컬럼끼리 데이터 타입이 호환되어야 함
3. 결과 컬럼명은 첫 번째 SELECT의 컬럼명/별칭을 따라감

• Union :
  - 중복제거 된 상태로, 결과가 수직으로 합쳐짐
  - 행이 늘어남
• Union All
  - 중복유지된 상태, 결과가 수직으로 합쳐짐

SELECT col1, col2
FROM table_a
UNION [ALL]
SELECT col1, col2
FROM table_b;

SELECT student_id, student_name, 'SEOUL' AS tag
FROM basic.students
WHERE region='SEOUL'
--group by는 select안에 사용 가능(*단, union 뒤의 select에도 동일한 group이 지정되어야 함.)
UNION
SELECT student_id, student_name, 'new' AS tag
FROM basic.students
WHERE segment='new';
--order by는 맨 마지막에 (두 테이블이 합쳐진 모든 결과를 정렬)

---

2. JOIN 🔴

⭐️ Join을 위해선 각 Table에 대한 이해가 필수!
⭐️ PK, FK를 파악하고 있어야 한다.

⭐️ 여러 개의 Table을 묶어서 어떤 목적의 데이터 인사이트를 얻을 것인지 명확히 설정해야 한다.

🍯 https://sql-joins.leopard.in.ua/?utm_source=chatgpt.com

(1) KEY

• PK(Primary Key, 기본키): 한 테이블에서 각 행을 유일하게 식별하는 컬럼
  -특징: 중복되면 안 됨, NULL이면 안 됨
• FK(Foreign Key, 외래키): 다른 테이블의 PK를 참조하는 컬럼

(2) 다중 Join

: (A와 B를 먼저 붙인 결과) 에 C를 또 붙이는 것 (A JOIN B JOIN C)

⭐️ join을 한번 할 때마다 row 수가 내가 설정한 단위(레벨, 기준 key)에 맞게 유지되고 있는지 확인!

• 왜 row 수가 변했지? (1:N이면 늘어나는 게 정상)
• 내가 보고 싶은 단위(레벨)가 뭐였지? (강좌 1행? 신청 1행? 결제 1행?)

--courses + enrollments
SELECT
  c.course_id,
  c.course_name,
  e.enrollment_id
FROM basic.courses c
LEFT JOIN basic.enrollments e
ON c.course_id = e.course_id
ORDER BY c.course_id, e.enrollment_id;

--enrollments + payments
SELECT
  e.enrollment_id,
  p.payment_id,
  p.payment_status,
  p.amount
FROM basic.enrollments e
LEFT JOIN basic.payments p
ON e.enrollment_id = p.enrollment_id
ORDER BY e.enrollment_id, p.paid_at;

--courses + enrollments + payments
--뒤에 붙이는 쿼리문에서 'join ~ on ~' 부분 이어서 붙이기.
--order by는 맨 뒤에 작성
SELECT
  c.course_id,
  c.course_name,
  e.enrollment_id,
  p.payment_id,
  p.payment_status,
  p.amount
FROM basic.courses c
LEFT JOIN basic.enrollments e
ON c.course_id = e.course_id
LEFT JOIN basic.payments p
ON e.enrollment_id = p.enrollment_id
ORDER BY c.course_id, e.enrollment_id, p.paid_at;

(3) 관계(카디널리티) 감각 1:1 / 1:N

• 1(PK)에 N개의 결과가 붙어서 최종 결과로 출력되는 행의 개수가 늘어날 수 있다. = 'Join 결과가 뻥튀기된다'
  students (1) : enrollments (N) → 학생 1명은 수강신청을 여러 번 할 수 있음
  enrollments (1) : payments (N) → 1개 신청에 결제가 여러 번 찍힐 수 있음(재결제/부분환불/분할결제 등)
• 최종 결과 분석 시에 헷갈리지 않으려면??
  
  • ⭐️ 각 테이블의 PK/FK 관계를 파악하고 있어야 한다.
  • 먼저 내가 원하는 최종 행 단위(grain)를 선언
    학생 1행씩 보고 싶다 → 최종 grain = student_id
    신청(수강) 1행씩 보고 싶다 → 최종 grain = enrollment_id
  • N쪽 테이블은 “JOIN 전에” 집계/선택해서 1행으로 만들어 붙여라
    학생 1행씩 + 수강신청 요약만 붙이기
    신청 1행씩 + 결제 총액만 붙이기

Q.1) JOIN

상황: 운영팀이 “수강신청 리스트(enrollments)”를 보는데, `course_id`만 있으면 사람이 읽기 불편해서 '강좌명/카테고리'를 붙여 달라고 합니다.

목표: `enrollments`와 `courses`를 **INNER JOIN**해서 아래를 출력하세요.
출력 컬럼: enrollment_id, course_id, course_name, category, final_price

조건: `enrollment_status = 'active'` 인 신청만
정렬: `enroll_date` 오름차순

--정답
select e.enrollment_id,
       c.course_id,
       c.course_name,
       c.category,
       e.final_price
from basic.enrollments as e inner join basic.courses as c
on e.course_id=c.course_id
where e.enrollment_status='active'
order by enroll_date;

Q.2) JOIN

상황: CS팀이 수강신청(enrollments) 목록을 기준으로,

• 결제가 성공(paid)한 신청이면 결제 정보를 보여주고
• 결제 성공 기록이 없으면 “결제 정보 칸이 비어있게(NULL)” 보여주고 싶어합니다.

즉, 수강신청은 전부 보여야 하므로 enrollments가 기준(LEFT) 입니다.

목표: enrollments e를 기준으로 payments p를 LEFT JOIN해서 아래 컬럼을 출력하세요.

출력 컬럼

• e.enrollment_id
• e.student_id
• e.course_id
• e.enroll_date
• e.final_price
• p.payment_id (결제 성공이 없으면 NULL)
• p.paid_at (결제 성공이 없으면 NULL)
• p.amount (결제 성공이 없으면 NULL)

조건: 결제 정보는 `payment_status = 'paid'`인 것만 붙이기
- 단, 결제 성공이 없어도 enrollments 행은 사라지면 안 됨
정렬: `e.enroll_date ASC`, `e.enrollment_id ASC`, `p.paid_at ASC`

--정답
SELECT e.enrollment_id,
       e.student_id,
       e.course_id,
       e.enroll_date,
       e.final_price,
       p.payment_id,
       p.paid_at,
       p.amount
from basic.enrollments as e
left join basic.payments as p
on e.enrollment_id=p.enrollment_id
and p.payment_status='paid'
order by e.enroll_date ASC, e.enrollment_id ASC, p.paid_at ASC;

Q.3) 다중JOIN

상황
운영팀이 **강좌(courses)** 별로
1. 신청이 몇 건인지(신청 수)
2. 결제 성공(`paid`) 금액이 얼마인지(매출)를 보고 싶어합니다.

사용 테이블(3개)
- `courses c`
- `enrollments e`
- `payments p`

조인 규칙
- `c.course_id = e.course_id`
- `e.enrollment_id = p.enrollment_id`
- 결제는 `payment_status='paid'`인 것만 매출로 인정 (이 조건은 **ON**에 두는 걸 추천)

출력 컬럼
- `course_id`
- `course_name`
- `enroll_cnt` = 신청 건수 (`COUNT(DISTINCT e.enrollment_id)`)
- `paid_revenue` = 결제 성공 금액 합계 (`SUM(p.amount)`)
- `paid_payment_rows` = 결제 성공 결제 이벤트 수 (`COUNT(p.payment_id)`)

조건/정렬
- 조건: `c.is_active = 1` 인 강좌만
- 정렬: `paid_revenue DESC`, `course_name ASC`

--📌오답
select c.course_id,
       c.course_name,
       count(distinct e.enrollment_id) as enroll_cnt,
       sum(p.amount) as paid_revenue,
       COUNT(p.payment_id) as paid_payment_rows
from basic.courses as c
	left join basic.enrollments as e
	on c.course_id=e.course_id
	left join basic.payments as p
	on e.enrollment_id=p.enrollment_id
	and p.payment_status='paid'
where c.is_active=1
group by c.course_id,c.course_name --📌select에 집계컬럼과 일반컬럼이 같이 있으면 group by 필수
order by c.course_name asc, paid_revenue desc;

<오답노트>
- select에 집계컬럼과 일반컬럼이 같이 있으면 group by 필수!!
- 'join ~ on ~ and'는 해당하는 join에 같이 적고, where은 from(+join) 밖에 적기

---

🍯실무 팁

데이터 명세서

1. 데이터 분석 전, 테이블 요약표를 정의하는 것이 필수

2. 품질 규칙: 테이블 값 구성에서 데이터 품질이 깔끔(?)하기 위해서 정해야 하는 규칙

---

3. 데일리 퀘스트
# 과제

과제1)

✅ 상황
운영팀이 공지 발송을 위해 “사람 목록”이 필요합니다.
학생(students) 과 강사(instructors) 를 한 결과로 합쳐서(세로로 쌓아서) 보고 싶습니다.
- 이름이 같은 사람이 있을 수도 있으니(동명이인) 중복 제거를 하면 안 됩니다.

✅ 목표
학생/강사 데이터를 아래 컬럼으로 통일해서 한 결과로 합치기

출력 컬럼
- `person_type` : 학생이면 `'student'`, 강사면 `'instructor'`
- `person_id` : 학생은 `student_id`, 강사는 `instructor_id`
- `person_name` : 학생은 `student_name`, 강사는 `instructor_name`

정렬
- `person_type ASC`, `person_id ASC`

select 'student' as person_type,
        student_id as person_id,
        student_name
from basic.students
union all
select 'instructor' as person_type,
		instructor_id as person_id,
		instructor_name
from instructors
order by person_type asc, person_id ASC;

과제2)

✅ 상황
운영팀이 “어떤 카테고리가 신청이 잘 들어오는지” 보고 싶어합니다.
그런데 `enrollments`에는 `category`가 없고, `courses`에만 있습니다.
→ **수강신청(enrollments)** 과 **강좌(courses)** 를 `course_id`로 **INNER JOIN**해서 카테고리를 붙인 뒤 집계합니다.

✅ 목표
`enrollment_status = 'active'` 인 신청만 대상으로, 카테고리별 지표를 뽑으세요.

출력 컬럼
- `category`
- `active_enrollments` : 진행중 신청 건수 (`COUNT(*)`)
- `unique_students` : 진행중 신청한 “고유 학생 수” (`COUNT(DISTINCT e.student_id)`)
- `avg_final_price` : 진행중 신청의 평균 결제대상금액 (`ROUND(AVG(e.final_price), 0)`)

조건
- `enrollment_status = 'active'`

추가 조건 (그룹 필터)
- `active_enrollments >= 2` 인 카테고리만 남기기 (`HAVING`)

정렬
- `active_enrollments DESC`, `category ASC`

select category,
       count(*) as active_enrollments,
       count(distinct e.student_id) as unique_students,
       round(avg(e.final_price), 0) as avg_final_price
from courses c
inner join enrollments e
on c.course_id=e.course_id
where e.enrollment_status='active'
group by c.category
having count(*) >= 2
order by active_enrollments DESC, category ASC;

과제3)

✅ 상황
CS팀이 “결제 성공이 안 된 신청건”을 찾아서 안내 메시지를 보내려 합니다.
포인트는 아래와 같습니다.:
- 기준(모수)은 **수강신청(enrollments)** 입니다 → 신청은 일단 존재하니까
- 결제 성공(`payment_status='paid'`)이 있으면 결제 정보를 붙이고
- **결제 성공이 없으면** 결제 컬럼이 `NULL`로 남게 만들고(LEFT JOIN)
- 그 `NULL`을 이용해 결제 성공이 없는 신청만 골라냅니다.

✅ 목표
결제 성공(paid) 기록이 없는 신청만 출력하세요.

출력 컬럼
- `e.enrollment_id`
- `e.student_id`
- `e.course_id`
- `e.enroll_date`
- `e.enrollment_status`
- `e.final_price`

조건
- 신청 상태는 `active`, `completed`만 포함 (취소 `cancelled` 제외)
- 결제는 `paid`만 “성공”으로 인정
    - 이 조건은 **WHERE가 아니라 ON에** 넣는 게 핵심!

정렬
- `e.enroll_date ASC`, `e.enrollment_id ASC`

select
	e.enrollment_id,
	e.student_id,
	e.course_id,
	e.enroll_date,
	e.enrollment_status,
	e.final_price
from basic.enrollments e
	left join basic.payments p
	on e.enrollment_id=p.enrollment_id
	and p.payment_status='paid' --우측 테이블에 대한 조건이므로 on에 조건달기
where e.enrollment_status in ('active', 'complete') --기준(모수)에 대한 조건이므로 where에 조건달기 가능
      and p.payment_status is null
order by e.enroll_date ASC, e.enrollment_id ASC;

<오답노트>
JOIN에서 조건 추가시,
- 기준(모수) 조건: where에 추가 (우측 테이블의 null 유지됨)
- 우측 테이블 조건: on에 추가 (null 존재를 유지하기 위해서)

 

 

 

 

 

 

 

 

 

 

 

 

과제4)

✅ 상황
운영팀이 강좌별로 아래를 보고 싶어합니다.
- 신청이 얼마나 들어왔는지(신청 수)
- 결제 성공 매출이 얼마인지(매출)
- 결제 이벤트가 몇 번 발생했는지(결제 rows)

사용 테이블 (3개)
- `courses c` (1행=강좌 1개)
- `enrollments e` (1행=수강신청 1건)
- `payments p` (1행=결제 이벤트 1건)

JOIN 규칙
- `c.course_id = e.course_id`
- `e.enrollment_id = p.enrollment_id`
- 매출은 `p.payment_status = 'paid'` 인 결제만 인정 (이 조건은 **ON**에 두기)

출력 컬럼
- `c.course_id`
- `c.course_name`
- `enroll_cnt` : 신청 건수 (`COUNT(DISTINCT e.enrollment_id)`)
- `paid_enroll_cnt` : 결제 성공 이력이 있는 신청 수 (`COUNT(DISTINCT p.enrollment_id)`)
- `paid_payment_rows` : 결제 성공 결제 이벤트 수 (`COUNT(p.payment_id)`)
- `paid_revenue` : 결제 성공 금액 합 (`SUM(p.amount)`)
- `paid_enroll_rate_pct` : `paid_enroll_cnt / enroll_cnt * 100` (소수점 1자리)

조건
- `c.is_active = 1` (운영중 강좌만)

주의(중요)
- 신청이 1건도 없으면 `enroll_cnt=0`이어서 비율 계산이 애매해집니다.
    → 이번 과제에서는 **신청이 1건 이상인 강좌만** 포함시키세요. (`HAVING`)

정렬
- `paid_revenue DESC`, `c.course_name ASC`

select c.course_id,
       c.course_name,
       count(distinct e.enrollment_id) as enroll_cnt,
       count(distinct p.enrollment_id) as paid_enroll_cnt,
       count(distinct p.payment_id) as paid_payment_rows,
       sum(p.amount) as paid_revenue,
       ROUND(count(distinct p.enrollment_id)/count(distinct e.enrollment_id)*100, 1) as paid_enroll_rate_pct
from basic.courses c
	left join basic.enrollments e
	on c.course_id=e.course_id
	left join basic.payments p
	on e.enrollment_id=p.enrollment_id
		and p.payment_status='paid'
where c.is_active=1
group by c.course_id, c.course_name
having count(distinct e.enrollment_id) >= 1
order by paid_revenue DESC, c.course_name ASC;

# 데일리 퀴즈

Q1. `UNION`과 `UNION ALL`의 가장 큰 차이는?
A. UNION은 가로 결합, UNION ALL은 세로 결합이다
B. UNION은 중복 행 제거, UNION ALL은 중복 행 유지다
C. UNION은 ORDER BY를 못 쓰고, UNION ALL은 ORDER BY를 쓸 수 있다
D. UNION ALL은 PK/FK가 있어야만 가능하다

Q2. UNION으로 “전체 결과”를 정렬하려면 `ORDER BY`는 어디에 써야 할까요?
A. 첫 번째 SELECT 안에 쓴다
B. 각 SELECT마다 ORDER BY를 각각 쓴다
C. UNION으로 연결된 마지막 SELECT 뒤에 쓴다
D. UNION에서는 ORDER BY를 쓸 수 없다

Q3. UNION을 사용할 때 필요한 조건으로 올바른 것은?
A. 두 SELECT의 컬럼 이름이 완전히 같아야 한다
B. 각 SELECT가 반환하는 컬럼 개수는 달라도 된다
C. 각 SELECT는 같은 개수의 컬럼을 반환해야 한다
D. 두 테이블 사이에 PK/FK가 반드시 설정돼 있어야 한다

 

Q4. `LEFT JOIN`에서 오른쪽 테이블에 매칭되는 행이 없으면 어떤 일이 발생하나요?
A. 왼쪽 행도 함께 사라진다
B. 왼쪽 행은 남고, 오른쪽 컬럼들이 NULL로 채워진다
C. 에러가 발생한다
D. 자동으로 RIGHT JOIN으로 바뀐다

Q5. 아래 쿼리가 “LEFT JOIN을 했는데도 결제 없는 신청이 사라지는” 이유로 가장 적절한 것은?

SELECT e.enrollment_id, p.payment_status
FROM basic.enrollments e
LEFT JOIN basic.payments p
ON e.enrollment_id= p.enrollment_id
WHERE p.payment_status='paid';

A. LEFT JOIN은 원래 결제 없는 신청을 못 가져온다
B. WHERE 조건이 오른쪽 NULL 행을 제거해서 결과가 INNER JOIN처럼 된다
C. payment_status는 숫자형이 아니라서 비교가 안 된다
D. ON에는 조건을 쓸 수 없어서 에러가 난다

---

# SQL 사전캠프 문제 [달리기반]

문제1)

- user 테이블은 스파르타 코딩클럽에 가입한 유저들의 정보를 날짜별로 기록한 테이블입니다.

- name_cnt: “김”씨 성을 가지고 있는 교육생의 수

--정답
select count(distinct user_id) as name_cnt
FROM sparta.users
where name like '김%';

 

문제2)
날짜별 획득포인트 조회하기
- created_at: 익명화된 유저들의 아이디(varchar255)
- average_points: 유저가 획득한 날짜별 평균 포인트(int), 반올림 필수

--정답
select date(created_at) as created_at,
       round(avg(point), 0) as average_points
from sparta.point_users
group by date(created_at);

 

문제3)
이용자의 포인트 조회하기

- user_id: 익명화된 유저들의 아이디
- email: 유저들의 이메일
- point: 유저가 획득한 포인트
- users 테이블에는 있지만 point_users에는 없는 user는 포인트가 없으므로 0 으로 처리
- 포인트 기준으로 내림차순 정렬

--정답
select u.user_id,
       u.email,
       ifnull(pu.point, 0) as point
from sparta.users u
left join sparta.point_users pu
on u.user_id=pu.user_id
order by point desc;

 

문제4)

단골 고객님 찾기

--고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.
--정답
select c.CustomerName,
       count(OrderID) as OrderCount,
       sum(TotalAmount) as TotalSpent
from customers c
left join orders o
on c.CustomerID=o.CustomerID
group by c.CustomerName;

--나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.
--📌오답 (서브쿼리문 숙지 부족)
--💡해설
SELECT c.Country,
       c.CustomerName AS Top_Customer,
       SUM(o.TotalAmount) AS Top_Spent
FROM Customers c
	JOIN Orders o
    ON c.CustomerID = o.CustomerID
GROUP BY c.Country, c.CustomerName
HAVING --having에 서브쿼리문 작성하여, 국가별 1위 선별
    SUM(o.TotalAmount) = (
        SELECT MAX(SumSpent)
        FROM (SELECT SUM(o2.TotalAmount) AS SumSpent
             FROM Customers c2
            	 JOIN Orders o2
                 ON c2.CustomerID = o2.CustomerID
             WHERE c2.Country = c.Country
             GROUP BY c2.CustomerID) AS Subquery
             );
--쉬운 답 (rank() 함수 사용해서 서브퀴리문 작성)
select Country, CustomerName, Top_Spent
from
(
select c.Country,
       c.CustomerName,
       sum(o.TotalAmount) as Top_Spent,
       rank() over(partition by c.Country order by sum(o.TotalAmount) desc) as rnk
from Customers as c
	left join Orders as o
	on c.CustomerID=o.CustomerID
group by c.Country, c.CustomerName
) a
where rnk=1
order by a.CustomerName;

<오답노트>
having 안에 서브쿼리문 작성하는 버전 답 이해 불가. 추가 공부 필요
다른 방법 찾음.

2025.12.29 - [스파르타 내일배움캠프(25.12.01~)] - [개념정리] Having + Subquery

[개념정리] Having + Subquery