데이터 웨어하우스, SQL, 데이터분석 (4)

JOIN

• 두 개 혹은 그 이상의 테이블들을 공통 필드를 가지고 머지하는데 사용
• Star 스키마 형태로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용
• 다양한 종류의 JOIN

출처 : https://theartofpostgresql.com/blog/2019-09-sql-joins/

• JOIN 문법
   - JOIN 앞의 빈 칸에 INNER, FULL, LEFT, RIGHT, CROSS의 다양한 형태의 JOIN이 들어감

SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON a.key1 = B.key1 and A.key2 = B.key2

---

JOIN 시 고려해야할 점

• 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 확인
   - 가장 중요한 작업!
• JOIN 하는 테이블들간의 관계를 명확하게 정의
  • one to one
  • one to many (=many to one)
  • many to many
• 어느 테이블을 베이스로 잡을지 (From에 사용할지) 결정해야함

---

예제 테이블

• 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

• raw_data_Alert

AlterID	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

---

INNER JOIN

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

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	2020-01-02	101

---

LEFT JOIN (=RIGHT JOIN 방향만 다른 차이)

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

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	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	WightIncrease	2020-01-02	101

---

FULL JOIN

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

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	2020-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

---

CROSS JOIN

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

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	4	WeightIncrease	2020-01-01	101
100	3	2020-01-02	78	1	4	WeightIncrease	2020-01-01	101
101	2	2020-01-01	90	1	4	WeightIncrease	2020-01-01	101
101	4	2020-01-02	95	1	4	WeightIncrease	2020-01-02	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

---

SELF JOIN

• 동일한 테이블들을 alias를 달리하여 자기 자신과 JOIN

SELECT * FROM raw_data.Vital v1
JOIN raw_data.Vital v2 ON v1.VitalID = v2.VitalID;

• 결과

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

---

 

 

BOOLEAN 타입 처리

• True와 False 두 가지 존재
• 다음 표현들은 동일할 표현
   - WHERE 또는 CASE WHEN에 사용이 가능
  
  • flag = True
  • flag is True
• 다음 표현들은 같은 표현이 아님
   - SQL의 값들 중에서는 True, False만 있는 것이 아닌 NULL도 존재하기 때문
  
  • flag is True
  • flag is not False
• raw_data.boolean_test 테이블

flag

True

False

True

NULL

False

 

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

---

NULL 비교

• NULL 비교는 항상 IS 또는 IS NOT으로 수행
• NULL 비교를 = 혹은 !=, <, > 으로 수행하면 잘못된 결과가 나옴
• raw_data.boolean_test 테이블

flag

True

False

True

NULL

False

SELECT COUNT(1)
FROM raw_data.boolean_test
WHERE flag is NULL;
-- Return : 1

SELECT COUNT(1)
FROM raw_data.boolean_test
WHERE flag = NULL;
-- Return : 0 (에러가 발생하지도 않음)