[데브코스][데이터 분석] SQL 심화 (GROUP BY와 CTAS)

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

 

1. GROUP BY와 AGGRECATE 함수

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

 

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

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

2) 그룹별로 계산할 내용 결정

    -> Aggregate 함수 사용 (COUNT, SUM, AVG, MIN, MAX, LISTAGG, ...)

 

월별 총 세션 수

-- 월별 세션수를 계산하는 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;

 

ts가 원래는 timestamp 함수이지만, 문자열 함수(LEFT)를 쓰면 자동으로 문자열로 바뀜

-> "연도 4자리 - 월 2자리 " 인 7자리 문자열로 뽑아냄

+ 일별일 경우 LEFT(ts, 10)을 하면 됨

 

가장 많이 사용된 채널

* 가장 많이 사용되었다는 정의는?

: 사용자 기반 아니면 세션 기반? -> 이런 문제를 받으면 문제 정의를 확실히 되묻을 필요가 있음

 

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

 

1) 사용자 기반으로 가장 많이 사용된 채널

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 3 DESC;
-- ORDER BY user_count DESC

 

 

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

 

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

 

- TO_CHAR(A.ts, 'YYYY-MM') : 문자열로 바꿔주는 함수

= LEFT(A.ts, 7), DATE_TRUNC('month', A.ts), SUBSTRING(A.ts, 1, 7)

 

- COUNT(DISTINCT xx) : userid가 유일한 값들만 카운드(유일한 userid의 수 = MAU)

-> 월 단위 안에서 유니크한 사용자 수만 count 했기 때문

 

 

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

위와 유사. 

 

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

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

 

groupby에서 여러 개의 필드 지정. 

 

2. CTAS와 CTE 소개

CTAS

: SELECT를 이용해 쉽게 테이블을 만드는 방법

자주 join하는 테이블들이 있다면 이를 CTAS를 사용해 조인하면 편리해짐

DROP TABLE IF EXISTS adhoc.keeyong_session_summary;

-- 충돌방지를 위해 자기 이름 설정
CREATE TABLE adhoc.자기이름_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;

 

자기이름_session_summary에 join한 결과를 기록한 테이블을 담음

 

==> 이렇게 JOIN 결과를 담은 테이블을 간편하게 활용할 수 있음!

-- 월별 유니크한 사용자 수
SELECT
	TO_CHAR(ts, 'YYYY-MM') AS month, 
	COUNT(DISTINCT userid) AS mau
FROM adhoc.자기이름_session_summary 
GROUP BY 1
ORDER BY 1 DESC;

 

 

데이터 품질 확인 방법

- 중복된 레코드 있는지 체크

: 두 개 쿼리의 카운트 비교

-- 주어진 테이블의 모든 레코드 수 체크
SELECT COUNT(1)
FROM adhoc.자기이름_session_summary;

-- 중복을 제거한 레코드 수 체크
SELECT COUNT(1) FROM (
	SELECT DISTINCT userId, sessionId, ts, channel
	FROM adhoc.자기이름_session_summary
);

 

==> 두 개 쿼리 결과가 동일하면, 중복된 레코드가 없는 것임!

 

* CTE를 사용해 중복 제거 후 카운트를 해보자. (WITH 구문)

-- 임시 테이블 ds
With ds AS (
	SELECT DISTINCT userId, sessionId, ts, channel 
	FROM adhoc.자기이름_session_summary
)
SELECT COUNT(1) 
FROM ds;

 

 

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

SELECT MIN(ts), MAX(ts)
FROM adhoc.자기이름_session_summary;

 

 

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

SELECT sessionId, COUNT(1)
FROM adhoc.자기이름_session_summary 
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

 

--> primary key는 sessionID임. 이 수를 count해보고, 이를 기준으로 내림차순 정렬한다. 

그 후, count 값이 1보다 크면 중복이 있다는 것임

==> 중복값이 없다면 group by를 했을 때 count는 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.자기이름_session_summary;