SQL - 프로그래머스 문제 몇 가지 기록

프로그래머스 SQL 문제 다 풀었다. 다 푼 기념으로 푸는 데 오래 걸린 몇 가지 문제를 정리한다.

 

 

1. SELF JOIN + CASE문 사용법 

 

1) 문제 분석

주어진 테이블은 렌트 회사의 자동차 대여 기록 테이블이다. 특정한 날짜에 대여가 가능한 자동차를 찾아야 한다. 보통은 자동차 정보 테이블 따로, 대여 기록 테이블이 따로 있는데, 그럴 경우에는 그냥 JOIN을 사용하면 쉽다. 자동차 정보 테이블과 대여 기록 테이블을 자동차 ID를 기준으로 LEFT JOIN한 다음, WHERE 절에서 날짜 조건을 걸어주면 된다. 

 

하지만 이 경우에는 대여 기록 테이블만 던져주고 풀라고해서, SELF JOIN으로 접근해야 한다. 그리고 특정 조건 충족 여부에 따라 값이 달라지는 칼럼을 새로 만들 것을 요구하므로, CASE문으로 칼럼을 만들어준다.

 

2) 풀이 

SELECT 
    X.CAR_ID, 
    CASE WHEN Y.END_DATE IS NULL THEN '대여 가능'
         ELSE '대여중' END AS 'AVAILABILITY'
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY X
LEFT OUTER JOIN
    CAR_RENTAL_COMPANY_RENTAL_HISTORY Y
ON 
    X.CAR_ID = Y.CAR_ID 
    AND ('2022-10-16' BETWEEN Y.START_DATE AND Y.END_DATE)
GROUP BY
    X.CAR_ID
ORDER BY
    X.CAR_ID DESC

 

먼저 동일한 테이블을 X와 Y로 나누고, X테이블과 Y테이블을 자동차 고유 번호로 LEFT JOIN했다. 이때 Y테이블에는 2022년 10월 16일이 포함되는 행들만 가져오도록 했다. 그러면 2022년 10월 16일에 대여기록이 없는 자동차들은 Y테이블에서 가져온 대여 종료 날짜 칼럼에는 NULL값이 들어가게 될 것이다.

 

자동차 고유 번호로 GROUP BY하고 특별한 연산 함수를 쓰지 않으면, 각 자동차 고유 번호에서 먼저 조회된 행만 남는 것 같다. 어차피 Y테이블의 조건에 의해서 대여기록이 없는 자동차들은 어떤 행이든, 대여 종료 날짜 칼럼이 NULL값이다. 

 

Y테이블의 대여 종료 날짜가 NULL이 뜨는 자동차들에는 '대여 가능', 그 외에는 '대여 중'이라고 나타내는 칼럼을 CASE문으로 만들었다. 

---

2. VIEW를 만들어서 JOIN 

1) 문제 분석

두 개의 테이블을 사용해서 풀어야 한다. 두 테이블은 동일한 칼럼 구조를 가지고 있다. 출하 번호, 출하된 아이스크림의 맛, 그리고 주문량이다. 그런데 첫번째 테이블 FIRST_HALF는 아이스크림의 맛이 고유키인 반면, JULY 테이블에서는 아이스크림의 맛이 중복이 가능하다. 두 테이블을 합해서 아이스크림 맛별로 총 주문량을 구해야 한다.

2) 풀이 

SELECT
    FIRST_HALF.FLAVOR
FROM
    FIRST_HALF LEFT OUTER JOIN 
(SELECT 
    FLAVOR, 
    SUM(TOTAL_ORDER) AS TOTAL_ORDER
 FROM
    JULY
 GROUP BY
    FLAVOR) X
ON
    FIRST_HALF.FLAVOR = X.FLAVOR
ORDER BY
    X.TOTAL_ORDER + FIRST_HALF.TOTAL_ORDER DESC
LIMIT 3

 

우선 JULY 테이블을 FIRST_HALF 테이블처럼, 아이스크림 맛을 고유키인 것처럼 만들어줘야 한다. JULY 테이블에 GROUP BY를 걸어서 아이스크림 별로 주문량을 합해주었다. 그러면 FIRST_HALF 테이블처럼 아이스크림 맛 하나당 하나의 행만 존재하게 된다.

 

GROUP BY된 SELECT문을 VIEW로 사용해서 FIRST_HALF 테이블과 JOIN한다. 그리고 두 테이블의 주문량을 합한다.

---

3. 두 테이블을 세로 방향으로 합치기: UNION + NULL AS 칼럼

 

1) 문제 분석

두 개의 테이블을 세로 방향으로 쌓아야 한다. 온라인 매출과 오프라인 매출을 통합하는 것이다. 그런데 문제는 오프라인 매출에는 USER_ID라는 칼럼이 없다. 오프라인에서 매출된 것이라 고객 정보 기록이 없는가보다.

 

두 테이블을 세로로 쌓는 것은 UNION을 쓰면 되는데, 두 테이블의 칼럼 구조가 다르면 어떻게 할까. NULL값을 갖는 칼럼을 인위적으로 만드는 방법을 사용했다.

2) 풀이 

NULL AS '칼럼명'을 쓰면 모든 행에서 NULL값을 갖는 칼럼이 만들어진다. 이렇게 인위적으로 칼럼구조를 동일하게 만들어준 후, UNION으로 합하였다.

(SELECT
    DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, 
    PRODUCT_ID, 
    USER_ID,
    SALES_AMOUNT
FROM
    ONLINE_SALE
WHERE
    SALES_DATE LIKE '2022-03-%')
UNION
(SELECT
    DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, 
    PRODUCT_ID, 
    NULL AS USER_ID,
    SALES_AMOUNT
FROM
    OFFLINE_SALE
WHERE
    SALES_DATE LIKE '2022-03-%')
ORDER BY
    SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC

---

4. WITH RECURSIVE 

1) 문제 분석

이 문제는 언뜻 보면 쉬워보인다. 시간대별로 입양 횟수를 구하는 문제니까, HOUR() 함수를 사용해서 입양일시에서 시간대만 남겨주고, 시간대별로 GROUP BY해서 고유칼럼을 COUNT히면 된다.

 

그런데 문제는 입양이 발생하지 않은 시간대는 아예 행이 조회가 안 된다. 문제에서 요구하는 것은, 입양이 발생하지 않은 시간대도 조회는 하되, 입양 횟수가 0건으로 조회되도록 하는 것이다.

  

2) 풀이 

WITH절로 빈 테이블을 하나 만들어준다. 이 빈 테이블 TEMP는 모든 시간대를 표시하되, 각 시간대별 입양 건수가 모두 0이다. 

 

이를 만족하는 TEMP는 어떻게 만들까. RECURSIVE절을 사용해서 만든다. 

RECURSIVE절은 아래와 같은 구조를 갖는다.

WITH RECURSIVE TEMP
AS ( 
    -- 처음에 한번만 실행되는 구문
    SELECT 1 AS n
    UNION ALL
    -- 아래 조건에 맞는 값을 자기 자신으로부터 조회하며 반복한다.
    SELECT n + 1 AS n FROM TEMP
    -- 반복문이 종료되는 조건
    WHERE n < 10 
)

 

WITH RECURSIVE 절을 활용해서 TEMP 테이블을 만들어주었다. 0시부터~23시까지 각 시간대별로 입양건수가 0이다.

그리고 원래 테이블과 TEMP 테이블을 RIGHT JOIN(즉 TEMP 테이블이 기준이다)한다. 마지막으로 JOIN된 테이블에서 시간대별로 고유키를 카운트한다.

WITH RECURSIVE TEMP AS 
    ((SELECT 0 AS HOUR)
    UNION ALL
    (SELECT HOUR + 1 FROM TEMP WHERE HOUR < 23))

SELECT 
    HOUR,
    COUNT(ANIMAL_ID)
FROM
    ANIMAL_OUTS RIGHT OUTER JOIN TEMP
ON
    HOUR(ANIMAL_OUTS.DATETIME) = TEMP.HOUR
GROUP BY
    HOUR