[SQL] GROUP BY, AGGREGATE

Peter·2021년 7월 12일

CTAS CTE aggregate groupby sql

SQL

목록 보기

9/19

GROUP BY

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

순서

그룹핑을 할 필드를 결정(하나 이상의 필드가 될 수 있음)
GROUP BY로 지정(필드 이름을 사용하거나 필드 일련번호를 사용)
Aggregate함수를 사용
Aggregate란 COUNT, SUM, AVG, MIN, MAX, LISTAGEE... 등등 연산하는 함수들을 말함

월별 세션수를 계산하는 SQL만들기

%%sql

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

session_timestamp에서 ts컬럼 왼쪽에서 7번째 글자까지 잘라서 mon이라는 이름의 컬럼으로 나열
세션 레코드 갯수를 session_count에 넣는건데
mon 기준으로 그룹핑해서 고유 월이 고유레코드가 됨
mon을 오름차순 정렬해줌

문제 풀어보기

문제

raw_data.session_timestamp, raw_data.user_session_channel 테이블을 활용

가장 많이 사용된 채널은 무엇인가?
가장 많은 세션을 만들어낸 사용자 ID는 무엇인가?
월별 유니크한 사용자 수(MAU - Monthly Active User)
월별 채널별 유니크한 사용자 수

가장 많이 사용된 채널은?

고려해야할 점

사용자 기반?, 세션이 자주 열린 기준? 가장 많이 사용되었다는 정의가 있어야함
채널 정보, 사용자 정보, 세션 정보 등 필요한 정보를 정리
어느 테이블을 사용해야하는지 생각!
- user_session_channel?
- session_timestamp?
- 혹은 이 2개의 테이블을 조인해야하나?

SQL 만들기

%%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

user_session_channel에서 channel을 가지고 오는데
전체 레코드 수를 카운트 해서 session_count 컬럼에 넣어줌
userId 컬럼 중복을 제거한 값을 user_count 컬럼을 만들어서 넣어줌
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 3 DESC;  -- ORDER BY user_count DESC

3번 컬럼을 내림차순으로 바꿨을 뿐

가장 많은 세션을 만들어낸 사용자ID는 무엇인가?

고려해야할 점

필요한 정보 - 세션 정보, 사용자 정보
어느 테이블을 사용해야하는지 생각

SQL 만들기

%%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;

user_session_channel 에서 userId 컬럼을 가져와주고
userId 전체 레코드 숫자를 카운트해서 count 컬럼에 넣어줌
userId 기분으로 그룹핑해줌
카운트를 기준으로 내림차순 정렬해주고
1개까지만 표현

월별 유니크한 사용자 수

고려해야할 점

이게 바로 MAU(Monthly Active User)에 해당
필요한 정보 - 시간 정보, 사용자 정보

어느 테이블을 사용해야할까? 이번엔 조인을 해줘야함
위와 같이 테이블을 만들어 줘야함

SQL 만들기

%%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;