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

주제

1. GROUP BY와 Aggregate 함수
2. CTAS
3. 중복 레코드 확인하는 법
4. 최근 데이터의 존재 여부 확인하는 법
5. Primary key uniqueness 확인하는 법
6. NULL 값 확인하는 법

 

 

GROUP BY와 Aggregate 함수(1)

• 테이블의 레코드를 그룹핑하여 그룹 별로 다양한 정보를 계산
• 두 단계 :
  • 먼저 그룹핑을 할 필드를 결정 (하나 이상도 가능)
  • 그룹 별로 계산할 내용을 결정
    • Aggregate 함수 사용
    • COUNT, SUM, AVG, MIN, MAX 등
      • 보통 필드 이름을 지정하는 것이 일반적 (alias)

 

• 월별 세션 수를 계산

SELECT
	LEFT(ts, 7) AS mon,
	COUNT(1) AS session_count
FROM raw_data.session_timestamp
GROUP BY 1    -- GROUP BY mon, GROUP BY LEFT(ts, 7)
ORDER BY 1;

 

• 월별 유니크한 사용자 수 계산
  • 이것이 바로 MAU !

SELECT TO_CHAR(a.ts, 'YYYY-MM') as month, COUNT(DISTINCT b.userId) as mau
FROM raw_data.session_timestamp a JOIN raw_data.user_session_channel b
ON a.sessionId = b.sessionId
GROUP BY 1;
ORDER BY 1 DESC;

 

• 월별 채널 별 유니크한 사용자 수

SELECT
	TO_CHAR(a.ts, 'YYYY-MM') AS month,
	channel,
	COUNT(DISTINCT b.userId) AS mau
FROM raw_data.sessino_timestamp a JOIN raw_data.user_session_channel
ON a.sessionId = b.sessionId
GROUP BY 1
ORDER BY 1 DESC, 2;

 

 

CTAS

• SELECT를 사용하여 테이블을 생성하는 것
• 간단하게 새로운 테이블을 만드는 방법
• 자주 조인하는 테이블들이 있다면 CTAS를 사용해서 조인하면 편리함

 

중복된 레코드 확인하는 법

1. 서로 다른 쿼리문을 작성 후 필드의 카운트를 비교

SELECT COUNT(1)
FROM adhoc.jyunghye_session_summary;

SELECT COUNT(1)
FROM (
	SELECT DISTINCT userId, sessionId, ts, channel
	FROM adhoc.jyunghye_session_summary 
);

 

   2.  CTE를 사용해서 중복 제거 후 카운트 해보기

WITH ds AS (
	SELECT DISTINCT userId, sessionId, ts, channel
	FROM adhoc.jyunghye_session_summary
)
SELECT COUNT(1)
FROM ds;

 

 

 

최근 데이터의 존재 여부 확인하는 법

• MIN과 MAX 함수를 이용하여 가장 최근과 가장 오래된 타임스탬프를 확인

SELECT MIN(ts), MAX(ts)
FROM adhoc.jyunghye_session_summary;

 

 

Primary key uniqueness 확인하는 법

• 결과가 1보다 크면 중복이 있다는 뜻

SELECT sessionId, COUNT(1)
FROM adhoc.jyunghye_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

 

 

NULL 값 확인하는 법

SELECT
	COUNT(CASE WHEN sessionId is NULL THEN 1 END) sessionId_null_count,
	COUNT(CASE WHEN userId is NULL THEN 1 END) userId_null_count,
	COUNT(CASE WHEN ts is NULL THEN 1 END) ts_null_count,
	COUNT(CASE WHEN channel is NULL THEN 1 END) channel_null_count
FROM adhoc.jyunghye_session_summary;

 

 

 

 

숙제

 

• 채널별 월 매출액 테이블 만들기
• 사용 테이블 : session_timestamp, user_session_channel, session_transaction
• 아래와 같은 필드를 구성 :
  • month
  • channel
  • uniqueUsers(총 방문 사용자)
  • paidUsers (구매 사용자 : refund한 경우도 판매로 고려)
  • conversionRate (구매사용자 / 총방문 사용자)
  • grossRevenue (refund 포함)
  • netRevenue (refund 제외)

 

첫 번째 시도 :

%%sql

DROP TABLE IF EXISTS adhoc.jyunghye_session_summary;
CREATE TABLE adhoc.jyunghye_session_summary AS
SELECT TO_CHAR(ts, 'YYYY-MM') AS month, 
channel, 
COUNT(DISTINCT userId) as uniqueUsers,
COUNT(c.sessionId) as paidUsers,
ROUND((paidUsers / uniqueUsers), 2) AS conversionRate,
SUM(amount) as grossRevenue,
SUM(CASE WHEN refunded = False THEN amount ELSE 0 END) as netRevenue
FROM raw_data.user_session_channel a LEFT JOIN raw_data.session_timestamp b
ON a.sessionId = b.sessionId
LEFT JOIN raw_data.session_transaction c
ON b.sessionId = c.sessionId
GROUP BY 1, 2
ORDER BY 2, 1;

 

다른 건 어려운 게 없어서 값이 잘 나왔지만 문제는 conversionRate였다. 나누기를 한 결과가 모두 0으로 나왔는데, 구글링을 해보니 uniqueUsers와 paidUsers가 integer 타입이어서 그랬던거다! 답이 float로 나아야 하는데 integer를 가지고 계산을 하니 모두 0으로 처리가 됐던 것. 그래서 저 두 필드의 타입을 바꾸는 작업이 필요했다.

 

두번째 시도 :

%%sql

SELECT TO_CHAR(ts, 'YYYY-MM') AS month, 
channel, 
COUNT(DISTINCT userId) as uniqueUsers,
COUNT(c.sessionId) as paidUsers,
ROUND((CONVERT(FLOAT, paidUsers) / CONVERT(FLOAT, uniqueUsers)), 2) * 100 AS conversionRate,
SUM(amount) as grossRevenue,
SUM(CASE WHEN refunded = False THEN amount ELSE 0 END) as netRevenue
FROM raw_data.user_session_channel a LEFT JOIN raw_data.session_timestamp b
ON a.sessionId = b.sessionId
LEFT JOIN raw_data.session_transaction c
ON b.sessionId = c.sessionId
GROUP BY 1, 2
ORDER BY 2, 1;

 

결과 :

 

일단 계산은 잘 되었는데 6.0, 5.0 이런식으로 rate를 나둬도 되는건가..라는 생각이 들었다. 조금 더 들어가자면 CONCAT함수를 이용해서 %를 붙일수도 있겠지만 일단은 이대로 두기로 했다..! ORDER BY 2, 1 이건 1, 2로 정렬하면 채널들이 뒤죽박죽 섞여서 보기 힘들기 때문에 일단 채널별로 그룹핑을 해주고 그 다음에 월 별을 오름차순으로 정렬했다.

---

느낀점

 

이번주에도 SQL 수업을 들었는데 아직까지는 프로젝트 전 주의 강의 내용과 크게 다르지 않아서 조금 당황스러웠다. 차라리 이번주 강의 내용을 프로젝트 전에 들었더라면 중복 확인하는 법 같은 것을 배워서 바로 사용할 수 있었을텐데 그 부분이 조금 아쉽다. 자꾸만 강의 내용이 몇 주째 중복되는 것 같아서 이 시간에 새로운 걸 배웠으면 좋겠다는 생각을 조금 했다..이제 Redshift도 개인적으로 한 번 론치해봤는데 굉장히 복잡했다. 그래도 이번 숙제는 적당히 어려워서 굉장히 재밌게 했다.