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

🌟🌟🌟5. JOIN - [주문량이 많은 아이스크림들 조회하기] _ LEVEL 4

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다.  

 

[ 정답 쿼리 ]

-- 풀이 1. UNION ALL 사용
WITH UNION_TABLE AS (
    SELECT FLAVOR, TOTAL_ORDER FROM JULY
    UNION ALL -- UNION ALL을 써야 우연히 중복됐을 ROW도 포함
    SELECT FLAVOR, TOTAL_ORDER FROM FIRST_HALF
)
SELECT FLAVOR
FROM UNION_TABLE
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3;

-- 풀이 2. JOIN 사용?(FROM 커뮤니티)
SELECT F.FLAVOR
FROM FIRST_HALF F
JOIN JULY J
    ON F.FLAVOR = J.FLAVOR
GROUP BY F.FLAVOR
ORDER BY (F.TOTAL_ORDER + SUM(J.TOTAL_ORDER)) DESC
LIMIT 3;
    
-- 풀이 2-2. JOIN 사용(FROM 나)
SELECT F.FLAVOR
    FROM FIRST_HALF F
    JOIN JULY J
        ON F.FLAVOR = J.FLAVOR
    GROUP BY F.FLAVOR
    ORDER BY (MIN(F.TOTAL_ORDER) + SUM(J.TOTAL_ORDER)) DESC
    LIMIT 3;

 

[ 풀이 ]

---

1. UNION ALL

• 이 풀이는 JULY 테이블과 FIRST_HALF 테이블에서 맛(FLAVOR)과 총 주문량(TOTAL_ORDER)을 선택하여 하나의 테이블로 합치는 접근 방식을 사용.
• UNION ALL은 중복된 행도 모두 포함하기 때문에, 두 테이블을 합치는 과정에서 의도치 않은 중복 행이 발생하더라도 제외하지 않게 됨
• 이렇게 합쳐진 결과를 기반으로 맛별로 그룹을 지어 총 주문량의 합을 계산하고, 이 합계를 기준으로 내림차순 정렬하여 상위 3개의 맛을 선택

---

2. JOIN

• 이 접근법에서는 FIRST_HALF 테이블과 JULY 테이블을 맛(FLAVOR)을 기준으로 내부 조인(JOIN).
• 조인의 결과로 얻은 테이블에서 맛별로 그룹화한 후, 각 그룹의 상반기 총 주문량(F.TOTAL_ORDER)에 7월의 총 주문량의 합(SUM(J.TOTAL_ORDER))을 더해 최종 합계를 계산
• 내 풀이) 일반적인 SQL 표준에 따르면, GROUP BY 절을 사용할 때, SELECT 절에는 GROUP BY에 명시된 열이나 집계 함수를 사용한 결과만 포함할 수 있음. 따라서, GROUP BY F.FLAVOR 후에 F.TOTAL_ORDER를 ORDER BY 절에서 직접 사용하는 것은 표준 SQL에서는 허용되지 않는 경우가 대부분 → F.FLAVOR 도MIN(F.TOTAL_ORDER) 처럼 집계의 형식으로 표현(값에는 지장이 없음)
• ORDER BY 절에는 SELECT 절에 없는 내용도 들어올 수 있다는 것 인지하기! (논리적 실행순서)

---

-- 풀이 3. 상관 서브쿼리
SELECT FLAVOR 
FROM JULY AS J
GROUP BY J.FLAVOR
ORDER BY SUM(J.TOTAL_ORDER) 
    + (SELECT SUM(F.TOTAL_ORDER) FROM FIRST_HALF AS F WHERE J.FLAVOR = F.FLAVOR) DESC 
LIMIT 3;

-- 풀이 4. 각 테이블에 존재하지 않을 맛 고려(OUTER JOIN 방식 이용)
WITH AA AS(
        (SELECT F.FLAVOR, SUM(F.TOTAL_ORDER) + COALESCE(SUM(J.TOTAL_ORDER), 0) AS TOTAL_ORDERS
         FROM FIRST_HALF F
         LEFT JOIN JULY J ON F.FLAVOR = J.FLAVOR
         GROUP BY F.FLAVOR)
         -- 먼저 FIRST_HALF에서 시작하여 JULY와 LEFT JOIN을 수행
    UNION ALL -- MySQL에는 OUTER JOIN이 지원되지 않기 때문에 UNION을 이용하는 방식 사용
        (SELECT J.FLAVOR, SUM(J.TOTAL_ORDER) AS TOTAL_ORDERS
         FROM JULY J
         LEFT JOIN FIRST_HALF F ON J.FLAVOR = F.FLAVOR
         WHERE F.FLAVOR IS NULL
         GROUP BY J.FLAVOR)
         -- 다음에 JULY에서 시작하여 FIRST_HALF과 LEFT JOIN을 수행한 뒤, 
         -- FIRST_HALF에 해당 맛이 없는 경우만 선택
    ORDER BY TOTAL_ORDERS DESC
    LIMIT 3
)
SELECT FLAVOR FROM AA;

 

3. 상관 서브쿼리

• 상관 서브쿼리 : 서브쿼리의 실행 결과가 외부 쿼리의 조건에 따라 동적으로 변경
• 이 방식에서는 JULY 테이블을 기준으로 하여 맛별로 그룹화. 그리고 각 맛에 대해, JULY 테이블의 총 주문량에 FIRST_HALF 테이블의 해당 맛의 총 주문량을 더하는 작업을 상관 서브쿼리를 사용하여 수행.
• 이렇게 계산된 총 주문량의 합계를 기준으로 내림차순 정렬하여 상위 3개의 맛을 찾게 됨. 상관 서브쿼리는 각 JULY의 맛별로 FIRST_HALF에서 해당 맛의 총 주문량을 찾아 더하는 과정을 반복하므로, 모든 가능한 맛에 대해 정확한 총 주문량의 합을 계산할 수 있음.
• 이 방식은 특히 복잡한 조건이나 여러 단계의 데이터 처리가 필요한 경우 유용할 수 있음. 그러나 상관 서브쿼리는 성능 저하를 일으킬 수 있으므로, 데이터가 많은 경우에는 성능 테스트와 최적화가 필요할 수 있음.

Q. 상관 서브쿼리는 어떻게 GROUP BY 없이 집계 성능을 내는가?

• 상관 서브쿼리 내에서는 GROUP BY 절이 필요하지 않은 경우가 많음. 이유는 서브 쿼리가 메인 쿼리에서 참조하는 특정 값(J.FLAVOR)에 대해 이미 '그룹화'된 효과를 내기 때문!

---

---

6. String, Date - [조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기] _ LEVEL 3

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

 

[ 정답 쿼리 ]

-- 풀이 1. JOIN 사용
SELECT CONCAT("/home/grep/src/",B.BOARD_ID, '/',FILE_ID,FILE_NAME, FILE_EXT) FILE_PATH
FROM USED_GOODS_BOARD B
JOIN USED_GOODS_FILE F ON B.BOARD_ID = F.BOARD_ID
WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY FILE_ID DESC;

-- 풀이 2. JOIN 미사용. SUBQUERY 사용
SELECT CONCAT("/home/grep/src/",BOARD_ID, '/',FILE_ID,FILE_NAME, FILE_EXT) FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID = (SELECT BOARD_ID FROM USED_GOODS_BOARD ORDER BY VIEWS DESC LIMIT 1)
ORDER BY FILE_ID DESC;

 

[ 풀이 ]

• 조건으로 사용되는 조회수(VIEWS)만 USED_GOODS_BOARD 에 존재하고, 그 외 출력에 필요한 변수들은 모두 USED_GOODS_FILE 에 존재하기 때문에, JOIN 없이 서브쿼리를 이용해서 풀 수 있음

 

• USING 사용 버전 해보기
  • 두 테이블에서 동일한 이름의 열을 사용해서 JOIN하는 것과 동일한 효과
  • 일반적으로 사용하지는 않는 것 같아서, 가독성 측면에서는 JOIN이 더 안전하고 이해하기 쉬울 수 있음

SELECT CONCAT("/home/grep/src/",BOARD_ID, '/',FILE_ID,FILE_NAME, FILE_EXT) FILE_PATH
FROM USED_GOODS_BOARD
JOIN USED_GOODS_FILE
USING (BOARD_ID)
WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY FILE_ID DESC;