[SQL TEST 36일차] String, Date, LV.3 : 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

코딩테스트_프로그래머스

주제 : 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

범위: String, Date

레벨: Lv.3

https://school.programmers.co.kr/learn/courses/30/lessons/164671

프로그래머스

---

문제 설명
다음은 중고거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS은 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

 

USED_GOODS_FILE 테이블은 다음과 같으며 FILE_ID, FILE_EXT, FILE_NAME, BOARD_ID는 각각 파일 ID, 파일 확장자, 파일 이름, 게시글 ID를 의미합니다.

 

문제
USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.

예시
USED_GOODS_BOARD 테이블이 다음과 같고

 

USED_GOODS_FILE 테이블이 다음과 같을 때

 

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

---

#답

답안이 참 다양하네유

(1)

SELECT
    CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM
    USED_GOODS_FILE
WHERE
    BOARD_ID IN (SELECT FIRST_VALUE(BOARD_ID) OVER (ORDER BY VIEWS DESC) 
                    FROM USED_GOODS_BOARD )
ORDER BY
    FILE_ID DESC

(2)

SELECT CONCAT('/home/grep/src/', F.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE F 
JOIN (SELECT BOARD_ID
 FROM USED_GOODS_BOARD
 WHERE VIEWS = (SELECT MAX(VIEWS)
                FROM USED_GOODS_BOARD)) I ON F.BOARD_ID = I.BOARD_ID
ORDER BY F.FILE_ID DESC

(3)

SELECT
    CONCAT('/home/grep/src/',f.BOARD_ID,'/',
           f.FILE_ID,f.fILE_NAME,f.FILE_EXT)
           AS FILE_PATH
FROM
    USED_GOODS_BOARD b INNER JOIN USED_GOODS_FILE f
    ON b.BOARD_ID = f.BOARD_ID
WHERE
    b.VIEWS IN (
        SELECT MAX(VIEWS)
        FROM USED_GOODS_BOARD)
ORDER BY
    f.FILE_ID DESC;

---

#오답노트

SELECT CONCAT("/home/grep/src", F.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT) AS FILE_PATH
  FROM USED_GOODS_BOARD B
  JOIN USED_GOODS_FILE F
    ON B.BOARD_ID = F.BOARD_ID
 WHERE B.VIEWS = (SELECT MAX(VIEWS)  FROM USED_GOODS_BOARD)
 ORDER BY F.FILE_ID DESC

 

오답인 이유?

 

논리적으로는 틀린 건 아니지만 “불안정”

• 조회수가 같은 게시물이 여러 개인 경우, 한 행만 반환되거나 / 두 행 반환될 수 있음
• => '=' 대신 IN을 스는게 '하나 이상일 수도 있다'라는 의미를 담고 있어 안전한 표현

서브쿼리의 반환 타입 문제

• IN (SELECT MAX(VIEWS)) 은 내부적으로 리스트(집합) 을 반환하는 형태 => 여러 값 올 수 있음을 허용
• = (SELECT MAX(VIEWS)) 은 정확히 한 값만 와야 함
• IN 을 쓰는 이유는 '문법적으로 안전한' 방식

답 풀이

#공통

SELECT
    CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH

출력 값 : /home/grep/src/B0001/IMG_000001photo1.jpg

=> CONCAT 함수를 통해 붙여버리기 & AS 를 통해 컬럼명 지정

 

(1)

WINDOW 함수 활용

WHERE
    BOARD_ID IN (SELECT FIRST_VALUE(BOARD_ID) OVER (ORDER BY VIEWS DESC) 
                    FROM USED_GOODS_BOARD )

 

WINDOW 함수 : FIRST_VALUE() OVER () 사용

  -> 조회수가 가장 높은 게시글(ORDER BY VIEWS DESC) 의 BOARD_ID 한 개만 추출

---

윈도우 함수란 ?

윈도우 함수 = 집계함수처럼 보이지만, 그룹으로 묶지 않고 행별로 계산 결과를 함께 보여주는 함수

 

구분	일반 집계 함수	윈도우 함수
예시	SELECT MAX(VIEWS) FROM BOARD	SELECT MAX(VIEWS) OVER() FROM BOARD
결과	전체에서 한 줄만 나옴	각 행별로 계산 결과가 같이 나옴
특징	GROUP BY 필요	GROUP BY 필요 없음
사용 목적	데이터 요약	순위, 누적합, 이전행/다음행 비교

 

 예시 :

BOARD_ID	VIEWS
1	50
2	80
3	30

 

결과 : 

BOARD_ID	VIEWS	MAX(VIEWS) OVER()
1	50	80
2	80	80
3	30	80

 

(2)

MAX(VIEWS) + JOIN 활용

JOIN (SELECT BOARD_ID
 FROM USED_GOODS_BOARD
 WHERE VIEWS = (SELECT MAX(VIEWS)
                FROM USED_GOODS_BOARD)) I ON F.BOARD_ID = I.BOARD_ID

 

MAX(VIEWS) 조회수 가장 높은 게시글의 BOARD_ID 만 추출해서 JOIN

JOIN + 서브쿼리 

가장 많이 쓰이는 실무형 

 

 

(3)
JOIN + IN (SELECT MAX(VIEWS)) 활용

WHERE
    b.VIEWS IN (
        SELECT MAX(VIEWS)
        FROM USED_GOODS_BOARD)

 

WHERE을 통해 조건 걸은 형태

B.VIEWS가 서브쿼리를 통해 구해낸 가장 높은 조회수 (MAX(VIEWS))에 속해있는 것 출력