개요
피벗(PIVOT) 테이블 만들기
+) 퍼널 분석, 코호트 리텐션 분석 과제 리뷰
📌 FAQ & REVIEW
📌 퍼널
💡 현업에서 WITH 문 을 이용해 단계별로 테이블을 만들어 전환율을 구하는지?
- WITH 문을 사용하면 가독성, 재사용성, 성능 측면에서 서브쿼리를 많이 붙이는 것보다 좋다.
1번 쿼리에서 페이지 방문 로그를 가진 사용자의 아이디와 행동 시간만 조회하는 테이블 생성,
2번 쿼리에서 결제 로그를 가진 사용자의 아이디와 행동 시간만 조회하는 테이블 생성
3번 쿼리에서 해당 테이블들을 조인해주는데,
1번 행동에서 2번 행동으로 전환되는 전환율을 분석하고 싶은 것이니까
이벤트 발생 시간에 대한 조건을 지정해주는 것이 중요 !t2.event_time > t1.event_time (결제가 페이지 방문 보다 나중에 일어났고,) AND t2.event_time <= t1.event_time + interval '1' day (페이지 방문 후 1일 이내로 결제)
주의 1
- 임시 테이블을 생성하고 조인을 사용하는 방법은
이벤트 발생 시간에 대한 조건을 순차적으로 전환된 사용자만 계산하는 상황을 보장해주지만,
유입 경로 기간이 길고 이벤트 수가 수십억 개에 달하는 상황이 된다면
공간, 시간 복잡성을 초래하게 된다.
+) 특히, 위 표와 같이 모든 이벤트를 모든 이벤트와 비교하는 과정에서 공간 복잡성을 유발한다.
주의 2
- 조인을 할 경우 공간 복잡성을 유발하는 것을 해결하기 위해 윈도우 함수를 활용할 수 있다.
📌 3회차 과제
💡 유저 아이디에 DISTINCT를 해줘야 하는 이유?
- 실무에서 다양한 활동에 대해 중복된 데이터가 쌓일 수 있기 때문에
각각 퍼널 단계의 고유한 user_id를 조회하기 위해서 DISTINCT 사용 !
+) 중복된 데이터가 쌓일 수 있는 경우
- 중복된 가입 시도
- 사용자가 여러 번 계정 생성을 시도할 때 (중복 방지 로직이 제대로 구현되지 않은 경우)
- 테스트 데이터
- 개발 및 테스트 환경에서 유사한 데이터를 여러 번 삽입하면서 동일한 사용자 정보로 생성
- 시스템 버그
- 계정 생성 로직에 버그가 있어 동일한 사용자가 여러 번 등록 되는 경우
- 사용자 세션 및 연결 문제
- 사용자의 세션이 만료되었거나 네트워크 문제가 발생해 사용자가 계정 생성을 여러 번 시도할 경우
💡 EPOCH 랑 DATE_PART 사용 ?
- 시간 계산을 주로 할 경우,
EPOCH가 적합 - 특정 시간 요소를 추출해야 할 경우,
DATE_PART가 적합
💡 퍼널의 조건에 따라서 선행되는 단계가 후행 단계보다 먼저 진행된다는 조건이 필요
AND ei.info_time <= cs.signup_time
📌 PIVOT
📌 피벗 테이블 만들기
가로 데이터를 세로 데이터로 만들기 위해 실행하는 피벗(PIVOT)
- 피벗 테이블 (pivot table) :
표의 행과 열을 전환하는 등의 과정을 통하여 통계를 재정렬하고,
그 결과로 표 데이터를 요약하는 방법. 집계 함수(합계, 평균 등)가 사용될 수 있다.
4분기 매출 테이블을 q1 ~ q4 기준으로 year, quarter, sales 값으로 만들기
- 일련 번호를 가진 테이블 만들기
- 피벗 테이블을 만들고 CROSS JOIN 하기
- 피벗 테이블 결합
- 일련 번호 테이블 생성
SELECT 1 AS idx UNION ALL SELECT 2 AS idx UNION ALL SELECT 3 AS idx UNION ALL SELECT 4 AS idx
- 피벗 테이블 생성 후 CROSS JOIN
년도별 인덱스 부여
SELECT * FROM quarterly_sales AS q CROSS JOIN ( SELECT 1 AS idx UNION ALL SELECT 2 AS idx UNION ALL SELECT 3 AS idx UNION ALL SELECT 4 AS idx ) AS p
년도별 기준, quarter 테이블 생성
SELECT q.year-- q1에서 q4까지의 레이블 이름 출력하기 ,CASE WHEN p.idx = 1 THEN 'q1' WHEN p.idx = 2 THEN 'q2' WHEN p.idx = 3 THEN 'q3' WHEN p.idx = 4 THEN 'q4' END AS quarter FROM quarterly_sales AS q CROSS JOIN ( SELECT 1 AS idx UNION ALL SELECT 2 AS idx UNION ALL SELECT 3 AS idx UNION ALL SELECT 4 AS idx )AS p
- 피벗 테이블 결합
매출액 데이터와 결합 과정
매출액 데이터와 결합 결과
SELECT q.year ,CASE WHEN p.idx = 1 THEN 'q1' WHEN p.idx = 2 THEN 'q2' WHEN p.idx = 3 THEN 'q3' WHEN p.idx = 4 THEN 'q4' END AS quarter ,CASE WHEN p.idx = 1 THEN q.q1 WHEN p.idx = 2 THEN q.q2 WHEN p.idx = 3 THEN q.q3 WHEN p.idx = 4 THEN q.q4 END AS sales FROM quarterly_sales AS q CROSS JOIN ( SELECT 1 AS idx UNION ALL SELECT 2 AS idx UNION ALL SELECT 3 AS idx UNION ALL SELECT 4 AS idx )AS p
커피 좋아하는 데이터 꿈나무