쿼리로 데이터베이스 다루기

[내일배움캠프] QA/QC_6기 - 사전캠프 6일차

• TIL
  • SQL 서브쿼리와 테이블 병합
• To-Do List
  
  1. SQL 강의 수강 (Chap4 전체)
  2. 퀘스트 총 4개
  3. ADsP 강의 수강 (Chap3-1 ~ Chap4-2)

---

SQL

여러번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)

• Subquery가 필요한 경우
  • 여러번의 연산을 수행해야 할 때
    ex) 수수료를 부과할 수 있는 시간을 구하고
    → 구해진 시간에 주문 금액별 가중치를 주고
    → 가중치를 적용한 결과로 최종 배달비를 계산할 때
  • 조건문에 연산 결과를 사용해야 할 때
    ex) 음식 타입별 평균 음식 주문금액에 따라 음식비 상/중/하를 나누고 싶을 때 
  • 조건에 Query 결과를 사용하고 싶을 때
    ex) 30대 이상이 주문한 결과만 조회하고 싶을
• Subquery 문의 기본 구조
  • 쿼리 안에 부수적(Sub)으로 들어간 구문
    select 컬럼1, specific_column
    from
    ( 
    select 컬럼1, 컬럼2 specific_column
    from 테이블1
    ) a
  • 실습 : 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
    (조건 : 수량 5개 이하 → 10% / 15개 초과, 총 주문금액 300,000 이상 → 0.5%, 이 외에는 일괄 1%)

작성한 코드

결과

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

• JOIN이 필요한 경우
  ex) 주문 가격은 주문 테이블에 있지만, 결제 수단은 결제 테이블에 존재할 때
• JOIN의 기본 원리와 종류
  • 기본적으로 엑셀의 vlookup과 유사
  • 두 테이블이 공통으로 가지고 있는 컬럼을 기준으로 두 테이블을 합쳐 조회할 수 있도록 해주는 것
  • LEFT JOIN : 공통 컬럼(키 값)을 기준으로 다른 테이블에 값이 없더라도 모두 조회하는 경우
    select 조회 할 칼럼
    from 테이블1 a left join 테이블2 b on a.공통칼럼명=b.공통칼럼명
  • INNER JOIN : 공통 컬럼을 기준으로 두 테이블 모두가 가지는 값만 조회
    from 테이블1 a inner join 테이블2 b on a.공통칼럼명=b.공통칼럼명
  • 이 때 from 문에서 테이블1, 테이블2 옆의 a와 b는 각 테이블의 별칭 (a, b 말고 다른 문자도 상관 X)
  • 실습 : 50세 이상의 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
    할인 : (나이-50)*0.005
    고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

작성한 쿼리

결과

실습 문제

• 문제 : 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
• 조건 
  • 평균 음식 주문 금액 기준 : 5,000원 이하 / ~10,000 / ~30,000 / 30,000 초과
  • 평균 연력 : ~20대 / 30대 / 40대 / 50대 이상
  • 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

작성한 쿼리문

결과

 

과제

1. lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요.
   select *
   from lol_feedbacks
   order by satisfaction_order desc
2. lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요.
   select user_name, max(feedback_date)
   from lol_feedbacks
   group by user_name
   order by date desc
   
3. lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요.
   select count(1)
   from lol_feedbacks
   where satisfaction_score=5
   
4. lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요.
   select user_name, count(1) num_feedback
   from lol_feedbacks
   group by user_name
   order by 2 desc
   limit 3
5. lol_feedbacks 테이블에서 평균 만족도가 가장 높은 날짜를 찾는 쿼리를 작성해주세요.
   select feedback_date, avg(satisfaction_score)
   from lol_feedbacks
   group by 1
   order by 2 desc
   limit 1
   
6. doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요.
   select name
   from doctors
   where major='성형외과'
7. doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요.
   select major, count(1)
   from doctors
   group by major
   
8. doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요.
   select count(1) num_doctors
   from doctors
   where hire_date<=date_sub(curdate(), interval 5 year)
   
9. doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요.
   select name, datadiff(curdate(),hire_date) working_days
   from doctors
   
10. patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요.
    select gender, count(1)
    from patients
    group by gender
11. patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요.
    select count(1)
    from patients
    where birth_date<=date_sub(curdate(), interval 40 year)
12. patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요.
    select name, last_visit_date
    from patients
    where last_visit_date<=data_sub(curdate(), interval 1 year)
13. patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요.
    select count(1)
    from patients
    where birth_date between '1980-01-01' and '1980-12-31'

 

 

---

Camp Quests

미션3 : GPT로 공정문제 원인분석 보고서 작성

배경 : 자동차 브레이크 패드 생산 A사, 최근 3개월간 2라인에서 불량률 급증

1. 시나리오 파악하기
   • Q1. 2라인 불량품이 급증하기 시작한 시점은 언제인가?
     • 3월 이전에는 두 라인 모두 평균적으로 약 2% 정도의 불량률을 기록
     • 3월부터 2라인의 불량률이 4.3%로 급격히 증가하고, 이후 4월부터는 5%를 초과하는 불량률을 기록 중
   • Q2. 그 시점 전후로 바뀐 것이 있는지, 있다면 무엇인가?
     • 3월 중순 원자재 공급사가 B사에서 C사로 변경된 점
     • 2라인의 야간 조 작업자가 2명 교체된 점
   • Q3. 가장 의심되는 원인은 뭐라고 생각하는가? (직관적으로)
     • 발생한 불량 유형의 대부분이 치수 불량과 표면 불량 (합쳐서 91%)
     • 3월 초에 진행한 프레스기 정기 점검을 진행하여 이전에 문제가 없었다면 이후에도 괜찮을 가능성이 높음
     • 원자재 공급사의 변동으로 불량이 발생했다기에는 2라인에서만 문제가 발생
     • 따라서 야간 조 작업자 2명의 작업 숙련도 부족이 원인일 가능성이 매우 높음
2. GPT로 1차 원인 분석하기
   너는 제조업 품질관리 전문 콘설턴트야.
   아래는 자동차 브레이크 패드를 생산하는 A사의 2라인 불량률 데이터야.
   
   [월별 불량률]
   1월: 1라인 1.8%, 2라인 1.9%
   2월: 1라인 1.7%, 2라인 2.1%
   3월: 1라인 1.9%, 2라인 4.3%
   4월: 1라인 1.8%, 2라인 5.7%
   5월: 1라인 2.0%, 2라인 5.1%
   
   [3월에 있었던 변경 사항]
   - 3월 초: 2라인 프레스기 정기 점검 완료
   - 3월 중순: 원자재 공급사 변경 (B사 → C사)
   - 3월 초: 야간 조 작업자 2명 교체
   
   [불량 유형 비율]
   치수 불량 68%, 표면 불량 23%, 기타 9%
   
   이 데이터를 바탕으로:
   (1) 불량률 급증의 가능성이 높은 원인 3가지를 근거와 함께 제시해줘
   (2) 각 원인별로 확인해봐야 할 데이터나 현장 점검 항목도 알려줘
   (3) 표 형태로 정리해줘 (원인 / 근거 / 확인 방법)
3. GPT와 대화 이어가며 심화 분석하기
   • 반론 제기 프롬프트
     방금 분석 결과에 반론을 제기해줘.
     원자재 공급사 변경이 원인이 아닐 수도 있는 이유와 근거를 제시해줘.
     그리고 원자재 외에 놓쳤을 수 있는 원인이 있다면 추가해줘
   • 우선순위 결정 프롬프트
     가장 먼저 조치해야 할 순서를 정해줘.
     기준은 "빠르게 개선 효과를 볼 수 있는 것" 순서야.
     각 조치마다 예상 소요 기간과 담당 부서도 포함해줘.
4. 원인분석 보고서 초안 작성하기
   [ A사 2라인 불량률 급증 원인분석 보고서 ]
   작성일: 2026-04-27
   작성자: 김스파
   
   • 현황 요약
     3월부터 2라인의 불량률이 2배 이상 증가하여 불량 원인 탐색과 해결책이 필요한 상황
   • 데이터 분석
     
     
   • 추정 원인 (상위 3가지)
     • 1. 2라인 프레스기 점검 후 세팅 또는 금형 상태 변화
       • 근거: 3월 초에 진행하였고 치수불량과 직접적인 관련
       • 확인 방법: 점검 이전으로 세팅값을 원복한 뒤 제조
     • 2. 야간 조 작업자 교체에 따른 작업 편차 
       • 근거: 3월에 교체 진행했고, 장입량·금형청소·탈형 조건 등과 치수 불량의 관련성이 높음
       • 확인 방법: 3월 이후 생산품의 Lot를 생산일자, 주/야간, 작업자, 금형 캐비티, 원자재 Lot 기준으로 분류하여 불량이 집중된 조건이 있는지 확인
     • 3. 검사/측정 시스템 문제
       • 근거: 실제 공정 불량 문제가 아니라 검사 기기의 측정, 판정 문제일 가능성
       • 확인 방법: 원료 계량기·치수 측정기, 검사 Jig의 교정 상태 확인하고 동일 제품을 다른 검사자, 측정기로 교차 측정
   • 권고 조치 (우선순위 순)
     1. 2라인 프레스 recipe와 점검 전 조건 비교 후 원복
     2. 3월 이후 불량을 일자·조·작업자·캐비티·원자재  등 각 조건 별 재분류
     3. 검사/측정 시스템의 교차 점검
   • 기타 의견
     • 2라인의 불량률이 단독으로 상승한 것이기 때문에 1라인도 같이 이루어진 프레스기 점검, 원자재 공급사 변경이 원일일 것 같지는 않음
     • 가장 유력한 부분은 야간 조 작업자 교체로 추정
     • 정기 점검을 진행하지 않은 부분에서 발생한 문제인지도 확인 필요
5. GPT로 보고서 피드백 받고 마무리하기

---

출처

전용문, 박현민 지음, 2026 ADsP 데이터분석 준전문가, 위키북스

내일배움캠프 교육 자료