<프로그래머스 72> 오프라인/온라인 판매 데이터 통합하기(Lv.4)

 

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

---

 

문제 설명

다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블과 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 입니다. ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며
 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다

 

Column name	Type	Nullable
ONLINE_SALE_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOUNT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

 

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

Column name	Type	Nullable
OFFLINE_SALE_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOUNT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

 

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

---

문제

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

---

예시

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

 

[ ONLINE_SALE ]

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	3	2	2022-02-25
2	4	4	1	2022-03-01
4	2	2	2	2022-03-02
3	6	3	3	2022-03-02
5	5	5	1	2022-03-03
6	5	7	1	2022-04-06

 

[ OFFLINE_SALE ]  테이블이 다음과 같다면

OFFLINE_SALE_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	2	2022-02-21
4	1	2	2022-03-01
3	3	3	2022-03-01
2	4	1	2022-03-01
5	2	1	2022-03-03
6	2	1	2022-04-01

 

 

각 테이블의 2022년 3월의 판매 데이터를 합쳐서, 정렬한 결과는 다음과 같아야 합니다.

SALES_DATE	PRODUCT_ID	USER_ID	SALES_AMOUNT
2022-03-01	1	NULL	2
2022-03-01	3	NULL	3
2022-03-01	4	NULL	1
2022-03-01	4	4	1
2022-03-02	2	2	2
2022-03-02	3	6	3
2022-03-03	2	NULL	1
2022-03-03	5	5	1

 

 

---

이 문제는 
집합 연산자 UNION ALL
을 사용해서 푸는 것이 핵심이다.

 

---

♥ 오라클 정답 

select to_char(sales_date, 'rrrr-mm-dd') sales_date, product_id, user_id, sales_amount
    from online_sale
    where sales_date between to_date('20220301', 'rrrrmmdd') and to_date('20220331', 'rrrrmmdd')
union all
select to_char(sales_date, 'rrrr-mm-dd') sales_date, product_id, null as user_id, sales_amount
    from offline_sale
    where sales_date between to_date('20220301', 'rrrrmmdd') and to_date('20220331', 'rrrrmmdd')
    order by sales_date asc, product_id asc, user_id asc;

♥   MySQL 정답

select date_format(sales_date, '%Y-%m-%d') sales_date, product_id, user_id, sales_amount
    from online_sale
    where sales_date between str_to_date('20220301', '%Y%m%d') and str_to_date('20220331', '%Y%m%d')
union all
select date_format(sales_date, '%Y-%m-%d') sales_date, product_id, null as user_id, sales_amount
    from offline_sale
    where sales_date between str_to_date('20220301', '%Y%m%d') and str_to_date('20220331', '%Y%m%d')
    order by sales_date asc, product_id asc, user_id asc;

 

 

---

■ 집합 연산자 UNION ALL 

집합 연산자 UNION ALL은 합집합을 의미한다.

UNION과 UNION ALL의 차이는 UNION 은 중복 데이터까지 제거되어 출력된다는 점이다.

 

◈ 집합 연산자 사용할 때 주의 사항

1. 위의 SQL과 아래의 SQL 컬럼 개수가 동일해야한다.

2. 위의 SQL과 아래의 SQL의 컬럼의 데이터 유형이 동일해야한다.

3. 위의 SQL의 컬럼명으로 결과가 출력된다.

4. ORDER BY 절은 맨 아래에 있는 쿼리문에만 사용할 수 있다. 

 

 

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

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

   - DAY : 요일 ( ex. 금요일)

   - DY : 요일 ( ex. 금)

   - D : 요일을 숫자로 표현 ( 1은 일요일을 의미하며, 2는 월요일을 의미한다.)

   - HH : 12시간 형식으로 나타내는 시간을 의미 

   - HH24 : 24시간 형식으로 나타내는 시간을 의미

   - MI : 분

   - SS : 초

   - AM/PM : 오전/오후

 

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

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

 

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

   - %H : 시간을 24시간 형식으로 표기

   - %h : 시간을 12시간 형식으로 표기

   - %i : 분

   - %s : 초

   - %p : 오전 또는 오후를 나타낸다.

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

---

정답 해설

이 문제는 ONLINE_SALE 테이블과 OFFLINE_SALE 테이블의 2022년 3월 데이터를단순하게 위 아래로 합쳐서 출력하는 결과를 원하고 있다.중복 데이터를 제거할 필요도 없이 그냥 합치는 것이기 때문에여기서 필요한 것은 UNION ALL이라는 집합연산자이다. 위에서 설명한 집합연산자의 주의사항에 잘 맞추어 결과를 출력하는 것이 포인트이다.

 

 

# 1번 쿼리문 

 

-- SELECT절

출력해야할 컬럼인

판매 날짜(SALES_DATE), 상품 ID(PRODUCT_ID), 유저 ID(USER_ID), 판매량(SALES_AMOUNT)을 작성해준다.

이때, SALES_DATE 컬럼은 출력예시와 데이터 형식을 동일하게 맞춰주기 위해서

Oracle의 경우에는 TO_CHAR()를 사용하여 날짜형 데이터 형식을 문자형으로 맞춰주고,

MySQL의 경우에는 DATE_FORMAT()을 사용하여 데이터 형식을 맞춰준다.

 

 

-- FROM절

ONLINE_SALE 테이블에 대한 내용을 가져올 것이기 때문에

ONLINE_SALE 테이블명을 기입해준다.

 

 

-- WHERE절

2022년 3월의 데이터만 출력하기 위해서

SALES_DATE에 제한을 걸어주는데, 이때 BETWEEN AND를 사용해주었다.

( TO_CHAR 혹은 DATE_FORMAT을 사용해도 된다)

 

 

# 2번 쿼리문 

 

-- SELECT절

출력해야할 컬럼인

판매 날짜(SALES_DATE), 상품 ID(PRODUCT_ID),  판매량(SALES_AMOUNT)을 작성해준다.

이때, SALES_DATE 컬럼은 출력예시와 데이터 형식을 동일하게 맞춰주기 위해서

Oracle의 경우에는 TO_CHAR()를 사용하여 날짜형 데이터 형식을 문자형으로 맞춰주고,

MySQL의 경우에는 DATE_FORMAT()을 사용하여 데이터 형식을 맞춰준다.

 

여기서 주의해야할 것은,

1번 쿼리문과 2번 쿼리문의 컬럼 개수가 동일해야 집합연산자를 사용할 수 있다는 것이다.

문제를 보면, OFFLINE_SALE 테이블의 판매 데이터의 유저 ID(USER_ID) 값은 NULL로 표시되도록 조건을 주었기 때문에 NULL AS USER_ID로 컬럼의 개수를 일치시켜준다.

 

-- FROM절

OFFLINE_SALE 테이블에 대한 내용을 가져올 것이기 때문에

OFFLINE_SALE 테이블명을 기입해준다.

 

 

-- WHERE절

2022년 3월의 데이터만 출력하기 위해서

SALES_DATE에 제한을 걸어주는데, 이때 BETWEEN AND를 사용해주었다.

( TO_CHAR 혹은 DATE_FORMAT을 사용해도 된다)

 

 

-- ORDER BY절

정렬은 맨 마지막에 있는 쿼리문 아래에서 진행해주면 된다.

SALES_DATE를 기준으로 오름차순 정렬하고,

동일하다면 PRODUCT_ID를 기준으로 정렬하고, 

이것마저 동일하다면 USER_ID를 기준으로 정렬하도록 조건을 넣어준다.

---

 

 

 

 

 

 

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