[내일배움캠프] QA/QC_6기 - 사전캠프 7일차
- TIL
- SQL로 데이터 전처리 및 피봇테이블 만들기
- To-Do List
- SQL 강의 수강 (Chap5 전체)
- SQL 과제
- 이전 ADsP 학습 내용 정리
- ADsP 강의 수강 (Chap4-3 ~ )
SQL
조회한 데이터에 아무 값이 없다면 어떻게 해야할까
- 데이터가 없을 때 연산 결과 변환 케이스
- 테이블에 잘못된 값 입력
- JOIN문 사용 시 값이 없는 경우
- 사용할 수 없는 데이터가 들어있거나, 값이 없는 경우
- 방법1. 없는 값 제외
- MySQL에서는 사용할 수 없는 값(Not given)일 때 해당 값을 0으로 간주
- 0으로 간주할 경우 평균과 같은 계산에서는 큰 오차가 발생
- Null로 변환하여 데이터 자체를 제외하는 것이 필요한 경우가 존재
Not given을 Null로 처리한 평균과 그렇지 않은 평균을 비교하기 위해 작성한 쿼리 
좌측(그냥 평균)이 우측(Null 처리)한 값보다 작은 것을 확인 - Null 문법 이용해 값을 제외하고 명확하게 연산 지정
where b.customer_id is not null
→ b.customer_id 에 없는 데이터는 제외 (즉, null이 아닌 데이터만 불러오기)
- 방법2. 다른 값을 대신 사용하기
- 다른 값이 있을 때 조건문 이용하기
if(rating>=1, rating, 15) → rating이 1이상일 때 15로 대체 - null 값일 때
coalesce(b.age, 20) "null 제거" b.age의 null 값을 20으로 대체
- 다른 값이 있을 때 조건문 이용하기
조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까
- 상식적이지 않은 데이터의 예시
- 주문 고객의 연령 데이터에서 일반적으로 2세는 존재할 수 없음
- 결제의 경우 가게를 창업한 이후의 데이터가 상식적이나, 1960년대의 결제 정보같은 데이터가 있을 수 있음
- 조건문으로 값의 범위 지정
- 조건문으로 상식적인 수준의 범위를 지정
2세, 6세, 82세와같은 값이 각 15세, 80세로 변경된 것을 확인
- 조건문으로 상식적인 수준의 범위를 지정
실습. SQL로 Pivot Table 만들어보기
- Pivot table : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
구분 컬럼1 구분 컬럼2 ... 집계 기준1 데이터1 데이터3 ... 집계 기준2 데이터2 데이터4 ... ... ... ... ... - 피봇테이블 예시 : 음식점별 시간별 주문건수 피봇테이블 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
- 음식점별, 시간별 주문건수 집계하기
- Pivot View 구조 만들기
- Pivot table로 만들 때 행 데이터를 열로 바꾸는 작업
- 조건문만 사용할 시 해당 조건이 아닌 행은 null이 되고, GROUP BY를 사용하면 여러 행을 하나로 줄일때 어떤 값을 대표로 할지 모르니 null이 출력
- 이 null을 무시하기 위해서 집계함수 MAX를 사용해서 대표값을 지정하는 것
- 음식점별, 시간별 주문건수 집계하기
업무 시작을 단축시켜 주는 마법의 문법 (Window Fuction)
- Window Function의 사례
- 각행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌
- 한식 식당 중 주문건수가 많은 순으로 순위매기기
- 한식 식당 전체 주문건수 중 A 식당이 차지하는 비율
- 기본 구조 : window_function (argument) over (partition by 그룹 기준 컬럼 order by 정렬기준)
- 각행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌
- 실습1 : N번째 까지의 대상을 조회하고 싶을 때, Rank
- Rank : 특정 기준으로 순위를 매겨주는 기준
- 실습 : 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회
a 서브쿼리에서 음식종류 및 가게명 별 주문 수 계산, b 서브쿼리에서 a를 바탕으로 랭킹 집계, b에서 집계한 랭킹의 상위 3곳만 조회 
음식종류 별 3위 이내의 가게만 출력
- 실습2 : 전체에서 차지하는 비율, 누적합을 구할 때 Sum
- 앞서 배운 합계를 구하는 기능과 동일하나, 누적합이 필요하거나 카테고리별 합계 컬럼과 원본 컬럼을 함께 이용할 때 유용하게 사용 가능
- 실습 : 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬
날짜 포맷과 조건까지 SQL로 한 번에 끝내기 (포맷 함수)
- 날짜 데이터의 이해
- 문자 타입, 숫자 타입과 같이 날짜 데이터도 특정 타입을 가지고 있음
- 년,월,일,시,분,초 드으이 값을 모두 가지고 있으며, 목적에 따라 '월', '주', '일' 등으로 포맷을 변경할 수 있음
- 실습1 : 날짜 데이터의 여러 포맷
- yyyy-mm-dd 형식의 컬럼을 data type으로 변경하기
DATE(컬럼1) : 컬럼1의 형식을 date 형식으로 변경
change_date 컬럼명 좌측 시계 모양으로 바뀐 것을 확인 - data type을 date_format을 이용하여 년, 월, 일, 주로 조회해보기
DATE_FORMAT(DATE(컬럼1), '%조회하고자 하는 것' : '%형식'으로 지정 가능
ㅅ일요일(0)부터 월용 토요일(6)으로 나타남
- yyyy-mm-dd 형식의 컬럼을 data type으로 변경하기
- 실습2 : 년도별 3월의 주문건수 구하기
실습 문제
- 문제 : 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)
작성한 쿼리 
pivot table - 주의 할 점
- case문 사용 시에 조건마다 , 사용 X
과제
- 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요.
select count(1)
from departments - 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요.
select e.name, d.name
from employees e inner join departments d on e.department_id=d.id - '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요.
select e.name
from employees e inner join departments d on e.department_id=d.id
where d.id='103' - 부서별로 직원 수를 계산하는 쿼리를 작성해주세요.
select d.name, count(e.name) cnt_employee,
from employees e left join departments d on e.department_id=d.id
group by 1 - 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요.
select d.name
from employees e left join departments d on e.department_id=d.id
where e.id is null - '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요.
select e.name
from employees e inner join departments d on e.department_id=d.id
where d.id='102' - 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요.
select o.id, p.name
from pruducts p left join orders o on p.id=o.product_id - 총 매출(price * quantity)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요.
select p.id, sum(o.price*p.quantity) tot_sales
from pruducts p inner join orders o on p.id=o.product_id
group by 1
order by 2 desc
limit 1 - 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요.
select p.id, sum(o.quantity) tot_quantity
from pruducts p inner join orders o on p.id=o.product_id
group by 1
- 2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요.
select p.name
from pruducts p inner join orders o on p.id=o.product_id
where o.order_date>'2023-03-03' - 가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요.
select p.name, sum(o.quantity) tot_quantity
from pruducts p inner join orders o on p.id=o.product_id
group by p.id
order by tot_quantity desc
limit 1 - 각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요.
select p.id, avg(o.quantity) avg_quantity
from pruducts p inner join orders o on p.id=o.product_id
group by 1
- 판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요.
select p.id, p.name
from pruducts p left join orders o on p.id=o.product_id
where o.id is null
SQL 실전
- SQL의 작동 순서와 작성 순서
- SQL의 작동 순서
FROM → ON → JOIN → WHERE → GROUB BY → HAVING → SELECT → DISTINCT → ORDER BY - SQL의 작성 순서
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
- SQL의 작동 순서
- IF문과 CASE문, 서브쿼리
- IF는 보통 MySQL에서 사용, CASE는 모든 RDBMS에서 사용 가능
- 조건에 따라 컬럼 값을 변경, 조건별 다른 계산을 할 때 사용
- 서브쿼리 vs WITH 문 (공통 테이블 표현식)
항목서브쿼리WITH 문 (CTE)위치SELECT, FROM, WHERE 절 안에 바로 씀쿼리의 맨 위에 작성재사용안됨여러 번 참조 가능가독성복잡해지기 쉬움가독성이 좋고 유지보수에 유리사용 예간단한 조건에 자주 사용복잡한 로직 처리 시 선호 - WITH 문을 추천하는 상황
- 쿼리가 길고, 중간 계산을 나눠서 작성하고 싶을 때 → 각 단계에 명명 가능, 가동성↑
- 같은 쿼리를 반복해서 써야할 때 → 중복 제거, 재사용성↑
- 쿼리가 짧고 단순할 땐 서브쿼리가 간결하고 명확하게 처리 가능
- 성능 최적화가 중요할 땐 사용하는 RDBMS에 따라 다름
- 예시
- 서브쿼리
- WITH 문
with 별칭 as ( 필요한 쿼리문 )
- 서브쿼리
- 문제 풀이가 어렵고, 어디서부터 손대야 할지 모를 때
- 문제를 2~3개의 질문으로 분해
- ex) 통과한 사람의 평균 점수
- 통과의 기준 점수
- 점수가 속한 칼럼 위치
- 평균의 사용에는 GROUP BY가 필요한가?
- SQL 작성 순서 (SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY)
- 우선 조회할 컬럼 등을 확인하기 위해서 select *로 확인하는 연습
- 문제를 2~3개의 질문으로 분해
- SQL 쿼리 작성 팁
- SQL 작성 전 체크리스트
- 문제 정의 (문제 분석)
- 무엇을 구하고 싶은가 문장으로 적기
- 요청사항을 정확히 아는 것이 중요
- SQL 작성 전 문제의 요지 분석
- 문제를 보며 예상 쿼리를 구성해보자
예시: 월별로 매출 합계를 구하고 싶다
~ SELECT, GROUP BY, SUM 무엇을 쓰는가?
- 데이터 구조 확인
쿼리 쓰기 전 반드시 테이블의 컬럼명 확인 (SELECT *) - SQL 쿼리 실행순서에 따라 작성 및 해석
- FROM
- JOIN
- 항상 작은 범위부터 테스트하라
- 여러 테이블 JOIN 시 먼저 두 개만 조인하여 결과를 확인하라
⇒ 조인 조건이 잘못되면 데이터가 폭발적으로 늘어나거나 없어지기 때문이다. - JOIN 시 NULL 값이 필요한 경우가 있으니 고려하여 사용하기
- 항상 작은 범위부터 테스트하라
- ON
- WHERE
- WHERE은 행을 필터링 함을 기억하라
- WHERE 필터링을 적용할 때 NULL도 제외되는 것을 기억하라
- 인덱스에 설정되어있는 컬럼 사용
- 인덱스의 선두 컬럼에 해당하는 조건을 사용하라
- WHERE은 행을 필터링 함을 기억하라
- GROUP BY
- 집계함수 (SUM, AVG, MAX 등)
- HAVING
- HAVING은 집계된 결과를 필터링 함을 기억하라
- HAVING은 집계된 결과를 필터링 함을 기억하라
- DISTINCT
- SELECT
- 필요한 컬럼만 사용하라
- 필요한 컬럼만 사용하라
- ORDER BY
- LIMIT
- FROM
- 쿼리 작성에 도움되는 것
- 불필요한 서브쿼리보다는 JOIN, CTE, 윈도우함수를 사용하라
- CTE: 공통 테이블 표현식, 가상 테이블
- CTE: 공통 테이블 표현식, 가상 테이블
- 날짜 문제의 경우 애매한 경우가 많기에 여러 번 해독해야 함
- 날짜 계산은 무조건 함수로 하는 것이 좋다.
- 한국어 및 도메인의 특징에 따라 다르기 때문
- 대여의 개념
- 숙박의 개념
- D-DAY 개념
- 문자 계산, 날짜끼리의 계산
- 대여의 개념
- 날짜 계산은 무조건 함수로 하는 것이 좋다.
- WITH (가상 테이블)
- 가독성 향상을 위해 사용을 고려할 수 있다.
- N회 반복 사용 할 때 사용을 고려할 수 있다.
- 가독성 향상을 위해 사용을 고려할 수 있다.
- WITH RECURSIVE (가상 테이블 재귀)
- 인위적인 컬럼을 만들 때 유용하게 사용된다.
ex) 24시간의 시간표를 만들 때
- 인위적인 컬럼을 만들 때 유용하게 사용된다.
- 불필요한 서브쿼리보다는 JOIN, CTE, 윈도우함수를 사용하라
- SQL 작성 전 체크리스트
- GROUP BY vs OVER() 함수 사용 조건 차이
항목GROUP BYOVER() (윈도우 함수)데이터 집계행을 묶어서 줄 수를 줄임행의 개수는 그대로용도전체/그룹별 집계각 행 기준으로 누적합, 비율 등 계산결과 형태묶여서 줄어듦줄 수 그대로 유지됨- 예시
- GROUP BY
- OVER()
- GROUP BY
- 예시
SQL 실전 학습 '실습'
의약품 생산 Lot 품질 분석
- 수분 ≥ 8.0% : 기준 초과
- 순도 ≤ 92.0% : 기준 미달
- 중량은 ± 10g 이외면 기준 초과 (500g 정상 기준, 490~510g 이외는 초과)
- 문제
- Q1. 전체 로트 중 불량 로트 비율을 계산하라 (소수점 둘째자리까지 반올림된 백분율(%)로 출력하기.
select
round(
100*sum(case when
water_content>=8.0
or purity<=92.0
or abs(weight-500)>10))
then 1 else 0 end)/count(1), 2) defact_ratio
from lot_quality_checks - 핵심 SQL 문법 및 함수 정리
함수/문법설명정답 쿼리 참조집계 함수COUNT(*)테이블의 전체 행 개수 계산COUNT(*) → 전체 로트 수COUNT(*) FILTER (WHERE …)특정 조건에 맞는 행만 카운트COUNT(*) FILTER (WHERE water_content >= 8.0)수학 함수ABS(x)절댓값 반환
(중량 허용 오차 계산에 사용)ABS(weight - 500) > 10ROUND(x, n)숫자 x를 소수점 n자리까지 반올림ROUND(66.6666, 2) → 66.67산술 연산/나눗셈, 불량률 계산에 사용불량로트수 / 전체로트수*곱셈, %로 변환할 때 사용(불량/전체) * 100.0논리 연산자OR여러 조건 중 하나라도 맞으면 참water_content >= 8.0 OR purity <= 92.0별칭AS결과 컬럼명 지정AS defect_rate_percentage - Q2. 품질 항목별로 불량 로트 수를 각각 구하라
select
sum(case when water_content>=8.0 then 1 else 0 end) defect1
sum(case when purity<=92.0 then 1 else 0 end) defect2
sum(case when abs(weight-500)>10 then 1 else 0 end) defect3
from lot_quality_checks - 핵심 SQL 문법 및 함수 정리
구분함수 / 문법설명정답 쿼리 참조조건 분기CASE WHEN ... THEN ... ELSE ... END조건이 참이면 특정 값을 반환하고,
거짓이면 다른 값을 반환CASE WHEN purity <= 92.0 THEN 1 ELSE 0 END집계 함수SUM()여러 값을 더함 → 조건을 만족하는 로트 수를 세는 데 사용SUM(CASE WHEN water_content >= 8.0 THEN 1 ELSE 0 END)수학 함수ABS()절댓값 반환
중량 허용 오차 계산에 사용ABS(weight - 500) > 10비교 연산자>=, <=, >조건 설정에 사용purity <= 92.0, weight > 510별칭AS결과 컬럼 이름 지정AS water_defects
- Q1. 전체 로트 중 불량 로트 비율을 계산하라 (소수점 둘째자리까지 반올림된 백분율(%)로 출력하기.
차량 센서 진단 및 이상 패턴 분석
- 분석 목표
- 센서 이상이 있었던 차량이 어떤 비율로 실제 정비까지 이어졌는지 확인
- 센서 항목별로 실제 정비 이력과 연결된 케이스 수 분석
- 센서 이상 없었는데 정비 받은 차량, 또는 이상 있었지만 정비 안받은 차량 등 예외 케이스 탐색
- 문제
- Q1. 센서 이상이 1건 이상 있었던 차량 중, 정비 이력이 존재하는 차량의 비율(%)을 구하라.
with abnormal_vehicles as
(
select distinct vehicle_id
from vehicle_test_logs
where vibration_level>6.0
or brake_response>300
or abs(engine_temp-90)>20
),
repaired_vehicles as
(
select distinct v.
from abnormal_vehicles v left join vehicle_repairs r on v.vehicle_id=r.vehicle_id
)
select
count(distinct repaired_vehicles.vehicles_id)*100.0 /
count(distinct e.vehicles_id) as repair_ratio
from abnormal_vehicles v left join repaired_vehicles e on v.vehicle_id=e.vehicle_id - 핵심 SQL 문법 및 함수 정리
문법 / 함수예시설명WITH (CTE)WITH abnormal_vehicles AS (...)공통 테이블 표현식 (CTE). 반복적으로 사용할 서브쿼리를 정의하여 가독성과 재사용성을 높임SELECT
DISTINCTSELECT DISTINCT vehicle_id중복 제거. 동일 차량의 복수 로그가 있어도 한 번만 카운트FROMFROM vehicle_test_logs데이터를 조회할 테이블 지정WHEREWHERE vibration_level > 6.0조건절. 센서 이상 기준을 지정OROR brake_response > 300여러 조건 중 하나라도 참이면 결과 포함JOINJOIN vehicle_repairs ON v.vehicle_id = r.vehicle_id두 테이블을 조건(ON)에 따라 연결LEFT JOINLEFT JOIN repaired_vehicles ON ...기준 테이블은 모두 포함, 연결 안 되는 항목도 유지COUNT
(DISTINCT)COUNT(DISTINCT vehicle_id)중복을 제거한 개수를 카운트 (차량 단위 집계에 중요)산술 연산* 100.0 / ...비율(%) 계산을 위한 산술 연산별칭 (Alias)AS repair_rate_percent결과 컬럼에 이름 부여하여 결과 해석 용이하게 함 - Q2. 센서 항목별로 실제로 정비까지 이어진 건수를 구하라.
with abnormal_tests as
(
select distinct t.vehicle_id,
(case when vibration_level>6.0 then 1 else 0 end) vibration_flag,
(case when brake_response>300 1 else 0 end) brake_flag,
(case when abs(engine_temp-90)>20 then 1 else 0 end) engine_flag
from vehicle_test_logs t
),
repair_match as
(
select a.vehicle_id, r.issue_type
from abnormal_tests a left join vehicle_repairs r on a.vehicle_id=r.vehicle_id
)
select
sum(case when vibration_level>6.0 and issue_type='진동' then 1 else 0 end) cnt_vibration_repair,
sum(case when brake_response>300 and issue_type='브레이크' then 1 else 0 end) cnt_brake_repair,
sum(case when abs(engine_temp-90)>20 and issue_type='엔진' then 1 else 0 end) cnt_engine_repair
from abnormal_tests a left join repair_match r on a.vehicle_id=r.vehicle_id - 핵심 SQL 문법 및 함수 정리
문법 / 함수예시설명논리 연산자
AND / ORengine_temp < 70 OR engine_temp > 110여러 조건을 동시에 평가. AND는 모두 참일 때,
OR은 하나라도 참일 때 만족비교 연산자>, <값의 크기 비교 (센서 기준 초과/미만 판별에 사용) - Q3. 센서 이상이 없었음에도 불구하고 정비를 받은 차량 수를 구하라.
with normal_vehicles as
(
select distinct vehicle_id
from vehicle_test_logs
where vibration_level<=6.0
or brake_response<=300
or abs(engine_temp-90)<=20
),
repaired_normals as
(
select distinct nv.vehicle_id
from normal_vehicles nv left join vehicle_repairs vr on nv.vehicle_id=vr.vehicle_id
)
select
count(distinct vehicle_id) cnt_normal_repair
from repaired_normals - Q4. 정비 없이 출고된 차량 중, 센서 이상 건수가 2개 이상이었던 차량 수를 구하라.
with abnormal_counts as
(
select vehicle_id,
(case when vibration_level>6.0 then 1 else 0 end) +
(case when brake_response>300 1 else 0 end) +
(case when abs(engine_temp-90)>20 then 1 else 0 end) cnt_abnormal
from vehicle_test_logs
),
high_abnor_no_repair as
(
select distinct a.vehicle_id
from abnormal_counts a left join vehicle_repairs r on a.vehicle_id=r.vehicle_id
where a.cnt_abnormal>=2 and r.vehicle_id is null
)
select
count(vehicle_id) high_abnor_without_repair
from high_abnor_no_repair
- Q1. 센서 이상이 1건 이상 있었던 차량 중, 정비 이력이 존재하는 차량의 비율(%)을 구하라.
출처
전용문, 박현민 지음, 2026 ADsP 데이터분석 준전문가, 위키북스
내일배움캠프 교육 자료
'TIL > 내일배움캠프' 카테고리의 다른 글
| 파이썬 시작하기 Ⅱ (0) | 2026.05.04 |
|---|---|
| 파이썬 시작하기 Ⅰ (0) | 2026.04.30 |
| 쿼리로 데이터베이스 다루기 (0) | 2026.04.27 |
| AI와 대화? 프롬프팅 (0) | 2026.04.26 |
| 나는 AI를 잘 사용하고 있을까? (1) | 2026.04.23 |