[SQL]GROUP BY와 CTAS, 데이터 품질 확인하기

1. GROUP BY &  Aggregate 함수

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

테이블에 존재하는 필드 선택하고, 그 값을 기준으로 그룹화

- 그룹핑을 할 필드 결정 ( 하나 이상의 필드 가능)

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

- 다음 그룹별로 계산할 내용 결정

- Aggregate 함수 사용: count, sum, avg, min, max 등..

 

ex. 월별 세션수를 계산하는 SQL

%%sql

SELECT

    LEFT(ts, 7) AS mon, --ts필드에서 앞자리 7개만 출력 하고 mon으로 저장

    COUNT(1) AS session_count -- 월별로 몇개의 기록이 있는지 확

FROM raw_data.session_timestamp

GROUP BY 1 -- 월별로 그룹핑(mon 값이 같은 것들이 그룹핑 되는 것)

ORDER BY 1;

monsession_count

2019-05	6261
2019-06	10520
2019-07	14840
2019-08	17151
2019-09	15790
2019-10	18899
2019-11	18059

 

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

'가장 많이 사용되었다'의 기준은? 사용자 기반 vs 세션 기반

면접이라면 이 기준 먼저 정해야 함.

필요한 정보 - 채널 정보, 사용자 정보 혹은 세션 정보

필요한 테이블 - user_session_channel, session_timestamp 또는 두 테이블 join

%%sql

SELECT

    channel,

    COUNT(1) AS s  ession_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

channelsession_countuser_count

Youtube	17091	889
Google	16982	893
Naver	16921	882
Organic	16904	895
Instagram	16831	895
Facebook	16791	889

 

ex2. 가장 많은 세션을 만들어낸 사용자 ID는 무엇인가?

필요한 정보 - 세션 정보, 사용자 정보

user_session_channel 테이블 컬럼 확인

%%sql

SELECT * FROM raw_data.user_session_channel

LIMIT 10;

userid - sessionid - channel

184	c41dd99a69df04044aa4e33ece9c9249	Naver
251	0a54b19a13b6712dc04d1b49215423d8	Facebook
744	05ae14d7ae387b93370d142d82220f1b	Facebook
265	4c4ea5258ef3fb3fb1fc48fee9b4408c	Naver

 

userId 별로 묶고 몇 개의 세션Id 기록이 있는지 count 

%%sql

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 10;

userid - count

1615	528
989	429
1493	396
1651	385

 

ex3. 월별로 유니크한 사용자 수 카운트

MAU(Monthly Active User)에 해당

필요한 정보 - 시간 정보, 사용자 정보 

필요한 테이블 - user_session_channel (userid, sessionId, channel), session_timestamp (sessionId, ts) 

userId - sessionID - channel - ts 형태로 테이블 JOIN

 

%%sql

SELECT

  TO_CHAR(A.ts, 'YYYY-MM') AS month, --TO_CHAR 사용해서 A.ts를 문자열로 바꿔주기, 그룹화

  COUNT(DISTINCT B.userid) AS mau -- 연도,월이 같은 레코드 중에 유일한 userId 카운트

FROM raw_data.session_timestamp A -- session_timestamp 테이블 A라고 설정

JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid -- INNER JOIN 두 테이블에서 sessionId가 같은 값만 남기기

GROUP BY 1

ORDER BY 1 DESC;

 

monthmau

2019-11	721
2019-10	763
2019-09	639
2019-08	662
2019-07	623
2019-06	459
2019-05	281

 

Distinct 없이 count를 하면 모든 Id를 세기 때문에 세션 수와 같은 결과를 가짐.

유일한 사용자를 카운트하려면 Distinct를 해줘야함

 

2. CTAS와 CTE

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

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

DE: 외부 데이터를 data warehouse에 저장 -> raw_data 폴더에 테이블 저장

CTAS를 활용해 JOIN된 테이블 데이터 저장. 일회성으로 끝나는 것이 아니라 새로운 테이블에 저장하는 것  (hyuna_session_summary) 

%%sql

DROP TABLE IF EXISTS adhoc.hyuna_sesson_summary; -- adhoc 스키마를 활용해 두 개 테이블 조인 한 것 저장

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

 

3. 항상 확인해야되는 데이터 품질 확인 방법

- 중복된 레코들 체크하기 : 주어진 테이블의 모든 레코드 수 count, 중복 제거한 레코드 수 count 같으면 문제 없

%%sql

SELECT COUNT(1) FROM adhoc.hyuna_session_summary;

%%sql

SELECT COUNT(1)

FROM (

    SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.hyuna_session_summary

);

 

같은 결과 값 가지므로 중복된 레코드 없음

%%sql

With ds AS (

  SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.keeyong_session_summary

)

SELECT COUNT(1)

FROM ds;

ds라는 임시 테이블 생성 후 SELECT 에서 활용 가능. 위의 코드와 같은 결과를 갖지만 로직의 차이. 재사용할 수 있는 WITH 사용이 더 나음 => CTE

 

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

%%sql

SELECT MIN(ts), MAX(ts)

FROM adhoc.keeyong_session_summary;

MAX(ts): 가장 최근 데이터 업로드 시간을 알려줌

 

- Primary key uniqueness가 지켜지는지 체크하기

sessionid만 고유 값을 갖기 때문에 PK가 됨. GROUP BY를 통해 같은 값을 갖는 sessionid끼리 묶고,

카운트 했을 때 1이상이면 pk uniqueness가 깨지게 된다. 

%%sql

SELECT sessionId, COUNT(1)

FROM adhoc.keeyong_session_summary

GROUP BY 1

ORDER BY 2 DESC

LIMIT 1;

 

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

%%sql

SELECT

    COUNT(CASE WHEN sessionId is NULL THEN 1 END) sessionid_null_count, -- null이면 1 아니면 null 리턴되고 count 불가. 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.hyuna_session_summary;