[SQL] 상품 별 오프라인 매출 구하기(프로그래머스/MySQL/Level 2)

SMALL

안녕하세요! 데코입니다!

오늘은 프로그래머스 코딩테스트 연습에 있는 "상품 별 오프라인 매출 구하기" 문제를 포스팅하려고 합니다!

바로 포스팅 시작할게요! :)

(출처 : https://school.programmers.co.kr/learn/courses/30/lessons/131533)

1. 문제 : 상품 별 오프라인 매출 구하기(Lv. 2)

2. 문제 설명

다음은 어느 의류 쇼핑몰에서 판매중인 상품들의 상품 정보를 담은 PRODUCT 테이블과

오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 입니다.

PRODUCT 테이블은 아래와 같은 구조로 PRODUCT_ID, PRODUCT_CODE, PRICE는

각각 상품 ID, 상품코드, 판매가를 나타냅니다.

[ 테이블 구조('PRODUCT') ]

COLUMN NAME	TYPE	NULLABLE
PRODUCT_ID	INTEGER	FALSE
PRODUCT_CODE	VARCHAR(8)	FALSE
PRICE	INTEGER	FALSE

상품 별로 중복되지 않는 8자리 상품코드 값을 가지며, 앞 2자리는 카테고리 코드를 의미합니다.

OFFLINE_SALE 테이블은 아래와 같은 구조로 되어있며

OFFLINE_SALE_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는

각각 오프라인 상품 판매 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

[ 테이블 구조('OFFLINE_SALE') ]

COLUMN NAME	TYPE	NULLABLE
OFFLINE_SALE_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOINT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

동일한 날짜, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

[ 문제 ]

PRODUCT 테이블과 OFFLINE_SALE 테이블에서

상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요.

결과는 매출액을 기준으로 내림차순 정렬해주시고

매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

[ 예시 ]

예를 들어 PRODUCT 테이블이 다음과 같고

PRODUCT_ID	PRODUCT_CODE	PRICE
1	인문	10000
2	경제	9000
3	경제	9000

OFFLINE_SALE 테이블이 다음과 같다면

OFFLINE_SALE_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	2	2022-02-21
2	1	2	2022-03-02
3	3	3	2022-05-01
4	2	1	2022-05-24
5	1	2	2022-07-14
6	2	1	2022-09-22

각 상품 별 총 판매량과 판매가는 다음과 같습니다.

- PRODUCT_CODE 가 A1000011인 상품은 총판매량이 6개, 판매가가 15,000원

- PRODUCT_CODE 가 A1000045인 상품은 총판매량이 2개, 판매가가 8,000원

- PRODUCT_CODE 가 C3000002인 상품은 총판매량이 3개, 판매가가 42,000원

그러므로 각 상품 별 매출액을 계산하고 정렬하면 결과가 다음과 같이 나와야 합니다.

[ 출력 결과 ]

PRODUCT_CODE	SALES
C3000002	126000
A1000011	90000
A1000045	16000

3. 문제 풀이

-- 1안 
SELECT  A.PRODUCT_CODE
        , (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
  FROM  PRODUCT A
  LEFT
  JOIN  (
        SELECT  PRODUCT_ID
                , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
          FROM  OFFLINE_SALE B
         GROUP
            BY  PRODUCT_ID
        ) B
    ON  A.PRODUCT_ID = B.PRODUCT_ID
 ORDER
    BY  SALES DESC
        , PRODUCT_CODE ASC

-- 2안
SELECT  P.PRODUCT_CODE
        , P.PRICE*SUM(SALES_AMOUNT) AS SALES
  FROM  OFFLINE_SALE AS S 
  LEFT 
  JOIN  PRODUCT AS P 
    ON  S.PRODUCT_ID = P.PRODUCT_ID
 GROUP 
    BY  P.PRODUCT_CODE
 ORDER
    BY  SALES DESC
        , PRODUCT_CODE ASC;

이번 문제는 총 2안 두 가지로 작성했습니다!

# 1안 풀이 설명

# [ 문제 접근 설명 ]

문제를 읽고 조건에 맞는 쿼리를 작성하기 위해서 다음과 같이 네 가지 단계로 진행했어요!

1. OFFLINE_SALE 테이블에서 PRODUCT_ID별 판매 수량 집계하기(GROUP BY)
2. PRODUCT 테이블과 조인하기(JOIN)
3. 상품코드 별 매출액 계산
4. 매출액, 상품코드 기준으로 정렬

# [ 1안 쿼리 작성 설명 ]

1 첫 번째 단계에서는 OFFLINE_SALE 테이블에서 PRODUCT_ID별 판매 수량을 집계했습니다!

GROUP BY를 통해 집계해서 각 상품별 수량을 산출하였습니다.

SELECT PRODUCT_ID
, SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
FROM OFFLINE_SALE B
GROUP
BY PRODUCT_ID

2 두 번째 단계에서는 첫 단계에서 진행한 결과를 서브쿼리로 넣고,

PRODUCT 테이블을 기준으로 LEFT JOIN을 해주었습니다.

이 결과를 통해 각 상품별 총판매의 개수와, 상품별 금액을 알 수 있는 정보로 가공이 되었습니다.

SELECT *
FROM  PRODUCT A
LEFT
JOIN (
      SELECT PRODUCT_ID
, SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
FROM OFFLINE_SALE B
GROUP
BY PRODUCT_ID
) B
ON  A.PRODUCT_ID = B.PRODUCT_ID

3 세 번째 작업은 상품코드 별 매출액(판매가 * 판매량) 합계를 산출해 주었습니다!

SELECT *
, (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
FROM  PRODUCT A
LEFT
JOIN (
             SELECT PRODUCT_ID
, SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
FROM OFFLINE_SALE B
GROUP
BY PRODUCT_ID
) B
ON  A.PRODUCT_ID = B.PRODUCT_ID

4 마지막으로는 요구사항에 맞게 정렬했어요!

매출액(ASALES) 기준 내림차순으로, 그리고 상품코드(PRODUCT_CODE) 기준 오름차순으로 정렬했습니다!

SELECT *
, (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
FROM  PRODUCT A
LEFT
JOIN (
             SELECT PRODUCT_ID
, SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
FROM OFFLINE_SALE B
GROUP
BY PRODUCT_ID
) B
ON A.PRODUCT_ID = B.PRODUCT_ID
ORDER
BY  SALES DESC
, PRODUCT_CODE ASC

# 2안 풀이 설명

# [ 문제 접근 설명 ]

문제를 읽고 조건에 맞는 쿼리를 작성하기 위해서 다음과 같이 네 가지 단계로 진행했어요!

1. OFFLINE_SALE 테이블을 기준으로 PRODUCT 테이블과 조인하기(JOIN)
2. PRODUCT_CODE 별로 집계하기(GROUP BY)
3. 상품코드 별 매출액 계산
4. 매출액, 상품코드 기준으로 정렬

# [ 2안 쿼리 작성 설명 ]

1 첫 번째 단계에서는 OFFLINE_SALE 테이블을 기준으로 PRODUCT 테이블과 조인했습니다!

1안은 PRODUCT_ID별 팔린 수량을 집계한 후 가격 정보와 JOIN을 해서 테이블을 구성했다면,

2안은 OFFLINE_SALE_ID별 가격정보를 JOIN을 해서 테이블을 구성했습니다.

SELECT *
  FROM  OFFLINE_SALE AS S
LEFT
JOIN  PRODUCT AS P
ON  S.PRODUCT_ID = P.PRODUCT_ID

2 3 두 번째와 세 번째 단계에서는 첫 번째 단계에서 구성한 테이블을 대상으로 GROUP BY 집계를 해주었습니다.

PRODUCT_CODE별로 GROUP BY를 이용하고

P.PRICE*SUM(SALES_AMOUNT) AS SALES를 를 통해 상품 코드별 매출액을 산출했습니다!

SELECT  P.PRODUCT_CODE
, P.PRICE*SUM(SALES_AMOUNT) AS SALES
FROM  OFFLINE_SALE AS S
LEFT
JOIN  PRODUCT AS P
ON  S.PRODUCT_ID = P.PRODUCT_ID
GROUP
BY  P.PRODUCT_CODE

4 마지막으로는 요구사항에 맞게 정렬했어요!

1안에서 작성한 쿼리와 동일하게 매출액(ASALES) 기준 내림차순으로, 그리고 상품코드(PRODUCT_CODE) 기준 오름차순으로 정렬했습니다!

SELECT  P.PRODUCT_CODE
, P.PRICE*SUM(SALES_AMOUNT) AS SALES
FROM  OFFLINE_SALE AS S
LEFT
JOIN  PRODUCT AS P
ON  S.PRODUCT_ID = P.PRODUCT_ID
GROUP
BY  P.PRODUCT_CODE
ORDER
BY  SALES DESC
, PRODUCT_CODE ASC;

4. 데코의 문제 풀이 후기

이번 문제에서 개인적으로 2안보다는 1안이 더 효율적이라고 생각이 듭니다.

먼저, 1안은 OFFLINE_SALE 테이블에서 먼저 필요한 집계 작업을 수행합니다.

이 작업을 통해 필요한 정보를 미리 추출함으로써 효율적인 질의 실행을 가능하게 합니다.

GROUP BY 절을 사용하여 각 제품별로 총판매량을 계산하는 것은 이러한 효율성을 제공합니다.

또한, 1안은 두 테이블을 조인하기 전에 집계된 데이터를 활용합니다.

이는 데이터의 크기를 줄이고 조인 연산을 최적화하는 데 도움이 됩니다.

반면에 2안은 두 테이블을 조인한 후에 집계를 수행하는데, 이로 인해 불필요한 데이터가 생성되고 계산 비용이 커질 수 있습니다.

실제 서비스에서 대규모의 데이터가 발생했다고 가정을 했을 때,

1안이 효율적일 것으로 예상이 되네요!

이 외에는 이전 문제에서 모두 다룬 것들을 이용해서

따로 코멘트할 것은 없습니다!

혹시나 더 좋은 쿼리나 어렵게 느껴지신 부분이 있다면 댓글로 남겨주세요!

이번 문제는 2개의 테이블을 결합하고 연산해서 요구사항에 맞는 쿼리를 작성해 보았습니다!

설명이 어려운 부분 혹은 잘 이해가 안 가는 부분

그리고 더 궁금한 내용이 있다면

언제든지 댓글로 남겨주세요!

빠르게 궁금증을 해결해 드릴게요!

공감과 댓글은 큰 힘이 됩니다!

오늘도 블로그 방문해 주시고

포스팅 읽어주셔서 감사합니다!

LIST

저작자표시 비영리 변경금지

'Skill Sets > SQL' 카테고리의 다른 글

[SQL] 년, 월, 성별 별 상품 구매 회원 수 구하기(프로그래머스/MySQL/Level 4) (56)	2023.11.04
[SQL] SQL 쿼리 양식 및 작성 규칙 (66)	2023.10.21
[SQL] 진료과별 총 예약 횟수 출력하기(프로그래머스/MySQL/Level 2) (60)	2023.09.19
[SQL] 흉부외과 또는 일반외과 의사 목록 출력하기(프로그래머스/MySQL/Level 1) (70)	2023.09.13
[SQL] 취소되지 않은 진료 예약 조회하기(프로그래머스/MySQL/Level 4) (51)	2023.09.01

BigData + Data Analyst + Daily

[SQL] 상품 별 오프라인 매출 구하기(프로그래머스/MySQL/Level 2)

1. 문제 : 상품 별 오프라인 매출 구하기(Lv. 2)