2023-03-09 목요일

💡 저자 별 카테고리 별 매출액 집계하기

---

💡 문제 해석

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

---

💡 요약

1. 2022년 1월 기준
2. 판매량 구하기
   => 판매량 * 판매가 <명칭 : TOTAL_SALES>
3. GROUP BY 저자, 카테고리
4. SELECT 저자ID, 저자명, 카테고리, 매출액
5. ORDER BY 저자ID ASC, 카테고리 DESC

---

👀 테이블 전체 출력

1.1. BOOK 테이블

SELECT * FROM BOOK

💻 출력

💡 BOOK 테이블 설명

• BOOK_ID - INTEGER(N) - 도서ID
• CATEGORY - VARCHAR(N) - 카테고리 (경제, 인문, 소설, 생활, 기술)
• AUTHOR_ID - INTEGER - 저자ID
• PRICE - INTEGER - 판매가(원)
• PUBLISHED_DATE - DATE - 출판일

1.2. AUTHOR 테이블

SELECT * FROM AUTHOR

💻 출력

💡 AUTHOR 테이블 설명

• AUTHOR_ID - INTEGER - 저자ID
• AUTHOR_NAME - VARCHAR(N) - 저자명

1.3. BOOK_SALES 테이블

SELECT * FROM BOOK_SALES

💻 출력

💡 BOOK_SALES 테이블 설명

• BOOK_ID - INTEGER - 도서ID
• SALES_DATE - DATE - 판매일
• SALES - INTEGER - 판매량

---

1. GROUP BY별로 매출액(TOTAL_SALES) 구하기

GROUP BY를 사용해서 AUTHOR.author_id(저자ID)와 BOOK.category(카테고리)를 묶어주었다.

• AUTHOR.author_id : 저자별 매출액
• BOOK.category : 카테고리 별 매출액

💻 결론

1. SUM

SUM( BOOK_SALES.sales * BOOK.price ) AS TOTAL_SALES

2. GROUP BY

GROUP BY AUTHOR.author_id, BOOK.category

💭 생각의 흐름

전체 매출액 (BOOK.sales * BOOK.price) 을 저자, 카테고리별로 더했다.

SELECT SUM(TOTAL_SALES)
(생략)
GROUP BY AUTHOR.author_id

SELECT SUM(TOTAL_SALES)
(생략)
GROUP BY BOOK.category

---

2. LEFT JOIN을 활용하여 세 개의 테이블 묶기

FROM BOOK
    LEFT JOIN BOOK_SALES ON BOOK.book_id = BOOK_SALES.book_id
    LEFT JOIN AUTHOR ON BOOK.author_id = AUTHOR.author_id

💡 해석

BOOK 테이블의 book_id와 author_id 컬럼들을 기준으로 BOOK_SALES, AUTHOR 테이블을 묶어주었다.

---

3. '2022-01'월 기준으로 조회하기

WHERE DATE_FORMAT(BOOK_SALES.sales_date, '%Y-%m') = '2022-01'

💡 해석

DATE_FORMAT() 함수를 활용해서 연도와 월만 출력하였으며 WHERE 조건을 걸어 '2022-01' 기준으로 조회를 했다.

---

4. SELECT author_id, author_name, category, sales 출력

SELECT AUTHOR.author_id
        , AUTHOR.author_name
        , BOOK.CATEGORY
        , SUM( BOOK_SALES.sales * BOOK.price ) AS TOTAL_SALES
FROM BOOK
    LEFT JOIN BOOK_SALES ON BOOK.book_id = BOOK_SALES.book_id
    LEFT JOIN AUTHOR ON BOOK.author_id = AUTHOR.author_id
WHERE DATE_FORMAT(BOOK_SALES.sales_date, '%Y-%m') = '2022-01'
GROUP BY AUTHOR.author_id, BOOK.category

💻 출력

---

5. ORDER BY 저자ID ASC, 카테고리 DESC

SELECT AUTHOR.author_id
        , AUTHOR.author_name
        , BOOK.CATEGORY
        , SUM( BOOK_SALES.sales * BOOK.price ) AS TOTAL_SALES
FROM BOOK
    LEFT JOIN BOOK_SALES ON BOOK.book_id = BOOK_SALES.book_id
    LEFT JOIN AUTHOR ON BOOK.author_id = AUTHOR.author_id
WHERE DATE_FORMAT(BOOK_SALES.sales_date, '%Y-%m') = '2022-01'
GROUP BY AUTHOR.author_id, BOOK.category
ORDER BY AUTHOR.author_id ASC, BOOK.category DESC

💻 출력

---

💡 chatGPT 사용

SELECT 
    b.AUTHOR_ID, 
    a.AUTHOR_NAME, 
    b.CATEGORY, 
    SUM(b.PRICE * s.SALES) AS SALES
FROM 
    BOOK b
    JOIN AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_ID
    JOIN BOOK_SALES s ON b.BOOK_ID = s.BOOK_ID
WHERE 
    s.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY 
    b.AUTHOR_ID, 
    a.AUTHOR_NAME, 
    b.CATEGORY
ORDER BY 
    b.AUTHOR_ID ASC, 
    b.CATEGORY DESC

💻 chatGPT 해설

1. 먼저, BOOK, AUTHOR, BOOK_SALES 테이블을 조인합니다.
   조인 시에는 도서 정보 테이블(BOOK)과 저자 정보 테이블(AUTHOR)을 AUTHOR_ID로 조인하고, 판매 정보 테이블(BOOK_SALES)은 도서 ID(BOOK_ID)로 조인합니다.
2. WHERE 절을 사용하여 2022년 1월의 판매 정보만을 가져옵니다.
   이를 위해서는 판매 정보 테이블의 SALES_DATE 컬럼이 2022년 1월인 데이터만 가져오도록 설정합니다.
3. GROUP BY 절을 사용하여, 저자 별(AUTHOR_ID), 카테고리 별(CATEGORY)로 그룹화합니다.
4. 판매량(SALES)과 판매가(PRICE)를 곱하여 매출액(TOTAL_SALES)을 계산하기 위해, SUM 함수를 사용합니다.
5. ORDER BY 절을 사용하여 저자 ID를 오름차순으로 정렬하고, 저자 ID가 같다면 카테고리를 내림차순으로 정렬합니다.

차이점

• WHERE 절에서 DATE_FORMAT 함수 대신 BETWEEN a AND b 함수를 사용
• GROUP BY 로 AUTHOR_NAME 까지 묶었다
  => 해당 행을 제외해도 정답으로 출력됨