[AI class W11 D4] SQL Analysis 4

Contents

 

1. JOIN이란?

2. 다양한 종류의 JOIN

3. 3일차 숙제 리뷰

4. 숙제

 

1. JOIN이란?

• SQL의 조인은 두 개 혹은 그 이상의 테이블들을 공통 필드를 가지고 머지하는데 사용된다.
• 이는 스타 스키마로 구성된 테이블들로 분산되어있던 정보를 통합하는데 사용된다.
• 왼쪽 테이블을 LEFT, 오른쪽 테이블을 RIGHT라고 하자. 
• JOIN의 결과는 방식에 상관없이, 양쪽 필드를 모두 가진 새로운 테이블을 만들게 된다.
• JOIN의 방식에 따라 다음 두 가지가 달라진다.
  1. 어떤 레코드들이 선택되는지
  2. 어떤 레코드들이 채워지는지
• JOIN의 문법ex>

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

• JOIN시 고려해야할 점
  • 먼저 중복 레코드가 없고, primary key uniqueness가 보장되는지 체크 (매우 중요)
  • 조인하는 테이블 간 관계를 명확하게 정의해야함
    • One to one (join되는 key가 테이블당 한 번씩 나오는 경우)
      • 완전한 one to one : user_session_channel & session_timestamp (sessionId 한번씩- 어떤 join을 해도 동일한 결과)
      • 한쪽이 부분집합이 되는 one to one: user_session_channel & session_transaction (key sessionId이지만, transaction은 일부이기 때문에, inner join과 left join의 결과물이 크게 다르다)
    • One to many (order vs order_items)
      • 이 경우 중복이 더 큰 문제가 됨 -> 증폭
    • Many to one
      • 방향만 바꾸면 one to many 로 보는 것과 사실상 동일
    • Many to many
      • 이런 경우는 많지 않으며 one to one이나 one to many로 바꿀 수 있다면 변환하여 조인하는 것이 덜 위험
  • 어느 테이블을 base로 잡을지(FROM에 사용할지) 결정해야 함

2. 다양한 종류의 JOIN

 

raw_data.Vital : 유저가 체중을 잴 때마다 기록되는 테이블

raw_data.Alert : 머신러닝을 통해 예측해서 문제 가능성이 있을 때 Alert을 만들어내고, 이를 기록하는 테이블

 

1. INNER JOIN

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

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

2. LEFT JOIN

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

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

3. RIGHT JOIN

4. FULL JOIN

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

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

5. CROSS JOIN

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

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

6. SELF JOIN

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

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

3. 3일차 숙제 REVIEW

• 채널별 월 매출액 테이블 만들기 (본인 스키마 밑에 CTAS로 테이블 만들기)
  • session_timestamp, user_session_channel, session_transaction 테이블들 사용
  • 아래와 같은 필드로 구성
    • month
    • channel
    • uniqueUsers (총방문 사용자)
    • paidUsers (구매 사용자 : refund한 경우도 판매로 포함)
    • conversionRate (구매 사용자 / 총방문 사용자)
    • grossRevenue (refund 포함)
    • netRevenue (refund 제외)
• BOOLEAN 타입 처리
  • True or False
  • 다음의 2개는 동일한 표현
    • flag = True
    • flag is True
  • 다음의 2개는 동일한 표현인가?
    • flag is True
    • flag is not False
    • (아니다 Null이 있을 수 있어)

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

    
• NULL 비교
  • 항상 is 혹은 is not으로 비교해야 함
  • = 혹은 != 혹은 <>으로 비교하면 잘못된 결과가 나옴 (주로 return 0)

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

 

• 복잡한 JOIN 시 먼저 JOIN 전략을 수립하라
  1. raw_data.user_session_channel
  2. raw_data.session_timestamp
  3. raw_data.session_transaction

- 위의 3개 테이블 모두 sessionId를 기반으로 join해야 함

- user_session_channel 과 session_timestamp는 일대일로 join 가능 : INNER JOIN

-session_transaction은 모든 sessionId에 존재하는 것은 아님

• LEFT JOIN (혹은 RIGHT JOIN)
• FROM 에 사용하는 테이블은 user_session_channel 혹은 session_timestamp가 되어야 함

 

1. 유일한 사용자 수부터 세보기

SELECT LEFT(ts, 7)"month",
	user.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 테이블 추가하기

SELECT LEFT(ts, 7)"month",
	user.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_transcation st ON st.sessionId = usc.sessionId
GROUP BY 1,2
ORDER BY 1,2;

3. paidUsers 추가하기

SELECT LEFT(ts, 7)"month",
	user.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 conversionRate : 정수/정수 이므로 제대로 계산되지 않는다.
2. paidUsers::float/uniqueUsers AS conversionRate : 너무 소숫점이 많이 나온다.
3. ROUND(paidUsers*100.0 / uniqueUsers, 2) AS conversionRate : 100.0이 실수이므로 실수로 전환되어 계산됨
4. ROUND(paidUsers*100.0 / NULLIF(uniqueUsers, 0), 2) AS conversionRate : 분모가 0일 경우 NULL을 리턴

• NULLIF
  • paidUsers/uniqueUsers
    • 0으로 나누는 경우 dived by 0 에러 발생
    • NULLIF를 사용하여 0을 NULL로 변경하여 방지
      • paidUsers/NULLIF(uniqueUsers,0)
      • 다시 한번 사칙연산에 NULL이 들어가면 결과도 NULL이 됨을 기억할 것

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

SELECT LEFT(ts, 7)"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)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;

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

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

• 공백 혹은 예약 키워드를 필드 이름으로 사용하려면? ""로 둘러싸서 사용

CREATE TABLE rollingsnowball.test(
	group int primary key,
    'mailing address'varchar(32)
);

6. 채널별 월매출액 테이블 만들기 (숙제 최종 완성본)

DROP TABLE IF EXISTS adhoc.rollingsnowball_monthly_channel_summary;
CREATE TABLE adhoc.rollingsnowball_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*100.0/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 쓸 때 ORDER BY는 무시되므로 쓸 필요 없음

4. 4일차 숙제

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

• ROW_NUMBER vs. FIRST_VALUE/LAST_VALUE
• 사용자 251번의 시간 순으로 봤을 때 첫 번째 채널과 마지막 채널은 무엇인가?

• 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​

  이렇게 해서 처음 채널과 마지막 채널을 볼 수도 있다
• ROW_NUMBER를 활용해보자 (셀렉트된 레코드들에 특정 기준에 따른 일련번호를 부여하는 것)
  • ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field2)nn
  

2. Gross Revenue가 가장 큰 UserID 10개 찾기

• user_session_channel과 session_transaction과 session_timestamp테이블을 사용
• Gross Revenue: refund 포함한 매출

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

• 고객들에게 다른 사람들에게 추천할 의향이 있는지 물음: 0(의향 없음)에서 10(의향 아주 높음)
• detractor(비추천자): 0~6
• passive(소극자): 7,8
• promoter(홍보자): 9, 10
• NPS = promoter 퍼센트 - detractor 퍼센트