TIL_day24 SQL-JOIN

230511 목요일

 

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

 

JOIN

SQL에서 JOIN이란 두 개 이상의 테이블을 공통 필드를 가지고 merge 하는 것이다.

 

JOIN은 6가지의 종류가 있다.

JOIN의 방식에 따라 아래의 두 가지가 달라진다. 

1. 어떤 레코드들이 선택되는지?

2. 어떤 필드들이 채워지는지?

 

아래의 6가지 JOIN 방식을 꼭 기억하자!!

다양한 종류의 조인, 출처:https://theartofpostgresql.com/blog/2019-09-sql-joins/

 

1. INNER JOIN

- 양쪽 테이블에서 매치가 되는 레코드들만 리턴함

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

2. LEFT JOIN

- RIGHT JOIN과 방향만 다르므로 RIGHT JOIN은 생략

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

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

3. FULL JOIN

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

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

4. CROSS JOIN

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

5. SELF JOIN

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

 

 

JOIN 문법은 아래의 형태이다.

SELECT A.*, B.*
FROM raw_data.table1 A
??? JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2
WHERE A.ts >= '2019-01-01'

-- ???자리에는 JOIN 방식을 작성한다.(INNER, FULL, LEFT, RIGHT, CROSS 등)
-- INNER와 SELF는 생략 가능

 

JOIN시 고려해야 할 점

1. 중복 레코드가 없고, Primary Key의 uniqueness가 보장되는지 체크

- 아주 중요!!!!!!!!!!!

2. JOIN 하는 테이블 간의 관계를 명확하게 정의

- one to one, one to many, many to one, many to many

3. 어느 테이블을 베이스로 잡을지(From에 사용할지) 결정

 

 

 

지난 시간 숙제 리뷰

아래와 같이 4개의 테이블(웹서비스 사용자/세션 정보)이 존재할 때, 예제를 통해 SQL 문법을 익혀보자.

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)

 

예제

/* 
<채널 별 월 별 매출액 테이블 만들기>
adhoc 스키마 밑에 CTAS로 본인이름을 포함한 테이블 만들기
3개의 테이블을 JOIN하기

7개의 field
-month
-channel
-uniqueUsers(총 방문 사용자, 중복 제외)
-paidUsers(구매 사용자: refund한 경우도 포함)
-conversionRate(구매 사용자 / 총 방문 사용자)
-grossRevenue(refund 포함)
-netRevenue(refund 제외) 
*/

--혹시 기존에 생성되어 있으면 삭제
DROP TABLE IF EXISTS adhoc.sikhye_monthly_channel_summary;

--Summary Table 생성
CREATE TABLE adhoc.sikhye_monthly_channel_summary
AS
SELECT TO_CHAR(ts, 'YYYY-MM') year_month, 
    usc.channel, 
    COUNT(DISTINCT usc.userid) unique_users, 
    COUNT(DISTINCT CASE WHEN amount>0 THEN userid END) paid_users,
    ROUND(paid_users*100./NULLIF(unique_users,0),2) conversion_rate, 
    SUM(amount) gross_revenue, 
    SUM(CASE WHEN refunded is False THEN amount
        ELSE 0 END) net_revenue
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
  LEFT JOIN raw_data.session_transaction str ON usc.sessionid = str.sessionid
GROUP BY 1, 2;

--정상적으로 생성되었는지 확인
SELECT * FROM adhoc.sikhye_monthly_channel_summary;

 

결과

 

풀이

1. JOIN 방식 결정

- session_timestamp과 user_session_channel는 sessionid를 기반으로 일대일로 조인이 가능 : INNER JOIN 사용

(이전 포스팅에서 중복 레코드가 없고, Primary Key의 uniqueness가 보장되는 것을 확인하였다.)

- 하지만 session_transaction의 경우에는 모든 sessionid가 존재하지 않기 때문에 LEFT JOIN(혹은 RIGHT JOIN)을 사용한다.

2. CASE WHEN 사용

3. ROUND와 NULLIF 사용

 

 

 

기타 SQL 문법

NULL 비교

- NULL 비교는 항상 IS 혹은 IS NOT으로 수행해야 한다!!!

- NULL 비교를 =, !=, <>으로 수행하면 잘못된 결과가 나온다.

 

NULLIF

- 0으로 나누는 경우 divide by 0 error가 발생한다.

- 따라서 이를 방지하기 위해 NULLIF를 사용하여 0을 NULL로 변경한다. (0 -> NULL)

- 사칙 연산에 NULL이 들어가면 결과도 NULL이 됨을 기억하자!

 

COALESCE

- NULL값을 다른 값으로 바꿔주는 함수이다. (NULL -> 0)

SELECT
	value,
    COALESCE(value, 0) -- value가 NULL이면 0을 리턴, 인자는 여러 개 가능
FROM 테이블 명;

COALESCE한 결과

 

공백이나 예약키워드를 필드명으로 사용하고 싶을 때

- ""로 둘러싸서 사용해야 한다. (''안됨!)