📖 학습주제

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

---

GROUP BY & Aggregate 함수

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

1. 그룹핑을 할 필드를 결정 (하나 이상의 필드가 될 수 있음)
   • GROUP BY로 지정 (필드 이름을 사용하거나 필드 일련번호를 사용)
2. 그룹별로 계산할 내용을 결정
   • Aggregate함수를 사용
   • COUNT, SUM, AVG, MIN, MAX, LISTAGG

e.g.) 월별 세션수 계산

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;

e.g.) 가장 많이 사용된 채널

세션 기반 or 유저 기반 생각

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 user_count DESC

e.g.) 가장 많은 세션을 만들어낸 사용자 ID

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;

e.g.) 월별 사용자 수(중복 x) -> MAU(Monthly Active User)

user_session_channel 테이블과 session_timestamp 모두 필요
-> 두 테이블 조인

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;

e.g.) 월별 채널별 사용자 수(중복 x)

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를 가지고 테이블 생성
• 간단하게 새로운 테이블을 만들 수 있음
• 자주 조인하는 테이블들이 있다면 이를 CTAS를 사용해서 조인해두면
  편리해짐

e.g.)

CREATE TABLE adhoc.trick_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;

e.g.) 월별 사용자 수(중복 x)

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

---

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

중복된 레코드들 체크

다음 두 개의 카운트를 비교

SELECT COUNT(1)
FROM adhoc.trick_session_summary;

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

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

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

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

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

Primary key uniqueness가 지켜지는지 체크

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

CASE WHEN 문에서 ELSE가 없어 NULL값을 리턴(COUNT는 NULL이 아닌 값만을 센다.)