이번 게시글에서는 2가지 풀이법이 나옵니다.

첫번째는 노가다(?)방식으로 여러 서브쿼리(총 3개)를 써서 푼 방식과

두번째는 윈도우 함수를 이용하여 2개의 서브쿼리를 쓴 방식입니다.

이번에도 풀이 과정이 다소 길기 때문에 가능한 정답 코드만을 보여주고, 제가 설명하고 싶은 개념만 추가하도록 하겠습니다.

또한, SQL문 작성 후 해당 SQL마다 결과 테이블을 확인하여 수정하시거나 이를 이용하여 그 다음 단계 작업을 생각하시면 훨씬 용이하실 것입니다.

---

1. 문제 설명

문제는 다음과 같습니다.

＊테이블:
1.MEMBER_PROFILE(MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH)

2.REST_REVIEW(REVIEW_ID, REST_ID(FK, 일반 참조), MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT, REVIEW_DATE)

＊문제:
1.리뷰를 가장 많이 작성한 회원(MEMBER_ID 기준으로 GROUP BY + COUNT)들의 리뷰 조회(각 리뷰 조회이므로, 이건 GROUP BY가 아닌 전부 다 보여줘야 함.)

2.회원 이름,리뷰 텍스트, 리뷰날짜 출력.

3.리뷰 작성일 오름차순, 리뷰 텍스트 기준 오름차순

＊문제 풀이 접근:

일단,
1. GROUP BY +COUNT으로 갯수 합산 후, MAX 뽑기
2. 근데 리뷰 텍스트는 전부 다 출력
3. JOIN은 MEMBER_NAME을 위해서 INNER JOIN 실시

->JOIN 보다 1,2번이 관건임.

---

2. 문제 풀이

2-1. JOIN 실시

공통된 열: MEMBER_ID(PK이자, FK)
조인: INNER JOIN

->직전 게시글과 같은 논리. 공통된 것(교집합)만 뽑으면 됨

<정답 코드>

SELECT
    MP.MEMBER_ID,
    MP.MEMBER_NAME,
    RR.REVIEW_TEXT,
    RR.REVIEW_DATE
FROM MEMBER_PROFILE MP INNER JOIN REST_REVIEW RR
ON MP.MEMBER_ID = RR.MEMBER_ID

위 결과를 보니,
여기서 MEMBER_ID 기준으로(MEMBER_NAME으로 해도 되는 거 같은데, MEMBER_ID가 PK이기도 하니, 더 확실하게 접근.)
GROUP BY + COUNT 하면 될 듯합니다.

<정답 코드>

SELECT
    MP.MEMBER_ID,
    MP.MEMBER_NAME,
    COUNT(MP.MEMBER_ID) AS COUNTS,
    RR.REVIEW_TEXT,
    RR.REVIEW_DATE
FROM MEMBER_PROFILE MP INNER JOIN REST_REVIEW RR
ON MP.MEMBER_ID = RR.MEMBER_ID
GROUP BY MP.MEMBER_ID
ORDER BY COUNTS DESC

그리고 난 다음,
저는 MAX()함수를 사용하여 COUNTS 중에 제일 많은 것을 출력하고 싶었습니다.

SELECT
    MP.MEMBER_ID,
    MP.MEMBER_NAME,
    COUNT(MP.MEMBER_ID) AS COUNTS,
    MAX(COUNTS),
    RR.REVIEW_TEXT,
    RR.REVIEW_DATE
FROM MEMBER_PROFILE MP INNER JOIN REST_REVIEW RR
ON MP.MEMBER_ID = RR.MEMBER_ID
GROUP BY MP.MEMBER_ID
ORDER BY COUNTS DESC

그치만 이 방법은 실패했습니다.
왜 그럴까요?

※실패한 이유(개념 설명_1)

'SELECT 절 안에서는 같은 SELECT절의 별칭을 참조할 수 없다.'

위 사진에서와 같이 SELECT절 별칭은 ORDER BY,HAVING절에서만 사용 가능합니다!

그래서 저는 별칭을 안 쓰고 해봤지만(이 방법이 해답 방법 중 1개였음), 이 마저 실패했습니다.
구조 접근 방법 자체를 다르게 설정해야했습니다.

---

2-2. 여러 개의 서브쿼리 만들기

위 사진과 같이 우리는 단계를 거쳐가며 SQL문을 작성해야합니다.

그래서 제가 2가지 방법으로 직접 작성해보았습니다.

<방법_1>

SELECT 
    MAX(COUNTS)
FROM (
SELECT
    MP.MEMBER_ID,
    MP.MEMBER_NAME,
    COUNT(MP.MEMBER_ID) AS COUNTS,
    -- MAX(COUNTS),
    -- MAX(COUNT(MP.MEMBER_ID)),
    RR.REVIEW_TEXT,
    RR.REVIEW_DATE
FROM MEMBER_PROFILE MP INNER JOIN REST_REVIEW RR
ON MP.MEMBER_ID = RR.MEMBER_ID
GROUP BY MP.MEMBER_ID
ORDER BY COUNTS DESC);

->이 방법은, 모든 '파생(유도) 테이블(DERIVED TABLE)은 별칭을 가져야한다.'라는 오류가 발생했습니다.

<방법_2>

(SELECT
    MP.MEMBER_ID,
    MP.MEMBER_NAME,
    COUNT(MP.MEMBER_ID) AS COUNTS,
    -- MAX(COUNTS),
    -- MAX(COUNT(MP.MEMBER_ID)),
    RR.REVIEW_TEXT,
    RR.REVIEW_DATE
FROM MEMBER_PROFILE MP INNER JOIN REST_REVIEW RR
ON MP.MEMBER_ID = RR.MEMBER_ID
GROUP BY MP.MEMBER_ID
ORDER BY COUNTS DESC) AS RESULT_1;

SELECT 
    MAX(COUNTS)
FROM RESULT_1;

->이 방법은, '유도 테이블은 세미콜론(;) 이후에 사용불가하다.'라는 오류가 발생했습니다.

SQL문에서 세미콜론(;)은 SELECT절이 끝날 때 쓰는데,
2개의 SELECT문을 작성해서 결과를 내보내려고 했는데,
이미 첫번째 SELECT문을 작성하고 별칭을 지어도 ';'으로 끝나버리기 때문에, 2번째 SELECT문에는 사용할 수가 없습니다.

※개념_2

Q. 유도 테이블(DERIVED TABLE)이란?

근데,어쨋든 위 '방법 1' 및 '방법 2' 모두 제대로 결과가 안 나왔습니다.
(위 방법들은 개념 설명을 위해서 추가했습니다.)

---

<정답 코드>

SELECT
    MP.MEMBER_NAME,
    RR.REVIEW_TEXT,
    RR.REVIEW_DATE
FROM MEMBER_PROFILE MP INNER JOIN REST_REVIEW RR
ON MP.MEMBER_ID = RR.MEMBER_ID
WHERE MP.MEMBER_ID IN (
    SELECT
        MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    HAVING COUNT(*) = (
        SELECT MAX(REVIEW_COUNT)
        FROM (
            SELECT COUNT(*) AS REVIEW_COUNT
            FROM REST_REVIEW
            GROUP BY MEMBER_ID
        ) AS TEMP
    )
)
ORDER BY MP.MEMBER_NAME ASC, RR.REVIEW_DATE ASC;

너무 길어지므로 정답 코드를 먼저 공개한 다음,
정답 코드 설명을 하도록 하겠습니다.

코드를 보면, 총 3개의 서브쿼리가 있음을 알 수 있습니다.

가장 안쪽의 서브쿼리부터 보면(안쪽부터 먼저 실행),
MEMBER_ID를 GROUP BY 열로 삼아 COUNT(＊)을 통해 집계를 한 다음 SELECT문으로 MAX(REVIEW_COUNT)를 선택합니다.
->이 작업은, '집계 함수 안에 집계 함수 삽입이 불가하다.'라는 문제 때문에 이렇게 서브쿼리화를 시켰습니다.

※개념_3

'집계 함수 안에 집계 함수 삽입이 불가능하다.'

＊집계 함수(AGGREGATE FUNCTION): COUNT(), SUM(), AVG(), MAX(), MIN() 등...

그 다음 안쪽 서브쿼리를 보면,
HAVING절(집계에서의 조건절)을 통해 위의 서브쿼리를 조건절로 내세워서 그 조건절에 부합하는 MEMBER_ID를 SELECT하는 것을 볼 수 있습니다.
->위 서브쿼리절과 이번 서브쿼리절의 구조가 거의 동일하게 중복되어 반복됨을 볼 수 있습니다.

마지막 서브쿼리를 보면,
WHERE 조건절과 IN을 통해, 최종적으로 이 MEMBER_ID에 속하는 인물만을 골라서, 우리가 최종 답으로 원하는 'MP.MEMBER_NAME','RR.REVIEW_TEXT','RR.REVIEW_DATE'을 SELECT함을 알 수 있습니다.

※개념_4

즉, FROM 절은 뒤에 테이블이 와야하므로 별칭이 필수이고(간편화,편리화를 위해), WHERE절 및 HAVING절 같은 조건절은 뒤에 값이 오므로 딱히 별칭이 필요하지 않기에, 별칭이 필수가 아닙니다.

그리고, 최종적으로 DATE 포멧을 요구한대로 맞추고

ORDER BY를 맞게 한다면,
최종 정답 코드가 됩니다.

<최종 정답 코드>

-- 코드를 입력하세요
SELECT
    -- MP.MEMBER_ID,
    MP.MEMBER_NAME,
    -- COUNT(MP.MEMBER_ID) AS COUNTS,
    -- MAX(COUNTS),
    -- MAX(COUNT(MP.MEMBER_ID)),
    RR.REVIEW_TEXT,
    DATE_FORMAT(RR.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE MP INNER JOIN REST_REVIEW RR
ON MP.MEMBER_ID = RR.MEMBER_ID
WHERE MP.MEMBER_ID IN (
    SELECT
        MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    HAVING COUNT(*) = (
        SELECT MAX(REVIEW_COUNT)
        FROM (
            SELECT COUNT(*) AS REVIEW_COUNT
            FROM REST_REVIEW
            GROUP BY MEMBER_ID
        )AS TEMP -- Every derived table must have its own alias(유도(파생) 테이블에 별칭 안 붙이면 작동 안 함.)
    )
)
-- GROUP BY MP.MEMBER_ID
-- ORDER BY COUNTS DESC
ORDER BY RR.REVIEW_DATE ASC, REVIEW_TEXT ASC;

---

2-3 윈도우 함수(WINDOW FUNCTION) 사용하기

저는, 2-2번 과정으로 문제를 푸는 중간에,

1. GROUP BY +COUNT으로 갯수 합산 후, MAX 뽑기
2. 근데 리뷰 텍스트는 전부 다 출력

이 부분에서 윈도우 함수를 사용하면 되지 않을까란 생각이 들었습니다.

저의 초반 게시글에 'GROUP BY' VS 'PARTITION BY'를 비교하는 개념 관련 글이 나오는데,

둘다 그룹화라는 특징이 있지만,
GROUP BY는 여러 행을 한 행으로 합치고,
PARTITION BY는 여러 행을 그대로 유지한다는 개념입니다.

※개념_5. 윈도우 함수(WINDOW FUNCTION)이란?

기존의 SQL(열과 열 간의 연산에 특화)와는 다르게, '행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수.'

기본 문법은,

함수명() OVER ( (선택)PARTITION BY/ORDER BY)

이렇게 작성한다.

OVER()는 필수로 작성해야한다.
OVER()의 의미는, '어떤 윈도우를 통해 볼 것인지'를 정의하는 부분이다.

SELECT
    MEMBER_NAME,
    REVIEW_TEXT,
    REVIEW_DATE
FROM (
    SELECT
        -- MP.MEMBER_ID,
        MP.MEMBER_NAME,
        RR.REVIEW_TEXT,
        -- RR.REVIEW_DATE,
        DATE_FORMAT(RR.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE,
        COUNT(*) OVER (PARTITION BY MP.MEMBER_ID) AS REVIEW_COUNT,
        MAX(COUNT(*)) AS MAX_REVIEW_COUNT -- 이 부분에서 에러 발생
    FROM MEMBER_PROFILE MP
    INNER JOIN REST_REVIEW RR
        ON MP.MEMBER_ID = RR.MEMBER_ID
) AS TEMP
-- WHERE REVIEW_COUNT = MAX(REVIEW_COUNT)
WHERE REVIEW_COUNT = MAX_REVIEW_COUNT
ORDER BY
    REVIEW_DATE ASC,
    REVIEW_TEXT ASC;

위 코드를 보면,
MAX(COUNT(＊)) AS MAX_REVIEW_COUNT에서 막혀서(집계 함수 중복의 이유, 그 외의 이유들 등..),
이번에는 CLAUDE가 아닌 GPT5에게 질문을 해봤습니다.

그러더니, 윈도우 함수를 쓰지만 그래도 2번의 서브쿼리를 사용해야하더군요.

<최종 정답 코드>

SELECT 
    MEMBER_NAME,
    REVIEW_TEXT,
    DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM (
    SELECT
        MEMBER_NAME,
        REVIEW_TEXT,
        REVIEW_DATE,
        REVIEW_COUNT,
        -- 여기서는 더 이상 중첩이 아님: 일반 컬럼 REVIEW_COUNT에 대해 윈도우 MAX
        MAX(REVIEW_COUNT) OVER () AS MAX_REVIEW_COUNT
    FROM (
        SELECT
            MP.MEMBER_NAME,
            RR.REVIEW_TEXT,
            RR.REVIEW_DATE,
            -- 회원별 리뷰 개수(각 리뷰 행에 동일 값)
            COUNT(*) OVER (PARTITION BY MP.MEMBER_ID) AS REVIEW_COUNT
        FROM MEMBER_PROFILE MP
        JOIN REST_REVIEW RR
          ON MP.MEMBER_ID = RR.MEMBER_ID
    ) x
) y
WHERE REVIEW_COUNT = MAX_REVIEW_COUNT
ORDER BY REVIEW_DATE ASC, REVIEW_TEXT ASC;

제일 안쪽의 서브쿼리는,
윈도우 함수의 PARTITION BY를 통해 MEMBER_ID를 PARTITION BY의 열로 하여 각 행은 남기면서 COUNT(＊)을 진행합니다.

그 다음 서브쿼리에서는, 앞에서 말한 개념인 '집계 함수는 중복이 허용되지 않는다.'라는 오류로 이렇게 MAX(REVIEW_COUNT)로 만들어줍니다.
->여기에서는 OVER절에 딱히 PARTITION BY라든지, ORDER BY의 절이 쓰이지 않아도 되므로 OVER()로만 표현합니다.

나머지는 위 첫번째 풀이과정(서브쿼리 3개)와 동일합니다.

※개념_6

DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
에서 %Y-%m-%d가 아니라 '%Y-%M-%D'로 하면 결과가 다르게 나온다.

위는 '%Y-%M-%D' 했을 때의 결과이다.

우리가 원하는 답처럼 나오려면, '%Y-%m-%d' 이렇게 해야한다.

※개념_7

'윈도우 함수는 중첩이 불가하다.'

즉, 한 명령어 안에 중첩되게 윈도우 함수를 사용하는 것은 불가능하지만, 서브쿼리로 감싸서 윈도우를 중첩 사용하는 것은 가능하다.

---

3. 마무리

이렇게 해서 2가지 풀이법에 대해서 알아보았습니다.

이번에도 제가 오답 풀이과정이지만 굳이 넣은 이유는,
개념 설명을 위함이었습니다.

따라서, 오답 과정은 안 보시고, 개념만 빠르게 훑으시는 것도 추천합니다.

또한, 최종 정답 코드만 확인하여 문제를 분석하는 것도 좋은 방법인 거 같습니다.

제 긴 글을 읽어주셔서 또 한번 감사드리며,
이번 게시글 작성을 마치겠습니다.

감사합니다~~ :) bb