[프로그래머스] 3주차 MySQL 스터디 2

1. SELECT - [조건에 부합하는 중고거래 댓글 고회하기] _ LEVEL 1

USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된

게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요.

결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고,

댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.

※ CREATED_DATE의 포맷이 예시의 포맷과 일치해야 정답처리 됩니다.

 

[ 정답 쿼리 ]

SELECT B.title, B.board_id, R.reply_id, R.writer_id, R.contents
    , DATE_FORMAT(R.created_date,'%Y-%m-%d') AS created_date
FROM used_goods_board B
JOIN used_goods_reply R ON B.board_id = R.board_id
WHERE DATE_FORMAT(B.created_date,'%Y-%m') = '2022-10'
ORDER BY R.created_date, B.title;

 

[ 풀이 ]

• 포인트는 두 테이블의 이름이 동일한 컬럼을 헷갈리지 않게 구분하는 것
  • 예를 들어 WHERE절의 작성일은 게시글 작성일이기 때문에 used_goods_board 로부터, SELECT절의 작성일은 댓글 작성일이기 때문에 used_goods_reply 로부터 가져와야 함

---

2. SUM, MAX, MIN - [동물 수 구하기] _ LEVEL 2

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. 동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 SQL 문을 작성해주세요.

 

[ 정답 쿼리 ]

SELECT COUNT(DISTINCT animal_id) as counts
FROM animal_ins;

---

3. GROUP BY - [성분으로 구분한 아이스크림 총 주문량] _ LEVEL 2

다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 아이스크림 성분에 대한 정보를 담은 ICECREAM_INFO 테이블입니다. 상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성해주세요. 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정해주세요.

 

[ 정답 쿼리 ]

SELECT ingredient_type
    , SUM(total_order) AS total_order
FROM first_half F
JOIN icecream_info I ON F.flavor = I.flavor
GROUP BY ingredient_type
ORDER BY 2;

 

[ 풀이 ]

• 상반기 주문 정보 테이블과 아이스크림 성분 테이블을 key인 flavor를 통해 조인
• 문제에서 아이스크림 성분 타입(ingredient_type)별로 총 주문량을 출력하길 원하므로, GROUP BY ingredient_type
• ORDER BY 절에서 총주문량 오름차순 명시. 단, 위의 문제처럼 집계값에 또 다시 원 변수명과 같은 별칭을 부여하게 되면 ORDER BY에서 제대로 인식을 못하는 경우가 있으므로 이런 경우에는 순서로 명시하기.

-- 아래와 같이 같은 변수명을 덮어쓰는 경우
--  total_order가 정확히 무엇인지가 명확하지 않아서 에러가 나는 경우가 있음
SELECT ~, SUM(total_order) AS total_order
ORDER BY total_order

-- 따라서 아래와 같이 SELECT 절의 순서로 명시하면 에러날 확률↓
SELECT ~, SUM(total_order) AS total_order
ORDER BY 2

---

4. IS NULL - [나이 정보가 없는 회원 수 구하기] _ LEVEL 1

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블입니다.  USER_INFO 테이블에서 나이 정보가 없는 회원이 몇 명인지 출력하는 SQL문을 작성해주세요. 이때 컬럼명은 USERS로 지정해주세요.

 

[ 정답 쿼리 ]

SELECT COUNT(*) AS users
FROM user_info
WHERE age IS NULL;

---

🌟🌟🌟 5. JOIN - [그룹별 조건에 맞는 식당 목록 출력하기] _  LEVEL 4

다음은 고객의 정보를 담은 MEMBER_PROFILE테이블과 식당의 리뷰 정보를 담은  REST_REVIEW 테이블입니다. MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

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

 

[ 정답 쿼리 ]

-- 풀이 1. MAX함수와 서브쿼리(WITH 구문) 사용
-- 리뷰 정보 테이블로부터 member_id별 리뷰 수(counts)를 나타내는 임시 테이블 구성 : review_counts
WITH review_counts AS (SELECT R.member_id
    , COUNT(*) counts
FROM rest_review R
GROUP BY member_id
ORDER BY 2 DESC)

SELECT member_name
    , review_text
    , DATE_FORMAT(review_date,'%Y-%m-%d') review_date
FROM rest_review R
JOIN member_profile M ON R.member_id = M.member_id
WHERE R.member_id IN (
    SELECT member_id -- review_counts로부터 최대 리뷰 수를 기록한 member_id 리스트 추출
    FROM review_counts 
    WHERE counts = (SELECT MAX(counts) FROM review_counts))
ORDER BY 3, 2;


-- 풀이2. RANK()함수와 서브쿼리 사용
SELECT
    MEMBER_NAME,
    R.REVIEW_TEXT AS REVIEW_TEXT,
    DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM
    MEMBER_PROFILE AS M JOIN REST_REVIEW AS R ON M.MEMBER_ID = R.MEMBER_ID
WHERE
    M.MEMBER_ID IN (
        SELECT MEMBER_ID
        FROM (
            SELECT MEMBER_ID, DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
            FROM REST_REVIEW
            GROUP BY MEMBER_ID
        ) AS SUB
        WHERE RNK = 1
    )
ORDER BY
    REVIEW_DATE ASC, REVIEW_TEXT ASC;

 

[ 풀이 ]

• 회원 별 가장 많은 리뷰 수를 조회해보면 3회. 또한 3회의 리뷰를 쓴 회원이 한 명이 아님을 확인할 수 있음 → 이런 경우 내림차순 후 LIMIT 1을 이용해서 서브쿼리를 구성하면 위험
  • 개인적으로는 문제에서 명확하게 설명해줬어야 하는 부분이라고는 생각하지만, 상세한 설명이 없었으므로 일단은 최대 리뷰를 쓴 모든 회원에 대해서 출력하기로 함

    

    리뷰 정보 테이블에서 member_id 별로 리뷰 수 집계 후 내림차순한 결과

---

풀이 1) MAX함수와 서브쿼리(WITH 구문) 사용 (내 풀이)

-- 리뷰 정보 테이블로부터 member_id별 리뷰 수(counts)를 나타내는 임시 테이블 구성 : review_counts
WITH review_counts AS (SELECT R.member_id
    , COUNT(*) counts
FROM rest_review R
GROUP BY member_id
ORDER BY 2 DESC)

SELECT member_name
    , review_text
    , DATE_FORMAT(review_date,'%Y-%m-%d') review_date
FROM rest_review R
JOIN member_profile M ON R.member_id = M.member_id
WHERE R.member_id IN (
    SELECT member_id -- review_counts로부터 최대 리뷰 수를 기록한 member_id 리스트 추출
    FROM review_counts 
    WHERE counts = (SELECT MAX(counts) FROM review_counts))
ORDER BY 3, 2;

• 먼저 리뷰 정보 테이블로부터 member_id별 리뷰 수(counts)를 나타내는 임시 테이블 구성 : review_counts
• 임시 테이블을 가지고 최대 리뷰 수를 기록한 member_id 리스트 추출 ← 해당 리스트를 추출하는 과정에서 WHERE 절 안에서 서브쿼리로 최대 리뷰수(MAX(counts))를 뽑아낼 수 있음

---

풀이 2) RANK()함수와 서브쿼리 사용 (프로그래머스 유저 풀이)

SELECT 
		member_name
    , review_text
    , DATE_FORMAT(review_date,'%Y-%m-%d') review_date
FROM rest_review R
JOIN member_profile M ON R.member_id = M.member_id
WHERE 
    R.member_id IN (
        SELECT member_id
        FROM (
            SELECT member_id, DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
            FROM rest_review
            GROUP BY member_id
        ) AS SUB
        WHERE rnk = 1
    )
ORDER BY 3,2;

• 서브쿼리에서 DENSE_RANK() 함수를 이용해, 외부 쿼리에서 순위를 가지고 최대 리뷰를 쓴 member_id를 뽑아낼 수 있게 구성
• 개인적으로는 순위를 이용하는 쿼리가 훨씬 직관적이고, 이해하기 좋은 것 같음!

---

---

6. String, Date - [조건별로 분류하여 주문상태 출력하기] _ LEVEL 3

다음은 식품공장의 주문정보를 담은 FOOD_ORDER 테이블입니다.  FOOD_ORDER 테이블에서 5월 1일을 기준으로 주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문을 작성해주세요. 출고여부는 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력해주시고, 결과는 주문 ID를 기준으로 오름차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT 
    order_id
    , product_id
    , DATE_FORMAT(out_date,'%Y-%m-%d') AS out_date
    , CASE
        WHEN DATE_FORMAT(out_date,'%m-%d') <= '05-01' THEN '출고완료'
        WHEN DATE_FORMAT(out_date,'%m-%d') > '05-01' THEN '출고대기'
        ELSE '출고미정' END AS `출고여부`
FROM food_order
ORDER BY order_id;

 

[ 풀이 ]

• 출고일자에 따라 값을 가지는 범주형 변수 출고여부 를 상황에 맞게 구성하는 것이 이 문제의 핵심
  • 출고여부의 값이 (출고완료, 출고대기, 출고미정)으로 3개 이상이므로 CASE WHEN 문 이용
  • 문제에서는 출고미정의 조건이 ‘미정’ 이라고만 되어있는데, 테이블 구조로 본다면 NULL인 경우를 의미