2024-01-10

SQL 코드카타

 

자동차 대여 기록 별 대여 금액 구하기(SQL) (with, substr, locate, datediff, case when, join, round)

https://school.programmers.co.kr/learn/courses/30/lessons/151141

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

With truck_discount AS(

    SELECT disc.CAR_TYPE,
            substr(disc.DURATION_TYPE,1,locate('일',disc.DURATION_TYPE)-1) duration_type,
            disc.DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN disc
    WHERE disc.CAR_TYPE = '트럭'

),

truck_table AS(

SELECT hst.HISTORY_ID, car.CAR_TYPE, 
    car.DAILY_FEE * (datediff(hst.END_DATE,hst.START_DATE)+1) pre_fee,
    case 
        when datediff(hst.END_DATE,hst.START_DATE)+1>=90
        then 90
        when datediff(hst.END_DATE,hst.START_DATE)+1 between 30 and 89
        then 30
        when datediff(hst.END_DATE,hst.START_DATE)+1 between 7 and 29
        then 7
        else 0
    end duration_type
FROM CAR_RENTAL_COMPANY_CAR car
join CAR_RENTAL_COMPANY_RENTAL_HISTORY hst on car.CAR_ID = hst.CAR_ID
WHERE car.CAR_TYPE = '트럭'

)

SELECT distinct tt.HISTORY_ID,
        case when tt.duration_type=0 then tt.pre_fee
            else ROUND(tt.pre_fee * (1-(td.DISCOUNT_RATE/100)))
            end FEE
FROM truck_table tt
JOIN truck_discount td on tt.CAR_TYPE=td.CAR_TYPE
WHERE tt.DURATION_TYPE = td.DURATION_TYPE or tt.DURATION_TYPE = 0
ORDER BY FEE desc, tt.HISTORY_ID desc

이번 문제는 한번에 최대한 깔끔하게 해보려다가 좀 많이 헤맸다. 팀 도메인 결정 등의 사정으로 인해 신경쓸 시간이 별로 없는데 이것을 푸는데도 시간을 꽤 소비해버려서 설명을 TIL적을 때 다시 적느라, 그냥 바로 쿼리 설명적고 적으면서 생각나는 어려웠던 점을 적도록 하겠다.

우선 With구문을 이용하여 CAR_RENTAL_COMPANY_DISCOUNT_PLAN테이블로 부터 car_type이 트럭인 데이터들에 대하여 car_type과 duration_type을 처음 위치부터 일전까지 글자를 추출한 행과, 할인율을 출력하는 테이블을 truck_discount테이블로 선언해 주었다 그리고 CAR_RENTAL_COMPANY_CAR테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY테이블을 CAR_ID을 공통 컬럼으로 하여 join해주고 car_type이 트럭인 데이터들만 가져와서 HISTORY_ID, CAR_TYPE, 일당 대여요금*대여기간(대여기간은 datediff를 이용하여 +1해줘서 계산)하여 할인전 요금으로 그리고 기간 분류를 90일 이상, 30일 이상, 7일 이상, 0일이상으로 분류해주었다(각 숫자로만 표시) 그러한 테이블을 truck_table로 선언하였다.

그 뒤 truck_table테이블과 truck_discount테이블을 join해주고 둘의 DURATION_TYPE이 같거나 truck_table의 DURATION_TYPE가 0인 경우에 대하여 필터링 해주고 HISTORY_ID와 DURATION_TYPE=0일 경우는 할인전 가격 그대로, 아닐 경우는 해당하는 할인율을 적용하여 계산해주어 요금을 표시해주었다 그런 뒤, 마지막으로 요금으로 내림차순, 같으면 HISTORY_ID로 내림차순 정렬해주었다.

이 문제를 풀면서 어려웠던 점은 CAR_RENTAL_COMPANY_RENTAL_HISTORY(이하 history)테이블로 부터 대여기간을 계산한 뒤 , 거기에 맞게 CAR_RENTAL_COMPANY_DISCOUNT_PLAN(이하 discount)테이블에서 할인율을 맞게 가져와서 CAR_RENTAL_COMPANY_CAR(이하 car)테이블의 요금과 함께 계산해 주어야 한다는 점이 어려웠다 history테이블과 car테이블을 조인하여 만들어 할인전 요금을 계산해주는 것 까지는 무난했으나 대여기간에 따라 대여할인율을 매칭할려고하니 7일 미만인 경우들에 대하여는 할인율이 없어 discount테이블에서 관련 행이 없었기 때문에 매칭 되지 않아 마지막 메인 쿼리에서 해줄 때 어거지로 truck_table에서 기간타입이 0일 때도 필터링에 통과할 수 있게 해주었으며, 그에 따라 case when을 이용해서 할인율 적용 계산을 처리해주었으나 사실 바로 해줄 방법이 없을까 굉장히 고민했었다(join해준거라 기간 0인 것에 대하여 모든 discount 행이 크로스join 되었기 때문이다, 사실 0아닌 것도 0인 것 처리해주느라 같이 크로스join되었다 (아니였으면 join ~on 뒤에 조건을 두개 달아주니 의도대로 잘되었다 (나머지 하나 조건은 where에 달아두었던 조건이다)))

그리고 추가로 이번에 locate를 처음 써보았는데 사용법은 locate(찾을문자, 문자열)이렇게 하면 찾을 문자의 위치로 나온다 locate(찾을문자, 문자열,검색시작위치)로도 사용이 가능하다 단 이때 시작위치 이전에 문자열이 있더라도 시작위치이후에 문자가 없다면 없다고 나온다(없으면 -1로 나온다)

 

상품을 구매한 회원 비율 구하기(SQL) (with, count, year, month, join, round)

https://school.programmers.co.kr/learn/courses/30/lessons/131534

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

WITH join_2021 AS (
    SELECT count(USER_ID) cnt
    FROM USER_INFO info
    WHERE year(JOINED)='2021'
),
puchased_2021 AS (
    SELECT year(onl.SALES_DATE) YEAR, 
        month(onl.SALES_DATE) MONTH,
        count(distinct onl.USER_ID) PUCHASED_USERS
    FROM ONLINE_SALE onl
    JOIN USER_INFO info on onl.USER_ID = info.USER_ID
    WHERE year(JOINED)='2021'
    GROUP BY year(onl.SALES_DATE), month(onl.SALES_DATE)
)

SELECT p.YEAR, 
       p.MONTH,
       p.PUCHASED_USERS,
       round(p.PUCHASED_USERS/(SELECT j.cnt 
                               FROM join_2021 j),1) PUCHASED_RATIO
FROM puchased_2021 p

우선 wtih구문을 이용해 USER_INFO테이블에서 joined의 년도가 2021년인 데이터 들에 대해서만 필터링 하여 user_id를 세어 주어 2021년에 가입한 유저수를 계산해주는 join_2021 테이블을 선언하고, ONLINE_SALE테이블과 USER_INFO를 USER_ID를 공통컬럼으로 join해주고 연도가 2021년인 것에 대해서만 필터링 한뒤 년과 월로 그룹을 지은 뒤 연,월, 구매한(중복없이)유저수 를 가져온 테이블을 puchased_2021테이블로 선언해준다

그 뒤 puchased_2021테이블로 부터 년,월,(2021년에 가입한 사용자중 구매한 )사용자 수(PUCHASED_USERS), join_2021로 구했던 2021년에 가입한 총인원으로 PUCHASED_USERS를 나누어서 나타낸 비율(PUCHASED_RATIO)를 출력하는 쿼리문이다.

 

1757. Recyclable and Low Fat Products(SQL) (첫 영어 leetcode 문제)

https://leetcode.com/problems/recyclable-and-low-fat-products/

SELECT pdt.product_id FROM Products pdt
WHERE pdt.low_fats="Y" and pdt.recyclable= "Y";

Products( 이하 pdt )테이블로 저지방이고(pdt.low_fats="Y") 재활용 가능한(pdt.recyclable= "Y") 데이터만을 필터링하여 product_id를 출력하는 쿼리다

여담으로 첫 leetcode문제라 달라진 환경에 대하여 더 서술해두도록하겠다

일단 영어다…ㅠ 그래서 번역기 돌려서 대강 읽고 풀었다!

그리고 제출까지 하고나면 내 쿼리문이 얼마나 걸렸는지와 제출한 사람 중에서 백분율로 어디쯤 위치하는지 이런 것도 확인이 가능하다(엄청 빨리 했는 사람과 비교를 해봐도 사실 간단한 문제라 크게 다른 것이 없어보이는데 왜 속도가 저정도나 차이나는지 모르겠다 이 부분은 튜터님께 물어보는게 빠를듯하다)

그리고 추가여담으로 다크모드 환경에서 계속할려다가 흰 화면에서 할려니 불편해서 다크모드로 바꿀 수 있는 설정을 찾아보니 딱히 없는 듯 했고 (최근 상위 버전의 leetcode에서는 토글로 설정 가능하다고 하는 것 같은데 찾아봐도 안보였다) 그래서 따로 크롬 확장프로그램이 있길래 찾아서 설치 후 적용하니 다크모드가 적용되었다.

아 그리고 자동 깃허브 업로드도 프로그래머스가 아니라 leetcode라서 전에 다른 강의에서 leetcode 것으로 repositories해둔 것이 있는데(백준허브가 아닌 다른 확장프로그램으로 작동했었다) 거기로 자동 업로드 되는 듯 했다. 이거 관련해서는 체크해봐야 할듯하다

오늘은 파이썬 개인과제 해설 특강도 있고 곧 이어진 기초 프로젝트 팀 도메인 결정도 있고 하여, 개인 공부를 거의 하지 못하였다(파이썬 코드카타는 한문제라도 풀어볼려고 했으나 오래걸리다가 결국 못풀었다). 파이썬 개인과제는 크게 새로운 사실은 거의 없었던 것 같다(나중에 다시 복습해볼 때 그래도 정리해두면 좋겠다 싶은 내용이 있을 경우 정리해보도록 하겠다) 그리고 원래 오늘 SQL특강해주신 튜터님께 계속 질문할려다가 아직 정리가 덜되었거나, 튜터님의 출근날이 아니라서 못 질문하고 있던 질문을 할려고 정리를 최대한 많이 해보았었는데 일반적인 규칙으로는 튜터님이 출근하시는 요일인데 튜터시간표를 보니 오늘 안오시는 날이였다...ㅠㅠ 많이 허무했지만 내일은 오시니 프로젝트가 내일부터 시작되지만 틈을 보고 잠깐 여유가 날 때 튜터님께 가서 여쭤봐야겠다(튜터님이 다른 분들 도와주시느라 바빠서 못 물어볼수도 있을 것 같다, 이래서 질문 생기면 최대한 빨리 여쭤봤어야 했는데...ㅠㅠ) 오늘은 이래저래 변수가 많이 생겨서 계획대로 하지 못한 찝찝한 날이였다