데이터 웨어하우스와 SQL과 데이터분석 (3)

GROUP BY & Aggregate 함수

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

이는 두 단계로 이뤄짐

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

월별 세션수를 계산하는 SQL

• raw_data.session_timestamp를 사용 (sessionId와 ts 필드)

SELECT
 LEFT(ts, 7) AS mon, -- left를 쓰는 순간 ts가 문자열로 바뀜 7개 빼면 yyyy-mm 가져옴
 COUNT(1) AS session_count
FROM raw_data.session_timestamp
GROUP BY 1 -- GROUP BY mon, GROUP BY LEFT(ts, 7)
ORDER BY 1;

-> 앞서 설명한 raw_data.session_timestamp raw_data.user_session_channel 테이블들을 사용해서 다음을 계산하는 SQL을 만들어 보자

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

• 가장 많이 사용되었다는 정의는?
  - 사용자 기반 아니면 세션 기반?
• 필요한 정보 - 채널 정보, 사용자 정보 혹은 세션 정보
• 먼저 어느 테이블을 사용해야하는지 생각!
  - user_session_channel?
  - session_timestamp?
  - 혹은 이 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?
  - session_timestamp?
  - 혹은 이 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)?
  - session_timestamp (sessionId, ts)?
  - 혹은 이 2개의 테이블을 조인해야하나?

--내부 조인(inner 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;

TO_CHAR (A.ts, ‘YYYY-MM’)와 같은 기능을 하는 코드

• LEFT(A.ts, 7)
• DATE_TRUNC(‘month’, A.ts) : ts타입의 값을 리턴
• SUBSTRING(A.ts, 1, 7)

필드/테이블 이름에 Alias 사용. AS는 필수가 아님.

• COUNT(DISTINCT B.userid) AS mau와
• COUNT(DISTINCT B.userid) mau는 동일

ORDER BY와 GROUP BY:

• 포지션 번호 vs. 필드 이름
  - GROUP BY 1 == GROUP BY month == GROUP BY TO_CHAR(A.ts, 'YYYY-MM'0)

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

• 필요한 정보 - 시간 정보, 사용자 정보, 채널 정보
• 먼저 어느 테이블을 사용해야하는지 생각!
  - user_session_channel (userId, sessionId, channel)?
  - session_timestamp (sessionId, ts)?
  - 혹은 이 2개의 테이블을 조인해야하나?

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와 CTE 소개

CTAS: SELECT를 가지고 테이블 생성

• 간단하게 새로운 테이블을 만드는 방법
• 자주 조인하는 테이블들이 있다면 이를 CTAS를 사용해서 조인해두면 편리해짐

--충돌을 막기 위해 summary 앞에 영문이름 넣음
DROP TABLE IF EXISTS adhoc.keeyong_session_summary;
CREATE TABLE adhoc.keeyong_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;

월별 유니크한 사용자 수를 다시 풀어보기

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

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

1. 중복된 레코드들 체크하기

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

SELECT COUNT(1)
FROM adhoc.keeyong_session_summary;
-- 문제가 없으면 동일해야 함
SELECT COUNT(1)
FROM (
 SELECT DISTINCT userId, sessionId, ts, channel
 FROM adhoc.keeyong_session_summary
);

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

-- CTE : FROM 안에 넣지 않고 외부로 빼서 사용 (재사용 가능)
With ds AS (
 SELECT DISTINCT userId, sessionId, ts, channel
 FROM adhoc.keeyong_session_summary
)
SELECT COUNT(1)
FROM ds;

2. 최근 데이터의 존재 여부 체크하기 (freshness)

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

3. Primary key uniqueness가 지켜지는지 체크하기

-- 1보다 크면 중복이 있다
SELECT sessionId, COUNT(1)
FROM adhoc.keeyong_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

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

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