SQL공부 feat. 프로그래머스 (5) (Level 3)

JOIN에 대해 알아보자

RDB의 특징인 관계를 맺어주어 다른 테이블의 값을 가져올수있는 JOIN

JOIN의 종류

출처: https://hongong.hanbit.co.kr/sql-기본-문법-joininner-outer-cross-self-join/

위 출처를 대강 훑고 오면, 기초적인 JOIN은 INNER JOIN임을 알게 된다.

INNER JOIN

• 기초적인 JOIN의 디폴트 방식이다.
  • A, B 테이블의 교집합을 구한다.

LEFT OUTER JOIN

• 차집합을 구할때 쓰인다.
  • LEFT OUTER JOIN
  • LEFT OUTER JOIN을 활용해 나는 가지고 있지만 다른 테이블엔 없는 애를 찾고 싶다. 아래가 그 코드

  SELECT * 
  FROM A a
  	LEFT JOIN B b
  	ON a.KEY = b.KEY
  WHERE b.KEY IS NULL
  

RIGHT OUTER JOIN

• 반대방향의 차집합을 구할 수 있다.
  • RIGHT OUTER JOIN

  SELECT * 
  FROM A a 
  		RIGHT OUTER JOIN B b
  WHERE a.KEY IS NULL
  

JOIN 활용

오랜 기간 보호한 동물 (1)

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

-- 코드를 입력하세요
SELECT I.name,
       I.datetime
FROM   animal_ins AS I
       LEFT JOIN animal_outs AS O
              ON I.animal_id = O.animal_id
WHERE  O.animal_id IS NULL
ORDER  BY I.datetime
LIMIT  3;

이 문제는 위에서 훑어본 LEFT JOIN 을 활용하여 차집합을 구하는 문제였다. LEFT JOIN의 개념을 알면 간단한 문제였다.

있었는데요 없었습니다

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

-- 코드를 입력하세요
SELECT I.animal_id,
       I.NAME
FROM   animal_ins AS I
       JOIN animal_outs AS O
         ON I.animal_id = O.animal_id
WHERE  I.datetime > O.datetime
ORDER  BY I.datetime;

오랜 기간 보호한 동물 (2)

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

-- 코드를 입력하세요
SELECT O.animal_id,
       O.name
FROM   animal_ins AS I
       RIGHT OUTER JOIN animal_outs AS O
                     ON I.animal_id = O.animal_id
WHERE  O.animal_id IS NOT NULL
ORDER  BY Datediff(O.datetime, I.datetime) DESC
LIMIT  2;

이 문제는 위에서 훑어본 RIGHT OUTER JOIN 을 활용해 차집합을 구하는 문제였다. 사실 반대로 FROM 을 걸고, LEFT JOIN을 걸어도 동일한 결과가 나왔을것 같은데… 우선 나는 RIGHT OUTER JOIN을 활용해 풀었다.

둘의 차이가 궁금해서 구글링을 좀 해봤는데, 차이가 없고 위차만 다르다는 말이 있네..

뭔가 내가 모르는 차이점이 있을것 같다. 예를 들면 왼쪽 오른쪽의 기준을 어떤걸로 하느냐에 따라 같은 결과지만 다른 성능을 보인다던지? 이건 SQL 문 다 풀고나서 책을 통해 배워야겠다.

이외 Level3 문제

조건별로 분류하여 주문상태 출력하기

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

-- 코드를 입력하세요
SELECT ORDER_ID,    
       PRODUCT_ID,
       DATE_FORMAT(OUT_DATE, "%Y-%m-%d") AS OUT_DATE,
       (CASE
            WHEN OUT_DATE IS NULL THEN "출고미정"
            WHEN OUT_DATE <= "2022-05-01" THEN "출고완료"
            ELSE "출고대기"
       END) AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID;

카테고리 별 도서 판매량 집계하기

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

-- 코드를 입력하세요
SELECT category,
       Sum(S.sales) AS TOTAL_SALES
FROM   book AS B
       JOIN book_sales AS S
         ON B.book_id = S.book_id
WHERE  Year(S.sales_date) = 2022
       AND Month(S.sales_date) = 1
GROUP  BY category
ORDER  BY category

없어진 기록 찾기

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

-- 코드를 입력하세요
SELECT o.animal_id,
       o.NAME
FROM   animal_ins AS i
       RIGHT OUTER JOIN animal_outs AS o
                     ON i.animal_id = o.animal_id
WHERE  i.animal_id IS NULL

헤비 유저가 소유한 장소

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

-- 코드를 입력하세요
SELECT id,
       NAME,
       host_id
FROM   places
WHERE  host_id IN (SELECT host_id
                   FROM   places
                   GROUP  BY host_id
                   HAVING Count(host_id) > 1)
ORDER  BY id

와 이게 풀렸네??

엄청 난해하다고 생각했다가 혹시 { WHERE SELECT문 안에서 host_id가 있으면? } 으로 찾으면 가능할지 해봤는데 되더라.

이게 말로만 듣던 서브쿼리라고한다. 쿼리안의 쿼리… 신기했다.

그리고 개선점을 찾기 위해 구글링해보니 첫 포스팅에 이런 내용이 있었다.

출처: https://velog.io/@rgunny/SQLProgrammers-헤비-유저가-소유한-장소

Exists를 통해 true false 만 확인하고 얼리 불필요한 연산이 줄어든다는 설명인데 일단 그렇구나 하고 넘어갈 생각이다. 자세한 최적화는 책을 통해 배울거라서.. ㅎㅎ

-- 코드를 입력하세요
SELECT *
FROM   places P1
WHERE  EXISTS (SELECT 1
               FROM   places P2
               WHERE  P1.host_id = P2.host_id
               GROUP  BY host_id
               HAVING Count(host_id) > 1)
ORDER  BY id

이런게 있구나, In 보다 Exists가 빠를 가능성이 있구나 정도로 넘어갈 생각이다.

대여 기록이 존재하는 자동차 리스트 구하기

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

-- 코드를 입력하세요
SELECT DISTINCT CAR.car_id
FROM   car_rental_company_car AS CAR
       JOIN car_rental_company_rental_history AS HISTORY
         ON CAR.car_id = HISTORY.car_id
WHERE  CAR.car_type = "세단"
       AND Month(HISTORY.start_date) = 10
ORDER  BY CAR.car_id DESC;

즐겨찾기가 가장 많은 식당 정보 출력하기

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

-- 코드를 입력하세요
SELECT food_type,
       rest_id,
       rest_name,
       favorites
FROM   rest_info
WHERE  ( food_type, favorites ) IN (SELECT food_type,
                                           Max(favorites)
                                    FROM   rest_info
                                    GROUP  BY food_type)
ORDER  BY food_type DESC;

GROUP BY HAVING으로 풀랬는데 잘 안되어서 검색해보았더니 WHERE에 IN을 사용해서 했더라. 잘 이해안되는데 억지로 짜맞춰보면 WHERE절에 비교 대상인 애들이 ( food_type, favorites )라는 집합이고, 그 집합이 max로 맞춘 1열짜리 데이터에 있는지 확인하는 방식인것 같다.

조건에 맞는 사용자와 총 거래금액 조회하기

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

-- 코드를 입력하세요
SELECT user_id,
       nickname,
       Sum(price) AS TOTAL_SALES
FROM   used_goods_user AS USER
       JOIN used_goods_board AS board
         ON board.writer_id = USER.user_id
WHERE  status = "done"
GROUP  BY user_id
HAVING total_sales >= 700000
ORDER  BY total_sales;

분명 그룹 바이 하면서 많이 접했던 유형인데, Sum을 구하는데 꽤 헤매었다.

결국 처음부터 다시 짜서 해결했다. 이게 작은 조각들을 순차적으로 맞추는 느낌으로 가야 되는게 알고리즘 문제풀이랑 결국 방법은 똑같다는게 느껴진 문제다.

조건에 맞는 사용자 정보 조회하기

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

-- 코드를 입력하세요
SELECT user_id,
       nickname,
       Concat(USER.city, " ", USER.street_address1, " ", USER.street_address2)
       AS
       전체주소,
       Concat(LEFT(tlno, 3), "-", Mid(tlno, 4, 4), "-", RIGHT(tlno, 4))
       AS 전화번호
FROM   used_goods_user AS USER
       JOIN used_goods_board AS board
         ON USER.user_id = board.writer_id
GROUP  BY user_id
HAVING Count(user_id) >= 3
ORDER  BY user_id DESC;

답을 내긴 했는데, Concat 부분이 너무 지저분하다. 분명히 이것보다 더 좋은 방법이 있을것같다.

출처: https://jaimemin.tistory.com/2264

위 링크를 보면 Concat 부분은 동일하게 푼게 보인다. 근데 특이하게 GROUP BY Having Count(user_id) ≥ 3 이 아니라 FROM 에서 가져올 테이블을 서브쿼리로 만든 테이블을 가져왔다.

엄청 신기하다.

머릿속으로 (뇌피셜로) 생각해보면 조인을 먼저 한 뒤에 GROUP BY로 그룹화하는 내 방식이랑 서브쿼리로 그룹화된 테이블을 짠 다음에 조인을 하는게 데이터 핸들링 하는 양이 훨씬 적을 것같다. 아무래도 서브쿼리에서 그룹화를 통해 로우를 엄청 짤라냈기 때문에 훨씬 가벼울것 같다.

아래 코드가 위 링크에 있는 쿼리문

SELECT
    USER_ID,
    NICKNAME,
    CONCAT(CITY, " ", STREET_ADDRESS1, " ", STREET_ADDRESS2) AS 전체주소,
    CONCAT(LEFT(TLNO, 3) , "-" , MID(TLNO,4,4) , "-", RIGHT(TLNO,4)) AS 전화번호
FROM
(
    SELECT
        *
    FROM
        USED_GOODS_BOARD
    GROUP BY
        WRITER_ID
    HAVING
        COUNT(*) >= 3
) AS UGB
INNER JOIN
    USED_GOODS_USER AS UGU
ON
    UGB.WRITER_ID = UGU.USER_ID
ORDER BY
    USER_ID DESC

음… 근데 위 뇌피셜이 사실일수도 있지만 서브쿼리를 돌린다는거 자체가 셀렉 쿼리를 한번 더 친다고 생각할 수도 있는 부분이라 성능비교는 해봐야할것 같다.

---

출처: https://blog.naver.com/jwyoon25/221289425535

위 글을 보면 “실행계획”을 직접 보면서 어떤 쿼리가 좋은지 확인하는 방식이 눈에 띈다.

또한 결론부분에 “집계 후 조인”을 통해 조인시의 부담을 줄이는게 쿼리 튜닝의 기본이라는 말이 나온다.

어느 정도 유추는 성공했지만 위 내가 비교하고자하는 케이스와는 좀 다른 이야기이긴 하니까 아직 부족하다.

또 같은 이야긴데 일단 기본 쿼리짜는 법을 배운 다음에 책을 통해 쿼리 튜닝을 배워보자.

---

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

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

-- 코드를 입력하세요
SELECT Concat("/home/grep/src/", FILE.board_id, "/", FILE.file_id,
       FILE.file_name,
              FILE.file_ext) AS FILE_PATH
FROM   used_goods_file AS FILE
       JOIN used_goods_board AS board
         ON FILE.board_id = board.board_id
WHERE  board.views = (SELECT Max(views)
                      FROM   used_goods_board)
ORDER  BY FILE.file_id DESC

조회수 가장 높은 로우를 찾는데 꽤 많은 시간이 걸렸다. level3 까지 왔으나 이미 해본것 중 하나를 몰라서 헤매었네.. 역시 기초가 중요하다

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

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

-- 코드를 입력하세요
SELECT car_id,
       (CASE
            WHEN CAR_ID IN 
            (
                SELECT CAR_ID FROM car_rental_company_rental_history
                WHERE "2022-10-16" BETWEEN START_DATE AND END_DATE
            )
            THEN "대여중"
            ELSE "대여 가능"
        END) AS AVAILABILITY
FROM   car_rental_company_rental_history
GROUP  BY car_id
ORDER  BY car_id DESC;

진짜 어려웠다.

서브쿼리를 사용하면 되겠다는 건 알았지만 CAR_ID IN () THEN "대여중" 을 사용하면 된다는걸 결국 못알아냈다. IN을 사용도 해봤지만 CAR_ID를 통하지 않고 다른 걸 사용하고 정말 엄청난 삽질을 해댔는데…

우선은 다른 사람 풀이를 보고 해석하고 푼것으로 끝을 낸다. 다음에 꼭 다시 풀어봐야겠다.