CTAS

• SELECT를 가지고 테이블 생성
• 자주 조인하는 테이블들이 있다면 이를 CTAS를 사용해서 조인해두면 편리해짐

고려해야할점

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

SQL만들기

%%sql

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;

• session_timestamp를 A
• user_session_channel를 B로
• sessionid를 매개로 두개의 테이블을 조인해줌
• B는 전체컬럼을 A는 ts 컬럼만 가져와서
• keeyong_session_summary라는 테이블을 만들고 조인한 테이블을 넣어줌
• IF EXISTS는 테이블을 새로 만들때 같은 이름의 테이블이 있으면 오류가 생기니까 이미 있는지를 체크해서 지워주는 쿼리

%%sql

SELECT 
  TO_CHAR(ts, 'YYYY-MM') AS month,
  COUNT(DISTINCT userid) AS mau
FROM adhoc.keeyong_session_summary
GROUP BY 1 
ORDER BY 1 DESC;

• 원래는 로우데이터에서 가져와줬다면 지금은 미리 만들어둔 테이블에서 데이터를 가져와줌

중복체크하기

DISTINCT 사용하기

%%sql

SELECT COUNT(1) FROM adhoc.keeyong_session_summary;

%%sql

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

• 일반 레코드 갯수를 체크하고
• 고유 아이디, 세션아이디, 타임스탬프 셋다 고유값만 남긴 레코드 갯수를 체크
• 두 SQL 결과 갯수가 같음 -> 중복 없음

With ds AS 사용하기

%%sql

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

• 데이터를 FROM, SELECT를 할때 중복을 제거하고 FROM으로 가져옴

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

%%sql

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

• 데이터의 최소값과 최대값을 비교해보는 방법으로 체크

Primary key uniqueness 지켜지는지 체크하기

%%sql

SELECT sessionId, COUNT(1)
FROM adhoc.keeyong_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

• 데이터에서 sessionId를 가져와주고 그 아이디를 기준으로 그룹핑하고 카운트해줌
• 카운트 갯수를 내림차순해주고 1번째만 출력하는걸로 해주면
• 1 초과 숫자가 나오면 중복이 있다는것! 1이면 중복 없는것!

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

%%sql

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;

• CASE WHEN을 사용해서 sessionId, userId, ts, channel이 NULL이면 1로 바꿔줌
• 그리고 1이된 레코드들을 카운트 해주고 각 이름 아래 넣어줌
• 1이상이 나오면 해당 컬럼 레코드에 null값이 있다는걸로 확이할 수 있음