[데브코스] TIL 23일차

 

 

 

---

1. GROUP BY

• 테이블의 레코드를 그룹별로 묶어 계산할 수 있게함
• 두 단계로 이뤄짐
  • 그룹핑을 할 필드를 결정해 GROUP BY로 지정(하나 이상의 필드 가능)
  • 그룹별로 계산할 내용 결정
    • 여기서 AGGREGATE 함수 사용
    • COUNT, SUM, AVG, MIN, MAX, LISTAGG, ...
    • 보통 함수 결과에 필드 이름을 지정함
      
      

• 예제(1) - 가장 많이 사용된 채널은 무엇인가? 
  • 많이 사용되었다의 1)정의는?
    
    • 사용자 기반 vs. 세션 기반
  • 2)필요한 정보 - 채널 정보, 사용자 정보 혹은 세션 정보
  • 3)먼저 어느 테이블을 사용해야 하는지 생각해!
    • 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

 

 

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

SELECT
    TO_CHAR(A.ts, 'YYYY-MM') AS month,
    COUNT(DISTINCT B.userId) AS mau -- 한 사용자가 1번을 방문했건 100번을 방문했건 유니크한 사용자 수
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;

-- string functions 장단점 --

TO_CHAR(A.ts, 'YYYY-MM')    -- 문자열로 월만 받고싶을 때
LEFT(A.ts, 7)               
SUBSTRING(A.ts,1,7)

DATE_TRUNC('month', A.ts)   -- type : timestamp를 원할 때. 2020-04-01 00:00:00 해당 월에 첫날로 잘림

 

 

 

2. CTAS, CTE

• CTAS(CREATE TABLE AS SELECT)
  • SELECT로 테이블을 쉽게 생성함
  • 자주 JOIN되는 테이블들을 CTAS를 통해 미리 만들어두면 아주 편리함
  • 세 개의 스키마 raw_data(외부에서 가져와 ETL한 데이터), analytics(raw_data의 테이블들을 조합해 쉽게 쓸 수 있는 새로운 layer), adhoc가 있을 때, 이런 조인된 테이블은 analytics로 들어감

DROP TABLE IF EXISTS adhoc.yj_session_summary;

CREATE TABLE adhoc.yj_session_summary AS -- CTAS
  SELECT B.*, A.ts 
  FROM raw_data.session_timestamp A
  JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid;

 

 

• CTE(Common Table Expression)
  • 권한이 필요 없고 하나의 쿼리문이 끝날때까지만 지속되는 일회성 테이블을 만들 수 있음

With ds AS (
    SELECT DISTINCT userId, sessionId, ts, channel
    FROM adhoc.yj_session_summary
)
SELECT COUNT(1)
FROM ds;

 

 

 

 

 

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

• 중복된 레코드 체크

-- 1. 중복된 레코드 체크 -- 다음 두 개의 카운트를 비교 --
SELECT COUNT(1) 
    FROM adhoc.yj_session_summary;

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

• 최근 데이터 존재하는지 체크(freshness)

-- 2. 최근 데이터 존재하는지 체크(freshness) --
SELECT MIN(ts), MAX(ts)
FROM adhoc.yj_session_summary

• primary key uniqueness 체크

-- 3. primary key uniqueness 체크 --
SELECT sessionId, COUNT(1)       -- sessionId가 primary key. COUNT 값이 1보다 크면, 중복이 있다는 것
FROM adhoc.yj_session_summary
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 adhoc.yj_session_summary;

 

 

 

 

 

4. 새 테이블 소개

Table	Fields
session_timestamp	sessionId(string), ts(timestamp)
user_session_channel	userId(integer), sessionId(string), channel(string)
session_transaction	sessionId(string), refunded(boolean), amount(integer)
channel	channelname(string)

 

• 숙제 - 채널별 월 매출액 테이블 만들기(본인 스키마 밑에 CTAS로 테이블 만들기)
  • session_timestamp, user_session_channel, session_transaction 테이블 사용
  • 아래와 같은 필드로 구성
    • month
    • channel
    • uniqueUsers (총 방문 사용자)
    • paidUsers(구매 사용자. refund한 경우도 판매로 고려함)
    • conversionRate(구매 사용자 / 총 방문 사용자)
    • grossRevenue(refund 포함)
    • netRevenue(refund 제외)