1. GROUP BY & Aggregate 함수

  • 테이블의 레코드들을 그룹핑하여 그룹별로 다양한 정보 계산
  • 단계
    • 먼저 그룹핑을 할 필드 결정 (하나 이상의 필드가 될 수 있음)
    • 다음 그룹별로 계산할 내용 결정
      • 여기서 Aggregate 함수 사용
      • COUNT, SUM, AVG, MIN, MAX, LISTAGG, …

월별 세션 수 계산

SELECT 
    LEFT(ts, 7) AS mon,  -- field, 왼쪽에서 부터 몇번째까지
    COUNT(1) AS session_count
FROM raw_data.session_timestamp
GROUP BY 1 -- GROUP BY mon, GROUP BY LEFT(ts, 7)
ORDER BY 1;
  • ts 필드
    • timestamp, 문자열 함수에 들어가면 string으로 바뀜
    • YYYY-mm-dd-hh-mm-ss.ms
    • 7번째 자리까지 하면 년, 월을 뽑을 수 있음

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

  • 가장 많이 사용되었다는 정의?
    • 사용자 기반 혹은 세션 기반?
  • 필요한 정보 : 채널 정보, 사용자 정보, 혹은 세션 정보
  • 먼저 어느 테이블을 사용해야하는지 생각!
    • user_session_channel
SELECT 
    channel,
    COUNT(1) AS channel_count,
    COUNT(DISTINCT userId) AS user_count
FROM raw_data.user_session_channel
GROUP BY 1
ORDER BY 2 DESC;

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

  • 필요한 정보 : 세션 정보, 사용자 정보
  • 먼저 어느 테이블을 사용해야하는지 생각!
    • user_session_channel
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)
    • join!
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;

string cut

  • TO_CHAR(A.ts, 'YYYY-MM')
  • LEFT(A.ts, 7)
  • DATE_TRUNC('month', A.ts)
  • SUBSTRING(A.ts, 1, 7)

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

  • 필요한 정보 : 시간 정보, 사용자 정보, 채널 정보
  • 먼저 어느 테이블을 사용해야하는지 생각
    • user_session_channel (userId, sessionId, channel)
    • session_timestamp (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_chanel B ON A.sessionId = B.sessionId
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

3. CTAS

  • SELECT로 간단하게 새로운 테이블 생성하는 방법
  • 자주 조인하는 테이블들이 있다면 CTAS를 사용해 조인해두면 편리해짐
CREATE TABLE adhoc.bk_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. 데이터 품질 확인

중복된 레코드 확인

  • count 비교

  • 전체 count와 DISTINCT count 결과가 같으면 통과!

  • CTAS
SELECT COUNT(1)
FROM adhoc.bk_session_summary;

SELECT COUNT(1)
FROM (
    SELECT DISTINCT userId, sessionId, ts, channel    -- 모든 필드
    FROM adhoc.bk_session_summary
);


  • CTE
With ds AS (
    SELECT DISTINCT userId, sessionId, ts, channel
    FROM adhoc.bk_session_summary
)
SELECT COUNT(1)
FROM ds;
  • With _ AS
    • AS 다음 결과가 _ 이름의 임시 table로 생성됨
    • 뒷단에서 반복 사용할 수 있기 때문에 조금 더 좋음

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

  • timestamp / create 등의 필드가 있다면 확인
SELECT MIN(ts), MAX(ts)
FROM adhoc.bk_session_summary;

Primary key uniqueness가 지켜지는지 체크

  • LIMIT 1을 했을 때 count가 1보다 크면 지켜지지 않고 있음을 나타냄
SELECT sessionId, COUNT(1)    -- sessionId가 pk
FROM adhoc.bk_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

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

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