(※개인 공부 목적의 게시물입니다.)
프로그래머스 > sql > select > 오프라인/온라인 판매 데이터 통합하기
<문제설명>
다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 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 | 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 조합에 대해서는 하나의 판매 데이터만 존재합니다.
<문제>
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요.
OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요.
결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.
<풀이코드>
SELECT DATE_FORMAT(A. SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
A.PRODUCT_ID,
A.USER_ID,
A.SALES_AMOUNT
FROM ONLINE_SALE AS A
WHERE A.SALES_DATE LIKE '2022-03-%'
UNION ALL
SELECT DATE_FORMAT(B.SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
B.PRODUCT_ID,
NULL AS USER_ID,
B.SALES_AMOUNT
FROM OFFLINE_SALE AS B
WHERE B.SALES_DATE LIKE '2022-03-%'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;<설명>
- SELECT~ UNION ALL SELECT~ : 오프라인 판매데이터와 온라인 판매데이터를 함께 한테이블로 출력해야하므로 오프라인 테이블과 온라인 테이블의 합집합을 구함
[UNION/UNION ALL]
-
집합연산자[UNION, UNION ALL, INTERSECT, MINUS]
: 테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용
: 여러개의 질의 결과를 하나로 결합하는 방식 -
UNION : 중복행 제거O
-
UNION ALL : 중복행 제거X
-
UNION/UNION ALL 조건 : SELECT 컬럼개수, 컬럼순서, 데이터타입이 모두 같아야함(두 테이블이 수직으로 결합되기 때문)
-
각 테이블에 '별칭' 을 붙여서 'SELECT 별칭.컬럼명' 처럼 작성하는 이유
- UNION으로 결과는 합쳐지지만 UNION 이전에 각 SELECT는 서로 독립적으로 실행되기 때문, 어느 테이블의 컬럼인지 DB가 구분해야함
- JOIN으로 바뀌어도 안전함
- 가독성 측면에서 좋음
-
SELECT DATE_FORMAT(B.SALES_DATE, '%Y-%m-%d') AS SALES_DATE, B.PRODUCT_ID, NULL AS USER_ID, B.SALES_AMOUNT-
NULL AS USER_ID
: 첫번째로 나온 온라인 테이블에서 SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT 순으로 컬럼순서가 지정되므로, 두번째 오프라인 테이블에서도 이 컬럼순서를 지켜야함: 오프라인 테이블의 세번째 컬럼에는 USER_ID가 와야하는데 값이 존재하지 않으므로, NULL을 적어줌으로써 해당 순서컬럼에 NULL을 채운다는 의미가 됨
: NULL만 적어도 상관없지만, 가독성을 위해 '해당 순서의 NULL값들이 USER_ID컬럼의 값이다' 라는 의미로 AS USER_ID을 작성
: 이때 원본테이블은 바뀌지 않음(오프라인 테이블에 NULL값이 채워진 USER_ID컬럼이 생기지 않는다는 뜻)
-
-
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID- ORDER BY는 UNION 전체 결과 기준이므로, 별칭을 붙이지 않음
- ORDER BY는 UNION 전체 결과 기준이므로, 별칭을 붙이지 않음
-
WHERE A.SALES_DATE LIKE '2022-03-%'- 2022년 3월 데이터 조회를 위해 '2022-03-%' 문자열로 검색
※ 컬럼이 VARCHAR이고 날짜형식 문자열이면 LIKE를 사용해도 되지만, DATE/DATETIME 컬럼일 경우 추천하지 않음
- 2022년 3월 데이터 조회를 위해 '2022-03-%' 문자열로 검색
-> 코드 개선
실무에서 날짜 조회는 무조건 범위조건으로 적는 것이 좋음
#정석 코드O WHERE A.SALES_DATE >= DATE('2022-03-01') AND A.SALES_DATE < DATE('2022-04-01')#이렇게 쓰면X WHERE A.SALES_DATE <= DATE('2022-03-31') #2022-03-31 00:00:00까지만 포함되기 때문에 안됨
DATE()
: 날짜/시간 값에서 날짜(YY-MM-DD)부분만 추출하거나, 문자열을 DATE타입으로 변환하는 함수(컬럼에 DATE() 씌우면X)
: 날짜만 중요할때 사용
DATE('2022-03-15 14:23:10')-> 2022-03-15(뒤의 시간은 완전히 잘림)
DATE('2022-03-01')-> 2022-03-01(결과는 그래도지만 문자열이 아닌 DATE타입 값으로 인식됨)
날짜 데이터 타입 : [DATE/DATETIME/TIMESTAMP]
1. DATE
: 날짜만 있음(시간X)
YYYY-MM-DD
#실무 베스트 코드 #굳이 DATE함수를 적용하지 않고 DATE를 사용해서 날짜조건설정 가능 WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE < '2022-04-01' #DB가 암묵적으로 DATE('2022-03-01')이라고 처리해줌
2. DATETIME
: 날짜 + 시간
: 저장한 값 그대로 나옴(서버시간대가 바뀌어도 값 안바뀜)
: 로그, 거래시점 기록에 많이 사용('이 시각에 거래가 발생했다')
YYYY-MM-DD HH:MM:SS
3. TIMESTAMP
: 날짜 + 시간(시간대에 따라 변함)
: '시스템이 이 시각에 생성했다' 를 알고 싶을때 사용
YYYY-MM-DD HH:MM:SS
