[TIL] 데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습 (3)

학습내용

---

1. GROUP BY와 AGGREGATE 함수

2. CTAS와 CTE 소개
   
   

1. GROUP BY와 AGGREGATE 함수

---

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

• 그룹핑할 하나 이상의 필드를 GROUP BY로 지정
  
  

• Aggregate 함수 사용해 그룹 별로 계산할 내용 지정

  • COUNT, SUM, AVG, MIN, MAX, LISTAGG, ...
    • 보통 이름(alias)을 설정
      
      

• 월 별 세션 수 계산 SQL

  • SQL을 작성하기 전에 어느 테이블이 필요한지 먼저 확인해야 함
    
    

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

  
  

• 가장 많이 사용된 채널 계산 SQL

  • "가장 많이 사용된"의 의미가 사용자 기반인지 혹은 세션 기반인지 확인해야 함
    
    

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

• 가장 많은 세션을 만들어낸 사용자 ID 계산 SQL

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

  
  

• 월별 유니크한 사용자 수 계산 SQL

  • = MAU(Monthly Active User)
    
    

  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 month
  ORDER BY month DESC;

2. CTAS와 CTE 소개

---

• CTAS

  • 간단하게 새로운 테이블을 만드는 방법

  • 자주 조인하는 테이블들이 있다면 CTAS를 사용해서 이를 조인해두면 편함
    
    

  DROP TABLE IF EXISTS adhoc.session_summary;
  
  CREATE TABLE adhoc.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;

  
  

• CTE

  • 재사용 가능한 임시 테이블
    
    

  With new_table AS (
      SELECT userId, sessionId, ts, channel
      FROM adhoc.session_summary;
  );
  
  SELECT COUNT(*) FROM new_table;

  
  

• 데이터 품질 확인

  • 중복된 레코드 체크

    -- 둘의 값이 같다면 중복된 레코드가 없는 것
    
    SELECT COUNT(*)
    FROM adhoc.session_summary;
    
    SELECT COUNT(*)
    FROM (SELECT DISTINCT userId, sessionId, ts, channel
    	  FROM adhoc.session_summary);

    
    

  • 최근 데이터 존재 여부 체크

    • 확인하고자 하는 테이블에 timestamp 타입의 필드가 존재한다면 MIN, MAX값 확인
      
      

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

      
      

  • Primary key uniqueness 지켜지는지 체크

    • Primary key로 그룹핑하고 COUNT해서 1보다 큰 값이 있다면 지켜지지 않은 것
      
      

      SELECT sessionId, COUNT(*) AS count
      FROM adhoc.session_summary
      GROUP BY sessionId
      ORDER BY DESC
      LIMIT 1;

      
      

  • 값이 비어있는 컬럼이 있는지 체크

    SELECT
    	COUNT(CASE WHEN sessionId IS NULL THEN 1 END) AS sessionId_null_count
        COUNT(CASE WHEN userId IS NULL THEN 1 END) AS userId_null_count
        COUNT(CASE WHEN ts IS NULL THEN 1 END) AS ts_null_count
        COUNT(CASE WHEN channel IS NULL THEN 1 END) AS channel_null_count
    FROM adhoc.session_summary;

• 테이블 2개 추가

  • session_transaction

    • 상품 반품 여부와 구매 수량 필드 포함
      
      

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

      
      

  • channel

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

메모

---