<프로그래머스 71> 그룹별 조건에 맞는 식당 목록 출력하기(Lv.4)

 

프로그래머스에서 제공하는 SQL문제 매일 풀기 챌린지

---

 

문제 설명

다음은 고객의 정보를 담은 MEMBER_PROFILE테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다. MEMBER_PROFILE 테이블은 다음과 같으며 MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는 회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미합니다.

Column name	Type	Nullable
MEMBER_ID	VARCHAR(100)	FALSE
MEMBER_NAME	VARCHAR(50)	FALSE
TLNO	VARCHAR(50)	TRUE
GENDER	VARCHAR(1)	TRUE
DATE_OF_BIRTH	DATE	TRUE

REST_REVIEW 테이블은 다음과 같으며  REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.

Column name	Type	Nullable
REVIEW_ID	VARCHAR(10)	FALSE
REST_ID	VARCHAR(10)	TRUE
MEMBER_ID	VARCHAR(100)	TRUE
REVIEW_SCORE	NUMBER	TRUE
REVIEW_TEXT	VARCHAR(1000)	TRUE
REVIEW_DATE	DATE	TRUE

---

문제

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

---

예시

MEMBER_PROFILE 테이블이 다음과 같고

 

[ MEMBER_PROFILE ]

MEMBER_ID	MEMBER_NAME	TLNO	GENDER	DATE_OF_BIRTH
jiho92@naver.com	이지호	01076432111	W	1992-02-12
jiyoon22@hotmail.com	김지윤	01032324117	W	1992-02-22
jihoon93@hanmail.net	김지훈	01023258688	M	1993-02-23
seoyeons@naver.com	박서연	01076482209	W	1993-03-16
yelin1130@gmail.com	조예린	01017626711	W	1990-11-30

 

[ REST_REVIEW ]

REVIEW_ID	REST_ID	MEMBER_ID	REVIEW_SCORE	REVIEW_TEXT	REVIEW_DATE
R000000065	00028	soobin97@naver.com	5	부찌 국물에서 샤브샤브 맛이나고 깔끔	2022-04-12
R000000066	00039	yelin1130@gmail.com	5	김치찌개 최곱니다.	2022-02-12
R000000067	00028	yelin1130@gmail.com	5	햄이 많아서 좋아요	2022-02-22
R000000068	00035	ksyi0316@gmail.com	5	숙성회가 끝내줍니다.	2022-02-15
R000000069	00035	yoonsy95@naver.com	4	비린내가 전혀없어요.	2022-04-16

 

SQL을 실행하면 다음과 같이 출력되어야 합니다.

MEMBER_NAME	REVIEW_TEXT	REVIEW_DATE
조예린	김치찌개 최곱니다.	2022-02-12
조예린	햄이 많아서 좋아요	2022-02-22

 

 

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

---

이 문제는
WITH AS 절, EQUI JOIN, 윈도우 함수 
3가지를 사용하여 해결할 수 있다.

---

♥ 오라클 정답

with max_member
as( select member_id, rank() over(order by count(*) desc) rnk
        from rest_review 
        group by member_id)
select p.member_name, r.review_text, to_char(r.review_date, 'rrrr-mm-dd') review_date
    from member_profile p, rest_review r, max_member m
    where p.member_id = r.member_id and r.member_id = m.member_id
    and m.rnk=1
    order by 3 asc, 2 asc;

 

 

♥   MySQL 정답

with max_member
as( select member_id, rank() over(order by count(*) desc) rnk
        from rest_review 
        group by member_id)
select p.member_name, r.review_text, date_format(r.review_date, '%Y-%m-%d') review_date
    from member_profile p, rest_review r, max_member m
    where p.member_id = r.member_id and r.member_id = m.member_id
    and m.rnk=1
    order by 3 asc, 2 asc;

---

■ EQUI JOIN

조인은 여러 개의 테이블의 컬럼들을 활용하여 한 번에 원하는 것을 출력하고자 할 때 사용하는 문법이다.

그 중 EQUI JOIN 이란, 

조인하려는 테이블 사이에 공통된 컬럼이 있을 경우 (컬럼명은 달라도 되고, 데이터가 공통될 때)

EQUAL(=) 조인하는 조인 문법이다.

 

 

■ WITH .. AS 절

쉽게 말해서 with 절로 temp table을 구성하는 것인데,

create table이 아니라 with절을 사용해서 임시적으로 테이블을 만드는 것이다.

이 임시 테이블을 사용해서 테이블을 생성하여 select 시 활용할 수 있다.

WITH [new임시테이블명] AS 
	( 새로운 임시 테이블로 만들 쿼리문 )
select ....; --만든 쿼리문을 활용해서 검색할 쿼리문

 

with절로 생성한 임시 테이블은 temporart tablespace에 저장되고, 

그 결과를 temp 테이블로 구성되는 것이다.

 

■ 윈도우 함수(Window function)

윈도우 함수는 특정 창(Window) 또는 그룹 내에서 데이터를 처리하고 계산하는 데 사용된다.
일반적으로 집계 함수와는 달리 개별 행에 대한 계산을 수행하며
데이터 집합을 여러 그룹으로 나눌 필요 없이 전체 데이터 집합에 대해 작동한다.

윈도우 함수는 OVER절을 사용하여 정의하고, 윈도우의 파티션(분할 기준), 정렬 순서(ORDER BY), 윈도우 프레임(계산 대상 행의 범위) 등을 지정할 수 있다. 
주로 아래와 같은 상황에서 사용한다.

-  순위를 계산하는 함수 : RANK(), DENSE_RANK(), ROW_NUMBER() 등
-  누적 합, 평균, 최소값, 최대값 계산: `SUM()`, `AVG()`, `MIN()`, `MAX()` 등

---

♥  정답 해설

위의 내용을 기반으로 해설해보자

 

# WITH.. AS 절 내부 

 

리뷰를 가장 많이 작성한 회원의 리뷰를 조회하기 위해서

REST_REVIEW 테이블에서 회원 ID 별로 리뷰를 몇 개 작성했는지 순위를 구한다.

 

회원 ID 별 순위를 구하기 위해서 group by절로 member_id를 묶어주고,

윈도우함수 rank를 사용하여 순위를 구해준다.

 

순위를 구하는 기준은 member_id별 review를 작성한 개수가 많을 수록 높은 순위를 차지한다.

따라서 over절을 사용하여 count(*)를 해주어서 member_id 별 review 작성 개수를 구한 후,

그에 대해서 desc로 정렬한 값에 대한 rank를 구해준다. 

구한 값은 rnk라는 컬럼으로 출력되도록 별칭을 지정해준다.

 

이 쿼리문을 with .. as절을 사용하여 임시 테이블로 구성해주는데,

테이블 명을 max_member로 주었다.

 

 

# WITH .. AS 절 외부 

 

# 1. FROM 절

문제에서 요구하는 데이터를 조회하기 위해서 3개의 테이블 조인이 필요하다.

With 절로 생성한 임시 테이블인 MAX_MEMBER 테이블에서는 가장 높은 순위를 차지한 데이터를 출력하기 위한 조건으로 rnk 컬럼을 사용하기 위해서 필요한 테이블이며,

REST_REVIEW 테이블은 review_text와 review_date를 출력하기 위해 필요한 테이블이고

MEMBER_PROFILE 테이블은 member_name을 출력하기 위해서 필요한 테이블이다.

 

이 3개의 테이블을 별칭과 함께 나열해준다.

 

# 2. SELECT절

출력해줘야할 컬럼들인 member_name, review_text와 review_date 을 

각자의 테이블 명과 함께 나열해주는데,

review_date의 데이트 포맷이 예시와 동일하게 출력되게 하기 위해서

 

Oracle은 to_char 함수를 사용해주고,

MySQL은 date_format 함수를 사용해주어 출력 형식을 변환해준다.

 

 

# 3. WHERE 절

 

-- 연결조건

3개의 테이블 모두에 공통된 컬럼인 member_id가 있어

equi join으로 연결해주면 된다.

 

-- 일반조건

임시테이블로 생성한 MAX_MEMBER 의 컬럼인 rnk 를 사용하여

리뷰를 가장 많이 작성한 회원의 조건을 충족하기 위해서

1등만 출력되게 조건을 준다. 

 

 

# 4. ORDER BY 절

review_date를 기준으로 asc하게 정렬하고, 

review_date가 같다면 reivew_text를 기준으로 asc되게 작성해준다.

이때 select절에 작성된 컬럼의 순서를 활용하여 컬럼명을 대체할 수 있다.

 

 

 

 

 

출처 : 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/challenges