[데이터분석 데브코스] TIL Day 23 - 데이터 웨어하우스와 SQL 바탕으로 데이터 분석 학습 (3)

콩이·2024년 3월 20일
데브코스 2기데이터분석프로그래머스

프로그래머스 데이터분석 데브코스 2기

목록 보기
19/46

🟨 SQL을 이용한 데이터 분석: GROUP BY와 CTAS

📍 GROUP BY와 AGGREGATE 함수

실습위주로 진행됨

GROUP BY와 AGGREGATE 함수

  • GROUP BY는 테이블 레코드들을 컬럼값 기준으로 그룹핑해서 그룹별로 다양한 정보를 계산할 때 사용됨.
    ex) COUNT, SUM, AVG, MIN, MAX, LISTATT 등

  • 보통은 하나의 필드를 가지고 그룹핑을 하지만 여러 개의 필드가지고 그룹핑 할 수도 있음.

  • Aggregate 함수 적용하면 alias써서 새로운 결과에 대한 필드이름을 붙이는게 일반적임.

실습

이전과 동일한 테이블 사용

TableFields
session_timestampsessionid (string), ts (timestamp)
user_session_channeluserid (integer), sessionid (string), channel (string)
session_transactionsessionid (string), refunded (boolean), amount (integer)
channelchannelname (string)

1. 월별 세션 수 계산 

%%sql

SELECT
    LEFT(ts, 7) AS mon,   -- 일 별로 보고싶으면 7이 아니라 10으로 변경하면 됨
    COUNT(1) AS session_count
FROM raw_data.session_timestamp
GROUP BY 1  -- GROUP BY mon / GROUP BY LEFT(ts,7) 이렇게 써도 됨
ORDER BY 1;

  • LEFT는 문자열만 받을 수 있는 함수인데 ts같이 날짜가 들어오면 알아서 문자열로 CAST 됨.

  • GROUP BY mon이기 때문에 month로 그룹핑이 되고, 같은 그룹내에 속한 레코드들의 카운트를 진행한 예시.

2. 가장 많이 사용된 채널

주의할 점은 가장 많다는 것의 정의를 확실히 해야한다는 점.
(사용자 기반인지 사용자 관계없이 세션 기반인지와 같은)

실습에서는 2가지로 작성해봄.

1) 사용자가 많은 채널

2) 세션이 많은 채널

2가지 경우 동시에 보는 코드는 다음과 같음.

%%sql

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

  • COUNT(DISTINCT userID) as user_count에서 DISTINCT 없으면 그 위 COUNT(1)한거와 동일한 결과 나오게 됨.

3. 가장 많은 세션을 만들어낸 사용자 id

사용자 id를 가지고 그룹핑을 해야하는 예시

필요한 정보는 session, 사용자 정보!

%%sql

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;

  • userid로 그룹핑하고 그룹내에서 레코드 수 count 진행.

  • ORDER BY 내림차순하고 LIMIT 1하면 제일 큰 값 보겠다는 의미.

4. 월별 유니크한 사용자 수(MAU - Monthly Active User)

월별로 사용자가 몇 번을 방문했는지 알아보는 예시
(사용자가 한달에 몇 번을 방문해도 한 번으로 카운트)

%%sql

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;

  • 방문 시기와 사용자 정보 얻어야해서 user_session_channel 테이블과 session_timestamp 테이블을 JOIN 해야함(JOIN 관련해서는 다음 시간에 자세히 소개 예정).

    • 두 테이블을 JOIN 하면 이러한 테이블이 만들어짐

  • COUNT(DISTINCT B.userid) AS mau 해당 구문에 DISTINCT 안쓰면 session 수와 동일한 결과 값이 나옴.

  • TO_CHAR은 문자열이 아닌 필드를 인자로 받아서 문자열로 바꿔주는 함수

    • TO_CHAR 함수와 차이는 있지만 동일한 역할을 하는 함수들

      1. LEFT(A.ts, 7)

        • 결과가 문자열(시간이나 날짜를 나타내는 타입이 아님)

      2. DATE_TRUNC('month', A.ts)

        • 결과가 timestamp
        • 첫 인자로 어떤 단위를 원하는지 적어줘야 함.
          만약 해당 경우에 ts가 2019-01-03이라면 2019-01-01 반환(일에 해당하는 건 무조건 첫번째꺼 반환)

      3. SUBSTRING(A.ts, 1, 7)

        • 결과가 문자열(시간이나 날짜를 나타내는 타입이 아님)

  • AS는 alias로 필드/테이블 이름에 붙일 수 있고 이해하기 쉬운 이름을 붙이는게 일반적임.

    AS는 필수는 아니고 생략 가능!

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

4번 실습에서 GROUP BY 하는 필드가 하나 더 늘어나고 나머지는 동일함.

%%sql

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;

  • 4번 실습에서는 월별로 그룹핑했는데 채널까지 해서 더 세세하게 그룹핑하는 예시.

  • 그렇게 그룹핑하고 나서 유니크한 사용자 수 구한 것.

  • 앞 문제와 차이는 GROUP BY, ORDER BY에 필드 하나가 아닌 두 개를 사용해 본 것.

📍 CTAS와 CTE 소개

CTAS

  • CTAS는 SELECT를 가지고 새로운 테이블을 생성하는 방법

  • 언제 유용한가?

    데이터 엔지니어들이 ETL을 해서 외부 데이터를 데이터 웨어하우스에 저장하면 데이터 웨어하우스 2단계 구조에서 raw_data 스키마 밑에 테이블을 저장하게 됨.

    그러한 테이블들을 JOIN 해야하는 경우가 발생하고 이러한 JOIN 한 새로운 테이블을 만들어두면 분석하는 코드가 훨씬 간단해짐.

  • 실습은 각자 부여된 스키마 밑에 테이블을 저장하면서 진행.

CTAS 실습

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

%%sql

DROP TABLE IF EXISTS mkflower0917.mikyung_session_summary;
CREATE TABLE mkflower0917.mikyung_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;

2. 월별 유니크한 사용자 수

이전에 했던 월별 유니크한 사용자 수를 생성된 새로운 테이블을 바탕으로 풀어봄.

%%sql

SELECT
  TO_CHAR(ts, 'YYYY-MM') AS month,
  COUNT(DISTINCT userid) AS mau
FROM mkflower0917.mikyung_session_summary  -- 만들어 둔 테이블 
GROUP BY 1
ORDER BY 1 DESC;

데이터 품질 확인 방법

  • 새로운 테이블을 사용하게 되면 다양한 형태로 테이블의 데이터 품질을 확인해봐야함.

  • 4가지 방법이 존재

    1. 중복된 레코드 체크
    2. 최근 데이터 존재 여부 체크
    3. Primary key uniqueness 지켜지는지 체크
    4. 값이 비어있는 컬럼 있는지 체크

  • CTAS 통해 테이블을 새로 만들었으니 해당 테이블을 검증해봄
    (강사님이 만들어둔 테이블은 위 조건에 걸리지 않게 만들어놔서 문제는 없을 것이지만 방법에 대한 연습으로 생각하고 실습진행)

데이터 품질 확인 실습

1. 중복된 레코드 체크

1) 우선 주어진 테이블의 모든 레코드 수를 카운트하고 중복제거한 레코드 수를 카운트해서 비교

%%sql

SELECT COUNT(1) FROM mkflower0917.mikyung_session_summary;

아래는 중복제거 후 count ver.

%%sql

SELECT COUNT(1)
FROM (
    SELECT DISTINCT userId, sessionId, ts, channel FROM mkflower0917.mikyung_session_summary
);  -- 중복 제거 후 다시 count

  • 두 개의 결과가 동일하게 101520이 나왔기 때문에 중복된 데이터가 없다는 것을 의미

  • 중복 제거 코드를 FROM에 nested하지 않고 WITH 문 쓰는 코드로도 가능

    %%sql

-- FROM에 nested하지 않고 WITH문 써서 한 경우
With ds AS (
SELECT DISTINCT userId, sessionId, ts, channel FROM mkflower0917.mikyung_session_summary
)
SELECT COUNT(1)
FROM ds;

    위에서는 FROM안에 SELECT 문을 넣었는데 그렇게 안하고 WITH 등을 써서 위로 빼내는 경우를 CTE라고 부름.

    WITH 새로운 테이블 이름 as (SELECT문) 형태.

    이렇게하면 WITH의 결과가 새로운 테이블 이름을 갖는 임시 테이블로 생성됨.

    새로운 테이블을 밑에서 빼내어 중복 사용 가능함!

2. 최근 데이터 존재여부 체크

ts 필드에서 가장 작은값과 가장 큰 값을 체크함.

그러면 언제부터 언제까지 레코드를 갖고있는지 파악 가능.

%%sql

SELECT MIN(ts), MAX(ts)
FROM mkflower0917.mikyung_session_summary;

3. Primary key uniqueness 지켜지는지 체크

예시에서는 sessionid가 primary key가 됨.

sessionid를 가지고 GROUP BY를 하면 같은 sessionid끼리 모이는데, 그것을 count한 값이 항상 1이 되어야 uniqueness가 지켜진거고 값이 1보다 크면 중복이 있다는 의미.

%%sql

SELECT sessionId, COUNT(1)
FROM mkflower0917.mikyung_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;   -- 가장 큰 값 하나만 뽑음

4. 값이 비어있는 컬럼 있는지 체크

CASE WHEN 구문써서 컬럼이 NULL이면 카운트하는 방식을 사용.

%%sql

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 mkflower0917.mikyung_session_summary;

  • CASE WHEN 구문에서 ELSE문이 없는데 이럴 경우 NULL이 return됨.
    (즉, 컬럼값이 NULL이면 1, NULL이면 NULL이 리턴되는 형태)

  • COUNT함수는 NULL을 세지 않기 때문에 컬럼값이 NULL이 아닌 경우만 카운트 됨.

  • 결과는 NULL 값이 없기 때문에 전부 0이 리턴된 것.

  • 강사님이 빈 값이 없게 테이블을 만들어 놓으셔서 그런거고 현업에서는 비어있는 값이 존재함.

📍 숙제

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

  • session_timestamp
    user_session_channel
    session_transaction

    위 3개의 테이블 사용하여 숙제 진행.

  • 다음과 같은 필드로 구성할 것.

    컬럼
    month
    channel
    uniqueUser(총 방문자)
    paidUsers(구매자) - refund한 경우도 판매로 고려
    conversionRate(구매자 / 총 방문자)
    grossRevenue(총 수입) - refund 포함
    netRevenue - refund 제외한 수입

    paidUser는 session_transaction 테이블에 나타나는 user를 말함.

  • 고려할 점은 session_transaction 테이블에는 모든 세션이 존재하지는 않는다는 점(sessionid 가지고 JOIN 시 INNER JOIN말고 LEFT JOIN해야함)

profile
콩이
이전 포스트

[데이터분석 데브코스] TIL Day 22 - 데이터 웨어하우스와 SQL 바탕으로 데이터 분석 학습 (2)

다음 포스트

[데이터분석 데브코스] TIL Day 24 - 데이터 웨어하우스와 SQL 바탕으로 데이터 분석 학습 (4)

0개의 댓글