[SQL] BOOLEAN, NULL 값 처리하기|채널별 월별 매출액 테이블 만들기

BOOLEAN 타입 처리

flag = True 와 flag is True 는 동일한 표현.

flag is True 와 flag is not False 는 다른 표현 => flag가 True 이거나 Null 일 수도 있기 때문

 

%%sql

SELECT

    COUNT(CASE WHEN flag = True THEN 1 END) true_cnt1, -- flag가 true인 경우 1씩 더하고 cnt1에 저장

    COUNT(CASE WHEN flag is True THEN 1 END) true_cnt2, -- flag is true이면 1씩 더하고 cnt2에 저장

    COUNT(CASE WHEN flag is not False THEN 1 END) not_false_cnt -- falg is not false면 1씩 더해서 cnt3

FROM raw_data.boolean_test;

 

true_cnt1 true_cnt2 not_false_cnt

3

3

4

 

NULL 비교 

Null 비교는 항상 IS , IS NOT으로 수행

Null 비교를 = 혹은 != 혹은 <> 로 수행하면 잘못된 결과가 나옴

%%sql

SELECT COUNT(1)

FROM raw_data.boolean_test

WHERE flag is NULL;

결과 값 1 

 

%%sql

SELECT COUNT(1)

FROM raw_data.boolean_test

WHERE flag = NULL;

결과 값 0 

Null 값은 항상 IS IS NOT 을 사용해야됨!!

 

COALESCE

NULL 값을 다른 값으로 바꿔주는 함수. NULL 대신 다른 백업 값을 리턴해줌.

COALESCE(exp1, exp2, exp3. ....)

인자 하나씩 살펴서  NULL이 아닌 값이 나오면 그걸 리턴

끝까지 갔는데도 모두 NULL이면 최종적으로 NULL 리턴

%%sql

SELECT

     value,

     COALESCE(value, 0) -- value 값이 null이면 0을 리턴, 아니면 원래 값 리턴.

FROM raw_data.count_test;

 

value      coalesce

None	0
1	1
1	1
0	0
0	0
4	4
3	3

 

공백 혹은 예약 키워드를 필드 이름으로 사용하려면 " "를 사용해야함.

%%sql

DROP TABLE IF EXISTS adhoc.keeyong_test;

CREATE TABLE adhoc.keeyong_test (

    group int primary key,

    'mailing address' varchar(32)

);

에러 발생

%%sql

CREATE TABLE adhoc.keeyong_test (

    "group" int primary key,

    "mailing address" varchar(32)

);

에러 해결완

 

필드값에 NULL 값을 받지 않도록 테이블 생성

%%sql

DROP TABLE IF EXISTS adhoc.keeyong_test_null;

CREATE TABLE adhoc.keeyong_test_null (

    value int NOT NULL

);

'NOT NULL' 값을 추가하면 NULL 값을 가지지 못하도록 테이블 생성

 

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

session_timestamp 테이블: sessionid, ts

user_session_channel 테이블: userid, sessionid, channel

session_transaction 테이블: sessionId (pk), refunded True 환불 False 환불x, amount 구매 가격 

channel 테이블: channelname

 

필드 구성

- month

- channel

- uniqueUSers (총방문 사용자)

- paidUsers ( 구매 사용자: refund한 경우도 판매로 고려, session_transaction의 sessionid에 해당하는 user)

- conversionRate (구매 사용자 / 총방문 사용자, 정수 /정수 값은 항상 0 이기 때문에 float으로 추출)

- grossRevenue (refund 포함, sum 활용하기)

- netREvenue (refund 제외)

 

SQL 작성 -> 단계별로!

1. usc 테이블과 t 테이블 INNER JOIN -> month, channel, uniqueUsers 필드 먼저 리턴 

2. st 테이블과 LEFT JOIN -> amount, conversionRate, grossRevenue, netRevenue 리턴

 

%%sql

SELECT LEFT(ts,7) as month, usc.channel,

       COUNT(DISTINCT userid) uniqueUsers, -- 중복없는 사용자id 갯수

       COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers, -- amount가 0 이상인 userid 중복없이 리턴

       ROUND(paidUsers::float*100/NULLIF(uniqueUsers,0),2) conversionRate, -- 정수를 float 형태로 바꾸고 계산

       SUM(amount) grossRevenue,

       SUM(CASE WHEN refunded is False THEN amount END) netRevenue  -- refund가 false인 경우(환불되지 않은 것)만 더해주기

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 -- usc, t 테이블 INNER JOIN 후 st테이블 left join

GROUP BY 1,2

ORDER BY 1,2;

year_month channel unique_users paid_users conversion_rate gross_revenue net_revenue

2019-05	Organic	238	17	7	1846	1571
2019-05	Facebook	247	14	5	1199	997
2019-05	Instagram	234	11	4	959	770
2019-07	Facebook	558	32	5	2222	2144
2019-08	Facebook	611	18	2	1009	1009
2019-08	Google	610	27	4	2210	1894

* ConversionRate 추가할때 주의사항

정수/정수 = 0 이 나오기 때문에 분자,분모 중 하나의 데이터를 실수형으로 바꿔줘야 함. ROUND(paidUsers*100.0/NULLIF(uniqueUsers,0),2) AS conversionRate

• 0으로 나누는 경우 divide by 0 에러 발생
• 이를 방지하기 위해 uniqueUsers의 값이 0이면 Null이라고 표시. 아니라면, 그냥 리턴.
• Null이 사칙연산에 들어가면 모든 값이 Null이 되기때문