📒 SQL(3)

📌 GROUP BY & Aggregate

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

• 이는 두 단계로 이루어져있다.

  1. 그룹핑할 필드를 결정
     -> Group by로 지정(필드 이름 or 필드 일련번호 사용)
  2. 다음 그룹별로 계산할 내용을 결정
     -> Aggregate함수 사용
     -> COUNT, SUM, AVG, MIN, MAX, LISTAGG,...
     보통 필드 이름을 지정하는 것이 일반적이다.(alias)

• 월별 세션수 -> 만약 일별을 원한다면 LEFT(ts, 10)으로 하면 된다.

SELECT
	LEFT(ts, 7) AS mon,
    COUNT(1) AS session_count
FROM raw_data.session_timestamp
GROUP BY 1
ORDER BY 1;

• 가장 많이 사용된 채널
  • 사용자 기반 or 세션 기반
  • 필요한 정보 -> 채널 정보, 사용자 정보 or 세션 정보
  • 테이블 선택(Join 여부 확인)

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
  • 필요한 정보 -> 세션 정보, 사용자 정보
  • 테이블 선택(Join 여부 확인)

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

• 월별 유니크한 사용자 수(MAU - Monthly Active User)
  • 필요한 정보 -> 시간 정보, 사용자 정보
  • 테이블 선택
    • user_session_channel(userid, sessionid, channel)
    • session_timestamp(sessionid, ts) -> ts = timestemp
      -> Join 해야함

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;

• TO_CHAR(A.ts, 'YYYY-MM')
  • Left(A.ts, 7)
  • DATE_TRUNC('month', A.ts)
  • SUBSTRING(A.ts, 1, 7)

• 월별 채널별 유니크한 사용자 수

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;

• 채널별 월별 매출액 테이블 만들기
  • session_timestamp, user_session_channel, session_transaction 사용
  • 필드 구성
    • month
    • channel
    • uniqueUser(총 방문 사용자)
    • paidUsers(구매 사용자 : refund한 경우도 판매로 고려)
    • conversionRate(구매 사용자 / 총 방문 사용자)
    • grossRevenue(refund 포함)
    • netRevenue(refund 제외)

%%sql
CREATE TABLE adhoc.dongki_amount AS
SELECT
    TO_CHAR(A.ts, 'YYYY-MM') AS month,
    B.channel AS channel,
    COUNT(DISTINCT B.userid) AS uniqueUsers,
    COUNT(DISTINCT CASE WHEN C.refunded = 'false' THEN B.userid END) AS paidUsers,
    CASE
        WHEN COUNT(DISTINCT B.userid)=0 THEN 0
        ELSE ROUND(paidUsers * 100 / uniqueUsers, 1)
    END AS conversionRate,
    SUM(C.amount) AS grossRevenue,
    SUM(CASE WHEN C.refunded = 'true' THEN 0 ELSE C.amount END) AS netRevenue
FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid
JOIN raw_data.session_transaction C ON A.sessionid = C.sessionid
GROUP BY
    TO_CHAR(A.ts, 'YYYY-MM'),
    B.channel;

---

📌 CATS

• SELECT를 가지고 테이블을 생성한다.
• 간단하게 새로운 테이블을 만드는 방법
• 자주 조인하는 테이블들이 있다면 CATS를 사용해서 조인해주면 편리해진다.

DROP TABLE IF EXISTS adhoc.kdk_session_summary;
CREATE TABLE adhoc.kdk_session_summary AS
SELECT B.*, A.ts FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.session = B.sessionid;

---

📌 데이터 품질 확인 방법

📌 중복된 레코드 체크

1. 다음 두 개의 카운트를 비교

SELECT COUNT(1)
FROM adhoc.kdk_session_summary;

SELECT COUNT(1)
FROM(
	SELECT DISTINCT userid, sessionid, ts, channel
    FROM adhoc.kdk_session_summary
);

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

With ds AS(
	SELECT DISTINCT userid, sessionid, ts, channel
    FROM adhoc.kdk_session_summary
)

SELECT COUNT(1)
FROM ds

---

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

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

---

📌 Primary key uniqueness가 지켜지는지 체크

SELECT sessionid, COUNT(1)
FROM adhoc.kdk_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.kdk_session_summary;