[사전캠프] 데이터기반 QA/QC 부트캠프 6일차-2

내일배움캠프

[사전캠프] 데이터기반 QA/QC 부트캠프 6일차-2

min0jun 2026. 5. 6. 16:34

1. 오늘 학습 목표

- null 필터링 하는 법, 범위 지정하기, Pivot table, Window Function 사용하는 법

2. 오늘 학습 한 내용

SQL

null이 아닌 것만 보여지게 필터링하기

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null

다른 값을 대신 사용하기 (coalesce)

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거",
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null

조회한 데이터가 상식적이지 않은 값을 가지고 있을 때

- 조건문으로 값의 범위를 지정하기

select customer_id, name, email, gender, age,
       case when age<15 then 15
            when age>80 then 80
            else age end "범위를 지정해준 age"
from customers

엑셀의 Pivot Table을 SQL에서도 사용하기

Pivot Table이란 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미한다.

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

Window Function (Rank, Sum)

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

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

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

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

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) 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

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

Tip. 계산하는 함수에는 group by절 꼭 적기

3. 오늘의 과제

10) 이젠 테이블이 2개입니다

38.

SELECT COUNT(*) FROM departments

39.

SELECT e.name, d.name 
FROM employees e INNER JOIN departments d ON e.department_id = d.id

40.

SELECT e.name 
FROM employees e INNER JOIN departments d ON e.department_id = d.id 
WHERE d.name = '기술팀'

41.

SELECT d.name, COUNT(e.id) AS employee_count 
FROM departments d LEFT JOIN employees e ON d.id = e.department_id 
GROUP BY d.id

42.

SELECT d.name 
FROM departments d LEFT JOIN employees e ON d.id = e.department_id 
WHERE e.id IS NULL

43.

SELECT e.name 
FROM employees e INNER JOIN departments d ON e.department_id = d.id 
WHERE d.name = '마케팅팀'

마지막 연습 문제

44.

SELECT o.id, p.name 
FROM orders o JOIN products p ON o.product_id = p.id

45.

SELECT p.id, SUM(p.price * o.quantity) AS total_sales 
FROM products p INNER JOIN orders o ON p.id = o.product_id 
GROUP BY p.id 
ORDER BY total_sales DESC 
LIMIT 1

46.

SELECT p.id, SUM(o.quantity) AS total_quantity 
FROM products p INNER JOIN orders o ON p.id = o.product_id 
GROUP BY p.id

47.

SELECT p.name 
FROM products p INNER JOIN orders o ON p.id = o.product_id 
WHERE o.order_date > '2023-03-03'

48.

SELECT p.name, SUM(o.quantity) AS total_quantity 
FROM products p INNER JOIN orders o ON p.id = o.product_id 
GROUP BY p.id 
ORDER BY total_quantity DESC 
LIMIT 1

49.

SELECT p.id, AVG(o.quantity) AS average_quantity 
FROM products p INNER JOIN orders o ON p.id = o.product_id 
GROUP BY p.id

50.

SELECT p.id, p.name 
FROM products p LEFT JOIN orders o ON p.id = o.product_id 
WHERE o.id IS NULL

과제하며 복습!

1️⃣ FROM  
   → 어떤 테이블에서 데이터를 가져올지 정함  

2️⃣ ON  
   → 테이블 간의 연결 조건 확인 (JOIN 조건)

3️⃣ JOIN  
   → 여러 테이블을 합침 (조건에 따라 행 결합)

4️⃣ WHERE  
   → 합쳐진 데이터에서 조건에 맞는 행만 필터링

5️⃣ GROUP BY  
   → 남은 데이터를 기준 컬럼으로 그룹화

6️⃣ HAVING  
   → 그룹화된 결과에 조건 적용 (집계값 조건)

7️⃣ SELECT  
   → 최종적으로 보여줄 컬럼을 선택

8️⃣ DISTINCT  
   → 결과에서 중복 행 제거

9️⃣ ORDER BY  
   → 결과를 특정 기준으로 정렬

WITH avg_score AS (
    SELECT AVG(score) AS avg FROM students
)
SELECT name
FROM students, avg_score
WHERE score > avg;

쿼리 작성에 도움되는 것 SQL 쿼리 실행순서에 따라 작성하고 해석!

FROM

JOIN

항상 작은 범위부터 테스트하라

여러 테이블 JOIN 시 먼저 두 개만 조인하여 결과를 확인하라

⇒ 조인 조건이 잘못되면 데이터가 폭발적으로 늘어나거나 없어지기 때문이다.

JOIN 시 NULL 값이 필요한 경우가 있으니 고려하여 사용하기

WHERE

WHERE은 행을 필터링 함을 기억하라

WHERE 필터링을 적용할 때 NULL도 제외되는 것을 기억하라

인덱스에 설정되어있는 컬럼 사용

인덱스의 선두 컬럼에 해당하는 조건을 사용하라

GROUP BY

집계함수

HAVING

HAVING은 집계된 결과를 필터링 함을 기억하라

DISTINCT

SELECT

보다는 필요한 컬럼만 사용하라

ORDER BY

LIMIT

번외

불필요한 서브쿼리보다는 JOIN, CTE, 윈도우함수를 사용하라

CTE: 공통 테이블 표현식, 가상 테이블

날짜 문제의 경우 애매한 경우가 많기에 여러 번 해독해야 함

날짜 계산은 무조건 함수로 하는 것이 좋다.

한국어 및 도메인의 특징에 따라 다르기 때문

대여의 개념

숙박의 개념

D-DAY 개념

문자 계산, 날짜끼리의 계산

WITH (가상 테이블)

가독성 향상을 위해 사용을 고려할 수 있다.

N회 반복 사용 할 때 사용을 고려할 수 있다.

WITH RECURSIVE (가상 테이블 재귀)

인위적인 컬럼을 만들 때 유용하게 사용된다.

⇒ 24시간의 시간표를 만들 때

실전문제 - 의약품 생산 Lot 품질 분석

Q1. 전체 로트 중 불량 로트의 비율을 계산하시오.

SELECT 
    ROUND(                                       -- 전체 결과를 소수점 2자리까지 반올림
        100.0 * SUM(                             -- 불량 로트 수를 100배 해서 백분율로 계산
            CASE WHEN water_content >= 8.0       -- 조건 1: 수분 함량이 8.0% 이상인 경우
                  OR purity <= 92.0              -- 조건 2: 순도가 92.0% 이하인 경우
                  OR ABS(weight - 500) > 10      -- 조건 3: 무게가 기준 500g에서 ±10g 초과한 경우
                THEN 1                           -- 위 조건 중 하나라도 만족하면 불량 → 1
                ELSE 0                           -- 조건을 모두 만족하지 않으면 정상 → 0
            END
        ) / COUNT(*),                            -- 전체 로트 수로 나누어 불량 비율 계산
        2                                        -- 소수점 둘째 자리까지 반올림
    ) AS defect_rate_percentage                  -- 최종 컬럼명: 불량률 (백분율로 표시)
FROM lot_quality_checks                          -- 검사 결과가 담긴 테이블

-오답노트: round 쓰는 법 몰라서 틀림/ 불량은 1, 정상은 0으로 변환/ 전체 로트 수로 나누어 불량 비율 계산
(사실 가운데 조건문 빼고는 거의 다 틀림)

Q2. 품질 항목별로 불량 로트 수를 각각 구하시오.

SELECT 
    SUM(CASE WHEN water_content >= 8.0 THEN 1 ELSE 0 END) AS water_defects,
    SUM(CASE WHEN purity <= 92.0 THEN 1 ELSE 0 END) AS purity_defects,
    SUM(CASE WHEN ABS(weight - 500) > 10 THEN 1 ELSE 0 END) AS weight_defects
FROM lot_quality_checks

Tips. ABS()를 사용하면 오차계산 가능!

실전문제 - 차량 센서 진단 및 이상 패턴 분석

Q1. 센서 이상이 1건 이상 있었던 차량 중, 정비 이력이 존재하는 차량의 비율을 구하시오.

해설 버전
-- 이상 조건을 만족하는 차량만 추출 (1건 이상이라도 해당되면 포함)
WITH abnormal_vehicles AS (
    SELECT DISTINCT vehicle_id
    FROM vehicle_test_logs
    WHERE vibration_level > 6.0                -- 진동 이상
       OR brake_response > 300                -- 제동 응답 시간 이상
       OR engine_temp < 70 OR engine_temp > 110 -- 엔진 온도 이상
),

-- 그 중 실제로 정비 이력이 있는 차량만 추출
repaired_vehicles AS (
    SELECT DISTINCT v.vehicle_id
    FROM abnormal_vehicles v
    JOIN vehicle_repairs r ON v.vehicle_id = r.vehicle_id
)

-- 전체 이상 차량 대비 정비 차량 비율 (%) 계산
SELECT 
    COUNT(DISTINCT repaired_vehicles.vehicle_id) * 100.0 
    / COUNT(DISTINCT abnormal_vehicles.vehicle_id) AS repair_rate_percent
FROM abnormal_vehicles
LEFT JOIN repaired_vehicles ON abnormal_vehicles.vehicle_id = repaired_vehicles.vehicle_id

Q2. 센서 항목별로 실제로 정비까지 이어진 건수를 구하시오.

해설 버전
-- 각 차량의 센서 이상 여부를 플래그로 변환
WITH abnormal_tests AS (
    SELECT DISTINCT vt.vehicle_id,
           CASE WHEN vibration_level > 6.0 THEN 1 ELSE 0 END AS vibration_flag,
           CASE WHEN brake_response > 300 THEN 1 ELSE 0 END AS brake_flag,
           CASE WHEN engine_temp < 70 OR engine_temp > 110 THEN 1 ELSE 0 END AS engine_flag
    FROM vehicle_test_logs vt
),

-- 센서 이상 차량과 실제 정비 기록 연결
repair_matches AS (
    SELECT a.vehicle_id,
           r.issue_type
    FROM abnormal_tests a
    JOIN vehicle_repairs r ON a.vehicle_id = r.vehicle_id
)

-- 센서별 이상과 정비 유형이 매칭되는 경우만 집계
SELECT 
    SUM(CASE WHEN vibration_flag = 1 AND issue_type = '진동' THEN 1 ELSE 0 END) AS vibration_repair_count,
    SUM(CASE WHEN brake_flag = 1 AND issue_type = '브레이크' THEN 1 ELSE 0 END) AS brake_repair_count,
    SUM(CASE WHEN engine_flag = 1 AND issue_type = '엔진' THEN 1 ELSE 0 END) AS engine_repair_count
FROM abnormal_tests a
JOIN repair_matches r ON a.vehicle_id = r.vehicle_id;

Q3. 센서 이상이 없었음에도 불구하고 정비를 받은 차량 수를 구하시오.

해설 버전
-- 센서 값이 모두 정상 범위에 속한 차량만 필터링
WITH normal_vehicles AS (
    SELECT DISTINCT vehicle_id
    FROM vehicle_test_logs
    WHERE vibration_level <= 6.0
      AND brake_response <= 300
      AND engine_temp BETWEEN 70 AND 110
),

-- 정상 차량 중 실제 정비 받은 차량만 추출
repaired_normals AS (
    SELECT DISTINCT nv.vehicle_id
    FROM normal_vehicles nv
    JOIN vehicle_repairs vr ON nv.vehicle_id = vr.vehicle_id
)

-- 정상인데도 정비 받은 차량 수
SELECT COUNT(DISTINCT vehicle_id) AS normal_repair_count
FROM repaired_normals;

Q4. 정비 없이 출고된 차량 중, 센서 이상 건수가 2개 이상이었던 차량 수를 구하시오.

해설 버전
-- 차량별 센서 이상 항목 수 계산
WITH abnormal_counts AS (
    SELECT vehicle_id,
           -- 각 센서 이상 여부를 1/0로 변환 후 합산
           (CASE WHEN vibration_level > 6.0 THEN 1 ELSE 0 END) +
           (CASE WHEN brake_response > 300 THEN 1 ELSE 0 END) +
           (CASE WHEN engine_temp < 70 OR engine_temp > 110 THEN 1 ELSE 0 END) AS abnormal_count
    FROM vehicle_test_logs
),

-- 이상 건수가 2개 이상이면서 정비 이력이 없는 차량만 필터링
high_abnormal_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.abnormal_count >= 2         -- 이상 항목 2개 이상
      AND r.vehicle_id IS NULL          -- 정비 이력이 없는 차량
)

-- 해당 조건을 만족하는 차량 수
SELECT COUNT(vehicle_id) AS high_abnormal_without_repair
FROM high_abnormal_no_repair;

나의 간단 소감

- 영어 처음 배울때처럼 문법이 어렵다. 단어는 거의 다 알지만 이걸 어떻게 어디에 써야하는지 모르는 느낌.

많이 해보면 언젠가 감이 오겠지...?

'내일배움캠프' 카테고리의 다른 글

[사전캠프] 데이터기반 QA/QC 부트캠프 8일차 (0)	2026.05.07
[사전캠프] 데이터기반 QA/QC 부트캠프 7일차 (0)	2026.05.06
[사전캠프] 데이터기반 QA/QC 부트캠프 6일차-1 (1)	2026.05.04
[사전캠프] 데이터기반 QA/QC 부트캠프 5일차 (0)	2026.05.04
[사전캠프] 데이터기반 QA/QC 부트캠프 4일차 (0)	2026.05.04

현재글[사전캠프] 데이터기반 QA/QC 부트캠프 6일차-2

min0jun 님의 블로그

min0jun 님의 블로그 입니다.

Today :
Yesterday :

min0jun 님의 블로그