[AI class W11 D3] SQL Analysis 3

Contents

1. GROUP BY와 AGGREGATE함수

2. CTAS와 CTE 소개

    데이터 품질 확인

3. 숙제

1. GROUP BY와 AGGREGATE함수

1. GROUP BY

• 테이블의 레코드를 그룹핑하여 그룹 별로 다양한 정보를 계산
• 두 단계로 이뤄짐
  • 먼저 그룹핑 할 필드를 결정(하나 이상의 필드가 될 수 있음 ex. 월별, 채널별)
    • GROUP BY로 지정(필드 이름을 사용하거나 필드 일련번호를 사용)
  • 다음 그룹별로 계산할 내용를 결정
    • 여기서 AGGREGATE 함수를 사용
    • COUNT, SUM, AVG, MIN, MAX, LISTAGG, ....
      • 보통 필드 이름을 지정하는 것이 일반적 (alias -> as)
• 월별 세션수를 계산하는 SQL
  • raw_data.session_timestampe를 사용 (sessionId 와 ts필드) 
  • SELECT LEFT(ts, 7) AS mon, --YYYY-MM-DD 구성이므로 YYYY-MM까지 잘라서 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;

2. GROUP BY로 다음 문제를 풀어보자

• raw_data.session_timestamp / raw_data.user_session_channel 테이블 사용

• 다음을 계산하는 SQL을 만들어 보자
  1. 가장 많이 사용된 채널 
  2. 가장 많은 세션 만들어낸 사용자 ID
  3. 월별 유니크 사용자 수(MAU - Monthly Active User) : 한 사용자는 한번만 카운트 되어야 함
  4. 월별 채널별 유니크 사용자 수
• 가장 많이 사용된 채널
  • 가장 많이 사용되었다의 정의는? 사용자 기반? 세션 기반?
  • 필요한 정보 : 채널 정보, 사용자 정보 or 세션 정보
  • 먼저 어느 테이블을 사용해야하는지 생각 :
    • session_timestamp
    • user_session_channel
    • 두 테이블 조인?

      SELECT
      	channel,
          COUNT(1) AS session_count,
          COUNT(DISTINCT userId) AS user_count
      FROM raw_data.user_session_channel
      GROUP BY 1			-- GROUP BY channel
      ORDER BY 2 DESC;	-- ORDER BY session_count DESC

       
• 가장 많은 세션을 만들어낸 사용자 ID
  • 필요한 정보 : 세션 정보, 사용자 정보
  • 먼저 어느 테이블을 사용해야 하는지 생각해라
    • session_timestamp
    • user_session_channel
    • 두 테이블 조인?

      SELECT
      	userId,
          COUNT(1) AS count
          COUNT(DISTINCT userId) AS user_count
      FROM raw_data.user_session_channel
      GROUP BY 1			-- GROUP BY userId
      ORDER BY 2 DESC	-- ORDER BY count DESC
      LIMIT 1;

• 월별 유니크 사용자 수 MAU
  • 필요한 정보 : 시간 정보, 사용자 정보
  • 먼저 어느 테이블을 사용해야 하는지 
    • session_timestamp (userId, sessionId, channel)
    • user_session_channel (sessionId, ts)
    • 두 테이블 조인?
    

    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  -- INNER JOIN 동일
    GROUP BY 1			-- GROUP BY userId
    ORDER BY 2 DESC;	-- ORDER BY count DESC

  • 비슷한 기능을 하는데, 차이점은?
    • TO_CHAR(A.ts, 'YYYY-MM') AS month
    • LEFT(A.ts, 7)
    • DATE_TRUNC('month', A.ts) -- timestamp type이 유지 day 를 무조건 1로 리턴
    • SUBSTRING(A.ts, 1, 7)
  • COUNT의 동작을 잘 이해하는 것이 중요
    • DISTINCT 없이 COUNT 하면 session 수와 동일
    • DISTINCT와 연동 : unique 유저를 원하니까 필요
  • INNER JOIN (default) vs. LEFT JOIN : 조건에 맞는 것만 join vs. 왼쪽에 있는 테이블은 매칭과 상관없이 무조건 살림
  • alias : 필드/ 테이블 이름에 사용 AS는 필수는 아님
  • ORDER BY와 GROUP BY : 포지션 번호 vs. 필드 이름
    • GROUP BY 1 == GROUP BY month == GROUP BY TO_CHAR(A.ts, 'YYYY-MM')
• 월별 채널별 유니크 사용자 수
  • 필요한 정보 : 시간 정보, 사용자 정보, 채널 정보
  • 먼저 어느 테이블을 사용해야 하는지
    • session_timestamp  (userId, sessionId, channel)
    • user_session_channel (sessionId, ts)
    • 두 테이블 조인?

      SELECT
      	TO_CHAR(A.ts, 'YYYY-MM') AS month,
          channel,
          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,2			-- GROUP BY userId
      ORDER BY 1 DESC, 2;	-- ORDER BY count DESC

 

2. CTAS와 CTE 소개

1. CTAS: SELECT를 가지고 테이블 생성

• 간단하게 새로운 테이블을 만드는 방법
• 자주 조인하는 테이블들이 있다면, 이를 CTAS를 사용해서 조인해두면 편리해짐

  DROP TABLE IF EXISTS adhoc.rollingsnowball_session_summary;
  CREATE TABLE adhoc.rollingsnowball_session_summary AS
  SELECT B.*, A.ts FROM raw_data.session_timestamp A
  JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid;​

2. 월별 유니크 사용자 수 다시 풀어보기

SELECT 
	TO_CHAR(ts,'YYYY-MM') AS month,
    COUNT(DISTINCT userid) AS mau
FROM adhoc.rollingsnowball_session_summary
GROUP BY 1
ORDER BY 1 DESC;

3. 항상 시도해봐야 하는 데이터 품질 확인 방법들

• 중복된 레코드들 체크하기
• 최근 데이터의 존재 여부 체크하기(freshness)
• primary key uniqueness 지켜지는지 체크하기
• 값이 비어있는 컬럼들이 있는지 체크하기

4. 중복된 레코드 체크

• 다음 두 개의 카운트를 비교

  SELECT COUNT(1)
  FROM adhoc.rollingsnowball_session_summary;​

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

• CTE를 사용해서 중복 제거 후 카툰트 해보기

  With ds AS(
  	SELECT DISTINCT userId, sessionId, ts, channel
      FROM adhoc.rollingsnowball_session_summary
  )
  SELECT COUNT(1)
  FROM ds;​

5. freshness 체크

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

6. primary key uniqueness 체크

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

7. 값이 비어있는 컬럼 있는지 체크

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.rollingsnowball_session_summary;

 

3. 숙제

• 2개의 새 테이블 소개 (지금까지 session_timestamp / user_session_channel)
• session_transaction
• channel

  CREATE TABLE raw_data.session_transaction(
  	sessionid varchar(32),
      refunded boolean,
      amount int
  );​

  CREATE TABLE raw_data.channel(
  	cahnnelName varchar(32)
  );

 

 

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