250620_SQL 어려운 문제 2가지 (다중테이블과 다중 조건) , 기초프로젝트 소회

요약/나만의 인사이트

• 어려운 SQL문제를 풀 땐 문제 구조화 → 사용되는 테이블, 컬럼, 조건 고려 → 문제 재정의를 하면 도움이 된다.
• 즉 구하고 싶은 값과 그것을 구하기 위한 쿼리문을 고려하면 좋다.
• 그리고 단계를 밟아가며 풀어야 한다.
• 만약 값이 틀리더라도 문제 구조화 즉 값을 구하기 위한 논리를 구상하고 실천해가는 것만으로도 실력이 향상될 것이니 계속해서 정진하자.
• 파이썬도 마찬가지! (참고로 파이썬 공부했는데도 못푼다고 신세한탄했는데 나아질거란 믿음으로 차근차근 풀이하니 되더라!! 긍정적인 마인드 가보자고!!)

특히 어려웠던 것

상품을 구매한 회원 비율 구하기

• 문제
  • https://school.programmers.co.kr/learn/courses/30/lessons/131534
• 나의 풀이 (문제 구조화 → 필요한 요소 생각 → 재구조화)
  • 요구사항
    1. user_info 테이블과 online_sale 테이블에서 21년에 가입한 전체 회원들 - user_info 테이블(user_id, YEAR(joined))
    2. 그 중 상품을 구매한 회원의 비율
       1. 21년에 가입한 회원 중 상품을 구매한 회원수 - online_sale 테이블(where 조건 1번 조회, count(distinct user_id)
       2. 21년에 가입한 전체 회원수 - online_info 테이블(count(distinct user_id), joined)
    3. 회원의 비율 소수점 두번째자리에서 반올림 - round(비율, 1)
    4. 정렬 : 년-오름차순, 월-오름차순 - year(sales_date), month(sales_date)
• 내가 풀었던게 오답이유
  • 비율을 백분률로 생각해서 풀었다.
  • 비율 계산 시 분모는 21년에 가입한 전체 회원수이고 분자는 21년에 가입한 회원 중 상품을 구매한 회원수라는 점을 간과했다.
  • 다양한 조건절로 인해 혼란스러웠다.
• 생각하면 좋은 질문리스트
  • 요구사항은뭐지?
  • 요구사항을 풀려면 어떤 테이블, 어떤 컬럼을 사용해야하지?
  • 조건은 서브, 메인쿼리 중 어디에 넣어야 하지?
  • 주어진 테이블은 어떤 형태이지?
• 정확한 풀이

#1 21년에 가입한 고유 회원들의 리스트  --> 나중에 서브쿼리로 쓸 것임..
-- SELECT USER_ID
-- FROM USER_INFO
-- WHERE YEAR(JOINED) = '2021'

#2 21년에 가입한 전체 회원수 변수에 저장
SET @TOTAL := 
	( SELECT COUNT(USER_ID)
	FROM USER_INFO
	WHERE YEAR(JOINED) = '2021')
		
#3 21년에 가입한 회원들의 구매목록 - WHERE 조건 서브쿼리
#4 이중 상품을 구매한 이력이 있는 회원수 (고유값) 
#5 21년에 가입한 전체 회원 수 중 구매 이력이 있는 회원수 비율
SELECT YEAR(SALES_DATE) AS YEAR, 
			 MONTH(SALES_DATE) AS MONTH,
			 COUNT(DISTINCT USER_ID) AS PURCHASED_USERS,
			 ROUND (COUNT(DISTINCT USER_ID)/@TOTAL, 1) AS PURCHASED_USERS
FROM ONLIN_SALE
WHERE USER_ID IN ( SELECT USER_ID
											FROM USER_INFO
											WHERE YEAR(JOINED) = '2021')
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY 1, 2 

• 핵심 포인트쿼리문 내용

• 쿼리문	내용
  SET @TOTAL := ( SELECT COUNT(USER_ID) FROM USER_INFO WHERE YEAR(JOINED) = '2021')	21년에 가입한 전체 회원수 변수에 저장하기
  WHERE USER_ID IN ( SELECT USER_ID FROM USER_INFO WHERE YEAR(JOINED) = '2021')	21년에 가입한 유저들의 구매데이터 확인하기 - 서브쿼리를 통해 - WHERE 구문에서 포함하는 모든 행 데이터 필터링
  ROUND (COUNT(DISTINCT USER_ID)/@TOTAL, 1)	- 구매한 이력이 있는 유저 수 / 21년에 가입한 유저 총원 으로 비율 구하기
  GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)	년도, 월별 집계 ⭐ 그룹바이를 사용하면 셀렉문에 해당 컬럼 또는 집계함수가 쓰여야 함

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

• 문제
  • CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.
• 문제구조화 (구조화, 필요한 요소 정리)
  • 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 - CAR_RENTAL_COMPANY_CAR , WHERE
  • 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능 - CAR_RENTAL_COMPANY_RENTAL_HISTORY , 대여불가능한 조건으로 차집합
  • 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 - CAR_RENTAL_COMPANY_DISCOUNT_PLAN , WHERE
  • 조회: 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE)
  • 정렬: 대여금액 내림차순, 자동차 종류 오름차순, 자동차 ID 내림차순
• 나의 풀이가 틀렸던 이유
  • 11월간 대여 가능하다는 조건을 어떻게 만들고 적용할지 몰랐다.
  • HISTORY 테이블에 CAR_ID의 중복이 여러번 기록되어 있다는 점을 간과했다.
  • 각종 조건이 많아서 혼란스러웠다.
• 문제 재구조화(풀이 순서대로)
  1. 자동차 종류가 세단 및 SUV인 자동차를 찾는다.
  2. 11월 동안 대여 불가능한 CAR_ID를 조회한다. 그것을 NOT IN 으로 필터링한다. (서브쿼리 사용)
  3. 세단, SUV의 30일 이상 DISCOUNT_RATE 값을 가져온다. (JOIN)
  4. 할인가가 적용된 데일리 요금에 30일을 곱한다.
  5. 대여금액이 50만원 이상, 200만원 미만인 자동차만 필터링한다.
• 정답

# 1. 자동차 종류가 세단 및 SUV인 자동차만 필터링
WITH C AS
( SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE IN ('세단', 'SUV')
),
# 2 11월 동안 대여중인 카 조회 
R AS ( SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= '2022-11-30' AND  END_DATE >= '2022-11-01'
)
# 3 세단, SUV인 테이블과 DISCOUNT 테이블 합치기
-- 조건1 30일이상 할일율만 출력되도록 한다.
-- 조건2 11월 대여중인 자동차는 제외한다.
-- 조건3 할인가를 적용한 데일리 비용 *30을 한다.
-- 조건4 50만원 이상 200만원 미만만 조회한다.
SELECT C.CAR_ID,
	   C.CAR_TYPE,
		ROUND(C.DAILY_FEE *(1- D.DISCOUNT_RATE/100)*30,0) AS FEE
FROM C JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
ON C.CAR_TYPE = D.CAR_TYPE
WHERE D.DURATION_TYPE = '30일 이상' 
AND
	   C.CAR_ID NOT IN ( SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01' ) 
AND
ROUND(C.DAILY_FEE *(1- D.DISCOUNT_RATE/100)*30,0) BETWEEN 500000 AND 1999999
ORDER BY 3 DESC, 2, 1 DESC

• 핵심 포인트

• 코드	해석
  WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01'	11월 동안 대여중인 카 이후 차집합 개념을 적용해 11월 동안 대여가능한 카 리스트를 구한다 🌾 보통 첫날과 끝날을 END_DATE와 START_DATE로 연결하면 될 것 같음.
  C.CAR_ID NOT IN ( SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01' )	11월 동안 대여중인 카 ID가 NOT IN인 CAR_ID 조회 = 대여가능한 카 조회
  C.DAILY_FEE *(1- D.DISCOUNT_RATE/100)*30	FEE* (1-할인율/100) = 할인가

 

금주 인사이트

이번주에는 팀 기초프로젝트를 진행하느라 여념없었다.

데이터셋확인 → 문제정의 → 전처리 → EDA → 분석 → 인사이트 도출 → 결론 등 데이터 분석가라면 필히 이행해야할 일련의 과정들을 수행했다.

문제 정의를 위해선 일단 도메인 지식과 분석할 데이터 셋이 어떤 내용을 가지고 있는지 탐색해보는 시간이 필요함을 느꼈다.

무엇을 분석할 것인지, 목표가 무엇인지 명확하게 하는게 문제정의의 핵심인데 사실 하면 할수록 문제정의는 점점 더 어려워지는 것 같다.

일단 주어진 데이터셋을 맛보고 씹어보고 뜯어보는 등 다각도로 바라보야 함은 명확한 것 같다.

전처리는 결측치, 이상치, 형변환 등을 말한다. 데이터를 분석하기 쉽게 가공한다고 보면 쉽겠다.

그 이후 EDA를 통해 시각화를 통해 변수간 상관관계를 분석하고 인사이트를 도출해 결과해석을 하는 것이다.

판다스, 맷플로립은 정말… 아직도 잘 모르겠는데, 그런 와중에 이 라이브러리들을 가지고 의미있는 분석을 진행하는 게 굉장히 어려웠다.

그래도 덕분에 판다스, 맷플로립이랑 다소 친밀해진(?) 느낌이고 향후 판다스를 복습함에 있어 덜 낯설 것으로 기대한다.

긍정적으로 생각하자…!

이번 한주간 머리가 띵할 정도로 괴롭고 힘든 순간들이 많았으니 스스로 자기칭찬을 하며 마무리하려고 한다.

• 난해한 과업임에도 포기하지 않고 버텨내고 완성해낸 것 칭찬합니다.
• 이 과업을 진척시키기 위해 다양한 방법과 진행과정들을 제안한 점 칭찬합니다.
• 또한 내가 생각하는 의견과 방향이 있다면 주저하지 않고 피력하며 그것의 용인여부와 무관하게 시도한 것 자체가 용감한 사람이라는 것을 증명합니다.
• 그냥 너 대단해 멋져 수고 많았어! 남은 시간도 파이팅이다!
• 계속해서 나아질거란 믿음으로 더 잘하고 싶고 꽤 재미있는 파이썬과 SQL 학습 가보자고!!!!