[프로그래머스] 그룹별 조건에 맞는 식당 목록 출력하기 [#SQL]

 

JOIN LEVEL 4 문제이다.

 

 

---

1. 출처

---

 

 

프로그래머스

 

• 고객정보를 담은 [MEMBER_PROFILE] 테이블
• 식당의 리뷰 정보를 담은 [REST_REVIEW] 테이블
• 리뷰를 가장 많이 작성한 회원의 전체 리뷰를 조회하는 SQL문 작성
• 리뷰 작성일 오름차순, 리뷰 텍스트 오름차순 정렬

 

 

---

2. 설계

---

 

최종적으로 리뷰를 가장 많이 쓴 사람의 ID를 찾아서 그 사람의 전체 리뷰를 출력해야 한다.

그럼 우린 세 단계를 거쳐서 답을 도출해야 한다.

 

첫번째, MAX 리뷰 수는 몇 개인가?

두번째, MAX 리뷰 수를 가진 회원(ID)를 도출

세번째, 회원 ID가 쓴 전체 리뷰를 조회하는 것

 

위 순서대로 쿼리문을 짜보도록 하겠다.

 

 

2-1) MAX 리뷰 수는 몇 개인가?

식당의 리뷰 정보를 담은 [REST_REVIEW] 테이블에서 MEMBER_ID별로 그룹을 짓고, 회원 별 몇 개의 리뷰를 썼는지 COUNT로 계산할 것이다. 계산한 결과를 ORDER BY로 리뷰수가 많은 순으로 (DESC) 정렬하고, 가장 위의 결과(LIMIT 1)를 뽑아 MAX 리뷰를 구한다.

 

 

max 리뷰수를 찾는 방식은 2가지이다.

회원의 ID를 GROUP BY로 묶을 수도 있고, OVER(PARTITION BY ~~) 로 묶을 수도 있기 때문이다.

 

- GROUP BY 사용

SELECT COUNT(r.REVIEW_ID) AS C
FROM REST_REVIEW r
GROUP BY r.MEMBER_ID
ORDER BY COUNT(r.REVIEW_ID) DESC
LIMIT 1;

 

 

- OVER(PARTITION BY ~~) 사용

SELECT COUNT(r.REVIEW_ID) OVER(PARTITION BY r.MEMBER_ID) AS C
FROM REST_REVIEW r
ORDER BY C DESC
LIMIT 1

 

 

LIMIT을 적용안해줬을 땐 아래처럼 나온다. 가장 위에 리뷰 수 MAX 값이 있으므로 LIMIT 1로 뽑아주는 것이다.

 

 

2-2) MAX 리뷰 수를 가진 회원(ID)를 도출

2-1번에서 구한 MAX 리뷰 수를 가지고, MEMBER_ID로 그룹되어진 결과에서 MAX 리뷰수를 가진 회원 ID를 도출한다.

SELECT r.MEMBER_ID
FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
GROUP BY r.MEMBER_ID
HAVING COUNT(r.REVIEW_ID) = 2-1 서브 쿼리 적용
ORDER BY m.MEMBER_NAME;

 

 

 

2-3) 회원 ID가 쓴 전체 리뷰를 조회하는 것

2-1번과 2-2번의 서브쿼리를 모두 합쳐 결과를 도출해낸다.

2-2번에서 도출한 MAX 리뷰수를 가진 회원 ID를 이용해 리뷰수를 전체 출력한다.

 

아래는 2-1의 GROUP_BY 버전이고, OVER(PARTITION BY ~~) 버전을 사용해도 정답이다.

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
WHERE r.MEMBER_ID IN ( #2-2 적용
    SELECT r.MEMBER_ID
    FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
    GROUP BY r.MEMBER_ID
    HAVING COUNT(r.REVIEW_ID) = ( #2-1 적용
        SELECT COUNT(r.REVIEW_ID) AS C
        FROM REST_REVIEW r
        GROUP BY r.MEMBER_ID
        ORDER BY COUNT(r.REVIEW_ID) DESC
        LIMIT 1
    )
)
ORDER BY r.REVIEW_DATE, r.REVIEW_TEXT;

 

 

날짜 포맷을 바꾸는 방법은 DATE_FORMAT 함수를 이용하면 된다.

DATEFORMAT(컬럼명, '어떻게 바꿀건지?')

• %Y = 2024
• %y = 24
• %M = January
• %m = 01
• %D = 22th
• %d = 22

 

 

---

3. 전체 코드

---

 

3-1) GROUP_BY 버전

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
WHERE r.MEMBER_ID IN (
    SELECT r.MEMBER_ID
    FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
    GROUP BY r.MEMBER_ID
    HAVING COUNT(r.REVIEW_ID) = (
        SELECT COUNT(r.REVIEW_ID) AS C
        FROM REST_REVIEW r
        GROUP BY r.MEMBER_ID
        ORDER BY COUNT(r.REVIEW_ID) DESC
        LIMIT 1
    )
)
ORDER BY r.REVIEW_DATE, r.REVIEW_TEXT;

 

 

 

3-2) OVER(PARTITION BY ~~) 버전

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
WHERE r.MEMBER_ID IN (
    SELECT r.MEMBER_ID
    FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
    GROUP BY r.MEMBER_ID
    HAVING COUNT(r.REVIEW_ID) = (
        SELECT COUNT(r.REVIEW_ID) OVER(PARTITION BY r.MEMBER_ID) AS C
        FROM REST_REVIEW r
        ORDER BY C DESC
        LIMIT 1
    )
)
ORDER BY r.REVIEW_DATE, r.REVIEW_TEXT;