[Programmers / MySQL] 오프라인/온라인 판매 데이터 통합하기 (Level 4)

📌 문제

문제 설명

다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블과 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 입니다.

ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

 

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

OFFLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 OFFLINE_SALE_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 오프라인 상품 판매 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

 

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

문제

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

---

📌 나의 풀이

Code

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM (
    SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM ONLINE_SALE
    UNION ALL
    SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
    FROM OFFLINE_SALE
) AS UNION_SALE
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

Solution

1. UNION ALL을 사용하여 ONLINE_SALE 테이블과 OFFLINE_SALE 테이블을 위아래로 붙이기

• ONLINE_SALE 테이블에서는 SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT만 가져오기
• OFFLINE_SALE 테이블에서는 SALES_DATE, PRODUCT_ID, SALES_AMOUNT를 가져오고, NULL로 채워진 USER_ID라는 컬럼을 만들어 추가적으로 가져오기

2. UNION ALL이 적용된 테이블을 서브쿼리로 FROM문에 가져오기

 

3. SALES_DATE가 '2022-03'으로 시작되는 행만 필터링

 

4. SALES_DATE 기준으로 오름차순 정렬, 같은 값이 있는 경우 PRODUCT_ID 기준으로 오름차순 정렬, 또 같은 값이 있는 경우 USER_ID 기준으로 오름차순 정렬

 

5. YYYY-mm-dd 날짜 포맷을 갖춘 SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT 컬럼 출력

---

📌 다른 사람의 풀이

Code

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE AS N
WHERE SALES_DATE LIKE '2022-03%'
UNION
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE AS F
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

Solution

1. UNION을 사용하여 ONLINE_SALE 테이블과 OFFLINE_SALE 테이블을 위아래로 붙이기

• ONLINE_SALE 테이블에서는 SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT만 가져오기
• OFFLINE_SALE 테이블에서는 SALES_DATE, PRODUCT_ID, SALES_AMOUNT를 가져오고, NULL로 채워진 USER_ID라는 컬럼을 만들어 추가적으로 가져오기
• 두 테이블 각각 WHERE문을 통해 SALES_DATE가 '2022-03'으로 시작하는 행만 필터링
• SALES_DATE는 DATE_FORMAT 함수를 사용하여 YYYY-mm-dd로 포맷팅

2. SALES_DATE 기준으로 오름차순 정렬, 같은 값이 있는 경우 PRODUCT_ID 기준으로 오름차순 정렬, 또 같은 값이 있는 경우 USER_ID 기준으로 오름차순 정렬

---

배운 내용

1. 두 테이블을 세로로 합쳐야하는지, 가로로 합쳐야하는지를 잘 판단하자

• 가로로 합쳐야 할 때: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN
• 세로로 합쳐야 할 때: UNOIN, UNION ALL

2. UNION과 UNION ALL의 차이를 잘 기억하자

• UNION
  • 중복된 행은 제거됩니다. 즉, 동일한 데이터가 여러 번 있을 경우 하나만 남기고 나머지는 제거합니다.
  • 중복을 제거하기 때문에 추가적인 연산이 필요하여 UNION ALL에 비해 성능이 조금 느릴 수 있습니다.
• UNION ALL
  • 중복된 행을 제거하지 않고 모두 포함합니다.
  • 중복 제거 작업이 없기 때문에 UNION보다 빠르게 동작할 수 있습니다.

3. UNION과 UNION ALL을 수행할 때에는 두 테이블의 컬럼이 같아야 함

이 문제의 경우 OFFLINE_SALE 테이블에 USER_ID라는 컬럼이 없어서 매우 애를 먹었다.

UNION ALL을 사용하기 위해서는 NULL로만 채워진 USER_ID 컬럼을 만들어야 했었는데, 어떻게하는지 몰라서 찾아봤다.

 

정답은 허무할 정도로 쉬웠다.

SELECT문에 'NULL AS USER_ID'라고만 적으면 됐던 것이다.

잊지 않겠어.....

4. ORDER BY의 실행 순서는 가장 마지막임

UNION, UNION ALL도 예외는 없다.

애초에 UNION의 대상이 되는 테이블에는 ORDER BY를 사용할 수도 없고, 당연히 적용되지도 않는다.

그래서 UNION을 사용하면 ORDER BY는 강제적으로 코드의 가장 아래로 가게 되고, 실행 역시 제일 마지막에 수행된다.

 

<참고 - SQL 명령어 실행 순서>

1. FROM : 각 테이블 확인
2. ON : 조인 조건 확인
3. JOIN : 테이블 조인 (병합)
4. WHERE : 데이터 추출 조건 확인
5. GROUP BY : 특정 칼럼으로 데이터 그룹화
6. HAVING : 그룹화 이후 데이터 추출 조건 확인
7. SELECT : 데이터 추출
8. DISTINCT : 중복 제거
9. ORDER BY : 데이터 정렬