📒 SQL(4)

📌 JOIN

SQL에서 JOIN이란 두 개 또는 그 이상의 테이블들 통합하는 것을 말한다.
JOIN은 6가지가 존재하는데 각각 INNER, FULL, CROSS, LEFT, RIGHT, SELF 가 있다.

📙 JOIN

SELECT A.*, B.*
FROM raw_data.table1 A
JOIN raw_data.table2 B ON A.key = B.key;

📌 주의

1. 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크해야 한다.
2. JOIN하는 테이블들 간의 관계를 명확하게 정의한다.
   -> One to one, One to manay, Many to one, Many to Many
3. 어느 테이블을 베이스로 할지 결정해야한다. -> FROM에 작성할 테이블

---

📌 INNER JOIN

• 두 테이블에서 매치가 되는 레코드들만 리턴한다.
• 내용이 모두 채워진 상태이다.

SELECT *
FROM raw_data.User A
JOIN raw_data.product B ON A.userid = B.userid;

A.userid	A.productid	A.weight	B.userid	B.type	B.date
1001	1010	90	1001	stick	2024-04-25

---

📌 LEFT JOIN

• 왼쪽 테이블(Base)의 모든 레코드들을 리턴한다.
• 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우만 채워서 리턴한다. -> 이외에는 NULL

SELECT *
FROM raw_data.User A
LEFT JOIN raw_data.product B ON A.userid = B.userid;

A.userid	A.productid	A.weight	B.userid	B.type	B.date
1001	1010	90	NULL	NULL	NULL
1002	2010	70	NULL	NULL	NULL
1003	1020	12	1003	case	2024-04-25
1004	3010	40	NULL	NULL	NULL

---

📌 FULL JOIN

• 왼쪽 테이블과 오른쪽 테이블의 모든 레코드를 리턴한다.
• 매칭 되는 경우에만 채우고 이외에는 NULL로 리턴한다.

SELECT *
FROM raw_data.User A
FULL JOIN raw_data.product B ON A.userid = B.userid;

A.userid	A.productid	A.weight	B.userid	B.type	B.date
1001	1010	90	NULL	NULL	NULL
1002	2010	70	NULL	pad	NULL
1003	1020	12	1003	case	2024-04-25
NULL	NULL	NULL	NULL	candy	2024-04-22

---

📌 CROSS JOIN

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

SELECT *
FROM raw_data.User A
CROSS JOIN raw_data.product B ON A.userid = B.userid;

A.userid	A.productid	A.weight	B.userid	B.type	B.date
1001	1010	90	1001	case	2024-04-24
1001	2010	70	1001	pad	2024-05-22
1002	1020	12	1002	case	2024-04-25
1003	1020	12	1003	case	2024-04-22

---

📌 SELF JOIN

• 동일한 테이블을 alias를 달리하여 자기 자신과 JOIN한다.

SELECT *
FROM raw_data.User A1
JOIN raw_data.User A2 ON A1.userid = A2.userid;

A1.userid	A1.productid	A1.weight	A2.userid	A2.productid	A2.weight
1001	1010	90	1001	1010	90

---

📌 BOOLEAN

• TRUE or FALSE
• flag = True
• flas is True

---

📌 NULL

• IS or IS NOT으로 사용해야 한다.
• =, !=, <, > 으로 연산하면 잘못된 결과가 나온다.

---

📌 COALESCE

• NULL값을 다른 값으로 바꿔주는 함수
  -> NULL대신 다른 백업값으로 리턴

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

---

📌 실습

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

• ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq 를 사용하면 가능하다.

%%sql

SELECT
    A.userid AS userid,
    A.channel AS First,
    B.channel AS LAST
FROM (
    SELECT
        userid,
        ts,
        channel,
        ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq  -- 내림차순으로 정렬된 상태
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
) A
JOIN (
    SELECT
        userid,
        ts,
        channel,
        ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts DESC) seq -- 오름차순으로 정렬된 상태
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
) B ON A.userid = B.userid and B.seq = 1
WHERE A.seq = 1;

OUTPUT ->

📌 Gross Revenue가 가장 큰 UserID 10개 찾기

user_session_channel, session_transaction, session_timestamp 3개 사용하라고 하셨는데 내 생각에는 timestamp는 필요 없어보임 Userid도 없고 amount도 없다.

📙 user_session_channel

%%sql
SELECT *
FROM raw_data.user_session_channel
LIMIT 2

• Userid가 존재함
• session_transaction 테이블의 sessionid와 연결해서 JOIN하면 될듯

📙 session_transaction

%%sql
SELECT *
FROM raw_data.session_transaction
LIMIT 2

• Gross Revenue에서 수익인 amount가 존재함
• Refund 여부와 상관 없으니까 다 더하면 될듯

📙 session_transaction

%%sql
SELECT *
FROM raw_data.session_timestamp
LIMIT 2

• Userid 찾기인데 Userid가 없는 테이블임

%%sql

SELECT
    userid,
    SUM(amount) AS Gross_Revenue
FROM raw_data.user_session_channel usc
JOIN raw_data.session_transaction st ON usc.sessionid = st.sessionid
GROUP BY userid
ORDER BY Gross_Revenue DESC -- 상위 10개니까 총 수익 기준으로 내림차순
LIMIT 10

OUTPUT ->

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

• 고객들이 0(의향 없음) 에서 10(의향 아주 높음)
• detractor(비추천자) : 0~6
• passive(소극자) : 7~8
• promoter(홍보자) : 9~10
• NPS : promoter% - detractor%

📙 nps

%%sql
SELECT *
FROM raw_data.nps
LIMIT 10

score를 이용하면 될것 같다.

%%sql

SELECT
    LEFT(created_at, 7) AS Month,
    ROUND(SUM(
            CASE
                WHEN score >= 9 THEN 1
                WHEN score <= 6 THEN -1 
            END)::float * 100 / COUNT(1), 2
        ) AS NPS
FROM raw_data.nps
GROUP BY Month
ORDER BY Month

OUTPUT ->