[TIL] 데이터분석 데브코스 23일차 - GROUP BY/AGGREGATE(집계) 함수/CTAS/CTE/데이터 품질 확인 방법

GROUP BY

: 테이블의 레코드를 특정 필드(컬럼)를 기준으로 그룹핑하여 해당 그룹별로 다양한 정보를 계산

 

그룹핑 절차

1) 그룹핑 할 필드를 결정

• `GROUP BY`로 지정해준다.

 

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

• AGGREGATE 함수(집계함수)를 사용한다.
  ex. `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `LISTAGG` 등

 

 

 

 

 

 

 

SQL 실습(with Google Colab)

[문제1] 월별 총 세션 수

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;

 

[문제2] 가장 많이 사용된 채널

▶ '가장 많이 사용되었다.' 의 정의가 무엇인지 명확히 해야함 → 사용자 or 세션 기반?

▶ 필요한 정보 : 채널 정보, 사용자 정보(혹은 세션 정보)

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

 

 

 

[문제3] 가장 많은 세션을 만들어 낸 사용자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;

 

 

 

[문제4] 월별 유니크한 사용자 수(MAU)  (단, 한 명의 사용자는 한 번만 카운트되어야 함)

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

SELECT
	TO_CHAR(A.ts, 'YYYY-MM') AS month,
    COUNT(DISTINCT B.userid) AS mau -- 유일한 사용자ID만 카운트
FROM raw_data.session_timestamp A 
JOIN raw_data.user_session_channel B -- INNER JOIN
ON A.sessionid = B.sessionid
GROUP BY 1
ORDER BY 1 DESC;

 

 

`TO_CHAR(A.ts, 'YYYY-MM')` 과 같은 의미인 함수들

LEFT(A.ts, 7) -- 주어진 문자열에서 왼쪽에서부터 7의 길이만큼 추출(리턴 결과: 문자열)
DATE_TRUNC('month', A.ts) -- 주어진 타임스탬프에서 원하는 단위까지 추출 (리턴 결과: 타임스탬프)
SUBSTRING(A.ts, 1, 7) -- 주어진 문자열을 1번째부터 7의 길이만큼 추출(리턴 결과: 문자열)

 

 

 

[문제5] 월별, 채널별 유니크한 사용자 수

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

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 (CREATE TABLE AS SELECT)

: `SELECT` 문으로 간단하게 새로운 테이블을 생성하는 구문

 

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

 

 

DROP TABLE IF EXISTS adhoc.본인이름_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;

 

 

 

 

 

 

 

CTE (Common Table Expressions)

: `WITH 블록명 AS( ~ );` 쿼리문으로 만들어 낸 임시 테이블

 

 

 

 

 

 

 

데이터 품질을 확인하는 방법

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

-- 주어진 테이블의 모든 레코드 수 출력해보기
SELECT COUNT(1)
FROM adhoc.본인이름_session_summary;

-- 중복이 제거된 테이블의 모든 레코드 수 출력해보기
SELECT COUNT(1)
FROM(
	SELECT DISTINCT userId, sessionId, ts, channel
    FROM adhoc.본인이름_session_summary
);

 

-- CTE를 사용해서 중복을 제거한 후 카운트
WITH ds AS(
	SELECT DISTINCT userId, sessionId, ts, channel
    FROM adhoc.본인이름_session_summary
)
SELECT COUNT(1)
FROM ds;

 

 

 

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

-- 타임스탬프의 최소/최댓값을 출력해서 데이터가 언제 것인지 파악
SELECT MIN(ts), MAX(ts)
FROM adhoc.본인이름_session_summary;

 

 

 

3. Primary Key Uniqueness가 지켜지는지 체크하기

-- pk를 기준으로 그룹핑한 후 해당 그룹의 개수 카운트(그룹 1개만)
-- 카운트 값 > 1 이면 중복이 있다는 의미
SELECT sessionId, COUNT(1) -- pk : sessionId
FROM adhoc.본인이름_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

 

 

 

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

SELET
	COUNT(CASE WHEN session 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;

 

 

 

 

 

 

 

 

 

과제 #1 실습하기

과제 목표 : 채널별 월 매출액 테이블 만들기 (본인 스키마 밑에 CTAS로 테이블 만들기)

필드 구성 : month, channel, uniqueUsers(총방문 사용자), paidUsers(구매 사용자: refund한 경우도 판매로 고려), conversionRate(구매사용자/총방문사용자), grossRevenue(refund 포함), netRevenue(refund 제외)

 

새로운 테이블 

1) session_transaction : 구매 정보를 담은 테이블

- sessionid varchar(32) # 세션아이디(pk)

- refunded boolean # 환불여부

- amount int # 구매 가격

** 모든 세션이 구매를 하진 않는다. -> LEFT JOIN 사용

 

테이블명	필드명(타입)
session_timestamp	sessionid(string), ts(timestamp)
user_session_channel	userid(integer), sessionid(string), channel(string)
session_transaction	sessionid(string), refunded(boolean), amount(integer)
channel	channelname(string)

 

DROP TABLE IF EXISTS adhoc.ybseo_session_transacition;
CREATE TABLE adhoc.ybseo_session_transaction AS 
SELECT B.*, A.ts FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid;

SELECT month, channel, uniqueUsers, paidUsers, conversionRate, grossRevenue, netRevenue
FROM session_timestamp ts
INNER JOIN user_session_channel sc ON ts.sessionid = sc.sessionid
LEFT JOIN session_transaction st ON ts.sessionid = st.sessionid
GROUP BY 1
ORDER BY