✏️ 오늘 학습한 내용

1. Group by와 AGGREGATE 함수
2. CTAS와 CTE

🔎 Group by와 AGGREGATE 함수

• 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산

• 이는 두 단계로 이루어짐

  • 먼저 그룹핑을 할 필드를 결정 (하나 이상의 필드가 될 수 있음)
    • GROUP BY로 지정
  • 다음 그룹별로 계산할 내용을 결정
    • 여기서 Aggregate 함수를 사용
    • COUNT, SUM, AVG, MIN, MAX, LISTAGG, ...
      • 보통 필드 이름을 지정하는 것이 일반적 (alias)

• 월별 세션수를 계산하는 SQL

  • raw_data.session_timestamp를 사용 (sessionId와 ts 필드)
    e.g.)

  SELECT
  	-- ts와 같은 Timestamp에 LEFT를 사용하는 경우 String으로 자동으로 캐스팅이 된다.
  	LEFT(ts, 7) AS mon, -- "0000-00", 년-월 문자열
    COUNT(1) AS session_count,
  FROM raw_data.session_timestamp
  GROUP BY 1 -- (select문의 첫번째 필드로 묶겠다)GROUP BY mon, GROUP BY LEFT(ts, 7)
  ORDER BY 1 -- 기본적으로 ASC 오름차순;

• 가장 많이 사용된 채널은 무엇인가?

  • 인터뷰 질문이였을 경우
    -> 일부러 모호하게 문제를 낸다
    -> 응시자가 무작정 대답을 하는 것이 아니라 불분명한 경우 알아내려하는 지 확인하려함

  • 가장 많이 사용되었다는 정의는?

    • 사용자 기반 아니면 세션 기반?
      -> 인터뷰 였을 경우 많이 사용되었다의 기준이 무엇인지 되물어야한다.

  • 필요한 정보

    • 채널 정보, 사용자 정보 혹은 세션 정보

  • 먼저 어느 테이블을 사용해야하는지 생각!

    • user_session_channel?
    • session_timestamp?
    • join?

  e.g.1) 가장 많이 사용된 채널은 무엇인가?

  SELECT
  	channel,
    COUNT(1) AS session_count,
    -- DISTINCT를 하지않으면 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

  e.g.2) 가장 많은 세션을 만들어낸 사용자 ID는 무엇인가?

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

• 월별 유니크한 사용자 수

  • MAU(Monthly Active User)에 해당
  • 필요한 정보 - 시간 정보, 사용자 정보
  • 먼저 어느 테이블을 사용해야하는지 생각!
    • user_session_channel (userId, sessionId, channel)
    • session_timestamp (sessionId, ts)
      -> sessionId로 join

  e.g.)

  SELECT
  	TO_CHAR(A.ts, 'YYYY-MM') AS month,
    COUNT(DISTNCT 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;

  TO_CHAR(A.ts, 'YYYY-MM')와 결과가 유사한 함수

  • LEFT(A.ts, 7) : 'YYYY-MM'의 String을 반환
  • DATE_TRUNC('month',A.ts) : Timestamp를 반환
    (이때, 년/월/일에 해당하는 일은 1일로 고정됨 ex) 1987.03.01 )
  • SUBSTRING(A.ts, 1, 7) : 'YYYY-MM'의 String을 반환

🔎 CTAS와 CTE

• CTAS : SELECT를 가지고 테이블 생성 ( create+insert가 동시에 )

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

  e.g.) CTAS를 사용해 join 테이블을 만들어 월별 유니크한 사용자 수를 다시 풀어보기

  CREATE TABLE adhoc.keeyong_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;
  
  SELECT
  	TO_CHAR(ts, 'YYYY-MM') AS month,
    COUNT(DISTNCT userid) AS mau
  FROM adhoc.keeyong_session_summary
  GROUP BY 1
  ORDER BY 1 DESC;

• 항상 시도해봐야하는 데이터 품질 확인 방법들
  ( 테이블 생성 시에도 확인해야함 )

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

• 1. 중복된 레코드 체크

  • 방법 : 다음 두 개의 카운트가 동일한지 비교
    • 모든 레코드 수를 count
    • 중복을 제거한 레코드 수를 count

  e.g.)

  SELECT COUNT(1)
  FROM adhoc.keeyong_session_summary;
  
  SELECT COUNT(1)
  FROM(
    SELECT DISTINCT userID, sessionId, ts, channel
    FROM adhoc.keeyong_session_summary;
  );

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

    • CTE(Common Table Expression) : 서브쿼리로 쓰이는 파생테이블
      ( With절을 사용 )

    e.g.)

    With ds AS(
        SELECT DISTINCT userID, sessionId, ts, channel
        FROM adhoc.keeyong_session_summary;
    )
    
    SELECT COUNT(1)
    FROM ds;

• 2. 최근 데이터의 존재 여부 체크하기 (freshness)

  • timestamp table의 min, max를 확인하기
    e.g.)

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

• 3. Primary key unqiueness가 지켜지는지 체크하기

  • Primary key를 기준으로 group by를 했을 때, count가 1인지 확인
    e.g.)

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

• 4. 값이 비어있는 컬럼들이 있는지 체크하기

  • 각 column마다 NULL의 갯수를 확인
    e.g.)

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

• 채널별 월 매출액 테이블 만들기 (CTAS로 추가)

  • 사용할 테이블 : session_timestamp, user_session_channel, session_transaction
  • 필드
    • month
    • channel
    • uniqueUsers (총방문 사용자)
    • paidUsers (구매 사용자, refund한 경우도 포함)
    • conversionRate (구매사용자/총방문사용자)
    • grossRevenue (refund 포함)
    • netRevenue (refund 제외)

%%sql

-- 채널별 월 매출액 테이블 만들기 (CTAS)
/* Table  
 session_timestamp : sessionid(string), ts(timestamp)
 user_session_channel : userid(integer), sessionid(string). channel(string)
 session_transaction : sessionid(string), refunded(boolean), amount(integer)
*/
DROP TABLE IF EXISTS adhoc.monthly_revenue_by_channel;

CREATE TABLE adhoc.monthly_revenue_by_channel AS
SELECT TO_CHAR(C.ts, 'YYYY-MM') AS month,
  channel,
  COUNT(DISTINCT C.userid) AS uniqueUsers,
  COUNT(DISTINCT CASE WHEN amount>0 THEN C.userid END) AS paidUsers,
 -- 100.0을 곱함으로써 FLOAT형으로 변환,
 -- 만에 하나, uniqueUsers가 0일 경우를 대비해, NULLIF를 사용해 0이 들어오면 NULL을 반환한다.
 ROUND(paidUsers*100.0/NULLIF(uniqueUsers,0),2) AS conversionRate,
  SUM(D.amount) AS grossRevenue,
  SUM(CASE WHEN refunded = True THEN amount ELSE 0 END) AS netRevenue
FROM (
  SELECT A.ts, B.* 
  FROM raw_data.session_timestamp A
  JOIN raw_data.user_session_channel B
  ON A.sessionid = B.sessionid
) C
LEFT JOIN raw_data.session_transaction D
ON C.sessionid = D.sessionid
GROUP BY 1, 2;
-- 관계형 DB에서 table을 생성할 때, order by는 무시된다.

SELECT * FROM adhoc.monthly_revenue_by_channel
LIMIT 50;

• 복잡한 JOIN시 먼저 JOIN 전략부터 수립

  1. raw_data.user_session_channel
  2. raw_data.session_timestamp
  3. raw_data.session_transaction

  • 위의 3개의 테이블 모두 sessionid를 기반으로 조인을 해야함

  • user_session_channel과 session_timestamp는 one to one
    -> INNERJOIN

  • 하지만 session_transaction의 경우에는 모든 sessionid가 존재하는 것은 아니다.

    • LEFT JOIN
    • FROM에 사용하는 테이블은 user_session_channel 혹은 session_timestamp가 되어야함