<프로그래머스 64> 5월의 식품들의 총매출 조회하기(Lv.4)

 

프로그래머스에서 제공하는 SQL문제 매일 풀기 챌린지

---

 

문제 설명

다음은 식품의 정보를 담은 FOOD_PRODUCT 테이블과 식품의 주문 정보를 담은 FOOD_ORDER 테이블입니다. FOOD_PRODUCT 테이블은 다음과 같으며RODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미합니다.

 

Column name	Type	Nullable
PRODUCT_ID	VARCHAR(10)	FALSE
PRODUCT_NAME	VARCHAR(50)	FALSE
PRODUCT_CD	VARCHAR(10)	TRUE
CATEGORY	VARCHAR(10)	TRUE
PRICE	NUMBER	TRUE

 

FOOD_ORDER 테이블은 다음과 같으며ORDER_ID, PRODUCT_ID, AMOUNT, PRODUCE_DATE, IN_DATE, OUT_DATE, FACTORY_ID, WAREHOUSE_ID는 각각 주문 ID, 제품 ID, 주문량, 생산일자, 입고일자, 출고일자, 공장 ID, 창고 ID를 의미합니다

 

Column name	Type	Nullable
ORDER_ID	VARCHAR(10)	FALSE
PRODUCT_ID	VARCHAR(5)	FALSE
AMOUNT	NUMBER	FALSE
PRODUCE_DATE	DATE	TRUE
IN_DATE	DATE	TRUE
OUT_DATE	DATE	TRUE
FACTORY_ID	VARCHAR(10)	FALSE
WAREHOUSE_ID	VARCHAR(10)	FALSE

---

문제

FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

---

예시

FOOD_PRODUCT 테이블이 다음과 같고

 

[ FOOD_PRODUCT ]

PRODUCT_ID	PRODUCT_NAME	PRODUCT_CD	CATEGORY	PRICE
P0011	맛있는콩기름	CD_OL00001	식용유	4880
P0012	맛있는올리브유	CD_OL00002	식용유	7200
P0013	맛있는포도씨유	CD_OL00003	식용유	5950
P0014	맛있는마조유	CD_OL00004	식용유	8950
P0015	맛있는화조유	CD_OL00005	식용유	8800
P0016	맛있는참기름	CD_OL00006	식용유	7100
P0017	맛있는들기름	CD_OL00007	식용유	7900
P0018	맛있는고추기름	CD_OL00008	식용유	6100
P0019	맛있는카놀라유	CD_OL00009	식용유	5100
P0020	맛있는산초유	CD_OL00010	식용유	6500

 

FOOD_ORDER 테이블이 다음과 같을 때

 

[ FOOD_ORDER ]

ORDER_ID	PRODUCT_ID	AMOUNT	PRODUCE_DATE	IN_DATE	OUT_DATE	FACTORY_ID	WAREHOUSE_ID
OD00000056	P0012	1000	2022-04-04	2022-04-21	2022-04-25	FT19980002	WH0032
OD00000057	P0014	2500	2022-04-14	2022-04-27	2022-05-01	FT19980002	WH0033
OD00000058	P0017	1200	2022-05-19	2022-05-28	2022-05-28	FT20070002	WH0033
OD00000059	P0017	1000	2022-05-24	2022-05-30	2022-05-30	FT20070002	WH0038
OD00000060	P0019	2000	2022-05-29	2022-06-08	2022-06-08	FT20070002	WH0035

 

따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.

PRODUCT_ID	PRODUCT_NAME	TOTAL_SALES
P0017	맛있는들기름	17380000
P0019	맛있는카놀라유	10200000

 

---

 

♥ 오라클 정답

select product_id, product_name, (price * sum_amount) total_sales
    from (select o.product_id, p.product_name, p.price, sum(o.amount) as sum_amount
        from food_product p, food_order o
        where p.product_id = o.product_id
          and produce_date between to_date('20220501', 'rrrrmmdd') and to_date('20220531', 'rrrrmmdd')
        group by o.product_id, p.product_name, p.price) d
    order by total_sales desc, product_id asc;

 

♥   MySQL 정답

select product_id, product_name, (price * sum_amount) total_sales
    from (select o.product_id, p.product_name, p.price, sum(o.amount) as sum_amount
        from food_product p, food_order o
        where p.product_id = o.product_id
          and produce_date between str_to_date('20220501', '%Y%m%d') and str_to_date('20220531', '%Y%m%d')
        group by o.product_id, p.product_name, p.price) d
    order by total_sales desc, product_id asc;

 

 

---

■ EQUI JOIN

조인은 여러 개의 테이블의 컬럼들을 활용하여 한 번에 원하는 것을 출력하고자 할 때 사용하는 문법이다.

 

그 중 EQUI JOIN 이란, 

조인하려는 테이블 사이에 공통된 컬럼이 있을 경우 (컬럼명은 달라도 되고, 데이터가 공통될 때)

EQUAL(=) 조인하는 조인 문법이다.

 

ORACLE과 MySQL의 차이점은

MySQL에서는 IN LINE VIEW에 별칭을 지정해줘야 오류가 발생하지 않는 것이다.

ORACLE은 별칭이 없어도 에러가 발생하지 않았다.

 

■ TO_DATE( [컬럼명/문자열], '변경할 데이터 형태')   =  Oracle
   위의 함수를 사용해서 문자 혹은 숫자 데이터 형식을 → 날짜형 데이터 형식으로 변환한다.

   * 표시한 형태 해석(오라클의 경우 대소문자 구분 없음)
   - RRRR/YYYY : Year 연도 4자리로 표기(ex. 2023, 2024)
   - MM : Month 월 2자리로 표기 (ex. 05, 07)
   - DD : Day 일 2자리로 표기 (ex. 01, 31)

 

 

■ STR_TO_DATE( [컬럼명/문자열], '변경할 데이터 형태')   = MySQL

   위의 함수를 사용해서 문자 혹은 숫자 데이터 형식을 → 날짜형 데이터 형식으로 변환한다.

 

   * 표시한 형태 해석(대소문자 구분 있음)
   - %Y : Year 연도를 4자리로 표기 (ex. 2023, 2024)
   - %m : Month 월을 2자리로 표기(ex. 05, 12)
   - %d : Day 일을 두 자리로 표기(ex. 04, 28)

* 참고
   - %c :Month 월을 한 자리로 표기(ex. 5, 12) 
   - %e :Day 일을 한자리로 표기(ex. 4, 28)

---

♥  정답 해설

이 문제는 total_sales라는 총매출 컬럼을 구하는게 중요하다.total_sales 컬럼을 구하기 위해서는 아래와 같은 산술연산이 필요하다.

 

TOTAL_SALES = (가격 * 총량) 

 

여기서 가격은 food_product 테이블에 존재하는 price 컬럼이며총량을 구하기 위해서는 food_order 테이블의 존재하는 amount를 활용해야한다.왜냐하면 amount 데이터 자체는 주문id에 따라 분산되어있기 때문이다.

 

따라서 product_id별 amount의 총 합을 구하여 아래와 같이 산술연산을 수행해야한다.

 

TOTAL_SALES = ( PRICE * SUM(AMOUNT))

 

# 1. IN LINE VIEW  

 

IN LINE VIEW를 사용해서

생산일자가 2022년 5월인 식품들의 식품id별 주문량의 합을 구한다. 

 

그러기 위해서는 product_id를 group by로 묶어주어야한다.

이 때 메인 쿼리의 최종 출력에 필요한 컬럼인 product_name과  price 컬럼도 필요하기 때문에

product_id를 이용하여 food_product테이블과 food_order 테이블을 equi join 해준다.

 

이때 product_id별 sum(amount)를 하기 위해 그룹함수인 sum을 사용하는데, 

위에 추가해준 컬럼들도 group by에 함께 적어주어야 에러가 나지 않는다. 

 

생산일자가 2022년 5월인 데이터만 출력하기 위해 between .. and를 사용해준다.

 

WHERE절 서브쿼리를 사용해서

대여 시작일(START_DATE)을 기준으로 2022년 8월 ~ 2022년 10월까지

총 대여횟수가 5회 이상인 자동차들에 대해서 출력한다.

 

 

 

● BETWEEN .. AND

BETWEEN .. AND 조건을 사용하여,

2022년 05월 01일 ~ 2022년 05월  31일 데이터만 추출한다.

이때 날짜 조건을 주기 위해서 to_date함수를 이용하여

문자형 데이터를 날짜형 데이터로 바꿔주는 것이다.

 

여기서 주의할 점은 DATE TYPE의 기본 구조는

년월일 시간:분:초 인데, 시간:분:초가 00:00:00으로 되어있다는 것이다.

따라서 2022년 05월 31일 00:00:00초까지의 값만 조건으로 걸러지는 것이기 때문에

+ 1을 해주어야 한다. 

왜냐하면 2022년 05월 31일 23:59:59초에 판매된 데이터도 포함되게 해야 하기 때문이다.

 

하지만, 제공된 데이터에 따르면 PRODUCE_DATE 의 모든 날짜가 

00:00:00 시간인 것만 존재하기 때문에 다른 5월 31일의 값은 신경 쓸 필요가 없다.

오히려 +1을 해주면, 2022년 6월 1일 00:00:00의 데이터도 

조건에 충족되기 때문에 +1을 해주면 오히려 틀린 정답이 된다.

 

produce_date between to_date('20220501', 'rrrrmmdd') and to_date('20220531', 'rrrrmmdd')

 

 

# 2. MAIN QUERY 

 

-- SELECT절

출력해야하는 컬럼을 나열하는데, 

TOTAL_SALES 컬럼을 IN LINE VIEW에서 출력한 SUM_AMOUNT 를 사용하여 완성해준다. 

 

TOTAL_SALES = ( PRICE * SUM(AMOUNT))

 

 

-- FROM 절

마지막으로 TOTAL_SALES를 기준으로 DESC하게 정렬해주고,

TOTAL_SAELS의 값이 같다면 PRODUCT_ID를 기준으로 ASC하게 정렬해준다.

 

 

 

출처 : 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/challenges