[데브코스][데이터 분석] SQL 심화 (JOIN)

< 5주차 데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습 (4) >

 

1. JOIN이란?

두 개 혹은 그 이상의 테이블들을 공통 필드를 가지고 머지하는데 사용. 

이는 스타스키마로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용됨.

 

95%는 inner join과 left join으로 가능!

 

-- JOIN 기본 문법
SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2 
-- INNER, FULL, LEFT, RIGHT, CROSS
WHERE A.ts >= '2019-01-01';

 

- 중복 레코드가 없고 Primary key의 유일성이 보장됨을 체크

- 조인하는 테이블간의 관계를 명확하게 정의 필요. 

1:1 -> 조인 필드를 기준으로 한 번씩만 나옴

1:N, N:1 -> 한 테이블에는 키가 한 번, 나머지 테이블에서는 여러 번

N:N -> 모든 테이블에서 키가 여러 번

==> 어느 테이블을 베이스로 잡을 것인지가 중요!

 

 

2. 다양한 종류의 JOIN

INNER JOIN

: 양쪽 테이블에서 매치가 되는 레코드들만 리턴(교집합)

: 양쪽 테이블의 필드가 모두 채워진 상태로 리턴

JOIN = INNER JOIN

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

 

LEFT JOIN

: 왼쪽 테이블(Base)의 모든 레코드들을 리턴

: 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴

 

FULL JOIN

: 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들을 리턴(합집합)

: 매칭되는 경우에만 양쪽 테이블들의 모든 필드들이 채워진 상태로 리턴

 

CROSS JOIN

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

 

SELF JOIN

: 동일한 테이블을 alias를 달리하여 자기 자신과 조인

ex.

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

 

 

 

3. SQL에서 주의할 점(Boolean, NULL)

BOOLEAN 타입 처리

: True or False

flag = True / flag is True 는 동일한 표현

flag is True와 flag is not False는 같은 표현이 아님

(NULL을 고려 필요)

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

 

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;
-- 0
-- =를 통해 NULL 비교를 했기 때문에 0이 나옴(잘못된 결과)

 

 

 

4. 채널별 월별 매출액 테이블 만들기

2개의 새로운 테이블 생성

 

CREATE TABLE raw_data.session_transaction ( 
	sessionid varchar(32),
	refunded boolean,
	amount int
);

CREATE TABLE raw_data.channel ( 
	channelName varchar(32)
);

 

Table	Fields
session_timestamp	sessionid (string), ts (timestamp)
user_session_channel	userid (integer), sessionid (string), channel (string)
session_transaction	sessionid (string), refunded (boolean), amount (integer)
channel	channelname (string)

 

채널별 월별 매출액을 구할 수 있는 테이블을 위해..

=> session_timestamp,user_session_channel,session_transaction 사용

- 아래와 같은 필드로 구성

• month
• channel
• uniqueUsers (총방문 사용자)
• paidUsers (구매 사용자: refund한 경우도 판매로 고려)
• conversionRate (구매사용자 / 총방문 사용자)
• grossRevenue (refund 포함)
• netRevenue (refund 제외)

 

(1) 먼저, 유일한 사용자 수를 세보자

SELECT 
	LEFT(ts, 7) "month",
	usc.channel,
	COUNT(DISTINCT userid) uniqueUsers
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid 
GROUP BY 1, 2
ORDER BY 1, 2;

 

복잡한 JOIN 시 먼저 JOIN 전략부터 수립

- user_session_channel

- session_timestamp

- session_transaction

==> 모두 sessionid를 기반으로 조인 필요

 

user_session_channel, session_timestamp는 1:1로 조인 가능 (INNER JOIN)

하지만, session_transaction의 경우에는 모든 sessionID가 존재하지 않음

-> LEFT JOIN (or RIGHT JOIN)

+ FROM 뒤에 올 테이블은 user_session_channel 혹은 session_timestamp가 되어야 함

 

(2) 이제, session_transaction 테이블을 함께 조인해보자. 

SELECT 
	LEFT(ts, 7) "month",
	usc.channel,
	COUNT(DISTINCT userid) uniqueUsers
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid 
GROUP BY 1, 2
ORDER BY 1, 2;

 

 

이제, paidUsers을 추가해보자. (paidUsers: 구매 사용자)

SELECT 
	LEFT(ts, 7) "month",
	usc.channel,
	COUNT(DISTINCT userid) uniqueUsers,
	COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid 
GROUP BY 1, 2
ORDER BY 1, 2;

 

(3) conversionRate을 추가해보자. (conversionRate : 구매 사용자 / 총 방문 사용자)

ROUND(paidUsers * 100.0 / NULLIF(uniqueUsers, 0), 2) AS conversionRate

-> 0으로 나누는 경우 divided by 0 에러 발생

-> NULLIF를 사용하여 0을 NULL로 변경 -> uniqueUsers가 0일 때 NULL로 반환

SELECT 
	LEFT(ts, 7) "month", -- "year month" channel,
	COUNT(DISTINCT usc.userid) uniqueUsers,
	COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers, 
	ROUND(paidUsers * 100.0 / NULLIF(uniqueUsers, 0), 2) AS conversionRate,
	SUM(amount) grossRevenue,
	SUM(CASE WHEN refunded is False THEN amount END) netRevenue
FROM raw_data.user_session_channel usc
LEFT JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid 
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid 
GROUP BY 1, 2
ORDER BY 1, 2;

 

(4) 최종적으로 CTAS로 테이블 만들기

: 채널별 월 매출액 테이블

DROP TABLE IF EXISTS adhoc.keeyong_monthly_channel_summary;

CREATE TABLE adhoc.자기이름_monthly_channel_summary AS
	SELECT LEFT(ts, 7) "month", channel,
	COUNT(DISTINCT usc.userid) uniqueUsers,
	COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
	ROUND(paidUsers::float*100/NULLIF(uniqueUsers, 0),2) conversionRate, 
	SUM(amount) grossRevenue,
	SUM(CASE WHEN refunded is False THEN amount END) netRevenue
FROM raw_data.user_session_channel usc
LEFT JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid 
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid 
GROUP BY 1, 2;

 

* CTAS에는 ORDEY BY가 무시되기 때문에 꼭 지정할 필요 없음

 

 

**** 

COALESCE

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

즉, NULL 대신 다른 백업값을 리턴해주는 함수

COALESCE(exp1, exp2, exp3, ...)

-> exp1부터 인자를 하나씩 살펴서 NULL이 아닌 값이 나오면 그것을 리턴. 

끝까지 갔는데 모두 NULL이면 최종적으로 NULL 리턴

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

 

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

=> " "로 둘러싸서 사용

-- 에러 발생
CREATE TABLE keeyong.test ( 
	group int primary key, 
	'mailing address' varchar(32)
);

-- 에러 미발생
CREATE TABLE keeyong.test ( 
	"group" int primary key, 
	"mailing address" varchar(32)
);
-- 그러나, 이를 사용할 때도 " "을 사용해야 함

 

***