[TIL - 24일 차] 데이터 웨어하우스와 SQL과 데이터 분석 (4)

JOIN 소개

JOIN이란?

두 개 이상의 테이블을 공통 필드를 가지고 merge하는 데 사용된다. 이는 스타 스키마로 구성된 테이블로 분산돼 있던 정보를 통합하는 데 사용된다. 6개의 JOIN 방법이 존재하지만, 대부분은 LEFT JOIN과 INNER JOIN으로 충분하다.

JOIN

JOIN 문법

JOIN 방식에 따라 ____에는 INNER, FULL, LEFT, RIGHT, CROSS가 들어갈 수 있다.

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';

JOIN 시 고려할 점

• 중복 레코드가 없고, Primary Key Uniqueness가 보장됨을 체크
• 조인하는 테이블들간의 관계를 명확하게 정의
  • One to one (1 : 1)
  • One to many (1 : N)
  • Many to one (N : 1)
  • Many to many (N : M)

다양한 종류의 JOIN

사용할 테이블

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

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

INNER JOIN

양쪽 테이블에서 매치가 되는 레코드들만 리턴하며 양쪽 테이블의 필드가 모두 채워친 상태로 리턴된다. INNER JOIN이라고 명시하지 않아도 Default로 INNER JOIN으로 작동한다.

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

INNER JOIN

LEFT JOIN

왼쪽 테이블(Base)의 모든 레코드들을 리턴하며, 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴된다. 만약 매칭되는 것이 없다면 NULL을 리턴한다.

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

LEFT JOIN

FULL JOIN

왼쪽 테이블과 오른쪽 테이블의 모든 레코드를 리턴하며, 매칭되는 경우에만 양쪽 테이블들의 모든 필드들이 채워진 상태로 리턴된다. 왼쪽 테이블에만 존재할 경우 오른쪽 테이블은 NULL, 오른쪽 테이블에만 존재할 경우 왼쪽 테이블은 NULL로 채워진 상태로 리턴된다.

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

FULL JOIN

CROSS JOIN

왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴한다. Vital 테이블의 레코드는 4개, Alert 테이블의 레코드는 3개였으므로 총 12개의 레코드가 리턴된다.

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

CROSS JOIN

SELF JOIN

동일한 테이블을 alias를 달리해서 자기 자신과 조인한다. 보통 SELF JOIN을 할 때는 조건(v1.vitalID = v2.vitalID)을 달리해서 사용하는 경우가 많다.

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

SELF JOIN

숙제

23일 차 숙제 리뷰

실행해 보면 어제 풀었던 결과와 똑같이 나오는 것을 확인할 수 있다. 코드 상 달라진 것은 conversionRate에 * 100을 해준 것과 NULLIF를 통해 Divide by zero 예외 처리를 추가해 주었다.

DROP TABLE IF EXISTS adhoc.sanseo_session_summary;

CREATE TABLE adhoc.sanseo_session_summary AS (
SELECT
  LEFT(B.ts, 7) AS month,
  A.channel AS channel,
  COUNT(DISTINCT A.userId) AS uniqueUsers,
  COUNT(DISTINCT CASE WHEN C.amount > 0 L THEN A.userId END) AS paidUsers, 
  COALESCE(ROUND(paidUsers*100.0/NULLIF(uniqueUsers, 0), 2), 0.0) AS conversionRate, -- Divide by zero 방지
  SUM(C.amount) AS grossRevenue,
  SUM(CASE WHEN C.refunded is false THEN C.amount END) AS netRevenue
FROM raw_data.user_session_channel A
JOIN raw_data.session_timestamp B ON A.sessionId = B.sessionId
LEFT JOIN raw_data.session_transaction C ON B.sessionId = C.sessionId
GROUP BY 1, 2
);

COALESCE 함수

NULL 값을 다른 값으로 바꿔주는 함수이다.

• COALESCE(exp1, exp2, exp3, ...) : 앞에서부터 NULL이 아닌 값이 나오면 리턴, 모두 NULL이면 NULL 리턴

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

숙제

사용자 별로 처음 채널과 마지막 채널 알아내기

• ROW_NUMBER vs FIRST_VALUE / LAST_VALUE

아래의 코드는 userid가 251인 사용자의 channel을 확인할 수 있다.

SELECT ts, channel
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
WHERE userid = 251
ORDER BY 1;

GrossRevenue가 가장 큰 UserID 10개 찾기

user_session_channel과 session_transaction 테이블을 사용한다.

• GrossRevenue : Refunded를 포함한 매출

raw_data.nps 테이블을 바탕으로 월별 NPS 계산

• 고객들이 0 (의향 없음)에서 10 (의향 아주 높음)
• detractor (비추천자) : 0 ~ 6점
• passive (소극자) : 7, 8점
• promoter (홍보자) : 9, 10점
• NPS = promoter 퍼센트 - detractor 퍼센트

문제 풀이 및 모범 답안

[숙제 - 24일 차] 데브코스 SQL 숙제

참고링크

What is an SQL JOIN?

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