[5주차] SQL을 이용한 데이터 분석(3)

🙂 GROUP BY와 AGGREGATE

✔ 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산

1. 그룹핑을 할 필드를 결정
   • GROUP BY로 지정 (필드 이름을 사용하거나 필드 일련번호를 사용)
2. 그룹별로 계산할 내용을 결정
   • Aggregate 함수 사용
   • COUNT, SUM, AVG, MIN, MAX, LISTAGG, ...

🍦 가장 많이 사용된 채널은 무엇인가?

SELECT
  channel,
  COUNT(1) AS session_count,
  COUNT(DISTINCT userId) AS user_count
FROM raw_data.user_session_channel
GROUP BY 1
ORDER BY 2 DESC;

🍦 가장 많은 세션을 만들어낸 사용자 ID는 무엇인가?

SELECT
  userId,
  COUNT(1) AS count
FROM raw_data.user_session_channel
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

🍦 장단점

TO_CHAR(A.ts, 'YYYY-MM')	셋 다 동일. 리턴 값이 String
LEFT(A.ts, 7)
SUBSTRING(A.ts, 1, 7)
DATE_TRUNC('month', A.ts)	일(dd)에 해당하는 부분이 '01'. 리턴 값이 timestamp

---

🙂 CTAS와 CTE

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

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

DROP TABLE IF EXISTS adhoc.[my_name]_session_summary;

CREATE TABLE adhoc.[my_name]_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;

 

✔ 데이터 품질 체크

• 중복 데이터
• 최근 데이터의 존재 여부 (freshness)
• Primary key uniqueness 체크
• null 값 체크

🍦 중복된 레코드 체크

SELECT COUNT(1)
FROM adhoc.[my_name]_session_summary;

SELECT COUNT(1)
FROM (
	SELECT DISTINCT userId, sessionId, ts, channnel
    FROM adhoc.[my_name]_session_summary
);

상단의 두 값을 비교

 

CTE를 사용해서 중복 제거 후 카운트

With ds AS(
	SELECT DISTINCT userId, sessionId, ts, channnel
    FROM adhoc.[my_name]_session_summary
)
SELECT COUNT(1)
FROM ds;

 

🍦 최근 데이터의 존재 여부 체크

SELECT MIN(ts), MAX(ts)
FROM adhoc.[my_name]_session_summary;

🍦 Primary key uniqueness 체크

SELECT sessionId, COUNT(1)
FROM adhoc.[my_name]_session_summary
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 adhoc.[my_name]_session_summary;