[DEV] 6주차. 데이터 웨어하우스와 SQL과 데이터분석(4)

1. JOIN

• 2개 이상의 테이블들을 공통 필드를 가지고 머지하는 데 사용됨

• star schema로 구성된 테이블들로 분산되어 있던 정보를 통합

• JOIN 방식에 따라 어떤 레코드들이 선택되는지, 어떤 필드들이 채워지는지가 달라짐

고려해야 할 점

• 중복 레코드가 없고, Primary key의 uniqueness가 보장됨을 반드시 체크해야 함!!
• 조인하는 테이블들의 간의 관계를 명확히 정의
  • One to One
    • 완전한 one to one
    • 한쪽이 부분집합이 되는 one to one
  • One to Many
    • 이 경우 중복이 더 큰 문제가 됨
    • order , order_items 등
  • Many to One
    • 방향만 바꾸는 One to Many와 사실상 동일
  • Many to Many
    • 이런 경우는 많지 않으며
    • one to one이나 one to many로 바꾸는 것이 가능하다면 바꾸어 조인하는 것이 더 나음
• 어느 테이블을 베이스로 잡을지 (From에 사용할지) 결정해야 함

Inner join

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

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

LEFT JOIN

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

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

FULL JOIN

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

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

CROSS JOIN

• 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴
• (왼쪽 테이블 row 수) x (오른쪽 테이블 row 수) 만큼의 행이 리턴됨

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;

2. BOOLEAN 타입

• raw_data.boolean_test

flag
True
False
True
NULL
False

• flag = True, flag is True는 동일한 표현
• flag is True, flag is not False는 항상 같은 표현은 아님!
  • 항상 True, False 값만 있는 건 아니기 때문 (NULL)

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

3. NULL 비교

• NULL 비교는 항상 IS 혹은 IS NOT 으로 수행!
• =, !=, < > 으로 수행하면 잘못된 결과가 나옴 (대부분 0으로 나옴)

SELECT COUNT(1)
FROM raw_data.boolean_test
WHERE flag is NULL;
-- 1

SELECT COUNT(1)
FROM raw_data.boolean_test
WHERE flag = NULL;
-- 0

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

• tables
  • session_timestamp
  • user_session_channel
  • session_transaction

field	설명
month	월
channel	채널명
uniqueUsers	총 방문 사용자
paidUsers	구매 사용자: refund한 경우도 판매로 고려
conversionRate	구매사용자 / 총방문사용자
grossRevenue	refund 포함
netRevenue	refund 제외

DROP TABLE IF EXISTS adhoc.bk_monthly_channel_summary;

CREATE TABLE adhoc.bk_monthly_channel_summary AS
    SELECT 
        LEFT(t.ts, 7) "month",
        usc.channel,
        COUNT(DISTINCT userid) uniqueUsers
        COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
        ROUND(paidUsers::float*100/NULLIF(uniqueUsers, 0), 2) conversionRate,
        SUM(st.amount) grossRevenue,
        SUM(CASE WHEN st.refunded is False THEN st.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;

session_transaction은 LEFT JOIN하는 이유

• 세 테이블 모두 session_id를 기반으로 조인함
• user_session_channel과 session_timestamp는 1:1로 조인이 가능하지만
• session_transaction에는 모든 sessionid가 존재하지 않기 때문에 SELECT의 배경이 되는 FROM 절에서 사용되는 테이블은 user_session_channel 또는 session_timestamp가 되어야 함!

::

• 어떤 표현식(값, 칼럼, 표현 등)의 데이터 타입을 쌍콜론 뒤 타입으로 변형해줌
• 비율을 구하기 위해 나눗셈을 해야하기 때문에 paidUsers의 타입을 float 타입으로 변형해준 것!

5. COALESCE 함수

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

6. 공백 또는 예약키워드를 필드 이름으로 사용하려면

• " "로 둘러싸서 사용!
• but 쿼리에서 사용할 때에도 계속 " "를 사용해야 하기 때문에 번거로움

CREATE TABLE bk.test (
    "group"           int primary key,   -- keyword
    "mailing address" varchar(32)        -- blank
);

7. order 테이블 JOIN 문제

테이블

CREATE TABLE raw_data.order (
    order_id character varying(32) PRIMARY KEY, 
    ordered_at timestamp without time zone, 
    refunded_at timestamp without time zone
);

CREATE TABLE raw_data.order_item ( 
    order_id character varying(32), 
    product_name character varying(32), 
    quantity smallint,
    amount double precision 
);

1)

• 환불되지 않은 주문들만 봤을 때 주문수량 (quantity) 기준으로 가장 많이 주문된 Top 5 상품의 이름과 총 주문수량을 출력하자

select
    DISTINCT oi.product_name,
    sum(case when o.refunded_at is null then oi.quantity else 0 end) sumQuantity
from raw_data.order_item oi
join raw_data.order o on oi.order_id = o.order_id
group by product_name
order by sumQuantity desc
limit 5;

• sum 문장에서 else 0을 넣지 않은 경우에 결과를 출력했을 때 None 값이 출력되었음!
• sum 함수는 그룹별로 null이 아닌 값들을 더하는데, case when 문에서 조건을 충족하지 않는 레코드에 대해서는 결과가 null이 됨
  • 즉, 환불되지 않은 주문이 없는 상품에 대해서는 합계가 null이 될 수 있음!
• 따라서 case에 맞지 않는 경우(모두 환불된 상품인 경우)에는 0을 더하도록 else 0을 추가해야 올바른 결과가 나옴

2)

• 환불되지 않은 주문들만 봤을 때 주문금액 (amount*quantity) 기준으로 가장 많이 주문된 Top 5 상품의 이름과 총 주문금액 출력

select
    DISTINCT oi.product_name,
    sum(case when o.refunded_at is null then oi.quantity * oi.amount else 0 end) orderAmount
from raw_data.order_item oi
join raw_data.order o on oi.order_id = o.order_id
group by product_name
order by orderAmount desc
limit 5;

• 첫번째 질문의 sum 문장에서 oi.quantity를 oi.quantity * oi.amount로만 변경!

8. 기타 SQL

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

WITH first AS (
    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
), last AS (
    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
)
select first.userid as userid, first.channel as first_channel, last.channel as last_channel
from first
join last on first.userid = last.userid and last.seq = 1
where first.seq = 1;

• WITH AS 구문, ROW_NUMBER() 함수 사용!
• ROW_NUMBER()
  • 각 파티션 내에서 order by 절에 의해 정렬된 순서를 기준으로 고유한 값을 반환하는 함수
  • = 그룹 내 순위!
• OVER()
  • 각 행별로 특정 기준에 따라 필요한 집합을 구해 함수를 적용하고 싶을 때 사용
  • = 특정 행 집합 적용!

Gross Revenue가 가장 큰 userid 10개 출력

• Gross Revenue: Refund를 포함한 매출

select 
    userid,
    sum(st.amount) grossRevenue
from raw_data.session_transaction st
join raw_data.user_session_channel usc on st.sessionid = usc.sessionid
group by userid
order by grossRevenue DESC
limit 10;

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

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

select
    left(created_at, 7) mon,
    count(case when score >= 9 then 1 end) promoter,
    count(case when score <= 6 then 1 end) detractor,
    round((promoter-detractor)::float / count(1), 4)  * 100 as nps
from raw_data.nps
group by mon
order by mon;

• 나눗셈을 할 땐 꼭 ::float을 붙여주어야 함!