[프로그래머스] 오프라인/온라인 판매 데이터 통합하기

 

프로그래머스 - 코딩테스트 - SQL 고득점 Kit의 SELECT 문제를 전부 해설하지는 않을 예정입니다.

이전 게시글에서는 문제를 풀며 기본적인 내용을 설명했으며, 이를 바탕으로 전반적인 문제를 해결할 수 있을 것입니다.

이제는 그 외의 새로운 문법을 사용하는 문제들만 다룰 것이다.

 

1. 문제 설명

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

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

 

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

 

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 조합에 대해서는 하나의 판매 데이터만 존재합니다.

 

2. 문제

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

 

3. 예시

예를 들어 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

 

4. 정답

SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT FROM (             SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT             FROM ONLINE_SALE             WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'             UNION ALL                     SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT             FROM OFFLINE_SALE             WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31' ) AS SALES_DATA ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

 

- SQL 작성 단계

• FROM ONLINE_SALE : ONLINE_SALE 테이블에서 2022년 3월에 해당하는 데이터 (SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31')를 조회한다.
• FROM OFFLINE_SALE : OFFLINE_SALE 테이블에서 2022년 3월에 해당하는 데이터 (SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31')를 조회하고, USER_ID 값은 NULL로 설정한다.
• UNION ALL : 두 테이블에서 조회된 데이터를 결합한다. UNION ALL을 사용하여 중복을 제거하지 않고 모두 가져온다.
• ORDER BY : SALES_DATE, PRODUCT_ID, USER_ID 순으로 오름차순 정렬한다.

 

5. 설명(서브쿼리(Subquery))

- 서브쿼리

 SQL 안에 포함된 또 다른 쿼리를 말한다. 서브쿼리는 외부 쿼리(메인 쿼리)의 일부로 사용되며, 그 결과를 외부 쿼리에서 참조하거나 처리하는 방식으로 동작한다. 서브쿼리는 일반적으로 소괄호 안에 작성되며, 마치 하나의 테이블처럼 결과를 반환한다.

 

- 서브쿼리 사용 목적

• 데이터 필터링 : 외부 쿼리에서 특정 조건을 만족하는 데이터를 찾기 위해 사용
• 계산 또는 집계 : 내부 쿼리에서 집계된 결과를 외부 쿼리에서 참조
• 데이터 결합 : 다른 테이블이나 다른 쿼리 결과를 이용해 데이터를 결합

- 서브쿼리 종류

서브쿼리는 주로 WHERE, FROM, SELECT 절에서 사용되며, 사용되는 위치에 따라 성격이 다르다.

 

[스칼라 서브쿼리(Scalar Subquery)]

• 단일 값을 반환하는 서브쿼리로, 보통 WHERE, SELECT, HAVING 절에서 사용된다.

SELECT product_id, sales_amount FROM sales WHERE sales_amount > (     SELECT AVG(sales_amount)     FROM sales );

• 서브쿼리에서 평균 판매량을 계산하고, 외부 쿼리에서 이 값보다 큰 판매량을 가진 상품을 필터링한다.
• 이 경우 서브쿼리는 단일 값(평균 값)을 반환한다.

[다중 행 서브쿼리(Multi-row Subquery)]

• 다중 행 서브쿼리는 여러 행을 반환할 수 있다. 주로 IN, ANY, ALL 연산자와 함께 사용된다.

SELECT product_name FROM products WHERE category_id IN (     SELECT category_id     FROM categories     WHERE category_name = 'Electronics' );

• 서브쿼리는 'Electronics' 카테고리의 모든 카테고리 ID를 반환하고, 외부 쿼리는 그 ID에 해당하는 상품을 조회한다.

[다중 열 서브쿼리(Multi-column Subquery)]

• 다중 열 서브쿼리는 여러 열을 반환한다. IN, EXISTS 연산자와 함께 사용되며, 복합 조건을 처리할 때 유용하다.

SELECT product_id, sales_date FROM sales WHERE (product_id, sales_date) IN (     SELECT product_id, MAX(sales_date)     FROM sales     GROUP BY product_id );

• 서브쿼리는 각 상품의 최대 판매일을 반환하며, 외부 쿼리는 해당 날짜에 판매된 상품을 조회한다.

[상관 서브쿼리(Correlated Subquery)]

• 외부 쿼리의 데이터를 참조하는 서브쿼리이다. 즉, 외부 쿼리는 각 행에 대해 서브쿼리가 한 번씩 실행된다. 상관 서브쿼리는 일반적인 서브쿼리와 달리, 외부 쿼리의 데이터를 필요로 하기 때문에 각 행마다 동적으로 실행된다.

SELECT product_id, sales_amount FROM sales s1 WHERE sales_amount > (     SELECT AVG(sales_amount)     FROM sales s2     WHERE s1.category_id = s2.category_id );

• 쿼리에서 서브쿼리는 s1의 category_id와 같은 값을 가진 s2의 평균 판매량을 계산한다. 이처럼 상관 서브쿼리는 외부 쿼리의 각 행에 대해 서브쿼리가 실행된다.

[인라인 뷰(Inline View)]

• FROM 절에서 서브쿼리를 사용하는 방식으로, 임시 테이블처럼 서브쿼리를 활용한다. 이를 통해 데이터를 미리 필터링하거나 계산한 뒤, 외부 쿼리에서 사용할 수 있다.

SELECT product_id, sales_amount FROM (     SELECT product_id, sales_amount     FROM sales     WHERE sales_amount >= 100 ) AS high_sales ORDER BY sales_amount DESC;

• 서브쿼리에서 조건에 맞는 데이터를 먼저 필터링하고, 외부 쿼리에서 이 결과를 임시 테이블로 사용하여 정렬된 데이터를 조회한다.

 

6. 설명(UNION & UNION ALL)

[UNION]

 SQL에서 두 개 이상의 SELECT 쿼리를 결합하여 중복을 제거한 결과 집합을 반환하는 연산자이다. 각 SELECT 쿼리의 결과가 하나의 테이블처럼 합쳐져 최종 결과가 도출된다. UNION을 사용할 때 주의할 점은 각 SELECT 쿼리의 열의 개수와 데이터 타입이 동일해야 한다는 것이다.

 

- UNION 특징

• 중복 제거 : UNION은 기본적으로 중복된 행을 제고하고 결과를 반환한다.
• 같은 개수와 데이터 타입 : 각 쿼리에서 반환하는 열의 개수와 데이터 타입이 동일해야 한다
• 열 이름 : 최종 결과의 열 이름은 첫 번째 쿼리에서 사용된 열 이름을 따른다.
• 정렬 가능 : 최종 결과에서 ORDER BY 절을 사용해 절렬할 수 있다. ORDER BY는 전체 결과에 적용하며, 개별 SELECT 문에서는 사용할 수 없다.

SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;

• table1과 table2에서 column1과 column2를 선택하고, 두 테이블의 결과를 결합하여 중복된 값을 제거한 후 결과를 반환합니다.

- 예시 : 두 테이블에서 중복을 제거하여 합치기

SELECT name FROM students UNION SELECT name FROM teachers;

• students 테이블과 teachers 테이블에서 name 열을 결합한 후, 중복된 이름을 제거하여 하나의 결과 집합으로 반환한다.

[UNION ALL]

 UNION과 비슷하지만 중복을 제거하지 않고 모든 결과를 반환하는 연산자가 UNION ALL이다. UNION ALL은 중복된 행까지 모두 포함하여 결과를 반환하기 때문에, 더 빠르게 실행되는 장점이 있다.

SELECT name FROM students UNION ALL SELECT name FROM teachers;

• students와 teachers 테이블에서 중복된 이름을 제거하지 않고, 모든 이름을 반환한다.

- UNION과 JOIN의 차이

• UNION : 두 쿼리의 결과 집합을 세로로 합쳐서 반환한다. 예를 들어, 두 테이블의 동일한 열에 대해 각각의 결과를 결합하는 것이 목적이다.
• JOIN : 두 테이블을 가로로 결합하여 각 테이블의 연관된 데이터를 함께 출력하는 것이 목적이다. 즉, 두 테이블 사이의 특정 조건을 기준으로 데이터를 조합한다.

UNION	JOIN
두 개 이상의 결과 집합을 수직으로 결합	두 테이블을 수평으로 결합
각 쿼리의 결과가 서로 독립적	두 테이블 간 관계를 기반으로 결합
중복된 데이터는 제거(UNION), 또는 포함(UNION ALL)	중복된 데이터 허용
같은 개수와 데이터 타입의 열만 허용	테이블 간의 관계만 충족하면 됨