데이터 엔지니어링 TIL 23일차

학습내용

월별 유니크한 사용자 수
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;

데이터 품질 확인방법
1. 중복된 레코드들 체크
2. 최근 데이터의 존재여부 체크(freshness)
3. Primary key uniqueness가 지켜지는지 체크
4. 값이 비어있는 컬럼들이 있는지 체크

CTAS : SELECT를 가지고 테이블을 생성
- adhoc위에서 write가능
DROP TABLE IF EXISTS adhoc.suha_session_summary;
CREATE TABLE adhoc.suha_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;

1. 중복체크 
SELECT COUNT(1) FROM adhoc.suha_session_summary;

SELECT COUNT(1)
FROM (
    SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.suha_session_summary
);
위 둘의 값이 같아야한다.

CTE 사용
With ds AS (
  SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.suha_session_summary
)
SELECT COUNT(1)
FROM ds;

2. 최근 데이터의 존재여부 체크
SELECT MIN(ts), MAX(ts)
FROM adhoc.suha_session_summary;

3. Primary key uniqueness가 지켜지는지 체크하기
SELECT sessionId, COUNT(1)
FROM adhoc.suha_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;
값이 항상 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.suha_session_summary;

 

느낀 점

sql언어의 기본사용법에 대해 알 수 있었고, 내가 알지 못하던 여러 방법들도 알 수 있어 유익한 시간이었다.

코랩에서 aws를 불러와 사용할 수 있다는 점이 신기했다. aws양이 방대해서인지 내 오래된 노트북으로 돌릴 때 종종 응답없음이 뜨는 문제만 빼면 큰 문제는 없이 잘 돌아가는 것 같다.

실습으로 숙제를 내주셨는데 여러 문제를 생각해봐야해서 쉽지 않았다. 구글링을 통해 여러 방법들을 보면서 풀었다.