[프로그래머스] 4주차 MySQL 스터디 1

1. SELECT - [조건에 맞는 도서 리스트 출력하기] _ LEVEL 1

BOOK 테이블에서 2021년에 출판된 '인문' 카테고리에 속하는 도서 리스트를 찾아서

도서 ID(BOOK_ID), 출판일 (PUBLISHED_DATE)을 출력하는 SQL문을 작성해주세요.

결과는 출판일을 기준으로 오름차순 정렬해주세요.

※ PUBLISHED_DATE의 데이트 포맷이 예시와 동일해야 정답처리 됩니다.

 

[ 정답 쿼리 ]

SELECT book_id
    , DATE_FORMAT(published_date,'%Y-%m-%d') published_date
FROM book
WHERE YEAR(published_date) = 2021 AND category = '인문'
ORDER BY published_date;

---

2. SUM, MAX, MIN - [중복 제거하기] _ LEVEL 2

동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요.

이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.

 

[ 정답 쿼리 ]

SELECT COUNT(DISTINCT name) AS count
FROM animal_ins;

 

[ 풀이 ]

• COUNT(변수명) 은 자동으로 NULL값은 제외하고 카운트
  • 참고로 COUNT(*), COUNT(1)은 NULL값도 모두 카운트
• 중복값을 하나로 치라고 했으므로 DISTINCT 키워드 사용

---

🌟🌟🌟 3. GROUP BY - [즐겨찾기가 가장 많은 식당 정보 출력하기] _ LEVEL 3

다음은 식당의 정보를 담은 REST_INFO 테이블입니다. 

REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

 

[ 정답 쿼리 ]

-- 틀린 풀이) 잘못된 HAVING절 사용 
SELECT food_type, rest_id, rest_name, favorites
FROM rest_info
GROUP BY food_type
HAVING favorites = MAX(favorites)
ORDER BY food_type DESC;


-- 풀이 1. 상관 서브쿼리 사용
SELECT food_type, rest_id, rest_name, favorites
FROM rest_info P
WHERE favorites =
(
    SELECT MAX(favorites)
    FROM rest_info
    WHERE food_type = P.food_type # 상관 서브쿼리
)
ORDER BY food_type DESC;

-- 풀이 2. GROUP BY 이용
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;

-- 풀이 3. WINDOW함수 이용
SELECT food_type, rest_id, rest_name, favorites
FROM 
(
    SELECT *
        , DENSE_RANK() OVER (PARTITION BY food_type ORDER BY favorites DESC) AS ranking
    FROM rest_info
) SUB
WHERE ranking = 1
ORDER BY food_type DESC;

---

[ 풀이 ]

틀린 풀이)

• HAVING 절은 GROUP BY 이후 실행되기 때문에, 집계된 조건을 기반으로 필터링하기 위한 조건이 필요
• MAX(favorites) = favorites 가 오류나는 이유는 비교의 레벨이 맞지 않기 때문
• 집계함수와 특정 행의 값을 비교할 수는 없다!
• HAVING절은 보통 아래와 같은 형태로 사용한다

HAVING MAX(favorites) = 200;

---

풀이 1) 상관 서브쿼리 사용

• 외부 쿼리에서 P.food_type이 하나씩 들어와 동적계산이 이루어짐

1. 각각의 음식종류 별로 최대 즐겨찾기를 계산하고

2. 외부 쿼리의 WHERE절에서 또 각각의 음식 종류 별로 최대 favorites값과 일치하는 row를 찾아낸다.

---

풀이 2) GROUP BY 이용

• WHERE 조건에 2개 이상의 field를 넣을 수 있음!
• 의미상 음식 종류 별 최대 즐겨찾기를 매칭해야 하므로, WHERE 절에서 favorites만 매칭하는 게 아니라 (food_type, favorites) 쌍을 매칭해야 함!

WHERE (food_type, favorites) IN ~

---

풀이 3) WINDOW 함수 이용

• 윈도우 함수를 사용해 각 음식 종류 별(PARTITION BY 키워드 사용)로 즐겨찾기 수가 가장 많은 식당에 순위를 매길 수 있음
• 외부쿼리에서 이 순위로 필터링하면, 각 음식 종류별로 가장 즐겨찾기 수가 많은 식당만을 선택할 수 있음

---

참고) 풀이 1,2와 비교할 쿼리문(오답)

SELECT food_type, rest_id, rest_name, favorites
FROM rest_info P
WHERE favorites IN
(
    SELECT MAX(favorites)
    FROM rest_info
    GROUP BY food_type
)
ORDER BY food_type DESC;

• 풀이 1처럼 상관 서브쿼리도 아니고, 풀이 2처럼 WHERE 문에서 field의 쌍을 비교하는 것도 아님
• 서브쿼리 내에서 음식 종류별 최대 즐겨찾기값들을 뽑아내고 → 외부 쿼리에서 그 즐겨찾기 값이 매칭되는 row를 출력
• 그 결과 일식의 최대 즐겨찾기 값이었던 230과 매칭되는 한식 가게가 하나 더 출력되어버림
• 따라서 상관 서브쿼리나 추가 매칭 field 없이, WHERE 절에서 favorites 필드 하나로만 값을 비교하는 것은 잘못된 방식!

---

---

4. IS NULL - [ROOT 아이템 구하기] _ LEVEL 2

다음은 해당 게임에서 사용되는 아이템 정보를 담은 ITEM_INFO 테이블과 아이템 관계를 나타낸 ITEM_TREE 테이블입니다.

각 아이템들은 오직 하나의 PARENT 아이템 ID를 가지며,

ROOT 아이템의 PARENT 아이템 ID는 NULL 입니다.

ROOT 아이템을 찾아 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME)을 출력하는 SQL문을 작성해 주세요.

이때, 결과는 아이템 ID를 기준으로 오름차순 정렬해 주세요.

 

[ 정답 쿼리 ]

SELECT I.item_id, item_name
FROM item_info I
JOIN item_tree T ON I.item_id = T.item_id
WHERE parent_item_id IS NULL
ORDER BY 1;

 

[ 풀이 ]

• ROOT 아이템은 parent_item_id가 NULL이라고 했으므로, WHERE절에서 IS NULL 키워드로 필터링

---

5. JOIN - [없어진 기록 찾기] _ LEVEL 3

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. 

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데,

보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

 

[ 정답 쿼리 ]

-- 풀이 1. LEFT JOIN 이용
SELECT O.animal_id, O.name
FROM animal_outs O
LEFT JOIN animal_ins I ON O.animal_id = I.animal_id
WHERE I.animal_type IS NULL
ORDER BY 1;

-- 풀이 2. NOT IN 서브쿼리 사용
SELECT animal_id, name
FROM animal_outs
WHERE animal_id  
NOT IN 
(
	SELECT animal_id
	FROM animal_ins
)
ORDER BY 1;

 

[ 풀이 ]

• 입양을 간 기록은 있는데, 보호소에 들어온 기록은 없다 = animal_outs에는 있는데 animal_ins에는 없는 동물 조회
• 두 테이블의 차집합을 구하면 되는데, MySQL에는 MINUS 키워드가 없으므로 LEFT JOIN으로 구현
• 또는 JOIN없이 animal_ins 로부터 animal_id 리스트를 서브쿼리로 구성한 뒤, NOT IN 키워드를 사용

---

6. String, Date - [조건에 맞는 사용자 정보 조회하기] _ LEVEL 3

다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과

중고 거래 사용자의 정보를 담은 USED_GOODS_USER 테이블입니다. 

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의

사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요.

이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고,

전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요.

결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT 
    user_id
    , nickname
    , CONCAT(city, ' ', street_address1, ' ', street_address2) AS `전체주소`
    , CONCAT(LEFT(tlno,3), '-', SUBSTRING(tlno,4,4),'-',RIGHT(tlno,4))AS `전화번호`
FROM used_goods_user
WHERE user_id
IN
(
    SELECT writer_id
    FROM used_goods_board
    GROUP BY writer_id
    HAVING COUNT(*) >= 3
)
ORDER BY user_id DESC;

 

[ 풀이 ]

• 복잡해 보이지만 핵심은 서브쿼리를 이용해 게시물을 3건 이상 등록한 writer_id 리스트를 추출하고, SELECT 문에서 CONCAT 함수를 이용해 원하는 출력 형식을 맞춰주는 것

 

-- CONCAT 함수와 문자열 추출함수 이용
CONCAT(LEFT(tlno,3), '-', SUBSTRING(tlno,4,4), '-', RIGHT(tlno,4)) AS '전화번호'

-- INSERT 함수 이용
INSERT(INSERT(TLNO, 8, 0, '-'), 4, 0, '-') AS '전화번호'

-- REGEXP_REPLACE 함수 이용
REGEXP_REPLACE(TLNO, '(.{3})(.{4})(.{4})', '$1-$2-$3') AS '전화번호'

• 전화번호 출력 형식을 맞추는 문제에서 사용할 수 있는 함수는 크게 3가지
  • CONCAT, LEFT, RIGHT, SUBSTRING : 문자열을 다루는 데 있어 가장 일반적인 함수들
  • INSERT : (대상 문자열, 삽입할 위치, 삽입시 제거될 문자 개수, 삽입될 문자열) 문자열 사이사이에 삽입(또는 대체)하고픈 내용이 있을 때 유용
  • REGEXP_REPLACE : (대상 문자열, 찾고자 하는 패턴, 교체할 문자열) 셋 중 가장 유연한 함수. 전화번호의 자릿수가 고정적이지 않을 때나, 조금 더 복잡한 형식 변경이 필요할 때 사용 가능.

참고

https://k-wien1589.tistory.com/182