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

문제 설명

다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 7월의 아이스크림 주문 정보를 담은 JULY 테이블입니다.

FIRST_HALF 테이블

Column name	Type	Nullable	Description
SHIPMENT_ID	INT(N)	FALSE	출하 번호
FLAVOR	VARCHAR(N)	FALSE	아이스크림 맛
TOTAL_ORDER	INT(N)	FALSE	상반기 아이스크림 총주문량

FIRST_HALF 테이블의 SHIPMENT_ID는 JULY 테이블의 SHIPMENT_ID의 외래 키입니다.

JULY 테이블

Column name	Type	Nullable	Description
SHIPMENT_ID	INT(N)	FALSE	출하 번호
FLAVOR	VARCHAR(N)	FALSE	아이스크림 맛
TOTAL_ORDER	INT(N)	FALSE	7월 아이스크림 총주문량

JULY 테이블의 기본 키는 SHIPMENT_I입니다. 7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다.

---

문제

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

---

풀이 과정

1. 조건 확인
   • 7월 주문량(JULY.TOTAL_ORDER)과 상반기 주문량(FIRST_HALF.TOTAL_ORDER)의 합산 값이 큰 순서대로 상위 3개의 FLAVOR 조회
   • FLAVOR는 FIRST_HALF 테이블의 기본 키이며, JULY 테이블에서는 중복 가능
2. 테이블 결합 (JOIN)
   • FIRST_HALF와 JULY 테이블의 FLAVOR를 기준으로 FULL OUTER JOIN
   • FULL OUTER JOIN 선택 이유: 두 테이블 중 하나에만 있는 맛(FLAVOR)도 결과에 포함해야 하기 때문에 두 테이블 모두에 존재하는 FLAVOR 조회 할 수 있도록
3. NULL값 처리
   • 두 테이블 중 데이터가 없는 경우를 처리하기 위해 COALESCE 사용
     
     • COALESCE(F.FLAVOR, J.FLAVOR) : 두 테이블 중 하나의 FLAVOR를 우선 반환.
       
     • COALESCE(F.TOTAL_ORDER, 0) 및 COALESCE(J.TOTAL_ORDER, 0) : 상반기 또는 7월 주문량이 없는 경우 0으로 처리.
       이를 통해 상반기 주문량 + 7월 주문량을 정확히 계산 가능
4. 총 주문량 계산
   • TOTAL_ORDER로 alias
   • COALESCE(F.TOTAL_ORDER, 0) + COALESCE(J.TOTAL_ORDER, 0) : 각 맛별 총 주문량 계산
5. 정렬 조건 적용
   • 총 주문량(TOTAL_ORDER) 기준으로 내림차순 정렬
   • 상위 3개의 맛을 가져오기 위해 FETCH FIRST 3 ROWS ONLY 사용
6. 최종 결과 출력
   • 최종적으로 결과에는 아이스크림 맛(FLAVOR)만 출력

• 교훈
  • COALESCE 함수 <–> NVL 함수
    기존에 나는 NVL을 주로 사용해왔다.. 다른 DBMS에서도 사용 가능한 COALESCE 함수를 사용할 수 있도록 익숙해지자.
    • COALESCE vs NVL
      - COALESCE : 여러 인수를 받아 첫 번째로 NULL이 아닌 값을 반환, 표준 SQL 함수로 다양한 DBMS에서 지원됨
      ex) COALESCE(value1, value2, value3, ..., default_value) : 모든 값이 NULL인 경우, 마지막 default_value 반환
      - NVL: 두 개의 인수를 받아 첫 번째 인수가 NULL인 경우 두 번째 인수를 반환, 오라클 전용 함수로 다른 DBMS에서는 지원되지 않음
      ex) NVL(value1, 0)

---

정답

SELECT 
    F.FLAVOR
FROM 
    FIRST_HALF F
LEFT JOIN (
    SELECT 
        FLAVOR, 
        SUM(TOTAL_ORDER) AS JULY_TOTAL_ORDER
    FROM 
        JULY
    GROUP BY 
        FLAVOR
) J
ON 
    F.FLAVOR = J.FLAVOR
ORDER BY 
    F.TOTAL_ORDER + COALESCE(J.JULY_TOTAL_ORDER, 0) DESC
FETCH FIRST 3 ROWS ONLY;

---

Tip

1. 상반기와 7월 주문량 합산
   • F.TOTAL_ORDER + COALESCE(SUM(J.TOTAL_ORDER), 0) : FIRST_HALF 테이블의 주문량과 JULY 테이블의 주문량 합계를 계산
2. NULL 처리
   • FULL OUTER JOIN 했기 때문에 다른 테이블의 값이 존재하지 않을 경우 NUL로 표시됨
   • 이를 처리하기 위해 COALESCE 사용하여 7월 주문량이 없는 경우 0으로 처리
3. 맛별로 그룹화
   • GROUP BY F.FLAVOR, F.TOTAL_ORDER : FLAVOR를 기준으로 데이터를 그룹화하여 주문량 합계 계산

SQL Dialect Used: Oracle

---

개념 확인

• COALESCE 함수
  • NULL 값을 처리하기 위해 사용하며, 여러 인수 중 첫 번째로 NULL이 아닌 값을 반환
  • WHERE 컬럼명 BETWEEN 500000 AND 1999999
• FULL OUTER JOIN
  • 두 테이블의 모든 데이터를 포함하여 결합
  • 한쪽 테이블에만 존재하는 데이터도 결과에 포함되며, 다른 테이블의 값은 NULL로 표시됨
• GROUP 함수
  • 특정 컬럼 값을 기준으로 데이터를 그룹화하여 집계 계산 수행
  • SUM : 그룹화된 데이터를 합산하여 결과를 반환
• FETCH FIRST n ROWS ONLY
  • 결과에서 상위 n개의 행만 가져오는 절
  • Oracle 12c 이상에서는 이 구문을 사용하며, Oracle 11g 이하에서는 ROWNUM <= 3로 대체할 수 있음
  • MYSQL에서는 LIMIT n;

---

Useful link

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