구름 아카이브

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

습득한 점:

실제 코테라고 쫄지마, Lv 1 수준이야

문제:

이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부름.

헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 쿼리 작성

문제파악:

HOST_ID의 갯수가 2개 이상인 HOST_ID만 따로 뽑고, 원본데이터의 WHERE 조건절로 활용

풀이

-- 원하는 조건의 HOST_ID만 뽑은 뒤, 이를 원본데이터에 조건으로 넣기

# SELECT *
# FROM PLACES
# WHERE HOST_ID IN (
#              SELECT HOST_ID
#              FROM PLACES
#              GROUP BY HOST_ID
#              HAVING COUNT(HOST_ID)>=2
#             )
# ORDER BY ID            


-- 반대 풀이

SELECT *
FROM PLACES
WHERE HOST_ID NOT IN (
             SELECT HOST_ID
             FROM PLACES
             GROUP BY HOST_ID
             HAVING COUNT(HOST_ID)=1
            )
ORDER BY ID

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

습득한 점: 

* 컬럼의 값을 NULL 로 표시해야 할 경우에는, 문자열 "NULL" 나타내는 것이 아닌 NULL 로만 기재

* UNION , UNION ALL?

     - UNION은 두 개의 테이블을 하나로 만드는 연산.

        두 개 테이블의 컬럼 수, 컬럼 데이터 형식이 모두 일치해야 함.

       UNION 연산은 두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거한다. 정렬을 발생

     - UNION ALL은 중복을 제거하거나 정렬을 유발하지 않는다. 중복제거 없이 전부 보여줌

        두 개 테이블의 컬럼 수가 같아야 하는 것은 동일

- Reference

https://prinha.tistory.com/entry/MySQL-COALESCE-%ED%95%A8%EC%88%98

https://whitepro.tistory.com/580

https://gooreumwave.tistory.com/25

문제:

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서

2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력

OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시

판매일을 기준으로 오름차순 정렬,

판매일이 같다면 상품 ID를 기준으로 오름차순,

상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬

문제 파악:

두개 테이블을 합쳐서, 날짜에 맞는 조건들만 건져내면 된다.

날짜만 포맷을 바꿔서 바꿔주면 되었음.

아래와 같이 NULL 값 표기가 필요할 때는,

문자열 "NULL" 나타내는 것이 아닌 NULL 로만 기재하는 것만 유의하면 쉽게 풀 수 있는 문제

* 아래처럼 With 구문을 쓰지 않고 테이블 2개를 한번에 묶고, 정렬해줘도 풀 수 있을 것 같다.

(SELECT *

FROM TABLE_1

UNION

SELECT *

FROM TABLE_2)

ORDER BY ~~~

풀이:

WITH COUNTER AS (

        SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE
             , PRODUCT_ID
             , USER_ID
             , SALES_AMOUNT
        FROM ONLINE_SALE AS ONSALE

        UNION ALL

        SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE
             , PRODUCT_ID
             , NULL AS USER_ID
             , SALES_AMOUNT
        FROM OFFLINE_SALE AS OFFSALE
                )

                
SELECT *
FROM COUNTER
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE,PRODUCT_ID,USER_ID

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

With counter AS(
    
        SELECT CAR.DAILY_FEE
             , CAR.CAR_ID
             , CAR.CAR_TYPE
             , START_DATE
             , END_DATE
    
        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 IN ('세단', 'SUV')
        WHERE (START_DATE > '2022-11-30' or END_DATE < '2022-11-01') 
                ) 

SELECT DISTINCT CAR_ID
     , counter.CAR_TYPE
     , ROUND((100-discount_rate)/100 * DAILY_FEE* 30) AS FEE     
      
FROM counter
     INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN ON counter.CAR_TYPE = PLAN.CAR_TYPE AND DURATION_TYPE = '30일 이상'
     
     WHERE (100 - discount_rate)/100 * daily_fee * 30 >= 500000
     AND (100 - discount_rate)/100 * daily_fee * 30 < 2000000
     
     
ORDER BY 3 DESC, 2 ASC ,1 DESC

원래 CAR_TYPE의 SUV는 나오지 않아야 한다.

세단만이 나와야 하는데, 
조건에 해당되어서 일단은 suv값이 같이 나오고 있다.

일단 날짜 조건이 매우 까다롭다.

다들 까다로운 문제라고는 하지만 10시간 매달려도 못풀다니...
쿼리 하나하나 다시 디버깅 해봐야겠다.

어디가 문제일까?

링크:
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

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

1. 조인조건을 찾아보니 다들 Board_id로 조인했고,

나만 Writer_id 기준으로 묶었더라.

2. 쿼리 짜는 것은 쉬웠지만, 데이터 포맷을 바꾸는 것 같은 세부적인 작업에 아직 익숙하지 않아서 연습이 필요하다.

3. CREATED_DATE(댓글작성일) 기준으로 오름차순 정렬,

    댓글작성일이 같으면 TITLE(게시글 제목) 기준으로 오름차순 정렬이란 말에 어떻게 풀어야 할 지 살짝 멈칫.

    결과적으로 경우의 수는 같거나 다르냐 2가지 일 뿐인데, 댓글작성일이 같을 경우를 처리하는 쿼리를 어떻게 만들지 생각하고 있었다;;;

4. 문제 제대로 읽자. 쉬운 문제야

SELECT TITLE
     , B.BOARD_ID
     , REPLY_ID
     , R.WRITER_ID
     , R.CONTENTS
     , DATE_FORMAT(R.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE 
FROM USED_GOODS_BOARD AS B
    INNER JOIN USED_GOODS_REPLY AS R ON B.BOARD_ID = R.BOARD_ID

WHERE B.CREATED_DATE BETWEEN '2022-10-01' AND '2022-10-31'
ORDER BY R.CREATED_DATE, TITLE

문제 설명

-Reference

https://kig6022.tistory.com/7