[데브코스 TIL] DAY25 데이터 웨어하우스와 SQL 기본(4)

오늘의 학습 주제

---

1. JOIN

 

1. JOIN

---

1. JOIN이란

• SQL 조인은 두 개 혹은 그 이상의 테이블들을 공통 필드를 가지고 머지하는데 사용된다. 이는 스타 스키마로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용된다.
• 대부분 INNER JOIN과 LEFT JOIN을 사용
• 왼쪽 테이블을 LEFT라고 하고 오른쪽 테이블을 RIGHT이라고 하자. JOIN의 결과는 방식에 상관없이 양쪽의 필드를 모두 가진 새로운 테이블을 만들어내게 된다.
• 조인의 방식에 따라 다음 두 가지가 달라진다:
  • 1. 어떤 레코드들이 선택되는지?
  • 2. 어떤 필드들이 채워지는지?

• CROSS JOIN : 양 테이블의 모든 combination에 대해서 merge (JOIN 조건 의미 없음)
• SELF JOIN : 동일한 테이블 2개를 JOIN

2. JOIN 문법

SELECT A.*, B.*
  FROM raw_data.table1 A
  ____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2
 WHERE A.ts >= '2019-01-01';

____ = INNER, FULL, LEFT, RIGHT, CROSS

• JOIN시 고려해야할 점
  • 먼저 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크
    • 아주 중요함
  • 조인하는 테이블들간의 관계를 명확하게 정의
    • One to one
      • 완전한 one to one: user_session_channel & session_timestamp
      • 한쪽이 부분집합이 되는 one to one: user_session_channel & session_transaction
    • One to many? (order vs order_items)
      • 이 경우 중복이 더 큰 문제됨 -> 증폭
    • Many to one?
      • 방향만 바꾸면 One to many로 보는 것과 사실상 동일
    • Many to many?
      • 이런 경우는 많지 않으며 이는 one to one이나 one to many로 바꾸는 것이 가능하다면 변환하여 조인하는 것이 덜 위험
  • 어느 테이블을 베이스로 잡을지 (From에 사용할지) 결정해야함

3. JOIN 종류

예시1.raw_data.Vital

UserID	VitalID	Date	Weight
100	1	2020-01-01	75
100	3	2020-01-02	78
101	2	2020-01-01	90
101	4	2020-01-02	95

예시2.raw_data.Alert

AlertID	VitalID	AlertType	Date	UserID
1	4	WeightIncrease	2020-01-02	101
2	NULL	MissingVital	2020-01-04	100
3	NULL	MissingVital	2020-01-04	101

→ VitalID 기준으로 JOIN

• INNER JOIN
  1. 양쪽 테이블에서 매치가 되는 레코드들만 리턴함
  2. 양쪽 테이블의 필드가 모두 채워진 상태로 리턴됨

SELECT * FROM raw_data.Vital v
JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

v.UserID	v.VitalID	v.Date	v.Weight	a.AlertID	a.VitalID	a.AlertType	a.Date	a.UserID
101	4	2020-01-02	95	1	4	WeightIncrease	2021-01-02	101

• LEFT JOIN (RIGHT JOIN)
  1. 왼쪽 테이블(Base)의 모든 레코드들을 리턴함
  2. 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴됨

SELECT * FROM raw_data.Vital v
LEFT JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

v.UserID	v.VitalID	v.Date	v.Weight	a.AlertID	a.VitalID	a.AlertType	a.Date	a.UserID
100	1	2020-01-01	75	NULL	NULL	NULL	NULL	NULL
100	3	2020-01-02	78	NULL	NULL	NULL	NULL	NULL
101	2	2020-01-01	90	NULL	NULL	NULL	NULL	NULL
101	4	2020-01-02	95	1	4	WeightIncrease	2021-01-02	101

• FULL OUTER JOIN
  1. 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들을 리턴함
  2. 매칭되는 경우에만 양쪽 테이블들의 모든 필드들이 채워진 상태로 리턴됨

SELECT * FROM raw_data.Vital v
FULL JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

v.UserID	v.VitalID	v.Date	v.Weight	a.AlertID	a.VitalID	a.AlertType	a.Date	a.UserID
100	1	2020-01-01	75	NULL	NULL	NULL	NULL	NULL
100	3	2020-01-02	78	NULL	NULL	NULL	NULL	NULL
101	2	2020-01-01	90	NULL	NULL	NULL	NULL	NULL
101	4	2020-01-02	95	1	4	WeightIncrease	2021-01-02	101
NULL	NULL	NULL	NULL	2	NULL	MissingVital	2020-01-04	100
NULL	NULL	NULL	NULL	3	NULL	MissingVital	2020-01-04	101

• SELF JOIN
  1. 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴함

SELECT * FROM raw_data.Vital v CROSS JOIN raw_data.Alert a;

v.UserID	v.VitalID	v.Date	v.Weight	a.AlertID	a.VitalID	a.AlertType	a.Date	a.UserID
100	1	2020-01-01	75	1	4WeightIncrease	2020-01-01	101
100	3	2020-01-02	78	1	4WeightIncrease	2020-01-01	101
101	2	2020-01-01	90	1	4WeightIncrease	2020-01-01	101
101	4	2020-01-02	95	1	4WeightIncrease	2020-01-01	101
100	1	2020-01-01	75	2	MissingVital	2020-01-04	100
100	3	2020-01-02	78	2	MissingVital	2020-01-04	100
101	2	2020-01-01	90	2	MissingVital	2020-01-04	100
101	4	2020-01-02	95	2	MissingVital	2020-01-04	100
100	1	2020-01-01	75	3	MissingVital	2020-01-04	101
100	3	2020-01-02	78	3	MissingVital	2020-01-04	101
101	2	2020-01-01	90	3	MissingVital	2020-01-04	101
101	4	2020-01-02	95	3	MissingVital	2020-01-04	101

• CROSS JOIN
  1. 동일한 테이블을 alias를 달리해서 자기 자신과 조인함
  
  

---

- BOOLEAN 타입 처리

flag (raw_data.boolean_test)
True
False
True
NULL
False

• True or False
• 다음 2개는 동일한 표현
  • flag = True
  • flag is True
• 다음 2개는 동일한 표현인가? 항상 동일하지는 않다. NULL 때문에
  • flag is True
  • flag is not False

SELECT
COUNT(CASE WHEN flag = True THEN 1 END) true_cnt1, # 2
COUNT(CASE WHEN flag is True THEN 1 END) true_cnt2, # 2
COUNT(CASE WHEN flag is not False THEN 1 END) not_false_cnt # 3
FROM raw_data.boolean_test;

- NULL 비교

• NULL 비교는 항상 IS 혹은 IS NOT으로 수행
• NULL 비교를 = 혹은 != 혹은 <>으로 수행하면 잘못된 결과가 나옴

SELECT COUNT(1)
FROM raw_data.boolean_test
WHERE flag is NULL; # 1

SELECT COUNT(1)
FROM raw_data.boolean_test
WHERE flag = NULL; # NULL(매칭이안됨)

- NULLIF

• paidUsers/uniqueUsers
  • 0으로 나누는 경우 divide by 0 에러 발생
  • 이를 어떻게 방지할까? NULLIF를 사용하여 0을 NULL로 변경
    • paidUsers/NULLIF(uniqueUsers, 0)
    • 다시 한번 사칙연산에 NULL이 들어가면 결과도 NULL이 됨을 기억

- 숙제풀이
- 채널별 월 매출액 테이블 만들기 - 이제 conversionRate을 추가해보자
  - 첫 번째 시도:
    - paidUsers/uniqueUsers AS conversionRate
	    # 정수 타입이기 때문에 0 또는 1 출력. 따라서 type 변경 필요
  - 두 번째 시도:
    - paidUsers::float/uniqueUsers AS conversionRate
    	# ::float float으로 변경
  - 세 번째 시도:
    - ROUND(paidUsers*100.0/uniqueUsers, 2) AS conversionRate
    	# 실수 * 정수 = 실수
  - 네 번째 시도:
    - ROUND(paidUsers*100.0/NULLIF(uniqueUsers, 0), 2) AS conversionRate
    	# 분모가 0인 경우를 고려

- COALESCE

• NULL 값을 다른 값으로 바꿔주는 함수
  • 즉 NULL대신에 다른 백업값을 리턴해주는 함수
• COALESCE(exp1, exp2, exp3, …)
  • exp1부터 인자를 하나씩 살펴서 NULL이 아닌 값이 나오면 그것을 리턴
  • 끝까지 갔는데도 모두 NULL이면 최종적으로 NULL을 리턴

value(raw_data.count_test)
NULL
1
1
0
0
4
3

SELECT value,
	   COALESCE(value, 0) -- value가 NULL이면 0을 리턴
  FROM raw_data.count_test;

- 공백 혹은 예약키워드를 필드 이름으로 사용하려면?

• ""로 둘러싸서 사용

CREATE TABLE test (
group int primary key,
'mailing address' varchar(32)
);