[데브코스 TIL] DAY24 데이터 웨어하우스와 SQL 기본(3)

오늘의 학습 주제

---

1. GROUP BY와 AGGREGATE 함수
2. CTAS와 CTE

 

1. GROUP BY와 AGGREGATE 함수

---

1. GROUP BY & Aggregate 함수

• 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산
• 이는 두 단계로 이뤄짐
  • 먼저 그룹핑을 할 필드를 결정 (하나 이상의 필드가 될 수 있음)
    • GROUP BY로 지정 (필드 이름을 사용하거나 필드 일련번호를 사용)
  • 다음 그룹별로 계산할 내용을 결정
    • 여기서 Aggregate함수를 사용
    • COUNT, SUM, AVG, MIN, MAX, LISTAGG, …
      • 보통 필드 이름을 지정하는 것이 일반적 (alias)

- 월별 세션수를 계산하는 SQL
	- raw_data.session_timestamp를 사용 (sessionId와 ts 필드)
# 

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 ;

• 가장 많이 사용된 채널은 무엇인가?
  • 가장 많이 사용되었다는 정의는?
    • 사용자 기반 아니면 세션 기반?
  • 필요한 정보 - 채널 정보, 사용자 정보 혹은 세션 정보
  • 먼저 어느 테이블을 사용해야하는지 생각!
    • user_session_channel?
    • session_timestamp?
    • 혹은 이 2개의 테이블을 조인해야하나?

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는 무엇인가?
  • 필요한 정보 - 세션 정보, 사용자 정보
  • 먼저 어느 테이블을 사용해야하는지 생각!
    • user_session_channel?
    • session_timestamp?
    • 혹은 이 2개의 테이블을 조인해야하나?

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;

• 월별 유니크한 사용자 수
  • 이게 바로 MAU(Monthly Active User)에 해당
  • 필요한 정보 - 시간 정보, 사용자 정보
  • 먼저 어느 테이블을 사용해야하는지 생각!
    • user_session_channel (userId, sessionId, channel)?
    • session_timestamp (sessionId, ts)?
    • 혹은 이 2개의 테이블을 조인해야하나?

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;
 
- TO_CHAR (A.ts, ‘YYYY-MM’)
- LEFT(A.ts, 7)
- DATE_TRUNC(‘month’, A.ts)
- SUBSTRING(A.ts, 1, 7)

필드/테이블 이름에 Alias 사용. AS는 필수가 아님.
- COUNT(DISTINCT B.userid) AS mau와
- COUNT(DISTINCT B.userid) mau는 동일

ORDER BY와 GROUP BY
- 포지션 번호 vs. 필드 이름
- GROUP BY 1 == GROUP BY month == GROUP BY TO_CHAR(A.ts, 'YYYY-MM')

• 월별 채널별 유니크한 사용자 수
  • 필요한 정보 - 시간 정보, 사용자 정보, 채널 정보
  • 먼저 어느 테이블을 사용해야하는지 생각!
    • user_session_channel (userId, sessionId, channel)?
    • session_timestamp (sessionId, ts)?
    • 혹은 이 2개의 테이블을 조인해야하나?

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;

 

2. CTAS와 CTE

---

1. CTAS: SELECT를 가지고 테이블 생성

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

2. 항상 시도해봐야하는 데이터 품질 확인 방법들

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

1. 중복된 레코드들 체크하기

- 다음 두 개의 카운트를 비교
SELECT COUNT(1)
  FROM table;

SELECT COUNT(1)
  FROM (
        SELECT DISTINCT userId, sessionId, ts, channel
          FROM table
		);

- CTE를 사용해서 중복 제거 후 카운트 해보기
With ds AS (
    SELECT DISTINCT userId, sessionId, ts, channel
    FROM table
    )

SELECT COUNT(1)
FROM ds;

2. 최근 데이터의 존재 여부 체크하기 (freshness)
SELECT MIN(ts), MAX(ts)
  FROM table;

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