GROUP BY와 CTAS

GROUP BY와 AGGREGATE함수

---

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

두 단계로 이루어짐

1.먼저 그룹핑을 할 필드를 지정(하나 이상의 필드가 될 수 있음)

• GROUP BY로 지정(필드 이름을 사용하거나 필드 일련번호를 사용)

2.다음 그룹별로 계산할 내용을 지정

• 여기서 AGGREGATE함수를 사용
• COUNT, SUM, AVG, MIN, MAX, LISTAGG,...
  보통 필드 이름을 지정하는 것이 일반적이다.

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

SELECT 
     LEFT(ts, 7) AS mon, -- timestamp형식이 문자열 함수에 들어가 문자열로 추출됨
     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 필드를 기준으로 오름차순 정렬

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

• 가장 많이 사용되었다는 정의는? 사용자 기반인지 세션 기반인지
• 먼저 어느 테이블을 사용해야하는지 생각하자

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

EX3) 가장 많은 세션을 만들어낸 사용자 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; -- 가장 많은 세션을 만든 사용자

EX4) 월별 유니크한 사용자 수(MAU)

• 필요한 정보 : 시간 정보, 사용자 정보

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;

*TO_CHAR: timestamp 타입을 문자열로 변환
LEFT(A.ts, 7)
DATE_TRUNC('month', A.ts): 이것만 timestamp 타입으로 반환, 해당 월 1일로 바꿈
SUBSTRING(A.ts, 1, 7)

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

• 필요한 정보 : 시간 정보, 사용자 정보, 채널 정보

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.junhee_session_summary; -- 테이블이 이미 존재하면 DROP 
CREATE TABLE adhoc.junhee_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;

월별 유니크한 사용자 수를 다시 작성
SETECT
     TO_CHAR(A.ts, 'YYYY_MM') AS month, 
     COUNT(DISTINCT userid) AS mau
FROM adhoc.junhee_session_summary
GROUP BY 1
GROUP BY 1 DESC

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

• 중복된 레코드들 체크하기
• 최근 데이터의 존재 여부 체크하기(freshness)
• Primary key uniqyeness가 지켜지는지 체크하기
• 값이 비어있는 칼럼들이 있는지 체크하기

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

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

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

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

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

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

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

4.Primary key uniqueness가 지켜지는지 체크하기
여기서는 sessionId가 Primary key

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

5. 값이 비어있는 칼럼들이 있는지 체크하기

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

---