데이터 웨어하우스, SQL, 데이터분석 (3)

GROUP BY & Aggregate 함수

• 테이블의 레코드를 그룹화하여 그룹별로 다양한 정보를 계산
• 두 단계로 이루어짐
  • GROUP BY를 사용해 그룹핑 할 필드를 결정 (하나 이상의 필드 설정 가능)
  • Aggreagate 함수를 사용해 계산할 내용 결정 (COUNT, SUM, AVG, MIN, MAX, LISTAGG...)
• 월별 세션수를 계산하는 SQL

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
GROUP BY 1  -- GROUP BY channel
ORDER BY 2 DESC;  -- ORDER BY session_count DESC

• 가장 많은 세션을 만들어낸 사용자의 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;

• 월별 유니크한 사용자 수
  (COUNT의 동작 이해와 DISTINCT와의 연동을 잘 이해하는 것이 중요!)

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;

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

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;

---

CATS

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

DROP TABLE IF EXISTS 테이블 이름;
CREATE TABLE 복사하고자 하는 테이블 AS
SELECT B.*, A ts FROM 테이블 이름 A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid;

• 데이터 품질 확인하는 방법들
  중복된 레코드 확인하기 (카운트 된 숫자가 같아야지 중복된 레코드가 없는 것)

SELECT COUNT(1)
FROM 테이블 이름;

SELECT COUNT(1)
FROM(
    SELECT DISTINCT userId, sessionId, ts, channel
    FROM 테이블 이름
);

 

        CTE를 사용해 중복 제거 후 카운트 확인하기

 

With (테이블 이름 설정) AS (
    SELECT DISTINCT userId, sessionId, ts, channel
    FROM 테이블 이름
)
SELECT COUNT(1)
FROM (With에서 설정한 테이블 이름);

 

        최근 데이터의 존재 여부 확인 (Freshness)

 

SELECT MIN(ts), MAX(ts)
FROM 테이블 이름;

 

        Primary key uniqueness가 지켜지는지 확인

 

SELECT sessionId, COUNT(1)
FROM 테이블 이름
GROUP BY 1
ORDER BY 2 DESC
LIMIT 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 확인하고자 하는 테이블 이름;