[SQL] 주문량이 많은 아이스크림들 조회하기

문제링크

https://school.programmers.co.kr/learn/courses/30/lessons/133027

프로그래머스

1. 핵심

• 풀이
  • FIRST_HALF에서 FLAVOR는 기본키(primary key)이므로 중복이 발생하지 않는다. 하지만 JULY의 경우 예시에서 볼 수 있는 것처럼 FLAVOR가 기본키가 아니라서 중복되는 '맛'(Strawberry)이 존재한다. 따라서 '맛'에 대해서 상반기와 7월을 더하기 위해서는 7월에SHIPMENT_ID는 달라도 '맛'이 같은 경우는 합쳐야한다. 따라서 JULY 테이블에서 '맛'을 기준으로 group by을 한다 → 코드 중 temp
  • temp와 FIRST_HALF은 FLAVOR가 중복없이 존재한다. 따라서 이를 기준으로 group by하고 상반기 + 7월의 판매량을 합산하는 테이블을 만든다. → 코드 중 temp2
• SHIPMENT_ID 기준으로 FIRST_HALF와 JULY inner join하게 되었을 때 왜 틀릴까?

FIRST_HALF

JULY

F.SHIPMENT_ID = J.SHIPMENT_ID - inner join의 결과

• Inner join의 결과 테이블을 보면 JULY 테이블의 SHIPMENT_ID 209인 strawberry(220개)가 사라진 것을 볼 수 있다. 따라서 '맛'을 모두 살리기 위해서는 inner join, left outer join, right outer join을 모두 포함하는 full join을 해야한다. full join의 결과를 보면 맛이 다 살아있다.

-- left join 과 right join union해서 full join 생성
select *
from FIRST_HALF F
left outer join JULY J on F.SHIPMENT_ID = J.SHIPMENT_ID
union
select *
from FIRST_HALF F
right outer join JULY J on F.SHIPMENT_ID = J.SHIPMENT_ID;
-- full join 지원안함
select *
from FIRST_HALF F
full join JULY J on F.SHIPMENT_ID = J.SHIPMENT_ID;

• full join의 결과를 보면 7월 SHIPMENT_ID를 기준으로 right join을 하면 '맛'이 사라지는 경우는 없을 것이다. 결과 테이블을 보자.

right join의 결과 테이블

• 이 테이블을 보면 RIGHT JOIN - SHIPMENT_ID 을 통해서 풀 수 있을 것 같다....3번 풀이가 right join을 이용한 풀이이다. 자세한 내용은 밑에서 다루겠다. 

2. 코드(MySQL)

1. FLAVOR 기준 - inner join 사용하기

# 7월의 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량의 합
# 상위 3개 '맛' 조회
# !주의점!
# > FIRST_HALF 에서 flavor는 기본키이므로 맛의 중복이 없다.
# > 하지만 JULY에서는 예시에서 보는 것처럼 맛의 중복이 발생하고 있다. 따라서 이걸 처리해야한다. - temp
with temp as (
    select FLAVOR, sum(TOTAL_ORDER) as TOTAL_ORDER
    from JULY
    group by FLAVOR
),

temp2 as (
    select H.FLAVOR, H.TOTAL_ORDER + T.TOTAL_ORDER as TOTAL_ORDER
    from FIRST_HALF H
    inner join temp T on H.FLAVOR = T.FLAVOR
    order by TOTAL_ORDER desc
)

select FLAVOR
from temp2
limit 3;

---

2. FLAVOR 기준 - union all 사용하기

with temp as (
    select FLAVOR, sum(TOTAL_ORDER) as TOTAL_ORDER
    from FIRST_HALF
    group by FLAVOR

    union all

    select FLAVOR, sum(TOTAL_ORDER) as TOTAL_ORDER
    from JULY
    group by FLAVOR
),
temp2 as (
    select FLAVOR, sum(TOTAL_ORDER) as TOTAL_ORDER
    from temp
    group by FLAVOR
    order by TOTAL_ORDER desc
)

select FLAVOR
from temp2
limit 3;

 

---

3. SHIPMENT_ID 와 OUTER JOIN 사용하기

• JULY의 FLAVOR들을 모두 살려야하므로 RIGHT JOIN - SHIPMENT_ID 을 통해서 JULY 테이블을 모두 살려둔다. 그리고 '맛'으로 sum을 해서 TOTAL_ORDER을 구해야하는데 이때 JULY의 FLAVOR 기준으로 group by 해야한다. 그래야 중복된 FLAVOR을 합칠 수 있다.
• 그런데 RIGHT JOIN을 하다보니 HALF 테이블에 없는 SHIPMENT_ID 는 빈 행이 될 것이다.
• 따라서 H.TOTAL_ORDER에 없는 행의 경우 0을 더하도록 IFNULL을 사용해준다.

with temp as (
    select J.FLAVOR, sum(ifnull(H.TOTAL_ORDER, 0) + J.TOTAL_ORDER) as TOTAL_ORDER
    from FIRST_HALF H
    right outer join JULY J on H.SHIPMENT_ID = J.SHIPMENT_ID
    group by J.FLAVOR
    order by TOTAL_ORDER desc
)

select FLAVOR
from temp
limit 3;