JOIN

JOIN이란?

---

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

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

1. 어떤 레코드들이 선택되는지?
2. 어떤 필드들이 채워지는지?
   

• join문법
  

• JOIN시 고려해야 할 점
  - 먼저 중복 레코드가 없고 Primary key uniqueness가 보장됨을 체크하는 것이 중요

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

  -One to one (완전한 one to one : user_session)channel & session_timestamp or 한쪽이 부분집합이 되는 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에 시용할 지 결정해야 함)

다양한 JOIN의 종류

---

JOIN의 종류

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• SELF JOIN
• CROSS JOIN

예제 테이블

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

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

• LEFT JOIN

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

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

• FULL JOIN

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

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를 계산해보자

1. paidUsers/uniqueUsers AS comversionRate
   정수형 나누기 정수형이라 실수형이 안나옴

2. paidUser::float/uniqueUsers AS coversionRate
   실수형으로 나오지만 0.1 ,0.4등등 퍼센트로 나오지 않음

3. ROUND(paidUsers*100.0/uniqueUsers, 2)AS conversionRate
   퍼센트로 출력하기위한 명령어but uniqueUsers가 0이면 에러가 남

4. ROUND(paidUsers*100.0/NULLIF(uniqueUsers,0),2) AS conversionRate
   uniqueUsers의 값이 0이면 NULL을사용해라

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

*NULLIF

• paidUsers/uniqueUsers
  0으로 나누는경우 divide by 0 에러 발생
  이를 방지하기 위해 NULLIF를 사용하여 0을 NULL로 변경
  사칙연산에 NULL이들어가면 결과도 NULL이 됨

*COALESCE

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

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

(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

---