[Week6 데이터 웨어하우스와 SQL 기본] TIL 24일차 - JOIN 연산

이번시간에는 SQL의 JOIN 연산에 대해 알아보자.

0) JOIN 이란

두 개 이상의 테이블을 특정 조건에 맞춰서 merge 하는 것.

우선, 두 개의 테이블이라고 생각하고 왼쪽 테이블을 left, 오른쪽 테이블을 right이라고 한다.

 

1) 테이블의 관계

1:1

완전 1:1인 테이블이거나, 한 테이블이 다른 테이블의 부분 집합인 경우이다.

보통, join 연산 시 크게 문제가 발생하지 않는다.

1:다

join 연산시 기준을 잘 못 잡으면 문제가 발생한다.

다:다

가능하면 join 연산을 수행하지 않고 다:1, 1:1, 1:다 테이블로 변환해서 사용하도록 한다.

 

※ 어느 테이블을 기준으로 조인할지를 잘 설정해야 한다. ※

 

2) 6가지 JOIN 연산

INNER JOIN

LEFT, RIGHT 테이블의 교집합인 레코드

FULL JOIN

LEFT, RIGHT 테이블의 합집합인 레코드(매칭이 될 경우 MERGE, 매칭되지 않는 값에 대해서는 NULL)

CROSS JOIN

LEFT, RIGHT 테이블의 모든 레코드의 조합

LEFT JOIN

LEFT 테이블 기준으로 매칭하여 MERGE

RIGHT JOIN

RIGHT 테이블 기준으로 매칭하며 MERGE (보통 LEFT JOIN을 사용)

SELF JOIN

동일한 테이블로 조인을 수행

 

조인 한 눈에 살펴보기

 

코드

SELECT ~
FROM 테이블 A 조인이름JOIN 테이블 B ON A.컬럼명=B.컬럼명
WHERE ~
GROUP BY ~
HAVING ~
ORDER BY ~
LIMIT ~

 

※ 그런데 앞서 학습한 데이터 웨어하우스의 특징 중에 Primary key의 unique가 보장이 안된다는 항목이 있었다.

따라서 조인 연산을 수행 하기 전에 미리 Primary key 데이터의 중복을 체크해야 한다. ※

 

2) 실습

임의로 수집된 데이터를 기준으로 조인 연산을 수행해 보자.

raw_data

- vital (UserID, VitalID, Date, Weight) : 사용자가 하루에 한 번씩 측정해서 저장

- alert (UserID, VitalID, AlertType, Date, UserID) : 사용자의 행동(정보)에 따라 발생하는 알람, 사용자가 2일 이상 바이탈을 기록하지 않는 경우 VitalID NULL로 AlterType MissingType이 발생한다.

 

VitalID를 기준으로 조회한다.

1. InnerJoin

(NULL)값은 제외하고 공통일 경우 merge를 수행

SELECT ~
FROM VITAL A 
JOIN ALERT B ON A.VITALID=B.VITALID;

2. LeftJoin/RightJoin

(왼쪽 모든 레코드에 대해) 값이 같은 레코드가 있는 경우 merge, 없는 경우 null

SELECT ~
FROM VITAL A 
LEFT JOIN ALERT B ON A.VITALID=B.VITALID;

3. FullJoin

(왼쪽, 오른쪽 모든 레코드에 대해) 값이 같은 레코드가 있는 경우 merge, 없는 경우 null

SELECT ~
FROM VITAL A 
FULL JOIN ALERT B ON A.VITALID=B.VITALID;

4. CrossJoin

왼쪽, 오른쪽 레코드의 모든 조합의 수(왼쪽 레코드 수 X오른쪽 레코드 수

SELECT ~
FROM VITAL A 
CROSS JOIN ALERT B ON A.VITALID=B.VITALID;

5. SelfJoin

같은 테이블에 대해 alias만 다르게 해서 조인

SELECT ~
FROM VITAL A 
JOIN VITAL B ON A.VITALID=B.VITALID;

 

보통 해당 형태보다, 다른 조건들이 추가된다.

 

 

3) SQL 유의사항

1. BOOLEAN값 처리하기(True, False)

동일한 표현

컬럼=True

컬럼 is True

 

다른 표현(컬럼값이 True, False외에 다른 값을 가질 수 있다. ex.null값)

컬럼 is True

컬럼 is not False

 

2. NULL값 처리하기

null값은 비교연산자 대신 IS NULL, IS NOT NULL 이용한다.

NULLIF(컬럼값, 값) : 값 → NULL

나눗셈의 경우 0으로 나누면 문제가 발생하므로 NULLIF 함수를 사용하도록 한다.

NULLIF(컬럼값, 값) → 컬럼값=값이면 NULL을 반환 그렇지 않으면 컬럼 반환

COALESCE(값 1, 값 2,...) : NULL → 값

제일 처음 NULL이 아닌 값을 return(끝까지 NULL이면 NULL을 반환)

 

3. 예약된 키워드/공백을 컬럼 이름으로 사용하고 싶을 때

큰 따옴표를 이용한다.

CREATE TABLE 테이블명(
	"컬 럼 명" 자료형,
    "예약어" 자료형
);

나중에 사용할 때도 큰따옴표를 붙여서 사용해야 한다.

 

 

4) 어제숙제- 월별 채널별 매출액 테이블 만들기 코드 수정하기

SQL

SELECT
  TO_CHAR(T.ts, 'YYYY-MM') AS month,
  channel,
  COUNT(DISTINCT U.userid) as uniqueUsers, --한 사용자는 한 번만 count
  Count(DISTINCT CASE WHEN TR.amount >=0 THEN U.userid END)as paidUsers, --한 사용자는 한 번만 count
  ROUND(paidUsers AS Float*100.0/NULLIF(uniqueUsers, 0), 2) as conversionRate,
  Sum(CASE WHEN TR.refunded is NOT NULL then Tr.amount END) as grossRevenue,
  Sum(CASE WHEN TR.refunded=False then Tr.amount ELSE 0 END) as netRevenue
FROM raw_data.user_session_channel U
JOIN raw_data.session_timestamp T ON T.sessionid=U.sessionid
LEFT JOIN raw_data.session_transaction TR ON  T.sessionid=TR.sessionid
GROUP BY 1, 2
ORDER BY 1, 2;

 

- channel 테이블 조인 해제

user_session_channel 테이블에 채널 이름 존재

- paidUsers에 distinct userid 사용

한 사람이 여러 번 구매할 수 있으므로

- conversionRate에 typecasting 대신 실수형 곱셈

실수X정수=실수

- 나눗셈 연산에 NULLIF 함수 사용

0으로 나눌 수 없으므로 NULL을 반환하도록 한다.

 

CTAS 사용해서 redshift에 새로운 테이블 생성

%%sql
DROP TABLE IF EXISTS 스키마.session_summary;
CREATE TABLE 스키마.session_summary AS

그 후에, 위의 SQL 문장을 사용한다.(CTAS에서는 orderby 부분은 적용되지 않는다)

 

 

5) 오늘 숙제

1. 사용자별 처음 접속한 채널, 마지막 채널 확인하기

- first_value, last_value 이용

%%sql
SELECT
  distinct usc.userid,
  FIRST_VALUE(usc.channel) OVER (PARTITION BY usc.userid ORDER BY st.ts 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirstValue,
  LAST_VALUE(usc.channel) OVER (PARTITION BY usc.userid ORDER BY st.ts 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid;

2. Gross Revenue가 가장 큰 UserID 10개 확인하기

%%sql
SELECT
    usc.userid,
    SUM(amount) gross_revenue
FROM raw_data.user_session_channel usc
LEFT JOIN raw_data.session_transaction str ON usc.sessionid = str.sessionid
where str.amount>0
GROUP BY 1 
ORDER BY 2 desc
limit 10;

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

%%sql
SELECT LEFT(created_at, 7) AS month,
  ROUND(SUM(
    CASE
    WHEN score >= 9 THEN 1
    WHEN score <= 6 THEN -1 
    END)*100.00/COUNT(1), 2)
FROM raw_data.nps
GROUP BY 1
ORDER BY 1;