6주차-3

11/15 - 데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습(3)

 

1. Group By & Aggregate 함수

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;

 

>> 가장 많이 사용된 채널

SELECT channel, COUNT(1) AS session_count, COUNT(DISTINCT userid) AS user_count
FROM raw_data.user_session_channel
GROUPBY 1
ORDERBY 2 DESC;
LIMIT 1;

 

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

SELECT userid, COUNT(1) AS count
FROM raw_data.user_session_channel
GROUPBY 1
ORDERBY 2 DESC
LIMIT 1;

 

>> 월별 유니크한 사용자 수

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 B.userid) AS mau와 COUNT(DISTINCT B.userid) mau는 동일

 

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

SELECT TO_CHAR(a.ts, "YYYY-MM"), channel, COUNT(DISTINCT b.userid) AS MAU
FROM raw_data.user_session_channel a JOIN raw_data.session_timestamp b on a.sessionid = b.sessionid
GROUPBY 1, 2
ORDERBY 1 DESC 2;

 

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

간단하게 새로운 테이블을 만드는 방법

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

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;

 

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

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

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

SELECT COUNT(1)
FROM adhoc.keeyong_session_summary;

SELECT COUNT(1)
FROM (
 SELECT DISTINCT userId, sessionId, ts, channel
 FROM adhoc.keeyong_session_summary
);

 

두 값이 같으면 중복된 레코드 없다는 것을 확인

With ds AS (
 SELECT DISTINCT userId, sessionId, ts, channel
 FROM adhoc.keeyong_session_summary
)
SELECT COUNT(1)
FROM ds;

 

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

 

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

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

 

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

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;