프로그래머스 sql 문제 풀이하면서 배운 점(2)

문제1

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

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

SELECT 
    c.CAR_ID, 
    c.CAR_TYPE,
    ROUND(c.DAILY_FEE * (1 - dp.DISCOUNT_RATE / 100.0) * 30) AS FEE
FROM 
    CAR_RENTAL_COMPANY_CAR c
JOIN 
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN dp ON c.CAR_TYPE = dp.CAR_TYPE
WHERE 
    c.CAR_TYPE IN ('세단', 'SUV')
    AND dp.DURATION_TYPE = '30일 이상'
    AND NOT EXISTS (
        SELECT 1
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY rh
        WHERE rh.CAR_ID = c.CAR_ID
          AND rh.START_DATE <= '2022-11-30'
          AND rh.END_DATE >= '2022-11-01'
    )
    AND ROUND(c.DAILY_FEE * (1 - dp.DISCOUNT_RATE / 100.0) * 30) >= 500000
    AND ROUND(c.DAILY_FEE * (1 - dp.DISCOUNT_RATE / 100.0) * 30) < 2000000
ORDER BY 
    FEE DESC, 
    c.CAR_TYPE, 
    c.CAR_ID DESC;

 

사고 과정

 

1. from의 주체는 자동차임으로 자동차로 둔다. 

2. SELECT의 필요한 속성을 가진 테이블을 JOIN 해준다. ( ON 절로 걸를 수 있는 조건은 ON으로 거른다)

3. SELECT에는 필요없지만 조건을 검색하는 속성을 가진 테이블이 있는지 확인

3. WHERE 절, 서브쿼리로 거를지 JOIN을 할지 결정 

4. ORDER BY 작성

---

문제2

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

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

 

 

사고 과정

위 문제1과 다르게 조건문에서 각 속성별로 조건을 거는 것이 아닌 
테이블1의 속성과 테이블2의 속성을 비교해야 한다. 이럴 경우 어떻게 해야 할까?

일단 문제1번과 같은 사고과정을 거쳐 쿼리문을 작성했다. 

처음 코드

SELECT AI.ANIMAL_ID,	AI.NAME
FROM ANIMAL_INS AI
JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID AND AI.DATETIME > AO.DATETIME
ORDER BY AI.DATETIME ASC

 

결과는 무난하게 패스  ON 절에 기본키와 외래키를 일치시키는 것 말고 
AI.DATETIME > AO.DATETIME 와 같은 조건문은 WHERE 절로 빼는 게 나으려나? 

 

ON절과 WHERE절의 성능차이가 궁금해 알아봤다.

내부 조인일 경우에는 ON절과 Where절의 성능 차이는 없다.

외부 조인일 경우
규칙 LEFT OUTER JOIN 시 ON 절에서는 우측(널값으로 채워지는 쪽)의 추가 제약조건을 넣고 
좌측의 추가 제약조건은 WHERE절에 넣어야 한다.

on 명령어

SELECT t1.col1, t1.col2, t2.col1, t2.col2
FROM   table1 t1
LEFT OUTER JOIN table2 t2
ON t1.col1 = t2.col1
AND t2.col2 = '일';

• on 명령어로 join 전에 조건을 걸어줌. 
• 온전한 t1, 필터링된 t2가 LEFT JOIN 되는 거임

where 명령어

select t1.col1, t1.col2, t2.col1, t2.col2
from  table1 t1
LEFT OUTER JOIN table2 t2
ON t1.col1 = t2.col1
where t2.col2 = '일';

예시 코드, 표 출처 :https://velog.io/@crosstar1228/SQL-join%ED%95%A0%EB%95%8C-on-%EA%B3%BC-where%EC%9D%98-%EC%B0%A8%EC%9D%B4

 

그렇다면 다시 궁금증

인터넷에서 INNER JOIN에서는 ON 절과 WHERE절의 성능차이는 거의 없어 목적에 따라 ON절과 WHERE절의 사용을 나눈다고 한다. 

조인 조건 = ON

검색 조건 = WHERE

 

 

내가 생각하기엔 

ON절에서 거른다면, 필터링된 T2랑 온전한 T1이랑 조인하고

WHERE절에서 거른다면, 온전한 T1이랑 T2랑 조인하고 필터링 하는 것이 아닌가 

그렇다면 조인하는 과정 때문에 ON절을 쓰는 것이 메모리를 덜 쓰는 것이 아닐까? 

 

간단한 검색으로 나오진 않는다. 지금은 SQL 문제 풀이가 시급하므로 나중에 알아보겠다!!

---

문제3

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

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

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 YEAR(JOINED) = 2021) , 1) AS PUSHASED_RATIO
FROM USER_INFO AS a INNER JOIN ONLINE_SALE AS b USING(USER_ID)
WHERE YEAR(JOINED) = 2021
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY 1 ASC, 2 ASC

 

배운 점 

• YEAR(), MONTH(): 이것 뿐만 아니라 다양한 날짜함수가 있다는 것을 앎

---

문제4

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

PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

SELECT LEFT(PRODUCT_CODE, 2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY LEFT(PRODUCT_CODE, 2)

 

배운점

• LEFT 함수