프로그래머스 데이터분석 데브코스 1기 - 24일차

주제

1. JOIN
2. NULL 비교
3. 지난 숙제 풀이
4. 숙제

 

 

JOIN이란?

• 두 개 혹은 그 이상의 테이블을 공통 필드를 가지고 머지하는 데 사용
• 스타 스키마로 분산된 테이블 속 정보를 통합하는데 사용
• JOIN의 종류 :
  • INNER JOIN : C
  • FULL JOIN : A + B + C
  • CROSS JOIN : 양 테이블의 모든 레코드를 곱한다
  • LEFT JOIN : A + C
  • RIGHT JOIN : B + C
  • SELF JOIN : 한 개의 테이블이 자기 자신과 조인

조인 예시

 

 

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';

 

 

1. INNER JOIN

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

SELECT * FROM raw_data.Vital v
INNER JOIN raw_data.Alert a ON v.vitalId = a.vitalId;

 

 

2. LEFT JOIN

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

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

 

 

3. FULL JOIN

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

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

 

 

4. CROSS JOIN

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

SELECT * FROM raw_data.Vital v
CROSS JOIN raw_data.Alert a ON v.vitalId = a.vitalId;

 

 

JOIN 시 고려해야 할 점

• 먼저 중복 레코드가 없고 Primary key uniqueness가 보장됨을 확인 → 아주 중요!!
• 조인하는 테이블 간의 관계를 명확하게 정의
  • One to One
  • One to Many → 이 경우 중복이 있으면 증폭된다
  • Many to Many → 흔하지 않지만 one to one이나 one to many로 변환하여 조인하는 것이 좋음
• 어느 테이블을 베이스로 잡을지 결정해야함

 

 

NULL 비교

• NULL 비교는 항상 IS 혹은 IS NOT으로 수행해야함
• = 혹은 != 혹은 <>으로 수행하면 잘못된 결과가 나옴

 

 

지난 숙제 풀이

• 정답 :

SELECT 
	LEFT(ts, 7) 'month',
	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(amount) grossRevenue
	SUM(CASE WHEN refunded is False THEN amount END) netRevenue
FROM raw_data.user_session_channel usc
LEFT JOIN raw_data.sessino_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;

 

 

• NULLIF
  • paidUsers / uniqueUsers
  • 0으로 나누는 경우 divide by 0 에러 발생
  • 방지법 : NULLIF를 사용하여 0을 NULL로 변경
    • paidUsers / NULLIF(uniqueUsers, 0)
• COALESCE
  • NULL 값을 다른 값으로 바꿔주는 함수
  • 즉 NULL 대신에 다른 백업값을 리턴해주는 함수
  • COALESCE(exp1, exp2, exp3, ...)
    • exp1부터 인자를 하나씩 살펴서 NULL이 아닌 값이 나오면 그걸 반환
    • 끝까지 가도 모두 NULL이면 최종적으로 NULL을 반환
• 공백 혹은 예약 키워드를 필드 이름으로 사용하려면?
  • ""로 둘러싸서 사용

CREATE TABLE adhoc.jyunghye_new_table (
	"group" int not null,
    music varchar(32) not null
);

 

 

 

숙제

1. Gross Revenue가 가장 큰 UserID 10개 찾기

• refunded의 여부에 상관없이 모든 amount를 합친 값이 gross revenue이니까 SUM을 사용했다.
• sessionId를 이용하여 두 개의 테이블을 조인
• 그리고 합계를 내림차순으로 정렬 후 상위 10개만 반환되도록 LIMIT 사용

%%sql

SELECT userId, SUM(amount) as grossRevenue
FROM raw_data.user_session_channel a JOIN raw_data.session_transaction b
ON a.sessionId = b.sessionId
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

 

결과

 

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

• ROW_NUMBER() 이용해보기
• ROW_NUMBER는 처음 사용해봤기 때문에 우선 써보고 테이블이 어떤식으로 나오는지 확인해봤다.
• 단순하게 말하면 지정한 필드로 그룹핑을 해주고, 다른 필드를 기준으로 정렬을 해서 랭킹을 하는 함수였다.
• ROW_NUMBER() OVER (PARTITION BY userId ORDER BY ts) seq
• 첫번째 시도 : userId 별로 첫번째 채널의 순위와 마지막 채널의 순위를 알아내는 것까지는 성공

%%sql

WITH ranked_ts AS (
  SELECT userId, channel, ROW_NUMBER() OVER (PARTITION BY userId ORDER BY ts) seq
  FROM raw_data.user_session_channel a JOIN raw_data.session_timestamp b
  ON a.sessionId = b.sessionId
)
SELECT userId, channel, MIN(seq) as first_seq, MAX(seq) as last_seq
FROM ranked_ts
GROUP BY 1
ORDER BY 1;

조회 결과

 

 

• CTE를 사용해서 푸는 방향은 맞는 것 같은데 문제는 첫번째와 마지막 채널의 이름을 어떻게 하면 따로 뽑을 수 있을까였다.
• 그래서 첫번째 채널을 뽑을 수 있는 CTE 하나와 마지막 채널을 뽑을 수 있는 CTE 하나, 총 2개로 분리해서 사용해보기로 했다.
• 두번째 시도 : 

%%sql

WITH first_ch AS (
  SELECT userId, channel, ROW_NUMBER() OVER (PARTITION BY userId ORDER BY ts) seq
  FROM raw_data.user_session_channel a JOIN raw_data.session_timestamp b
  ON a.sessionId = b.sessionId
), last_ch AS (
  SELECT userId, channel, ROW_NUMBER() OVER (PARTITION BY userId ORDER BY ts DESC) seq
  FROM raw_data.user_session_channel a JOIN raw_data.session_timestamp b
  ON a.sessionId = b.sessionId
)
SELECT f.userId, f.channel as first_channel, l.channel as last_channel
FROM first_ch f JOIN last_ch l
ON f.userId = l.userId 
WHERE f.seq = 1 AND l.seq = 1;

결과

• 성공..! 사실 처음에 WHERE 절에 seq대신 userId를 넣고서는 왜 안나오지?? 이러고 있었다...
• 어이없는 실수를 발견하고 수정하니 바로 결과가 떴다..
• 이게 맞는지 확인하기 위해서 27번 유저의 채널과 ts를 확인하는 과정을 거쳤다
• 첫번째 채널은 Youtube, 마지막 채널은 Instagram이 나와야 한다

%%sql

SELECT userId, channel, ts
FROM raw_data.user_session_channel a LEFT JOIN raw_data.session_timestamp b
ON a.sessionId = b.sessionId
WHERE userId = 27 -- 윗 쿼리 결과 확인
ORDER BY 3;

 

첫번째 채널

마지막 채널

• 정상적으로 작동하는 것을 확인했다!

 

 

3. 월별 NPS 계산

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

%%sql

WITH counts AS (
  SELECT LEFT(created_at, 7) as month, 
    COUNT(CASE WHEN score <= 6 THEN 1 END) as detractor,
    COUNT(CASE WHEN score >= 9 THEN 1 END) as promoter,
    COUNT(CASE WHEN score BETWEEN 7 AND 8 THEN 1 END) as passive,
    COUNT(1) as total_count 
  FROM raw_data.nps
  GROUP BY 1
)
SELECT month, ROUND((promoter - detractor)::float/total_count * 100.0, 2) as nps
FROM counts
ORDER BY 1;

결과

• 그래도 나름 만족할만한 쿼리가 나온 것 같다..! 확실히 CTE를 사용하니까 서브쿼리를 쓰지 않고 깔끔하게 쿼리를 짤 수 있어서 좋은 것 같다

 

 

 

 

 

---

느낀점

숙제 풀이를 보니까 타입 변환을 할 때 CONVERT함수가 아닌 :: float 이런식으로 변환할 수 있다는 걸 처음 알았다. MySQL만 써봐서 다른 디비 문법들은 생소한데 훨씬 편하게 변환할 수 있을 것 같다. 오늘 새로운 숙제가 총 3개였는데 그 중 하나는 쉬웠고 나머지 두 개는 어려워서 함수에 대한 구글링도 해보면서 접근했는데 그 과정이 너무 재밌었고 처음으로 고급 SQL 함수도 사용해봤는데 자주 쓰이는 것 같으니 익숙해져야겠다는 생각이 들었다.