DevCourse TIL Day3 Week6

오늘은 SQL 문법 중 어제 학습한 select문에 이어 GROUP BY 관련 함수, CTAS 문법에 대해 알아보고자 한다.

✅ GROUP BY

테이블의 레코드를 그룹핑하여 그룹별 다양한 정보 계산.
이때 Aggregate함수 (COUNT, SUM, MAX, MIN, AVG, LISTAGG 등 사용)
보통 필드이름 지정(alias)
오늘은 주어진 두개의 테이블로 다음 과정을 실습해보려 한다.
테이블1 ) raw_data.session_timestamp

• sessionid, ts
  테이블2 ) rawdata.user_session
• userid, sessionid, channel

< 월 별 총 세션 수 >

SELECT LEFT(ts, 7) as MON, COUNT(1) as session_count
FROM raw_data.user_session_timestamp
GROUP BY MON
ORDER BY MON 

< 가장 많이 사용된 채널 >

SELECT channel, COUNT(1) AS session_count, count(DISTINCT userid) as user_count
FROM raw_data.user_session_channel
GROUP BY channel
ORDER BY session_count DESC

< 가장 많은 세션을 만들어낸 상위 10명의 사용자 ID >

SELECT userid, COUNT(1) as counting
FROM raw_data.user_session_channel
GROUP BY userid
ORDER BY counting DESC
LIMIT 10;

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

SELECT TO_CHAR(A.ts, 'YYYY-MM') as Month, COUNT(DISTINCT B.userid) AS MAU
FROM raw_data.session_timestamp as A
JOIN raw_data.user_session_channel as B on A.sessionid = B.sessionid
GROUP BY Month
ORDER BY Month

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

SELECT TO_CHAR(A.ts, 'YYYY-MM') AS Month, channel, COUNT(DISTINCT B.userid) as MAU
FROM raw_data.session_timestamp as A
JOIN raw_data.user_session_channel as B ON A.sessionid = B.sessionid
GROUP BY Month, channel
ORDER BY Month, channel

✅ CTAS

SELECT를 가지고 테이블 생성

• DROP TABLE IF EXISTS 사용해 테이블 존재하면 삭제진행
• CREATE TABLE로 테이블 생성 이후 SELECT로 타 테이블로부터 조회
  ex) 기존 2개 테이블 sessionid로 병합하여 진행

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

💯 항상 시도해보아야 하는 데이터 품질 확인

• 중복된 레코드 체크

# 서브쿼리 사용 (CTE 구문 WITH 사용해)
With ds AS (SELECT DISTINCT userId, sessionId, ts, channel FROM TJ)
SELECT COUNT(1)
FROM ds;

-> CTE 구문으로 SELECT문을 미리 정의해 이름을 붙인 후, 이어지는 쿼리에서 테이블처럼 사용함

• 최근 데이터 존재여부 체크

SELECT MIN(ts), MAX(ts) FROM TJ;

• PK uniqueness 여부

SELECT sessionId, COUNT(1) as COUNTING
FROM TJ
GROUP BY sessionid
ORDER BY COUNTING DESC
LIMIT 1;

• null값 체크

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

✅ Homework

테이블 소개) raw_data.session_transaction

• sessionid, refunded (Boolean), amount
  테이블 소개) raw_data.channel
• channelName

< 채널별 월 매출액 테이블 만들기 >

DROP TABLE IF EXISTS adhoc.taejunkim_channel_month_sales;
CREATE TABLE adhoc.taejunkim_channel_month_sales as TJ
SELECT LEFT(ts, 7) as month, channel, COUNT(DISTINCT usc.userid) uniqueUsers,
       COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
       ROUND(paidUsers * 100.0 / NULLIF(uniqueUsers, 0), 2) conversionRate,
       SUM(amount) grossRevenue,
       SUM(CASE WHEN refunded is False THEN amount END) netRevenue

FROM raw_data.user_session_channel as usc
LEFT JOIN raw_data.session_timestamp as time ON time.sessionid = usc.sessionid
LEFT JOIN raw_data.session_transaction as trans ON trans.sessionid = usc.sessionid
GROUP BY month, channel
ORDER BY month, channel;