SQL 중간 점검

원래 오늘 제출할 과제는 이번주에 진행했던 sql 연습문제 1-3의 복습이었다. 하지만 각각 해결하는데 복습을 할만큼 오래 걸리지 않았기 때문에 해당 문제들은 복습하기 보다는 내가 sql코드카타를 진행하면서 어렵게 느꼈던 문제를 다시 복습해보기로 했다.

 

1) 자동차 대여 기록 별 대여 금액 구하기

 

어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블,

자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블,

자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블이 있습니다.

 

CAR_RENTAL_COMPANY_CAR 테이블은 CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS으로 이루어져 있으며 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 HISTORY_ID, CAR_ID, START_DATE, END_DATE으로 이루어져 있으며 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE으로 이루어져 있으며 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다.

 

문제
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

 

 이 문제를 처음 보았을때  조건도 많고 테이블도 여러개라서 어디서부터 시도를 해야할지 방향이 잡히질 않았다. 그래서 나는 문제를 자세하게 읽어보면서 필요한 조건들을 정리해보았다.

1. 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서

2. 기록 별로 대여 금액(컬럼명: FEE)을 구하여

3. 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문 을 작성

4.  대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

 

보기 좋게 정렬을 해 뒀으니 한결 보기 편해졌다. 천천히 진행해보자.

가장 먼저 할 것은 CAR_RENTAL_COMPANY_CAR테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY테이블을 결합해서 출력하는 것이다. 자동차의 종류인 CAR_TYPE열과 대여기록  아이디인 HISTORY_ID가 모두 필요하기 때문읻다. 두 테이블은 공통된 열인 CAR_ID를 기준으로 결합해보자. 단, 열 전체를 출력하면 데이터가 너무 많아지기 때문에 CAR_TYPE과 HISTORY_ID 그리고 할인률 계산을 위해서 대여기간인 START_DATE와 END_DATE을, 대여금액을 알기위해서 DAILY_FEE까지 출력한다. 그리고 1번조건에 맞도록 where를 사용해서 CAR_TYPE이 트럭인 데이터만 필터링해주자.

SELECT
    RH.HISTORY_ID,
    CC.CAR_TYPE,
    RH.START_DATE,
    RH.END_DATE,
    CC.DAILY_FEE
from CAR_RENTAL_COMPANY_CAR CC inner join
    CAR_RENTAL_COMPANY_RENTAL_HISTORY RH on CC.CAR_ID = RH.CAR_ID
where CC.CAR_TYPE = '트럭'

트럭의 대여기록 ID별 대여기간과 대여금액

 

 

이번에는 대여기간별로 할인률을 계산한다. 그리고 그 할인률을 계산하기 전에 자동차의 종류별로 할인정책의 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 를 출력해서 내용을 확인한다.

select *
from CAR_RENTAL_COMPANY_DISCOUNT_PLAN

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블

 

 

해당 테이블은 자동차의 종류와 대여기간별 할인률의 데이터를 담고있다. 그런데 문제가 있다. 해당 테이블은 앞서 결합할 테이블과 합쳐줄 수 있는 공통된 열이 존재하지 않는다. 따라서 위의 테이블에 새로운 열을 추가해서 CAR_RENTAL_COMPANY_DISCOUNT_PLAN과 결합을 하고 필요한 데이터를 출력해주기로 했다.

가장먼저 CAR_RENTAL_COMPANY_DISCOUNT_PLAN의 트럭부분을 보자.

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 의 트럭데이터 부분

 

트럭의 경우 7일이상 대여하면 5%, 30일 이상 대여하면 8%, 90일이상 대여하면 15%의 할인률이 적용된다. 그리고 각각의 할인정책 ID는 10, 11, 12이다. 따라서 트럭의 대여기록 ID별 대여기간과 대여금액 테이블에 대여기간에 따라서 PLAN_ID열을 새로 생성한 후 해당열을 기반으로 결합하면 될 것이다.

SELECT
    RH.HISTORY_ID,
    CC.CAR_TYPE,
    CASE
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 90 THEN 12
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 30 THEN 11
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 7 THEN 10
    ELSE 0
    END PLAN_ID,
    DATEDIFF(RH.END_DATE, RH.START_DATE)+1 Date,
    CC.DAILY_FEE
from CAR_RENTAL_COMPANY_CAR CC inner join
    CAR_RENTAL_COMPANY_RENTAL_HISTORY RH on CC.CAR_ID = RH.CAR_ID
where CC.CAR_TYPE = '트럭'

 

트럭의 대여기간 ID별 할인정책 ID, 대여기간, 대여금액

case를 통해서 대여기간이 90일 이상일 경우 12, 30일 이상일 경우 11, 7일 이상일 경우 10, 그 이하일 경우 0을 값으로 가지는 새로운 열을 생성하고 해당열의 이름을 PLAN_ID로 했다. 이때, 총 대여금액을 산출해낼 수 있도록 Date라는 이름으로 대여 기간도 구해주었다. 또한 DATEDIFF를 사용하면서 대여 시작일은 1일로 취급해야했기 때문에 마지막에 +1을 더해줘서 시작일도 대여기간에 포함시켰다.

 

이제 새로 생성한 열인 PLAN_ID을 기준으로 두 테이블을 결합한다. 우선 지금 만든 쿼리를 서브쿼리로 만들어주고 CAR_RENTAL_COMPANY_DISCOUNT_PLAN과 결합하는데 PLAN_ID가 0인 데이터가 존재하기 때문에 left join을 통해서 10, 11, 12이외의 데이터도 표시해준다. 마지막으로 필요한 데이터면 선택해서 출력하면 된다.

select
    TR.HISTORY_ID,
    TR.DAILY_FEE,
    TR.Date,
    DP.DISCOUNT_RATE
from
(
SELECT
    RH.HISTORY_ID,
    CC.CAR_TYPE,
    CASE
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 90 THEN 12
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 30 THEN 11
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 7 THEN 10
    ELSE 0
    END PLAN_ID,
    DATEDIFF(RH.END_DATE, RH.START_DATE)+1 Date,
    CC.DAILY_FEE
from CAR_RENTAL_COMPANY_CAR CC inner join
    CAR_RENTAL_COMPANY_RENTAL_HISTORY RH on CC.CAR_ID = RH.CAR_ID
where CC.CAR_TYPE = '트럭'
) TR left join
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP on TR.PLAN_ID = DP.PLAN_ID

(트럭의) 대여기록 ID와 대여금액, 대여기간, 할인률

 

이제 드디어 두번째 조건인 대여기록 ID별로 총 대여금액을 계산할 수 있게 되었다. 할인률이 없는 경우 단순하게 대여기간과 대여금액을 곱해주면 되고 할인률이 있는 경우 대여금액에 할인을 적요한 후 대여기간을 곱해주면 된다.

select
    TR.HISTORY_ID,
    case
        when DISCOUNT_RATE is null then DAILY_FEE * Date
        else cast(DAILY_FEE - (DAILY_FEE * (DISCOUNT_RATE/100)) as signed) * Date
    end FEE
from
(
SELECT
    RH.HISTORY_ID,
    CC.CAR_TYPE,
    CASE
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 90 THEN 12
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 30 THEN 11
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 7 THEN 10
    ELSE 0
    END PLAN_ID,
    DATEDIFF(RH.END_DATE, RH.START_DATE)+1 Date,
    CC.DAILY_FEE
from CAR_RENTAL_COMPANY_CAR CC inner join
    CAR_RENTAL_COMPANY_RENTAL_HISTORY RH on CC.CAR_ID = RH.CAR_ID
where CC.CAR_TYPE = '트럭'
) TR left join
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP on TR.PLAN_ID = DP.PLAN_ID

대여기록 ID와 총 대여요금

 

 

이제 가장 복잡한 부분은 모두 끝났다. 남은 것은 대여기록 ID별로 그룹화한 후 정렬만 해주면 된다. 위에 작성한 case문의 합계를 구하기 위해서 sum으로 묶어주고 TR.HISTORY_ID를 기준으로 그룹화한다. 마지막으로 FEE에 대해서 내림차순, HISTORY_ID에 대해서 내림차순으로 정렬해주면 완료.

select
    TR.HISTORY_ID,
    sum(case
        when DISCOUNT_RATE is null then DAILY_FEE * Date
        else cast(DAILY_FEE - (DAILY_FEE * (DISCOUNT_RATE/100)) as signed) * Date
    end) FEE
from
(
SELECT
    RH.HISTORY_ID,
    CC.CAR_TYPE,
    CASE
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 90 THEN 12
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 30 THEN 11
        WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 >= 7 THEN 10
    ELSE 0
    END PLAN_ID,
    DATEDIFF(RH.END_DATE, RH.START_DATE)+1 Date,
    CC.DAILY_FEE
from CAR_RENTAL_COMPANY_CAR CC inner join
    CAR_RENTAL_COMPANY_RENTAL_HISTORY RH on CC.CAR_ID = RH.CAR_ID
where CC.CAR_TYPE = '트럭'
) TR left join
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP on TR.PLAN_ID = DP.PLAN_ID
group by TR.HISTORY_ID
order by FEE DESC, HISTORY_ID DESC

최종 코드

 

 

 

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

 

어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블, 온라인 상품 판매 정보를 담은 ONLINE_SALE 이 있습니다.

 

USER_INFO 테이블은  USER_ID, GENDER, AGE, JOINED으로 이루어져 있으며 각각 회원 ID, 성별, 나이, 가입일입니다.

ONLINE_SALE 테이블은  ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE으로 이루어져 있으며 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 입니다.

 

문제
USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

 

조금 전에 풀었던 문제보다는 간단해보인다.

1. 2021년에 가입한 전체 회원들 중

2. 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을

3. 년, 월 별로 출력하는 SQL문을 작성

4. 회원의 비율은 소수점 두번째자리에서 반올림하고

5. 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬

 

가장 먼저 해야할 것은 이번에도 역시 두 테이블을 결합하는 것이다. 그리고 필요한 데이터인 USER_ID 가입일, 판매일을 출력하는 것이다.

select
    UI.user_id,
    UI.joined,
    OS.sales_date
from USER_INFO UI
inner join ONLINE_SALE OS on UI.USER_ID = OS.USER_ID

회원 ID와 가입일, 구매일

 

 

다음은 가입년도가 2021년인데이터만 필터링한다. 간단하게 Where를 사용하면 된다. 그리고 이제 가입일자는 필요가 없어졌으므로 삭제해준다.

select
    UI.user_id,
    OS.sales_date
from USER_INFO UI
inner join ONLINE_SALE OS on UI.USER_ID = OS.USER_ID
where date_format(JOINED, '%Y') = '2021'

2021년에 가입한 회원의 회원 ID와 구매일

 

이번에는 상품을 구매한 회원과 전체 회원의 비율을 구하기 위해서 2021년에 가입한 전체 회원 수를 구해줘야한다. 간단하게 count를 사용해서 구할 수 있을 것 같지만 실제로 해보니 그렇지 않았다. 해당 쿼리는 가입일자에 대한 데이터를 가지고 있는 ONLINE_SALE테이블과 다른 테이블인 USER_INFO테이블이 결합되어있기 때문이다. 또한 계산하는 과정에서 그룹화를 사용할 텐데 그 과정에서 오류가 발생할 수 있기 때문읻다. 따라서 새로 서브쿼리를 작성하여 2021년에 가입한 전체 회원 수를 구해주어야한다.

select count(*) total
from USER_INFO
where date_format(JOINED, '%Y') = '2021'

2021년에 가입한 전체 회원의 수

 

그리고 해당 쿼리를 서브쿼리화 해서 결합한다.

select
    UI.USER_ID,
    OS.SALES_DATE,
    total
from USER_INFO UI
join ONLINE_SALE OS on UI.USER_ID = OS.USER_ID
join (select count(USER_ID) total
      from USER_INFO
      where date_format(JOINED, '%Y') = '2021'
) total_user
where date_format(JOINED, '%Y') = '2021'

2021년에 가입한 회원들의 구매날짜와 총 회원수

 

 

total이라는 새로운 열에 총 회원수 데이터가 나타나게 되었다. 다음으로 년 월의 데이터로 그룹화와 정렬를 할 수 있도록 해당 데이터를 분리하자. 

select
    UI.USER_ID,
    date_format(OS.SALES_DATE, '%Y') YEAR,
    date_format(OS.SALES_DATE, '%m') MONTH,
    total
from USER_INFO UI
join ONLINE_SALE OS on UI.USER_ID = OS.USER_ID
join (select count(USER_ID) total
      from USER_INFO
      where date_format(JOINED, '%Y') = '2021'
) total_user
where date_format(JOINED, '%Y') = '2021'

2021년에 가입한 회원들의 구매년, 월, 총 회원수

 

 

다음으로 2021년에 가입한 회원들중 구매한 회원들의 숫자를 세어준다. 총 가입한 회원의 수와 비율을 계산하기 위해서 이다. 중복을 제거하기 위해서 distinct를 사용하고 이제는 필요가 없는 USER_ID열을 지운다. 그리고 년, 월을 기준으로 그룹화하여 년, 월별로 데이터를 출력할 수 있도록 한다.

select
    date_format(OS.SALES_DATE, '%Y') YEAR,
    date_format(OS.SALES_DATE, '%m') MONTH,
    count(distinct OS.USER_ID) PURCHASED_USERS,
    total
from USER_INFO UI
join ONLINE_SALE OS on UI.USER_ID = OS.USER_ID
join (select count(USER_ID) total
      from USER_INFO
      where date_format(JOINED, '%Y') = '2021'
) total_user
where date_format(JOINED, '%Y') = '2021'
group by YEAR, MONTH

2021년 가입한 회원들의 년,월별로 구매한 회원수와 총 회원수

 

 

이제 마지막 단계이다. 구매한 회원수에서 총 회원수를 나눠주고 소수점 주번째 자리에서 반올림하여 첫번째 자리까지 표시가 되도록 하면 된다. order by를 이용한 데이터 정렬도 잊지말자.

select
    date_format(OS.SALES_DATE, '%Y') YEAR,
    date_format(OS.SALES_DATE, '%m') MONTH,
    count(distinct OS.USER_ID) PURCHASED_USERS,
    round(count(distinct OS.USER_ID) / total_user.total, 1) PUCHASED_RATIO
from USER_INFO UI
join ONLINE_SALE OS on UI.USER_ID = OS.USER_ID
join (select count(USER_ID) total
      from USER_INFO
      where date_format(JOINED, '%Y') = '2021'
) total_user
where date_format(JOINED, '%Y') = '2021'
group by YEAR, MONTH
order by YEAR, MONTH

최종 결과

 

 

 

이렇게 하여 복습이 끝났다. 처음 문제를 풀때는 어떻게 해야하는지 잘 몰라서 한참동안 고민하고 검색했었는데 한번 풀어보았던 경험을 살려서 차근차근 다시 풀어보았더니 처음 할때만큼 어렵게 느껴지지 않았다. 이제 공부를 시작한지 불과 3-4주지만 어느정도 기초는 쌓았다는 느낌이 들었다.