GROUP BY

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

1. 먼저 그룹핑을 할 필드를 결정 (하나 이상의 필드)
   → GROUP BY로 지정 (필드 이름을 사용하거나 필드 일련번호를 사용)

2. 다음 그룹별로 계산할 내용을 결정
   → 여기서 AGGREGATE함수 사용(COUNT, SUM, AVG, MIN, LISTAGG, ...)
   → 보통 필드 이름을 지정하는 것이 일반적(alias)

• 월별 세션수를 계산하는 SQL
  • raw_data.session_timestamp 사용 (sessionId와 ts필드)

SELECT
 LEFT(ts,7) AS 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;

• 가장 많이 사용된 채널은 무엇인가?
  • 가장 많이 사용되었다는 정의는? : 사용자 기반? or 세션 기반?
  • 필요한 정보 : 채널 정보, 사용자 정보 혹은 세션 정보
  • 어느 테이블을 사용해야 하는지 : user_session_channel or session_timestamp? or 이 2개의 테이블 조인?

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는 무엇인가?
  • 필요한 정보 : 세션 정보, 사용자 정보
  • 어느 테이블을 사용해야 하는지 : user_session_channel or session_timestamp? or 이 2개의 테이블 조인?

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

• 월별 유니크한 사용자 수
  • MAU(Monthly Active User)에 해당
  • 필요한 정보 : 시간 정보, 사용자 정보
  • 어느 테이블을 사용해야 하는지 : user_session_channel(userId, sessionId, channel)? or session_timestamp(sessionId, ts)? or 이 2개의 테이블 조인

SELECT
 TO_CHAR(A.ts, 7) AS month -- TO_CHAR(A.ts, 'YYYY-MM')
 COUNT(DISTINCT B.userId) AS mau
FROM raw_data.sessin_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid
GROUP BY 1
ORDER BY 1 DESC;

• 월별 채널별 유니크한 사용자 수
  • 필요한 정보 : 시간 정보, 사용자 정보, 채널 정보
  • 어느 테이블을 사용해야 하는지 : user_session_channel과 session_timestamp 두 테이블 조인

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
ORDER BY 1 DESC, 2;

CTAS

SELECT를 가지고 테이블 생성

• 자주 조인되는 테이블을 미리 만들어 놓을 때 자주 사용

CREATE TEBLE adhoc.table_name 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(DISTINCT userId) AS mau
FROM adhoc.table_name
GROUP BY 1
ORDER BY 1 DESC;

데이터 품질 확인 방법들

• 중복된 레코드들 체크하기

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

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

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

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

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

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

• Primary key uniquenesss가 지켜지는지 체크하기

SELECT sessionId, COUNT(1)
FROM adhoc.table_name
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.table_name;