4-2) MySQL 문법(GroupBy)

GROUP BY & Aggregate 함수(그루핑한 레코드에 통계적 계산을 하는 함수)

• 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산
• 이는 두 단계로 이뤄짐
  • 먼저 그룹핑을 할 필드를 결정 (하나 이상의 필드가 될 수 있음)
    • GROUP BY로 지정 (필드 이름을 사용하거나 필드 일련번호를 사용)
  • 다음 그룹별로 계산할 내용을 결정
    • 여기서 Aggregate함수를 사용
    • COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT …
      • 보통 필드 이름을 지정하는 것이 일반적 (alias)

예제1 : 월별 세션수를 계산하는 SQL

• prod.session을 사용 (id와 created 필드)

SELECT
LEFT(created, 7) AS mon, -- created가 2022-09-12 형식이므로 LEFT하면 2022-09가 나옴
COUNT(1) AS session_count 
FROM prod.session
GROUP BY 1 -- GROUP BY mon == GROUP BY LEFT(created, 7) -> 연도+월별로 묶임
ORDER BY 1;

추가적인 예제

• 앞서 설명한 prod.session와 prod.channel 테이블들을 사용
• 다음을 계산하는 SQL을 만들어 보자 1) 월별 총 세션 수 (이미 풀어봤음) 2) 가장 많이 사용된 채널은 무엇인가? 3) 가장 많은 세션을 만들어낸 사용자 ID는 무엇인가? 4) 월별 유니크한 사용자 수 (MAU - Monthly Active User)

  • 한 사용자는 한번만 카운트되어야 함

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

예제2 : 가장 많이 사용된 채널은 무엇인가?

• 가장 많이 사용되었다는 정의는?
  • 사용자 기반 아니면 세션 기반?
• 필요한 정보 - 채널 정보, 사용자 정보 혹은 세션 정보
• 먼저 어느 테이블을 사용해야하는지 생각!
  • prod.session?
  • prod.channel?
  • 혹은 이 2개의 테이블을 조인해야하나?
    • channel_id로만 충분하다면(channel 이름이 필요없다면) 조인이 필요 없고 session만으로 충분

SELECT
user_id,
COUNT(1) AS count
FROM prod.session
GROUP BY 1 -- GROUP BY user_id
ORDER BY 2 DESC -- ORDER BY count DESC
LIMIT 1;

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

SELECT
user_id,
COUNT(1) AS count
FROM prod.session
GROUP BY 1 -- GROUP BY user_id
ORDER BY 2 DESC -- ORDER BY count DESC
LIMIT 1;

예제4 : 월별 유니크한 사용자 수

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

SELECT
LEFT(created, 7) AS mon,
COUNT(DISTINCT user_id) AS user_count
FROM prod.session
GROUP BY 1 -- GROUP BY mon, GROUP BY LEFT(created, 7)
ORDER BY 1;

예제5 : 월별 채널별 유니크한 사용자 수

• 필요한 정보 - 시간 정보, 사용자 정보, 채널 정보
• 먼저 어느 테이블을 사용해야하는지 생각!
• 이번에는 channel_id가 아닌 channel 이름으로 계산해보자(위와 다름)
  • session, channel의 조인이 필요

1. 일단 2 테이블의 조인부터 해결

• JOIN이란 결국 서로 다른 테이블에 존재하는 레코드들은 특정 조건을 바탕으로 병합하는 작업

SELECT [s.id](http://s.id/), s.user_id, s.created, s.channel_id, c.channel
FROM session s
JOIN channel c ON [c.id](http://c.id/) = s.channel_id;

1. 정답

SELECT
	LEFT(s.created, 7) AS mon,
	c.channel,
	COUNT(DISTINCT user_id) AS mau
FROM session s
JOIN channel c ON c.id = s.channel_id
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

• 노란색 형광펜 : COUNT의 동작을 잘 이해하는 것이 중요! & 유니크 해야하니 DISTINCT와 연동 된다는 점!
• 초록색 형관펜 : 필드/테이블 이름에 Alias 사용 → AS는 없어도 되지만 가독성을 위해 쓰자
• GROUP BY, ORDER BY : 포지션 번호 vs 필드 이름 → 우리는 포지션 번호를 썼음