구름 아카이브

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

습득한 점:

테이블 끼리의 조인을 할 때도,

두 테이블에 단순히 같은 값이 있다고 자주쓰던 INNER JOIN으로 그냥 매칭 시킬 것이 아니라

묶어야 할 테이블의 데이터 형식에 맞추어 조인 할 수 있도록

CASE 문을 통해, 특정 구간의 숫자 레인지를 값으로 바꾸는 데이터 조작 방식과,

이를 NULL 값이 드러 날 수 있도록 LEFT JOIN 으로 묶는  쿼리 작성 방식을 알게되었음.

주어진 테이블의 전체 형태를 보고 데이터 조작과, 테이블 조인을 시도해야 겠다는 생각이 들었음.

테이블을 연계구조를 한눈에 파악하기가 아직은 힘들었는데, 한눈에 파악 안되면 직접 그림을 그려보는 것도 하나의 방법이 될 것 같음.

문제:

- 자동차 종류: 트럭 의 대여기록 별 대여금액, 대여기록 ID + 대여금액 리스트 출력
- 컬럼명은 FEE: 대여기록 별 대여 금액
- 대여금액을 기준으로 내림차순 desc, 기록 id 내림차순

문제파악:

날짜의 특정 레인지를 특정 값으로 바꾸는 것이 포인트.

- 히스토리 테이블의 기간범위 값들을 특정 4구간으로 변환 >>> why? 기간별 할인율이 다름
- DURATION_TYPE이 원래 존재하는 디스카운트 테이블과 조인 3개의 구간 존재
- none값은 할인을 받지 않는 값으로써 활용해야함. (0~6일 구간)
- 조인했을때, 조인된 테이블에 값이 null값이라면 0으로 치환하기
- 100-ifnull(discount_rate,0)/100 > 할인율이 null값이면 0을 반환, null값이 아니면 할인율 반환
- daily_fee*period*discount_rate FEE 컬럼 형성
- 0으로 치환된 것들은 자동으로 1로 변환 (100-0)/100 =값에 변화가 없음.

# 한눈에 파악 안되면, 테이블 그리기

# 문제 제대로 읽기

풀이:

WITH def AS (
    SELECT CAR.DAILY_FEE
         , CAR.car_type
         , HISTORY.history_id
         , DATEDIFF(END_DATE,START_DATE) + 1 AS lending_p
         , 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 'NONE'
           END AS duration_type
    
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
      INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID AND CAR.CAR_TYPE ='트럭'
    
               )


SELECT def.HISTORY_ID
     , ROUND(def.daily_fee * def.lending_p * 
            (1- (IFNULL(plan.DISCOUNT_RATE,0)/100))) AS FEE
     

FROM def
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN ON def.DURATION_TYPE = PLAN.DURATION_TYPE
AND PLAN.CAR_TYPE = '트럭'
ORDER BY FEE DESC, def.HISTORY_ID DESC

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

습득한 점:

1. 문제를 읽고, 내가 이해한 플로우가 맞는지 예시로 나온 부분도 제대로 파악하기!!!

이 문제의 경우 PUCHASED_USERS 를 구하는 과정에서 중복값을 고려하지 않아서 한참을 헤멧다.

읽기만 하고 문제가 원하는 바를 제대로 이해하지 않음.

PUCHASED_USERS 컬럼의 경우 중복을 제거한 숫자를 카운팅하는 점을 주의해야함.

(예시까지 이해하지 않으면, 계속 틀리게 될 것 같다)

2. JOIN의 경우 INNER JOIN으로 자동 인식된다는 점

3. 테이블 조인할 때, AND조건으로 조인될 조건을 추가할 수 있음.

  INNER JOIN USER_INFO U ON S.USER_ID = U.USER_ID AND YEAR(JOINED) = 2021

ONLINE_SALE 테이블의 JOINED 의 연도가 2021인 경우에만 테이블 조인 할 것.

- Reference

https://extbrain.tistory.com/60

***추가 - 날짜데이터에서 일부 데이터만 추출하기

• YEAR : 연도 추출
• MONTH : 월 추출
• DAY : 일 추출 (DAYOFMONTH와 같은 함수)
• DATE : 년 / 월 / 일 추출  (DATETIME 형식 -> DATE 형식)
• HOUR : 시 추출
• MINUTE : 분 추출
• SECOND : 초 추출

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서

2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율

             (=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성

상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림

전체 결과는 년을 기준으로 오름차순 정렬

년이 같다면 월을 기준으로 오름차순 정렬

문제 파악

# USER_INFO (회원정보)
# - USER_ID, GENDER, AGE, JOINED

# ONLINE_SALE (온라인 상품 판매정보)
# - ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE

# foreign key - USER_ID
# 특이사항 - 판매일이 DATETIME 형식, 성별이 1,2,null 로 구분

형식 :
# YEAR   MONTH   PUCHASED_USERS   PUCHASED_RATIO

풀이

SELECT YEAR, 
       MONTH,
       COUNT(*) AS PUCHASED_USERS, 
				-- COUNT(*) = 상품구매 회원 수                
       ROUND((COUNT(*)/(SELECT COUNT(*) 
                        FROM USER_INFO WHERE YEAR(JOINED) = 2021)),1) AS PUCHASED_RATIO  
     
     
     
FROM (
        SELECT DISTINCT YEAR(S.SALES_DATE) AS YEAR
                      , MONTH(S.SALES_DATE) AS MONTH
                      , U.USER_ID
        FROM ONLINE_SALE S
        INNER JOIN USER_INFO U ON S.USER_ID = U.USER_ID AND YEAR(JOINED) = 2021  -- JOINED 의 연도가 2021인 경우에만 함께 조인
    ) sub

GROUP BY YEAR, MONTH   
ORDER BY YEAR, MONTH