학습내용

!pip install ipython-sql==0.3.9

채널별 월별 매출액을 만드는 테이블 작성

• 임시 테이블을 만드는 방법이 맞았는지
  conversionRate이 헷갈렸음.

동일한 표현

• flag = True
• flag is True
  CASE ~ WHEN, WHERE 사용

다음 표현은?

• flag is True
• flag is not False
  수학적으로 생각해보면 True의 반대는 not False
  sql에서 이걸 가지고 필터링을 해보면 항상 동일한 표현이 아님.
  Null이 있기 때문. sql 타입들은 항상 Null값을 가질 가능성이 있음.

flag는 boolean 타입. 보면 NULL이 껴있는걸 볼 수 있다.
not False이 갯수는 True 2개, Null 1개 총 3개이다.

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

나도 보니까 not False로 집계했는데, 이러면 NULL도 같이 들어감.
-> 정정. NOT NULL로 집계함. 정상적임.

• 콜랩 주피터노트북의 경우 브라우저가 종료되면 다시 라이브러리, sql 로드 해줘야함.

3번째의 경우, not False인 것들 True, NULL을 세주기 때문에 3이라는 다른 값을 가짐.

boolean에선 True, False, NULL값을 갖는다고 생각해야함.

0, ""과 다른 값임 . 값이 없다는 뜻.

NULL은 무조건 IS, IS NOT으로만 비교 가능함.

• =, !=, <> 쓰면 결과 제대로 안나옴. 보통 0으로 나와버림

WHERE flag is NULL; -> 1
WHERE flag = NULL; -> 0 매칭이 아무것도 안됨.
오히려 에러가 나면 확인이라도 하는데 silent하게 값이 나와버려 조심해야함.

지난번 숙제를 살펴본다

먼저 유일한 사용자 수부터 세보자

SELECT LEFT(ts, 7) "month",
usc.channel,
COUNT(DISTINCT userid) uniqueUsers
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
GROUP BY 1, 2
ORDER BY 1, 2;

간단한 기능부터 순차적으로 풀어나가는 것이 좋다.
uniqueUsers까진 mau를 구해봤기 때문에 어렵지 않다.
timestamp 갖고 있는 테이블과 INNER JOIN 한 뒤,
GROUP BY 월, 채널 으로 그룹핑 후, DISTINCT userid를 COUNT 해준다.

이후 필드는 매출정보가 필요함.
총 3개의 테이블이 조인됨.

복잡해보일 경우. 순차적으로 기준 테이블, JOIN될 테이블을 결정함.

어느 조인을 쓸지, 키가되는 필드는 무엇이될지 결정해야함.

이제 우리는 session_transaction을 붙여야 함.
one-to-one이나 부분집합 관계임.
방문자의 일부만 구매를 일으킴.

만일 INNER JOIN을 해버리면 교집합만 리턴 -> 매출이 있는 사용자만 테이블로 생성해버림.
우리는 매출이 없는 사용자의 값도 필요함.

• LEFT JOIN 채택

앞에 두 테이블은 sessionid를 키를 사용해 매칭하였음.
다음 transaction도 sessionid를 키로 사용할 수 있을꺼 같음.

한줄 추가함
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid

보면 paidUser에 대한 조건이 붙는다
COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUSers

나는 리펀드, amount가 not null인 경우로 잡았는데, 여기선 amount > 0 인 사용자를 셌다.

한 사용자가 여러번 샀다고 하더라도 DISTINCT를 통해 1번으로 계산. 그 수를 집계한다. -> 월, 채별별

나의 경우 COUNT를 쓰지 않고, 그 paidUsers를 직접 추출하였다. userid

이제 uniqueUsers와 paidUsers가 숫자로 집계되었다.
이제 conversionRate을 구해본다

절차가 붙는이유는 고려해야될 점이 몇가지 있다.
정수/정수꼴 -> ::float실수형으로 변경해줘야 함. - type casting

세번째 시도부터는 처음 배움. 퍼센티지 계산
ROUND(paidUsers*100.0/uniqueUsers,2) As conversionRate
보면 100.0 으로 실수타입으로 명시되면, ::float로 캐스팅해주지 않아도 저 연산에서 실수형이 된다. 최종 계산은 가장 큰 자료형을 따라간다.
소숫점 둘째짜리까지 계산한 모습.

여기까지 끝이라 생각할수 있는데 간혹 에러가 날 수 있다.

• 만에 uniqueUsers가 0이면 sql이 divide zero 에러냄
  현재는 0인 경우는 없지만, 현업에서 충분히 있을 수 있음.
  ROUND(paidUsers*100.0/NULLIF(uniqueUsers,0),2) AS conversionRate
  만일 들어온 두개의 값이 같으면 NULL리턴, 그게 아니면 첫번째 값 리턴.
  NULL의 사칙연산은 모두 NULL -> 나중에 체크할 수 있음.

정수연산, 나눗셈 계산 시에 NULLIF는 자주 쓰임.

그 아래 SUM 이용은 내가 작성한 sql과 같음

이로써 CASE는 레코드 한줄 한줄에 조건을 부여하는 것을 알 수 있음.

COALESCE

NULLIF와 반대되는 개념
필드에 NULL이 있음. NULL을 만나면 다른 숫자로 대체하려고 한다.
다른 백업값.
COALESCE(exp1, exp2, exp3, ...)
첫번째부터 NULL인지
exp1은 필드, 레코드일 수 있음. 보통 필드를 넣게되고, 레코드 한줄씩 읽으며 비교함.
그리고 exp2에는 대체하고 싶은 문자열, 값을 넣으면,
exp1필드 레코드 중 NULL을 만나면, exp2값으로 대체함.
NULL값이 있고 중요하지 않거나 충분히 대체할 수 있을때 좋은 함수라 생각됨.

공백 혹은 예약키워드를 필드 이름으로 사용하려면?

• ""로 둘러싸서 사용

group은 이미 sql에서 예약 키워드로 사용함 -> ""
중간에 공백이 있는 필드를 사용하고 싶으면 "mailing address"
'mailing address'는 안됨. 쌍따옴표 사용.

예약키워드는 보통은 잘 안쓸꺼 같고, 공백있는 필드이름의 경우 종종 사용할 것 같음.
그러나 사용할 때도 "" 사용해야함. -> 상당히 번거로움

하단에 보면 GROUP BY 1, 2이다.
그리고 뒤따라오는 필드들은 모두 집계함수와 관련되어, 일종의 그룹핑이 된 모습이다.
나는 COUNT를 안쓰고 userid 자체를 가져다 쓰려고 하니 에러가 났었다.

강사님은 따로 임시테이블 안만들고 바로 테이블로 작성하심.

Boolean

파이썬이라 None으로 나옴 NULL임

not False는 NULL, True를 의미함.
조심해야될 실수. 에러가 나지 않음.
논리적인 실수 발생.

NULL의 경우 is, is not만 비교 가능

= 을 사용하면 세지 못하는 것을 볼 수 있음.

공백, 예약키워드

에러가 쓰는 것을 확임.

""을 사용해 해결함.

flag에서 NULL이 들어가면서 COUNT이슈가 있었음.
이젠 NULL값이 들어오면 REJECT 하고 싶음.
CREATE TABLE 시, 뒤에 NOT NULL을 부여하면 필드가 NULL값을 가질 수 없음.

NULL 값을 넣을 수 없다고 에러냄.

OUT JOIN 필요 여부

id가 한쪽에만 존재한게 있는지 확인하는 과정.
필드를 가져오고 minus으로 계산함. excecpt라고 써도 됨.
distinct를 써줘야 여러번 나올경우 1개씩 제거하기 때문에.

앞에 셀렉트에는 있고, 뒤에는 없는 id가 있다면 출력될 것임. 반대로도 해보자

매출이 0보다 같거나 마이너스(이상값) 확인

%%sql

SELECT LEFT(ts, 7) "month",  -- "year month"
       channel,
       COUNT(DISTINCT usc.userid) uniqueUsers,
       COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
       ROUND(paidUsers*100.0/NULLIF(uniqueUsers, 0),2) conversionRate,
       SUM(amount) grossRevenue,
       SUM(CASE WHEN refunded is False THEN amount END) netRevenue
   FROM raw_data.user_session_channel usc
   LEFT 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
   ORDER BY 1, 2;

월별 채널별로 유니크 유저의 수, paidUsers수가 집계되고, 그 걸 conversionrate으로 만듦

%%sql

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

--Summary Table 생성
CREATE TABLE adhoc.keeyong_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.keeyong_monthly_channel_summary;

보면 ORDER BY가 빠져있다.
CTAS에선 ORDER BY가 아무 소용이 없음.
레코드가 저장되는 순서는 RDBMS에서 알아서 저장함. ORDER BY는 무시됨.

COALESCE NULL 교체

숙제

일단 노가라도 알 수 있는 방법은
이렇게 눈으로 보면 시간순 첫번째 채널은 Facebook, 마지막 채널은 구글이다.
ORDER BY ASC, DESC 로 1행의 채널값을 확인하였다.
만일 한 사용자의 시간대별 생성한 타임스탬프값이 매우 많고, 각 유저별로 해당 데이터를 원할 경우 번거로운 일이 될 것이다.

모든 사용자에 대해서 첫번째 채널과, 마지막 채널을 범용적으로 구해본다.

• ROW_NUMBER
  ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field2) nn
  아직은 봐서는 잘 모르겠음. 필드 두개를 쓰는데 1개는 기준이 되는 필드 같고, 하나는 그에 맞는 추출 필드 레코드 인것 같다.

• 어떠 select된 레코드들에 다가 특정 기준을 붙여서 일련번호를 붙임. 새로운 필드를 추가함.
  PARTITION BY 일련번호를 붙일 건데, 그룹핑을 해가지고 1번부터 새로 붙임. 그룹바이랑은 조금 다름 각 그룹에서 1번부터 번호가 매겨짐.
  일련번호 붙이는 기준은 ORDER BY로 붙이게 됨. field2를 기준으로 순서를 정함.
  ORDER BY ts
  PARTITION BY userid - userid 값으로 묶어서 번호를 매기겠음.
  했을 때 첫번째가 채널값. 반대로 DESC 쓰면 마지막 값.

대강 만들어봤는데 WHERE 부여하니 에러남. 이를 window 함수라고 한다.

WINDOW 함수는 SQL에서 제공하는 매우 강력한 도구로, 행 간의 복잡한 비교와 계산을 수행할 수 있습니다. 그러나 WINDOW 함수는 SELECT, ORDER BY 또는 HAVING 절에서만 사용할 수 있으며, WHERE 절에서는 사용할 수 없습니다.

userid 10을 그룹으로 묶은 후, 같은 userid 내에서 ts를 기준으로 오름차순 정렬할 것임. 이를 seq로 일련번호를 부여함.

%%sql

With ds AS(
SELECT ts, channel,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts) first_channel
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
WHERE userid = 251
ORDER BY 1
)

SELECT * FROM ds WHERE first_channel = 1;

window 함수 특성상 GROUP BY 성질을 가지고 있어 WHERE문에서 사용할 수 가 없다. 이에 CTE로 임시 테이블을 생성해서 first_channel =1 인 레코드들을 수집함.

이런식으로 모든 사용자의 시간순 첫번째 채널 값만 모은 테이블을 만들 수 있다.

• FIRST_VALUE/LAST_VALUE

GROUP BY, JOIN 다시 복습
JOIN 후에
Refund를 포함한 매출.
가장 큰 UserID 10개 찾기 -> ORDER BY DESC 사용

%%sql

SELECT
  usc.userId,
  SUM(CASE WHEN st.amount is NULL THEN 0 ELSE st.amount END)
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON usc.sessionid = t.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
;

그냥 SUM을 할 경우 어떤 유저는 물건을 하나도 구매하지 않아 SUM이 NULL이 되는 경우가 있음. 이때 DESC로 하면 NULL부터 등장함. 이에 SUM을 사용할 때 NULL 레코드는 0으로 치환함. -> COALESCE 사용해봄.

%%sql

SELECT
  usc.userId,
  SUM(COALESCE(st.amount,0))
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON usc.sessionid = t.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
;

null레코드가 있을 경우 다음 인자 값으로 치환함. 아주 간단해짐.

nps 라는 새로운 테이블 아래
3개의 필드
timestamp
userid
rating
NPS를 계산해봄. net promoter score
서비스같은걸 사용자가 어떻게 생각하는지. 리뷰를 가지고 계산한 기법.
당신이라면 친구나 가족에게 추천하시겠습니까?
7점이나 8점은 아에 빼버림. -> 굉장히 보수적으로 접근
0~6점, 9~10점대를 사용함

예를들어 100명중 30명이 9점 이상, 0~6점은 40점
NPS = 30% - 40% -> -10%
정말 좋은 회사의 경우 NPS가 70%가 넘음

월별이 기준이므로 - 유저, 채널 관계 없음
별도의 JOIN 불필요

%%sql

SELECT
  TO_CHAR(created_at, 'YYYY-MM') mon,
  ROUND(COUNT(CASE WHEN score BETWEEN 9 AND 10 THEN score END)*100.0/NULLIF(COUNT(1),0),2) promoter,
  ROUND(COUNT(CASE WHEN score BETWEEN 0 AND 6 THEN score END)*100.0/NULLIF(COUNT(1),0),2) detracter,
  (promoter - detracter) nps
FROM raw_data.nps
GROUP BY 1
ORDER BY 1
;

전에 배웠던 TO_CHAR로 월별로 그룹핑 후,
promoter와 detracter를
ROUNT, COUNT, CASE~THEN, NULLIF를 사용한 0 나누기 방지. 등을 사용하여 계산하였다.
그 후 nps는 단순 뺄셈으로 계산함.
19년도 1월부터 12월까지의 nps를 볼 수 있었고, 점진적 성장세임을 알 수 있다.

다음엔
minus
트랜젝션
숙제 확인
앞으로의 방향