[TIL Day46] SQL과 데이터분석 - GROUP BY와 CTAS

GROUP BY와 Aggregate 함수

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

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

• 예제1. 월별 세션수를 계산하는 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; -- mon 필드를 기준으로 오름차순 정렬

• 예제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

• 예제3. 가장 많은 세션을 만들어낸 사용자 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; -- 가장 많은 세션을 만든 사용자만 보기

• 예제4. 월별 유니크한 사용자 수(MAU)
  - 필요한 정보 - 시간 정보, 사용자 정보
  - TO_CHAR: timestamp 타입을 문자열로 변환
  • LEFT(A.ts, 7)
  • DATE_TRUNC('month', A.ts): timestamp 타입을 변환, 해당 월 1일로 바꿈
  • SUBSTRING(A.ts, 1, 7)

SETECT
     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 -- INNER JOIN(조건에 맞는 레코드만 남음)
GROUP BY 1
GROUP BY 1 DESC;

• 예제5. 월별 채널별 유니크한 사용자 수
  - 필요한 정보 - 시간 정보, 사용자 정보, 채널 정보

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

CTAS와 CTE 소개

CTAS란 SELECT를 가지고 간단하게 새로운 테이블을 만드는 방법. 자주 조인하는 테이블들이 있다면 이를 CTAS를 사용해서 조인해두면 편리해진다.

DROP TABLE IF EXISTS adhoc.dahye_session_summary; -- 테이블이 이미 존재하면 DROP 
CREATE TABLE adhoc.dahye_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;

데이터 품질 확인 방법들

• 중복된 레코드들 체크하기(1)
  - 다음 두 개의 카운트를 비교

SELECT COUNT(1) FROM adhoc.dahye_session_summary;	-- 모든 레코드 수 체크

SELECT COUNT(1)
FROM (
    SELECT DISTINCT userId, sessionId, ts, channel	-- 유일한 레코드 수 체크
    FROM adhoc.dahye_session_summary
);

• 중복된 레코드들 체크하기(2)
  - CTE를 사용해서 중복 제거 후 카운트 해보기

With ds AS (	-- 중복 제거의 결과가 ds라는 이름의 임시 테이블로 만들어짐
  SELECT DISTINCT userId, sessionId, ts, channel 
  FROM adhoc.dahye_session_summary
)
SELECT COUNT(1)
FROM ds;

• 최근 데이터의 존재 여부 체크하기(freshness)
  - 필드에서 가장 작은 값, 가장 큰 값 확인

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

• Primary key uniqueness가 지켜지는지 체크하기
  - 세션아이디별 레코드가 1개 보다 많은 세션아이디가 있는지 확인

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

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

SELECT
    COUNT(CASE WHEN sessionId is NULL THEN 1 END) sessionid_null_count,	-- 값이 NULL이면 카운트
    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.dahye_session_summary;