[SQL] Programmers 문제 풀기(저자 별 카테고리 별 매출액 집계하기)

해당 문제는 Programmers에서 사용한 문제이며 모든 테이블의 자료와 출처는 Programmers임을 밝힙니다.

프로그래머스

해당 글에서는 집계함수와 GROUP BY를 이용하여 푸는 문제로 초보자가 하기에는 보통 정도의 난이도를 가지고 있는 것 같습니다.

해당 문제 원본을 확인 하시려면 해당 사이트를 들어가시면 될 것 같습니다.

 

시작하겠습니다.

---

 

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

해당 문제에서는 테이블을 잘 확인 해야 합니다. 왜냐하면 해당 테이블에서 저자와 카테고리가 같지만 BOOK ID가 다른 경우가 있기 때문입니다. (BOOK_ID가 다르면 그것은 다른 책으로 가격도 다르게 된다.)

예시

GROUP BY 시 PRICE 변화

원래 12000원과 9000원이 있어야 하는데 GROUP BY로 인해 테이블이 합쳐진 것을 볼 수 있습니다.

하지만, 그룹 내에 BOOK_ID와 PRICE는 따로 존재 합니다. (아래 이미지 참고)

SELECT a.AUTHOR_NAME,
       b.CATEGORY,
       GROUP_CONCAT(bs.BOOK_ID) AS BOOK_IDS,
       GROUP_CONCAT(b.PRICE) AS PRICES
FROM BOOK b
JOIN AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_ID
JOIN BOOK_SALES bs ON bs.BOOK_ID = b.BOOK_ID
GROUP BY b.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY;

그룹 내 분포 확인

이를 생각하지 않고 SUM(SALES) * PRICE를 하게 되면 먼저 수량을 더하고 PRICE는 특정 하나의 값만 곱해지는 것입니다. 

이 경우를 생각하지 않고 집계 함수를 사용하시면 계속 틀리게 될 것입니다.

 

1. 모든 테이블 JOIN 해주기

SELECT *
FROM BOOK b
        JOIN
     AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_ID
        JOIN
     BOOK_SALES bs ON bs.BOOK_ID = b.BOOK_ID
WHERE DATE_FORMAT(bs.SALES_DATE, '2022-01-%d') = DATE_FORMAT(bs.SALES_DATE, '%Y-%m-%d')

이렇게 3개의 테이블을 JOIN을 통해 합쳐 주고 DATE_FORMAT을 통해 2022년 1월의 값들만 가져와 주었습니다.

---

2. GROUP BY를 통해 값들 묶어주기

SELECT *
FROM BOOK b
        JOIN
     AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_ID
        JOIN
     BOOK_SALES bs ON bs.BOOK_ID = b.BOOK_ID
WHERE DATE_FORMAT(bs.SALES_DATE, '2022-01-%d') = DATE_FORMAT(bs.SALES_DATE, '%Y-%m-%d')
GROUP BY b.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY

 

우선 3개의 값을 묶어준 이유는 문제에서 저자별, 카테고리 별로 묶어서 계산하라했기 때문입니다.

저자를 의미하는(ID, NAME) 두 개의 컬럼을 넣어 주었으나 혹시나 모를 동명이인 때문에 ID도 추가로 넣어 주었습니다.

(ID만 넣어도 될 것 같긴 하지만요ㅎㅎ)

코드 결과

---

3.  SELECT 절에 문제에서 요구하는 값들만 추출하기

SELECT b.AUTHOR_ID
     , a.AUTHOR_NAME
     , b.CATEGORY
     , SUM(bs.SALES * b.PRICE) AS TOTAL_SALES
FROM BOOK b
        JOIN
     AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_ID
        JOIN
     BOOK_SALES bs ON bs.BOOK_ID = b.BOOK_ID
WHERE DATE_FORMAT(bs.SALES_DATE, '2022-01-%d') = DATE_FORMAT(bs.SALES_DATE, '%Y-%m-%d')
GROUP BY b.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY 
ORDER BY b.AUTHOR_ID ASC, b.CATEGORY DESC

해당 쿼리에서 중요한 점은 집계함수 사용 방법입니다. 

SUM(bs.SALES * b.PRICE) 이렇게 하여 각 책의 PRICE 마다의 SALES(판매 수량)합을 곱해주는 방법을 사용해주었습니다.(각 행의 값들을 곱하고 더하고를 반복합니다.)

위의 그룹 내 분포 이미지를 보시면서 생각하면 더 편할 것입니다.

 

마지막으로 문제 조건에 맞는 정렬을 넣어주면 해당 코드는 정답이 됩니다!!

---

이상으로 Programmers 문제 풀이를 마치도록 하겠습니다.