[TIL] 6주차_Day23: 데이터 웨어하우스와 SQL과 데이터분석 (3)

💡Today I Learned

• 데이터 웨어하우스와 SQL, 데이터분석에 대한 세 번째 수업을 진행했습니다.
• group by, aggregate 함수, ctas, cte 서브 쿼리 실습

 

---

 

1. GROUP BY, AGGREGATE

: 테이블의 레코드를 그룹핑해 그룹별로 정보를 계산

1) 그룹핑을 할 필드 결정 (하나 이상 가능)

2) GROUP BY 함수로 지정

3) 그룹별로 집계함수 계산 (COUNT, SUM, AVG, MIN, MAX, LISTAGG, ...) 

 

ex) 가장 많이 사용된 채널 (기준 a) 세션 수 b) 유니크한 사용자 수)

SELECT
	channel,
    COUNT(1) AS session_count,
    COUNT(DISTINCT userId) AS user_count,
FROM raw_data.user_session_channel
GROUP BY 1 -- select 질의 후 1번째 필드 기준 (channel)
ORDER BY 2 DESC; -- 2번째 필드 기준 (session_count)

 

*) 사용한 함수

- TO_CHAR(문자열 타입이 아닌 필드, 문자열로)

- SUBSTRING(문자열 필드, 시작 위치, 길이만큼 추출)

- LEFT(문자열 필드, 왼쪽부터 길이만큼 추출)

 

2. CTAS

: SELECT를 가지고 새로운 테이블을 생성하는 방법 (미리 만들어두기)

: 자주 조인하는 테이블이 있다면 CTAS를 사용해 조인해두기

DROP TABLE IF EXISTS adhoc.tmp_session_summary; -- 혹시 미리 존재하고 있는 경우 -> 삭제 먼저
CREATE TABLE adhoc.tmp_session_summary AS
	SELECT B.*, A.ts 
    	FROM raw_data.session_timestamp as A JOIN raw_data.user_session_channel as B
    		on A.sessionid = B.sessionid;

*) 스키마 3개 (폴더)

- raw_data = ETL 프로세스에서 외부에서 복사돼 온 테이블이 들어가는 폴더

- analytics = raw_data의 테이블 조합해 쉽게 쓸 수 있는 새로운 레이어, 테이블이 이 폴더에 들어감 (위 예제처럼 create table로 새롭게 만든 테이블이 들어갈 수 있음)

- adhoc = guest account로 쓸 수 있도록 만들어 놓은 write 가능한 스키마(폴더)

 

3. CTE로 서브쿼리 바깥으로 빼내기, 데이터의 품질 확인하는 4가지 방법

- 중복된 레코드 체크

1) 주어진 테이블의 모든 레코드 수 count  VS  중복 제거한 레코드(distinct) 수 count → 같으면 중복 없음

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

 

2) CTE 이용하기 (1)에서 select distinct 문을 from 바깥으로 빼냄)

With [새로운 테이블 이름] AS ( -- With AS 안의 쿼리 결과가 [새로운 테이블 이름] 으로 지정됨
	SELECT DISTINCT userId, sessionId, ts, channel
    	FROM adhoc.tmp_session_summary
)
SELECT COUNT(1)
FROM [새로운 테이블 이름];

 

 

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

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

 

- 기본키 유일성이 지켜지는지 체크: return 된 레코드에서 count가 2 이상이면 같은 기본키(sessionId)로 두 개 이상의 레코드가 존재한다는 의미

SELECT sessionId, COUNT(1) -- sessionId가 기본키임 (한 유저가 여러 개의 세션 생성할 수 있음)
FROM adhoc.tmp_session_summary
GROUP BY 1 -- GROUP BY sessionId
ORDER BY 2 DESC -- ORDER BY COUNT(1) DESC
LIMIT 1;

 

- 비어있는 컬럼 체크 (null): 필드별로 NULL이면 1로 카운트 (case when 구문 사용)

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.tmp_session_summary;

 

---

💡Furthermore

• SQL day_3 숙제 (LEFT_JOIN 이용