[SQL] 저자 별 카테고리 별 매출액 집계하기

About this offer

🔒 Lv.4 GRUOP BY https://school.programmers.co.kr/learn/courses/30/lessons/144856 🖋문제 다음은 어느 한 서점에서 판매중인 도서들의 도서 정보( BOOK ), 저자 정보( AUTHOR ) 테이블입니다. BOOK 테이블은 각 도서의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다. Column name Type Nullable Description BOOK_ID INTEGER FALSE 도서 ID CATEGORY VARCHAR(N) FALSE 카테고리 (경제, 인문, 소설, 생활, 기술) AUTHOR_ID INTEGER FALSE 저자 ID PRICE INTEGER FALSE 판매가 (원) PUBLISHED_DATE DATE FALSE 출판일 AUTHOR 테이블은 도서의 저자 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다. Column name Type Nullable Description AUTHOR_ID INTEGER FALSE 저자 ID AUTHOR_NAME VARCHAR(N) FALSE 저자명 BOOK_SALES 테이블은 각 도서의 날짜별 판매량 정보를 담은 테이블입니다. Column name Type Nullable Description BOOK_ID INTEGER FALSE 도서 ID SALES_DATE DATE FALSE 판매일 SALES INTEGER FALSE 판매량 2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요. 💻코드 SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(TOTAL_SALES) AS TOTAL_SALES FROM( SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(BS.SALES) * B.PRICE AS TOTAL_SALES FROM BOOK AS B LEFT JOIN BOOK_SALES AS BS ON B.BOOK_ID = BS.BOOK_ID JOIN AUTHOR AS A ON A.AUTHOR_ID = B.AUTHOR_ID WHERE BS.SALES_DATE IS NOT NULL AND BS.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31' GROUP BY B.BOOK_ID ) AS T1 GROUP BY AUTHOR_ID, CATEGORY ORDER BY AUTHOR_ID ASC, CATEGORY DESC 🔑풀이 SELECT B.AUTHOR_ID, A.AUTHOR_NAME, CATEGORY, SUM(BS.SALES), B.PRICE , SUM(BS.SALES) * B.PRICE AS TOTAL_SALES FROM BOOK AS B LEFT JOIN BOOK_SALES AS BS ON B.BOOK_ID = BS.BOOK_ID JOIN AUTHOR AS A ON A.AUTHOR_ID = B.AUTHOR_ID WHERE BS.SALES_DATE IS NOT NULL AND BS.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31' GROUP BY B.AUTHOR_ID, B.CATEGORY ORDER BY B.AUTHOR_ID ASC, B.CATEGORY DESC 처음에 이 쿼리로 풀었는데 오답이 났다. BOOK 과 AUTHOR , BOOK_SALES 테이블을 JOIN하고 AUTHOR_ID 와 CATEGORY 로 GROUP BY 하고 (저자, 카테고리)별로 총 판매량에 가격을 곱해주어 총 판매금을 구했다. 코드로 봤을 때에는 어디서 오류가 난건지 파악이 안되서 각 테이블을 출력해봤다. BOOK 테이블을 뜯어봤을 때 어디서 틀렸는지 감을 잡았다. SELET * FROM BOOK (AUTHOR_ID, CATEGORY)로 그룹화하면 하나의 그룹 안에 여러 BOOK_ID 가 존재할 수 있고, 각 책의 PRICE 가 서로 다를 수 있다. BOOK_ID 1, 2만 보더라도 저자 1번이고 경제 카테고리인 책의 가격이 9000원과 12000원 두 가지가 있는데 GROUP BY 로 (저자, 카테고리) 하면 둘 중 어느 PRICE 를 써야될지 모르게 된다. 따라서 이 그룹에서 특정 BOOK의 PRICE를 그대로 사용해 SUM(SALES) * PRICE를 계산하는 것은 올바르지 않다. 결국 각 BOOK_ID별로 판매량 × 가격을 먼저 계산한 후, 이를 저자와 카테고리별로 합산해야 한다. 도무지 이 방법을 하나의 쿼리로 풀어낼 방법이 떠오르지 않아서 문제를 뜯어보았다 일단 BOOK_ID 별로 총 판매금을 구하고, 그 후에 (저자, 카테고리)별로 총 판매금을 구하는거다. 이렇게 생각하니까 의외로 간단했다. SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(BS.SALES) * B.PRICE AS TOTAL_SALES FROM BOOK AS B LEFT JOIN BOOK_SALES AS BS ON B.BOOK_ID = BS.BOOK_ID JOIN AUTHOR AS A ON A.AUTHOR_ID = B.AUTHOR_ID WHERE BS.SALES_DATE IS NOT NULL AND BS.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31' GROUP BY B.BOOK_ID 먼저 BOOK_ID 별로 총 판매금을 구한다. 그리고 BOOK_ID 별로 총 판매금을 구한 테이블을 다시 한번 (AUTHOR_ID, CATEGORY)로 GROUP BY 하고 그룹별로 총 판매금 합을 구한다. SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(TOTAL_SALES) AS TOTAL_SALES FROM( SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(BS.SALES) * B.PRICE AS TOTAL_SALES FROM BOOK AS B LEFT JOIN BOOK_SALES AS BS ON B.BOOK_ID = BS.BOOK_ID JOIN AUTHOR AS A ON A.AUTHOR_ID = B.AUTHOR_ID WHERE BS.SALES_DATE IS NOT NULL AND BS.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31' GROUP BY B.BOOK_ID ) AS T1 GROUP BY AUTHOR_ID, CATEGORY ORDER BY AUTHOR_ID ASC, CATEGORY DESC 🧐후기 GPT한테 피드백 받아보니까 이 쿼리가 훨씬 낫다 왜 어렵게 풀었지 SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(BS.SALES * B.PRICE) AS TOTAL_SALES FROM BOOK B JOIN BOOK_SALES BS ON B.BOOK_ID = BS.BOOK_ID JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID WHERE BS.SALES_DATE >= '2022-01-01' AND BS.SALES_DATE < '2022-02-01' GROUP BY B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY ORDER BY B.AUTHOR_ID ASC, B.CATEGORY DESC;