[Week6 데이터 웨어하우스와 SQL 기본] TIL 23일차 - Google Colab에서 데이터웨어하우스 연결해 사용하기

이번시간에는 코랩에 데이터웨어하우스(Redshift)에 연결하고 데이터를 가공해 보자.

 

0) 데이터 스키마 정보(폴더)

📁 row data	📁 analytics	📁 adhoc
ETL 과정에서 수집된 데이터	row data의 테이블을 조합해서 만든 새로운 테이블

 

 

1) 데이터 웨어하우스(Redshift) Colab에 연결하기

%load_ext sql
%sql postgresql:/*redshift 서버 연결 주소*/
%%sql
원하는 sql 문장

 

위의 코드를 사용하면 아마존의 Redshift 데이터 웨어하우스에 연결하고, SQL 문장을 입력하여 원하는 작업을 수행할 수 있다.

 

 

2) 테이블을 (판다스) Dataframe 자료형으로 변환

result=%sql SELECT * FROM 인스턴스명.테이블명
df = result.DataFrame()

 

데이터 웨어하우스에 있는 테이블을 우리에게 익숙한 데이터프레임(Dataframe) 형태로 바꾸는 코드이다.

데이터 프레임 형태로 바꾼 뒤에 이제 원하는 작업을 수행할 수도 있다.

 

 

3) 데이터 품질 확인

중복된 레코드 체크하기

count == count(distinct)

최근 데이터 존재여부 파악

min(timestamp), max(timestamp)

Primary key uniqueness 체크하기

primarykey 기준으로 group 한 뒤에 count를 수행해서 값이 모두 1인지 확인(내림차순 정렬해서 첫 번째 레코드만 확인)

groupy by primarykey

count(primarykey), count(1)

order by 2 desc

분석할 컬럼의 값이 비어있는 비율 확인하기

- case 이용

count(CASE WHEN 컬럼명 is NULL THEN 1 END)

- 비교연산 이용

where 컬럼명 is null

 

cf) CTE란?

"Common Table Expressions"의 약자로, SQL 쿼리에서 사용되는 일종의 임시 결과 집합을 정의하는 데 사용되는 문법으로 쿼리 실행 시에만 유효하다. 주로 복잡한 쿼리를 더 간단하고 가독성 있게 만들기 위해 사용된다.

WITH CTE테이블명 (반환되는 컬럼1, 반환되는 컬럼2, ...) AS (
    -- CTE의 쿼리 정의
    SELECT ...
)

-- CTE를 활용한 메인 쿼리
SELECT ~
FROM CTE테이블명 
WHERE ~

 

 

4) 데이터 분석 - 집계함수 사용

채널별 세션수를 집계해보자.

• user_session_channel 테이블 이용 + COUNT 함수 사용

월별 세션수를 집계해 보자.

• session_timestamp 테이블 이용 + LEFT 함수 사용 + COUNT 함수 사용

가장 많이 사용된 채널에 대해 알아보자.

• 가장 많이 사용된 기준은? (사용자 기준 vs 세션 기준)
• user_session_channel 테이블 이용 + COUNT 함수 사용 + DISTINCT 함수 사용

가장 많은 세션을 가진 사용자 ID에 대해 알아보자.

• user_session_channel 테이블 이용 + COUNT 함수 사용 + LIMIT 지정

월별 유니크한 사용자수를 알아보자.

• user_session_channel, session_timestamp JOIN + TO_CHAR(기존 칼럼명, 형식) 함수 사용 + COUNT 함수 사용

월별 채널별 유니크한 사용자수를 알아보자.

• user_session_channel, session_timestamp JOIN + TO_CHAR(기존 컬럼명, 형식) 함수 사용+ COUNT 함수 사용 + DISTINCT 함수 사용

 

cf) 마케팅에서 중요하게 생각하는 지표

DAU(Daily Active User), WAU(Weekly Active User), MAU(Monthly Active User)

: 단위 시간(Daily, Weekly, Monthly)에 방문한 unique 한 사용자수

 

 

5) Timestamp 정보 중 일부를 추출하기

Timestamp를 가공해서 특정 정보를 만들고 싶을 때가 있다. 그럴 땐 아래의 4가지 방법을 활용하면 된다.

반환하는 값의 타입에 따라 두 가지로 나뉜다.

 

String을 반환

TO_CHAR( DATETIME칼럼명, 자릿수 )

LEFT( DATETIME컬럼명 , 자릿수 )

SUBSTRING( DATETIME칼럼명, START, 자릿수 )

 

Timestamp를 반환

DATE_TRUNC( '원하는 속성', DATETIME칼럼명)

 

 

6) Ctas 사용하기

테이블을 생성함과 동시에 내용을 채우는 기술로, 간단하게 새로운 테이블을 생성하는 작업.

주로 데이터 분석에서 많이 사용하는 조인연산 등을 미리 수행해서 새 테이블로 만드는 작업을 수행하는 데 사용된다.

해당 작업은 analytics 폴더에 저장하도록 한다(ETL 테이블을 조인해서 만든 분석용 테이블이기 때문)

%%sql

DROP TABLE IF EXISTS analytics.테이블명;
CREATE TABLE analytics.테이블명 AS
SELECT ~
FROM 테이블A JOIN 테이블B ON 조인조건;

 

 

7) 숙제

월별 채널별 매출액 테이블 만들기

주의할 점)

1. 모든 채널에 대해 구매가 일어나지는 않는다.(채널별이므로, 채널 기준 leftjoin을 수행해야 한다)

2. int끼리 나누면 int가 결과로 나온다(float로 타입캐스팅 할 것)

month
channel
uniqueUsers	총 방문 사용자
paidUsers	구매 사용자
conversionRate	구매사용자/총 방문 사용자
grossRevenue	session_transaction의 amount의 합
netRevenue	session_transaction의 amount 합(단, refund시 제외)

 

작성한 코드

%%sql
DROP TABLE IF EXISTS adhoc.session_summary;
CREATE TABLE adhoc.session_summary AS
SELECT 
  TO_CHAR(T.ts, 'YYYY-MM') AS month, 
  C.channelname as channel,  
  COUNT(DISTINCT U.userid) as uniqueUsers, 
  Count(CASE WHEN TR.amount >=0 THEN 1 END)as paidUsers,
  ROUND(Cast(paidUsers AS Float)*100/NULLIF(uniqueUsers, 0), 2) as conversionRate,
  Sum(CASE WHEN TR.refunded is NOT NULL then Tr.amount END) as grossRevenue,
  Sum(CASE WHEN TR.refunded=False then Tr.amount ELSE 0 END) as netRevenue
FROM raw_data.channel C 
LEFT JOIN raw_data.user_session_channel U on C.channelname=U.channel
JOIN raw_data.session_timestamp T ON T.sessionid=U.sessionid
LEFT JOIN raw_data.session_transaction TR ON  T.sessionid=TR.sessionid
GROUP BY 1, 2
ORDER BY 1, 2;