2024-10-27

Suhyeon Lee·2024년 10월 27일
내일배움캠프 Data 4기
0

CodeKata

SQL

75. 자동차 대여 기록 별 대여 금액 구하기

  • 문제
    CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
    • 자동차 종류가 '트럭'인 자동차의 대여 기록
      • car_rental_company_car 테이블 car_type 컬럼 '트럭'인 car_id
      • car_rental_company_rental_history 테이블에서 해당하는 car_id의 기록 추츨
    • 대여 기록 별로 대여 금액(컬럼명: FEE)
      • 대여금액: car_rental_company_rental_history 테이블 ((end_date - start_date) + 1) * car_rental_company_car 테이블 daily_fee
      • "할인" 적용해야 함
    • 최종 출력
      • 대여 기록 id
      • 대여 금액
      • 대여 금액 기준 내림차순 정렬, 대여 금액이 같은 경우 대여 id 기준 내림차순 정렬
  • 작성한 쿼리
  1. 트럭 찾기
SELECT
  car_id
  , daily_fee
  , car_type
FROM
  car_rental_company_car
WHERE
  car_type = '트럭'
  1. 대여 기록

(1) JOIN 활용

WITH truck AS (
  SELECT
    car_id
    , daily_fee
    , car_type
  FROM
    car_rental_company_car
  WHERE
    car_type = '트럭'
)
SELECT
  history_id
  , DATEDIFF(end_date,start_date) + 1 AS rental_period
  , daily_fee
  , car_type
FROM
  car_rental_company_rental_history rh
  JOIN truck t
  USING(car_id)

(2) WHERE IN 서브쿼리

WITH truck AS (
  SELECT
    car_id
    , daily_fee
    , car_type
  FROM
    car_rental_company_car
  WHERE
    car_type = '트럭'
)
SELECT
  history_id
  , DATEDIFF(end_date,start_date) + 1 AS rental_period
  , daily_fee
  , car_type
FROM
  car_rental_company_rental_history
WHERE
    car_id IN (SELECT car_id FROM truck)

SDL

Q. WHERE IN 서브쿼리와 JOIN 중 뭐가 더 좋을까?
A. JOIN 쓰세요 → WHERE IN vs. JOIN

  1. 대여기록별 할인 적용 여부
WITH truck AS (
  SELECT
    car_id
    , daily_fee
    , car_type
  FROM
    car_rental_company_car
  WHERE
    car_type = '트럭'
),
rental_history AS (
  SELECT
    history_id
    , DATEDIFF(end_date,start_date) + 1 AS rental_period
    , daily_fee
    , car_type
  FROM
    car_rental_company_rental_history rh
    JOIN truck t
    USING(car_id)
)
SELECT
  *
  , CASE
    WHEN rental_period BETWEEN 1 AND 6 THEN '할인 없음'
    WHEN rental_period BETWEEN 7 AND 29 THEN '7일 이상'
    WHEN rental_period BETWEEN 30 AND 89 THEN '30일 이상'
    ELSE '90일 이상'
  END AS duration_type
FROM
  rental_history
  1. 최종 제출
WITH truck AS (
  SELECT
    car_id
    , daily_fee
    , car_type
  FROM
    car_rental_company_car
  WHERE
    car_type = '트럭'
),
rental_history AS (
  SELECT
    history_id
    , DATEDIFF(end_date,start_date) + 1 AS rental_period
    , daily_fee
    , car_type
  FROM
    car_rental_company_rental_history rh
    JOIN truck t
    USING(car_id)
),
history_with_duration AS (
  SELECT
    *
    , CASE
      WHEN rental_period BETWEEN 1 AND 6 THEN '할인 없음'
      WHEN rental_period BETWEEN 7 AND 29 THEN '7일 이상'
      WHEN rental_period BETWEEN 30 AND 89 THEN '30일 이상'
      ELSE '90일 이상'
    END AS duration_type
  FROM
    rental_history
)
SELECT
  history_id
  , CONVERT(rental_period*daily_fee*0.01*(100-IFNULL(discount_rate, 0)), UNSIGNED) AS fee
FROM
  history_with_duration hd
  LEFT JOIN car_rental_company_discount_plan dp
  USING(duration_type, car_type)
ORDER BY
  2 DESC
  , 1 DESC
;

NULL 값 연산
아래 쿼리의 결괏값은 과연 무엇이 나올까?
SELECT 2 + NULL;
정답은 NULL 이다. 그래서 아무 것도 반환하지 않는다. 어떤 특정 수와 NULL 값을 더했을 때 NULL 값을 일종의 0 으로 취급하여 계산이 되지 않아 자연스레 기존의 수만 반환할 것이라 생각했다. 그런데 결론적으로 NULL 값을 반환하기 때문에 LEFT JOIN 을 활용해서 결합하여 amount 필드의 값이 NULL 인 경우에 대한 예외처리를 해줘야 한다.

참고할 만한 다른 풀이

WITH CTE AS (
    SELECT DISCOUNT_RATE, DURATION_TYPE	
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
)
SELECT H.HISTORY_ID, 
CONVERT((C.DAILY_FEE * H.DATE ) / 100 * (100 -
(CASE
    WHEN H.DATE >= 90 THEN (SELECT DISCOUNT_RATE FROM CTE WHERE DURATION_TYPE = '90일 이상')
    WHEN H.DATE >= 30 THEN (SELECT DISCOUNT_RATE FROM CTE WHERE DURATION_TYPE = '30일 이상')
    WHEN H.DATE >= 7  THEN (SELECT DISCOUNT_RATE FROM CTE WHERE DURATION_TYPE = '7일 이상')
    ELSE 0 
END)), UNSIGNED) FEE 
FROM 
CAR_RENTAL_COMPANY_CAR C JOIN (SELECT *, (DATEDIFF(END_DATE, START_DATE) + 1) DATE
                               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) H
ON C.CAR_ID = H.CAR_ID 
WHERE C.CAR_TYPE = '트럭'
ORDER BY 2 DESC, 1 DESC

→ CASE WHEN 문 안에 서브쿼리를 넣을 수 있다! 신기... 

SELECT history_id,
       round(daily_fee * (datediff(end_date, start_date) + 1) * (100 - ifnull(discount_rate, 0)) / 100, 0) fee
FROM (SELECT *,
             CASE
                 WHEN datediff(end_date, start_date) + 1 < 7 THEN NULL
                 WHEN datediff(end_date, start_date) + 1 < 30 THEN '7일 이상'
                 WHEN datediff(end_date, start_date) + 1 < 90 THEN '30일 이상'
                 ELSE '90일 이상'
                 END duration_type
      FROM car_rental_company_rental_history) a
         JOIN car_rental_company_car b
              ON a.car_id = b.car_id
         LEFT JOIN car_rental_company_discount_plan c
              ON c.car_type = b.car_type AND a.duration_type = c.duration_type
WHERE b.car_type = '트럭'
ORDER BY 2 DESC, 1 DESC
SELECT HISTORY_ID,
    ROUND(DAILY_FEE * 
        CASE
            WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 90 THEN (SELECT (1 - DISCOUNT_RATE * 0.01) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '90일 이상')
            WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 30 THEN (SELECT (1 - DISCOUNT_RATE * 0.01) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '30일 이상')
            WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 7 THEN (SELECT (1 - DISCOUNT_RATE * 0.01) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '7일 이상')
            ELSE 1
        END
          , 0) * (DATEDIFF(END_DATE,START_DATE) + 1) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY JOIN CAR_RENTAL_COMPANY_CAR USING(CAR_ID)
WHERE CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC;
WITH 기간계산 AS (
    SELECT HISTORY_ID 기록ID
    , DAILY_FEE 일일이용료
    , CAST(DATEDIFF(END_DATE,START_DATE)+1 AS UNSIGNED) 기간
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 기록DB
    INNER JOIN CAR_RENTAL_COMPANY_CAR 차DB
    ON 기록DB.CAR_ID = 차DB.CAR_ID AND CAR_TYPE = '트럭'
), 총계산 AS(
SELECT 기록ID, 일일이용료*기간*(1-IFNULL(할인율,0)*0.01) 총금액_유리
FROM 기간계산
LEFT JOIN (
  SELECT DISCOUNT_RATE 할인율, DURATION_TYPE 기간유형
  FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
  WHERE CAR_TYPE = '트럭'
) 할인정보
ON CASE
    WHEN 기간>=90 THEN 기간유형 = '90일 이상'
    WHEN 기간>=30 THEN 기간유형 = '30일 이상'
    WHEN 기간>=7 THEN 기간유형 = '7일 이상'
END
)
SELECT 기록ID HISTORY_ID, CAST(총금액_유리 AS UNSIGNED) FEE
FROM 총계산
ORDER BY 총금액_유리 DESC, HISTORY_ID DESC;
with rental as (
select h.CAR_ID, c.CAR_TYPE, c.DAILY_FEE, h.HISTORY_ID,
        datediff(h.END_DATE, h.START_DATE)+1 as renday
from CAR_RENTAL_COMPANY_RENTAL_HISTORY h
left join CAR_RENTAL_COMPANY_CAR c
    on h.CAR_ID=c.CAR_ID
    where c.CAR_TYPE in ('트럭')
),
discount as (
select p.DURATION_TYPE, p.DISCOUNT_RATE, c.CAR_TYPE, c.CAR_ID
from CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
left join CAR_RENTAL_COMPANY_CAR c
    on p.CAR_TYPE=c.CAR_TYPE
    where c.CAR_TYPE in ('트럭')
)

select r.HISTORY_ID,
round(r.DAILY_FEE*r.renday*(100-ifnull(d.DISCOUNT_RATE, 0))/100) FEE
from rental r
left join discount d
on r.CAR_ID=d.CAR_ID
    and case when renday >= 90 then '90일 이상'
             when renday >= 30 then '30일 이상'
             when renday >= 7 then '7일 이상' end = d.DURATION_TYPE
order by 2 desc, 1 desc
WITH TRUCK_RENT AS (
 SELECT *, REGEXP_REPLACE(duration_type, '[^0-9]+', '') AS t_numbers
 FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP
 WHERE car_type = '트럭'
)

SELECT  history_id AS HISTORY_ID, ROUND((100-IFNULL(aa.discount_rate,0)) / 100 * daily_fee * days, 0) AS FEE
FROM
(
 SELECT history_id, daily_fee, DATEDIFF(end_date, start_date) +1 AS days, max(discount_rate) as discount_rate
 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
 LEFT JOIN CAR_RENTAL_COMPANY_CAR C
 ON H.CAR_ID = C.CAR_ID
 LEFT JOIN TRUCK_RENT TR
 ON DATEDIFF(end_date, start_date) +1  >= TR.t_numbers
 WHERE C.CAR_TYPE = '트럭'
 GROUP BY history_id, daily_fee, days
) aa

ORDER BY FEE DESC, HISTORY_ID DESC
WITH CAR_AND_RENTAL_HISTORY AS(
    SELECT *, CASE 
                WHEN H.DUR_TIME >= 90 THEN '90일 이상'
                WHEN H.DUR_TIME >= 30 THEN '30일 이상'
                WHEN H.DUR_TIME >= 7  THEN '7일 이상'
                ELSE NULL 
              END AS 'DURATION_TYPE'
    FROM (SELECT *, DATEDIFF(END_DATE, START_DATE) + 1 AS 'DUR_TIME'
          FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) H JOIN CAR_RENTAL_COMPANY_CAR C
          USING (CAR_ID)
    WHERE C.CAR_TYPE = '트럭'
)

SELECT CR.HISTORY_ID,
       CASE IFNULL(CR.DURATION_TYPE, 0) 
          WHEN 0 THEN CR.DAILY_FEE * CR.DUR_TIME
          WHEN '7일 이상'  THEN TRUNCATE(CR.DAILY_FEE * (1 - (P.DISCOUNT_RATE/100)) * CR.DUR_TIME, 0)
          WHEN '30일 이상' THEN TRUNCATE(CR.DAILY_FEE * (1 - (P.DISCOUNT_RATE/100)) * CR.DUR_TIME, 0)
          ELSE TRUNCATE(CR.DAILY_FEE * (1 - (P.DISCOUNT_RATE/100)) * CR.DUR_TIME, 0)
       END AS 'FEE'
FROM CAR_AND_RENTAL_HISTORY CR LEFT OUTER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
     ON P.CAR_TYPE = '트럭' AND CR.DURATION_TYPE = P.DURATION_TYPE
ORDER BY FEE DESC, CR.HISTORY_ID DESC;
WITH TRUCK AS(
SELECT H.*, 
       C.DAILY_FEE,
       DATEDIFF(END_DATE, START_DATE) + 1 DURATION,
       CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
            ELSE '해당 없음' END  duration_type
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H JOIN CAR_RENTAL_COMPANY_CAR C
ON H.CAR_ID = C.CAR_ID AND C.CAR_TYPE = '트럭'    # CAR TABLE '트럭' 필터링
), TRUCK2 AS(
SELECT 
    HISTORY_ID,
    CASE WHEN T.DURATION_TYPE = '해당 없음' THEN DAILY_FEE * DURATION 
         WHEN T.DURATION_TYPE IN ('7일 이상','30일 이상','90일 이상') THEN DAILY_FEE * DURATION * (1-discount_rate/100)
         ELSE 0 END FEE
FROM TRUCK T LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON T.DURATION_TYPE = P.DURATION_TYPE AND P.CAR_TYPE = '트럭'  # PLAN TABLE '트럭' 필터링
ORDER BY 2 DESC, 1 DESC)

SELECT HISTORY_ID, ROUND(FEE)
FROM TRUCK2

# CAR_RENTAL_COMPANY_CAR : 자동차 정보
# CAR_RENTAL_COMPANY_RENTAL_HISTORY : 자동차 대여 기록 정보
# CAR_RENTAL_COMPANY_DISCOUNT_PLAN : 자동차 종류 - 대여 기간 종류 별 할인 정책 정보
# 7일 이상, 30일 이상, 90일 이상 -> 할인율 적용. 7일 미만은 할인 없음. 
# 자동차 종류가 '트럭' 대여 기록 별로 대여 금액(칼럼명: FEE)을 구하라.
# 대여 기록 ID와 대여 금액 리스트를 출력하라.

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

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

    • 2021년 가입한 전체 회원
      • user_info 테이블 YEAR(joined) = 2021
    • 2021년 가입자 중 상품을 구매한 회원 수
    • 2021년 가입자 중 상품을 구매한 회원 비율
      • 2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수
    • 년, 월 별로 출력
      • 상품을 구매한 회원의 비율은 소수점 두 번째 자리에서 반올림
      • 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬

  • 작성한 쿼리

WITH user_2021 AS (
  SELECT
    user_id
  FROM
    user_info
  WHERE
    YEAR(joined) = 2021
),
cnt_2021 AS (
  SELECT
    COUNT(*) AS cnt
  FROM
    user_2021
)
SELECT
  YEAR(sales_date) AS year
  , MONTH(sales_date) AS month
  , COUNT(DISTINCT os.user_id) AS purshased_users
  , ROUND(COUNT(DISTINCT os.user_id) / (SELECT cnt FROM cnt_2021), 1) AS puchased_ratio
FROM
  online_sale os
  JOIN user_2021 u
  USING(user_id)
GROUP BY
  YEAR(sales_date)
  , MONTH(sales_date)
ORDER BY
  YEAR(sales_date) ASC
  , MONTH(sales_date) ASC
;

참고할 만한 다른 풀이

  • WHERE IN
WITH USER_JOIN_2021 AS ( -- 2021 가입한 전체 회원 수
    SELECT COUNT(*) COUNT
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
),
USER_2021 AS ( -- 2021 가입한 회원 목록 
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
)
SELECT YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE) MONTH, 
COUNT(DISTINCT USER_ID) PURCHASED_USERS, -- 구매한 회원수 
ROUND((COUNT(DISTINCT USER_ID) / (SELECT * FROM USER_JOIN_2021)), 1) PUCHASED_RATIO 
FROM ONLINE_SALE
WHERE USER_ID IN (SELECT * FROM USER_2021) -- 2021 가입한 회원들 필터링 
GROUP BY 1, 2
ORDER BY 1, 2 -- 년, 월 오름차순 정렬
  • LEFT JOIN & WHERE절 조건
/*
left join을 사용하여 
joined와 sales_date를 모두 묶어주었고, 
그 가운데서 판매기록이 없는 것은 
is not null로 제거
*/
select year(sales_date) as year,month(sales_date) as month, 
count(distinct(a.user_id)) as puchased_users,
round(count(distinct(a.user_id))/
(select count(distinct(user_Id)) from user_info where joined like '2021%'),1) as puchased_ratio
from user_info a
left join online_sale b
on a.user_id = b.user_id 
where sales_date is not null and joined like '2021%'
group by 1, 2
order by 1, 2

SELECT 절이나 WHERE절의 서브쿼리는 드라이빙 테이블(ONLINE_SALE) 행의 수만큼 반복해서 계산되기에 성능이 좋지 못합니다.

Python

23. 콜라츠 추측

  • 작성한 코드
def solution(num):
	# num이 1인 경우 0 리턴
    if num == 1:
        return 0
    else:
    	# 500번 반복
        for i in range(500):
        	# 짝수면 2로 나눔
            if num % 2 == 0:
                num = num/2
            # 홀수면 3을 곱한 뒤 1을 더함
            else:
                num = num * 3 + 1
            # num이 1이 되면 시행 횟수 리턴
            if num == 1:
                return i + 1
    # 500번 반복 끝나고 for문 나오면 -1 리턴
    return -1
  1. return을 하나만 쓰고 싶었음
def solution(num):
    answer = 0
    if num == 1:
        answer = 0
    else:
        for i in range(500):
            if num % 2 == 0:
                num = num / 2
            else:
                num = num * 3 + 1
            if num == 1:
                answer = i + 1
                break
            else:
                answer = -1
    return answer

참고할 만한 다른 풀이

def solution(num):
    for i in range(1, 501): 
        if num == 1:
            return i - 1 
        num = num / 2 if num % 2 == 0 else num*3 + 1
    return -1
def solution(num):
    answer = 0
    
    if num == 1:
        return 0
    
    while True:
        num = num/2 if num % 2 == 0 else (num*3)+1
        answer += 1
        if num == 1:
            return answer 
        elif answer == 500:
            return -1
    
    return answer
  • num이 1로 주어질 경우 answer = 0으로 return 되어야 함으로 처음부터 처리해준다
  • 삼항 연사자를 사용하여 짝수/홀수 일 경우를 처리한다
  • 그런 다음 answer += 1을 하여 반복 횟수를 초기화한다 
  • num이 1이 되었을 때 answer 리턴 
  • num이 1이 아니고, answer가 500일 때 -1 리턴

SDL

파이썬의 반복문

profile
Suhyeon Lee
2 B R 0 2 B
이전 포스트

2024-10-26

다음 포스트

[MySQL] WHERE IN vs. JOIN / JOIN 종류

0개의 댓글