터칭 데이터
Redshift - GROUP BY & Aggregate 함수 본문
GROUP BY & Aggregate
테이블의 레코드들을 필드를 중심으로 그룹화해 그룹별로 다양한 정보를 계산합니다.
크게 두 단계로 이루어집니다.
1. 먼저 그룹핑을 할 필드를 결정합니다. 이때 그룹핑할 필드는 2개 이상이 될 수 있습니다.
그리고 GROUP BY로 그룹핑할 필드(들)의 이름을 사용하거나 필드 일련번호를 사용합니다.
(여기서 일련번호란 "ordinal numbering" 또는 "ordinal position"를 말합니다.)
2. 그룹별로 계산할 내용을 결정합니다.
이 단계에서 Aggregate함수를 사용합니다.
COUNT, SUM, AVG, MIN, MAX, LISTAGG 등이 있습니다. 이 때 이런 함수들을 사용하면 결과가 곧 새로운 필드가 되기 때문에 보통은 이때 alias(as)를 붙여 새로운 필드 이름을 붙여줍니다.
월별 세션수를 계산하는 SQL
raw_data 스키마(데이터 베이스)의 session_timestamp 테이블을 사용합니다. (raw_data.session_timestamp) session_timestamp의 ts필드에 세션이 생성된 시간 정보가 있기 때문입니다.
SELECT
LEFT(ts, 7) AS mon,
COUNT(1) AS session_count
FROM raw_data.session_timestamp
GROUP BY 1 --GROUP BY mon, GROUP BY LEFT(ts, 7)
ORDER BY 1;

PostgreSQL에서는 시간이 2019-05-01 12:18:27...와 같은 형식으로 나옵니다. 이 때 문자열 함수 LEFT를 사용하면 데이터 타입이 문자열로 바뀌고 7을 이용해 연도-월 형태로 출력할 수 있게 됩니다.
가장 많이 사용된 채널은 무엇인가?
먼저 가장 많이 사용된의 기준이 무엇인지 생각해봅니다. 만일 면접에서 받은 질문이었다면 면접관이 일부러 모호하게 물어보는 경우도 있습니다. 2가지 경우가 있을 것입니다. 첫번째 사용자(당연히 유니크한)가 가장 많이 사용한 채널인지, 두번째 세션이 가장 많이 생성된 채널인지 입니다.
2가지 모든 경우를 살펴보겠습니다.
이 때 어떤 테이블을 사용할지 고민합니다.
user_session_channel이 session_timestamp보다 올바른 테이블입니다.
우리는 지금 쿼리문을 작성하기에 앞서 다음과 같은 두 단계의 의사 결정과정을 거쳤습니다.
1. 기준을 명확하게
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

세션이 가장 많이 생성된 채널 기준
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 3 DESC; -- ORDER BY user_count DESC

가장 많은 세션을 만들어낸 사용자 ID는 무엇인가?
필요한 정보는 세션 정보와 사용자 정보입니다.
필요한 테이블은 user_session_channel입니다.
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;

월별 유니크한 사용자 수 (MAU)
월별 사용자수를 뜻하는 MAU(Monthly Active User)를 구해야 합니다.
월별 사용자 수이므로 같은 사용자는 한번만 카운트 되야 합니다. 즉 유니크해야 합니다.
시간 정보와 사용자 정보가 필요합니다.
user_session_channel과 session_timestamp 두 테이블이 모두 필요합니다. 즉 join을 해야합니다.
두 테이블 모두 sessionId 필드가 존재하므로 sessionId 필드로 join을 한 뒤 SELECT합니다.
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;

양쪽에 같은(매칭 되는) 밸류가 존재하는 레코드만 남긴 뒤 테이블을 합치는 INNER JOIN을 사용했습니다. 여기서는 ON 뒤에 적힌 조건대로 A의 sessionid 필드와 B의 sessionid필드의 value가 일치하는 record만 남겨 합쳐진 테이블을 만들었습니다.
TO_CHAR는 문자열이 아닌 timestamp 타입의 필드를 받아 문자열로 바꾸는 함수입니다. 위의 SQL에서는 'YYYY-MM'과 같이 연도 4자리 - 월 2자리 (ex. 2023-11) 형태로 바꾼다는 뜻입니다.
TO_CHAR(A.ts, 'YYYY-MM')
LEFT(A.ts, 7)
DATE_TRUNC('month', A.ts)
SUBSTRING(A.ts, 1, 7)
위의 4가지 함수들은 보기에는 같은 결과를 보여주지만 나름의 일장일단이 있습니다.
DATE_TRUNC는 나머지 3개의 함수와는 달리 timestamp 타입의 값이 리턴이 됩니다.
(TO_CHAR, LEFT, SUBSTRING은 문자열을 반환합니다.)
주의할 점은 DATE_TRUNC는 다음과 같이 datepart의 정밀도로 잘립니다. 예를 들어, month로 설정하면 해당 달의 첫번째 날로 잘립니다.
https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_DATE_TRUNC.html
DATE_TRUNC 함수 - Amazon Redshift
DATE_TRUNC 함수 DATE_TRUNC 함수는 시간, 일 또는 월 등 지정하는 날짜 부분을 기준으로 타임스탬프 표현식 또는 리터럴을 자릅니다. 조건 DATE_TRUNC('datepart', timestamp) 인수 datepart 타임스탬프 값을 자를
docs.aws.amazon.com
꼭 읽어보시기를 바랍니다.
월별 채널별 유니크한 사용자 수
시간 정보와 사용자 정보와 채널 정보가 필요합니다.
역시 2개의 테이블을 join해야 합니다.
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;

'SQL' 카테고리의 다른 글
Redshift - JOIN (0) | 2023.11.15 |
---|---|
Redshift - CTAS & CTE, 테이블(데이터) 품질 확인 방법 (0) | 2023.11.15 |
Redshift SELECT (0) | 2023.11.14 |
Redshift Cluster 생성 & Colab에 연결 (0) | 2023.11.14 |
Redshift: Scalable SQL 엔진 (0) | 2023.11.12 |