2025.03.06 본_캠프 12일차

1. 양질의 데이터를 판별하는 5가지 방법: ②믿을 수 있는 데이터인가?

• 요약 : 데이터 분석의 신뢰성을 높이기 위해 양질의 데이터를 판단하려면 데이터 오류,결측지,가공 정도를 분석해야 하며, 원천 데이터와 가공 데이터의 균형을 고려해야 한다.

• 주요 포인트 :

  • 데이터 오류와 결측 데이터는 신뢰성을 저하시킨다.
  • 데이터 품질은 데이터 활용돠와 직결되므로 양질의 데이터를 판별하는 능력이 중요하다
  • 원천 데이터는 오류가 적지만, 가공 데이터는 활용성도가 높지만 신뢰성이 낮아질 수 있다.
  • 데이터 분석 목정에 따라 허용 가능한 오류 수준을 설정하고, 데이터 가공 정도를 조절해야 한다.

• 인사이트 :

  • 데이터 분석의 신뢰성은 데이터 자체의 오류,결측치,가공 정도에 따라 크게 좌우되므로, 분석가는 데이터의 품질을 면밀히 검토해야 한다. 원천 데이터는 오류가 적지만 복잡하고, 가공 데이터는 활용도가 높지만 신뢰성이 낮아질 수 있으므로, 데이터 분석 목표에 따라 허용 가능한 오류 수준을 설정하고 데이터 가공 방식을 신중하게 결정하여 접근해야 한다.

• 핵심 개념 :

  • 데이터 품질 : 데이터 분석 결과의 신뢰성을 결정하는 핵심 요소
  • 데이터의 신뢰성 중요성 : 잘못된 입력,프로그래밍 오류,의도적 조작 등 다앙한 이유로 발생할 수 있다.
  • 데이터 신뢰성 vs 활용성 : 원천 데이터는 신뢰성이 높지만 분석이 어렵고, 가공 데이터는 활용성은 높지만 신뢰성이 낮아짐.

---

1. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

문제 설명

• USED_GOODS_BOARD 테이블과 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 게시글의 첨부파일 경로 조회
• 기본적인 파일 경로는 /home/grep/src/
• BOARD_ID를 기준으로 디렉토리를 구분하고, 파일 이름은 FILE_ID + FILE_NAME + FILE_EXT로 구성
• FILE_ID를 기준으로 내림차순 정렬

정답 쿼리

SELECT CONCAT('/home/grep/src/', B.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT) FILE_PATH
FROM USED_GOODS_BOARD B 
LEFT JOIN USED_GOODS_FILE F ON B.BOARD_ID = F.BOARD_ID
WHERE B.VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD) 
ORDER BY F.FILE_ID DESC;

쿼리 설명

• USED_GOODS_BOARD에서 가장 조회수가 높은 게시글(MAX(VIEWS))을 조회
• USED_GOODS_FILE과 BOARD_ID 기준으로 LEFT JOIN
• CONCAT을 사용해 파일 경로를 생성 (/home/grep/src/BOARD_ID/FILE_IDFILE_NAME.FILE_EXT)
• FILE_ID를 기준으로 내림차순 정렬

---

2. 주문량이 많은 아이스크림 조회하기

문제 설명

• FIRST_HALF 테이블(상반기 주문량)과 JULY 테이블(7월 주문량)의 SHIPMENT_ID를 기준으로 주문량 합산
• 주문량이 많은 순서대로 상위 3개의 아이스크림 맛 조회

정답 쿼리

SELECT H.FLAVOR 
FROM FIRST_HALF H 
LEFT JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) TOTAL_ORDER, SHIPMENT_ID 
           FROM JULY 
           GROUP BY FLAVOR) J 
ON H.SHIPMENT_ID = J.SHIPMENT_ID
ORDER BY H.TOTAL_ORDER + J.TOTAL_ORDER DESC
LIMIT 3;

쿼리 설명

• JULY 테이블에서 FLAVOR 기준으로 SUM(TOTAL_ORDER)를 구해 서브쿼리 생성
• FIRST_HALF 테이블과 SHIPMENT_ID 기준으로 LEFT JOIN
• ORDER BY를 사용해 총 주문량(H.TOTAL_ORDER + J.TOTAL_ORDER)이 많은 순으로 정렬
• 상위 3개만 조회(LIMIT 3)

---

3. 저자별 카테고리별 매출액 집계하기

문제 설명

• BOOK, AUTHOR, BOOK_SALES 테이블에서 2022년 1월 판매 데이터를 기준으로 매출액 집계
• TOTAL_SALES = 판매량 * 가격
• 저자 ID(AUTHOR_ID), 카테고리(CATEGORY), 매출액(TOTAL_SALES) 조회
• AUTHOR_ID 오름차순, 같은 경우 CATEGORY 내림차순 정렬

정답 쿼리

SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE * S.SALES) TOTAL_SALES
FROM BOOK B 
LEFT JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
LEFT JOIN BOOK_SALES S ON B.BOOK_ID = S.BOOK_ID
WHERE S.SALES_DATE LIKE '2022-01%'
GROUP BY 1,2,3
ORDER BY B.AUTHOR_ID, B.CATEGORY DESC;

쿼리 설명

• BOOK과 AUTHOR를 AUTHOR_ID 기준으로 LEFT JOIN
• BOOK과 BOOK_SALES를 BOOK_ID 기준으로 LEFT JOIN
• SALES_DATE가 2022-01%(2022년 1월)인 데이터만 조회
• SUM(B.PRICE * S.SALES)로 매출액 계산
• AUTHOR_ID 기준 오름차순, CATEGORY 기준 내림차순 정렬

---

4. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

문제 설명

• CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 8월~10월 기간 동안 대여 시작일(START_DATE)을 기준으로 대여 횟수 집계
• 총 대여 횟수가 5회 이상인 자동차만 조회
• 월별(MONTH) 대여 횟수(RECORDS) 조회
• MONTH 오름차순, 같은 경우 CAR_ID 내림차순 정렬

정답 쿼리

SELECT DATE_FORMAT(START_DATE, '%m') MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
AND CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
)
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC;

쿼리 설명

• START_DATE가 2022년 8월~10월 사이인 데이터를 필터링
• CAR_ID별 COUNT(*)를 구해 5회 이상인 CAR_ID만 조회
• GROUP BY MONTH, CAR_ID를 사용해 월별, 자동차별 대여 횟수 집계
• MONTH 오름차순, CAR_ID 내림차순 정렬

---

5. 그룹별 조건에 맞는 식당 목록 출력하기

문제 설명

• MEMBER_PROFILE과 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰 조회
• 회원 이름(MEMBER_NAME), 리뷰 텍스트(REVIEW_TEXT), 리뷰 작성일(REVIEW_DATE) 조회
• 리뷰 작성일 오름차순, 같은 경우 REVIEW_TEXT 오름차순 정렬

정답 쿼리

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
FROM MEMBER_PROFILE M 
LEFT JOIN REST_REVIEW R ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID = (
    SELECT MEMBER_ID
    FROM REST_REVIEW 
    GROUP BY MEMBER_ID
    ORDER BY COUNT(*) DESC
    LIMIT 1
)
ORDER BY 3,2;

쿼리 설명

• REST_REVIEW에서 MEMBER_ID를 기준으로 리뷰 개수를 집계해 가장 많이 작성한 회원을 서브쿼리로 추출
• MEMBER_PROFILE과 REST_REVIEW를 MEMBER_ID 기준으로 LEFT JOIN
• 리뷰 작성일(REVIEW_DATE) 오름차순 정렬, 같은 경우 REVIEW_TEXT 오름차순 정렬

---

3. 오늘 공부한 SQL 개념 정리

윈도우 함수와 GROUP BY

• 윈도우 함수는 GROUP BY와 함께 사용할 수 없음.
  • GROUP BY는 그룹별 집계 결과를 반환하는 반면, 윈도우 함수는 각 행별로 결과를 반환하기 때문.
• ROW_NUMBER()는 기본적으로 순위를 랜덤으로 지정하지만, 특정한 경우 명확한 순위를 지정해야 할 필요가 있음.
  • 게임 보상을 지급할 때: 순위가 겹치면 안 되는 경우.
  • 고객에게 순위를 제공할 때: 공동 랭크보다는 명확한 랭크가 필요할 때.

---

LAG() 함수

• LAG()는 이전 행의 값을 조회할 때 사용됨.
• 예제: 이전 결제 기록이 있는지 확인할 때 유용함.

  SELECT user_id, sales_date, sales_amount, 
         LAG(sales_amount, 1, 0) OVER (PARTITION BY user_id ORDER BY sales_date) AS previous_sales
  FROM online_sales;

  • LAG(sales_amount, 1, 0): 한 행 전의 sales_amount 값을 가져오고, 없으면 0을 반환.

---

재귀 CTE (WITH RECURSIVE)

• WITH RECURSIVE HOURS AS 부분에서 SELECT 0 AS HOUR는 재귀 CTE에서 초기 값을 설정하는 역할을 함.
• 예제:

  WITH RECURSIVE HOURS AS (
      SELECT 0 AS HOUR
      UNION ALL
      SELECT HOUR + 1 FROM HOURS WHERE HOUR < 23
  )

  • SELECT 0 AS HOUR: 재귀의 시작값 (초기값)
  • 이후 UNION ALL을 통해 0부터 23까지 숫자를 생성함.

---

NOT IN → NOT EXISTS 변경 이유

• NOT IN을 사용할 경우, 서브쿼리에 NULL이 존재하면 결과를 반환하지 않는 문제가 발생함.
• 해결 방법: NOT EXISTS를 사용하여 해당 기간 동안 대여된 자동차가 없는 경우만 선택하도록 변경.

  SELECT * 
  FROM CAR_RENTAL_COMPANY_CAR C
  WHERE NOT EXISTS (
      SELECT 1 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
      WHERE C.CAR_ID = H.CAR_ID
      AND H.START_DATE <= '2022-11-30'
      AND H.END_DATE >= '2022-11-01'
  );

  • SELECT 1은 존재 여부만 확인하므로 성능 면에서도 NOT IN보다 유리함.

---