[TIL Day47] SQL과 데이터분석 - JOIN

이다혜·2021년 7월 9일

TIL

목록 보기

49/60

JOIN이란?

두 개 혹은 그 이상의 테이블들을 공통 필드를 가지고 merge하는 방법. 이는 star schema로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용된다.

왼쪽 테이블을 LEFT, 오른쪽 테이블을 RIGHT라고 하면 JOIN의 결과는 양쪽의 필드를 모두 가진 새로운 테이블을 만들어내게 되는데, JOIN 방식에 따라 다음 두 가지가 달라진다.
1. 어떤 레코드들이 선택되는지?
2. 어떤 필드들이 채워지는지?

다양한 종류의 JOIN
JOIN 문법
JOIN시 고려해야할 점
- 먼저 중복 레코드가 없고 Primary key uniqueness가 보장됨을 체크하는 것이 중요
- 어느 테이블을 베이스로 잡을지(From에 사용할지) 결정
- (조인하는 필드를 기준으로) 조인하는 테이블들간의 관계를 명확하게 정의
- One to one (완전한 or 한쪽이 부분집합이 되는)
- One to many
- Many to one
- Many to many
JOIN의 종류
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- SELF JOIN
- CROSS JOIN
예제에 사용될 테이블
- 체중 측정 정보와 그에 따른 Alert 정보
- 일정 기간 이상 체중을 측정하지 않았을 때 'MissingVital' 발생

INNER JOIN

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

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

LEFT JOIN

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

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

FULL JOIN

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

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

CROSS JOIN

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

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

SELF JOIN

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

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

BOOLEAN 타입 처리

flag is True와 flag is not False가 동일한 표현인지 생각해보자. 아래와 같은 경우를 살펴보면 쉽게 차이를 알 수 있다.

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;

NULL 비교

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

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

아래와 같은 필드로 구성된 채널별 월별 매출액 테이블을 만들어보자.

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

(2) 매출액을 구하기 위해 session_transaction 테이블을 조인하자
3개 테이블 모두 sessionid를 기반으로 조인을 해야한다. user_session_channel과 session_timestamp는 일대일로 조인 가능(INNER JOIN)하지만 session_transaction의 경우에는 모든 sessionid가 존재하지 않는다. -> LEFT JOIN

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

(3) 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

(4) 방문 유저 중 몇 %가 실제 구매를 했는지 conversionRate를 계산해보자

SELECT
    LEFT(ts, 7) "month",
    usc.channel,
    COUNT(DISTINCT userid) uniqueUsers,
    COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
    ROUND(paidUsers*100.0/NULLIF(uniqueUsers, 0), 2) AS conversionRate 
    -- uniqueUsers가 0일 경우 발생하는 에러를 처리하기 위해 NULLIF 사용
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

(5) grossRevenue, netRevenue를 추가하자

SELECT
    LEFT(ts, 7) "month",
    usc.channel,
    COUNT(DISTINCT 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
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