풀스택
통계성 쿼리 작성하기 — GROUP BY, HAVING, CASE WHEN 활용
오늘 다룬 내용
- 서브쿼리 여러 개를 SELECT 절에 넣어 한 줄 통계 뽑기
- GROUP BY + HAVING으로 조건에 맞는 그룹만 추출하기
- CASE WHEN + SUM으로 조건별 개수 집계하기
- 날짜 함수로 일별/월별 통계 내기
사용한 샘플 데이터는 member, board, comments, board_like 테이블입니다.
1. 게시판 전체 현황 한눈에 보기
관리자 화면에 흔히 나오는 "오늘의 통계" 같은 형태를 서브쿼리로 구현했습니다.
SELECT
(SELECT COUNT(*) FROM member) AS total_member,
(SELECT COUNT(*) FROM board) AS total_board,
(SELECT COUNT(*) FROM comments) AS total_comment,
(SELECT COUNT(*) FROM board_like) AS total_like;포인트: 서브쿼리를 SELECT 안에 여러 개 넣으면, 서로 다른 테이블의 집계 결과를 한 줄로 모을 수 있습니다.
2. 카테고리별 활동 현황
-- 카테고리별 게시글 수, 평균 조회수, 최고 조회수
SELECT
category_id,
COUNT(*) AS board_count,
AVG(view_count) AS avg_view,
MAX(view_count) AS max_view
FROM board
GROUP BY category_id
ORDER BY board_count DESC;여기에 조건을 추가해서, 게시글이 일정 개수 이상이면서 평균 조회수도 유효한 카테고리만 골라낼 수도 있습니다.
-- 게시글이 5개 이상이면서 평균 조회수가 0보다 큰 카테고리만
SELECT
category_id,
COUNT(*) AS board_count,
AVG(view_count) AS avg_view
FROM board
GROUP BY category_id
HAVING COUNT(*) >= 5 AND AVG(view_count) > 0;포인트: WHERE는 그룹으로 묶기 전 행 단위 필터링, HAVING은 그룹으로 묶은 뒤 집계 결과에 대한 필터링이라는 차이를 다시 한번 체감했습니다.
3. 회원별 활동 점수 매기기 (기초 단계)
"게시글 작성 수 + 댓글 작성 수"로 활동량이 많은 회원을 가려내고 싶었지만, 이번 단계에서는 우선 각각 따로 집계하는 연습만 진행했습니다. 두 결과를 합치는 건 다음 단계인 JOIN에서 다룰 예정입니다.
-- 회원별 게시글 수
SELECT member_id, COUNT(*) AS board_count
FROM board
GROUP BY member_id;-- 회원별 댓글 수
SELECT member_id, COUNT(*) AS comment_count
FROM comments
GROUP BY member_id;4. CASE WHEN으로 조건별 집계하기
게시글을 "인기글(조회수 10 이상)"과 "일반글"로 나누어 개수를 한 번에 뽑고 싶을 때 CASE WHEN을 사용했습니다.
SELECT
SUM(CASE WHEN view_count >= 10 THEN 1 ELSE 0 END) AS popular_count,
SUM(CASE WHEN view_count < 10 THEN 1 ELSE 0 END) AS normal_count
FROM board;동작 원리: CASE WHEN이 행 하나하나를 조건에 맞게 1 또는 0으로 바꿔주고, SUM이 그 값들을 더해서 개수처럼 집계하는 방식입니다. 처음엔 좀 낯설었는데, "조건 → 1/0 변환 → SUM"이라는 흐름으로 이해하니 훨씬 명확해졌습니다.
카테고리별로 쪼개서 보면 이렇게 됩니다.
-- 카테고리별로 인기글/일반글 개수를 나누어 집계
SELECT
category_id,
SUM(CASE WHEN view_count >= 10 THEN 1 ELSE 0 END) AS popular_count,
SUM(CASE WHEN view_count < 10 THEN 1 ELSE 0 END) AS normal_count
FROM board
GROUP BY category_id;5. 좋아요 랭킹 만들기
-- 좋아요를 가장 많이 받은 게시글 TOP 5
SELECT board_id, COUNT(*) AS like_count
FROM board_like
GROUP BY board_id
ORDER BY like_count DESC
LIMIT 5;-- 좋아요를 가장 많이 누른(활동적인) 회원 TOP 5
SELECT member_id, COUNT(*) AS like_given_count
FROM board_like
GROUP BY member_id
ORDER BY like_given_count DESC
LIMIT 5;6. 날짜 기준 통계 — 일별/월별 게시글 수
H2 DB에서는 날짜 함수로 원하는 단위(일/월)를 추출할 수 있습니다.
-- 날짜별 게시글 작성 수 (시간은 빼고 날짜만 추출)
SELECT CAST(created_at AS DATE) AS write_date, COUNT(*) AS board_count
FROM board
GROUP BY CAST(created_at AS DATE)
ORDER BY write_date DESC;-- 월별 게시글 작성 수
SELECT DATE_FORMAT(created_at, '%Y-%m') AS write_month, COUNT(*) AS board_count
FROM board
GROUP BY write_month
ORDER BY write_month DESC;⚠️ 날짜 함수는 DBMS마다 문법이 조금씩 다릅니다. 나중에 MySQL로 옮길 때는
DATE_FORMAT(created_at, '%Y-%m')형태를 그대로 쓸 수 있지만, H2에서는FORMATDATETIME등 다른 문법을 쓰기도 한다는 점을 기억해두면 좋습니다.
오늘의 정리
- 서브쿼리를 SELECT 절에 여러 개 넣으면 서로 다른 테이블의 집계를 한 줄로 모을 수 있다
GROUP BY+HAVING을 조합하면 "조건을 만족하는 그룹만" 뽑아낼 수 있다CASE WHEN+SUM을 조합하면 조건별로 분류한 개수를 한 번에 집계할 수 있다- 날짜 함수를 활용하면 일별/월별 통계도 만들 수 있으며, DBMS마다 문법이 다르다는 점을 기억해야 한다
SQL 연습 문제 풀이
Q1. member, board, comments, board_like 테이블의 전체 건수를 한 줄의 결과로 조회하시오. (서브쿼리 사용)
SELECT
(SELECT COUNT(*) FROM member) AS total_member,
(SELECT COUNT(*) FROM board) AS total_board,
(SELECT COUNT(*) FROM comments) AS total_comment,
(SELECT COUNT(*) FROM board_like) AS total_like;Q2. board 테이블에서 카테고리별 게시글 수와 평균 조회수를 함께 조회하고, 게시글 수가 많은 순으로 정렬하시오.
SELECT category_id,
COUNT(*) AS board_count,
AVG(view_count) AS avg_view
FROM board
GROUP BY category_id
ORDER BY board_count DESC;Q3. board 테이블에서 조회수 10 이상/미만 게시글 수를 CASE WHEN으로 한 줄에 조회하시오.
SELECT
SUM(CASE WHEN view_count >= 10 THEN 1 ELSE 0 END) AS popular_count,
SUM(CASE WHEN view_count < 10 THEN 1 ELSE 0 END) AS normal_count
FROM board;Q4. board 테이블에서 카테고리별로 인기글/일반글 개수를 각각 구하시오. (GROUP BY + CASE WHEN)
SELECT category_id,
SUM(CASE WHEN view_count >= 10 THEN 1 ELSE 0 END) AS popular_count,
SUM(CASE WHEN view_count < 10 THEN 1 ELSE 0 END) AS normal_count
FROM board
GROUP BY category_id;Q5. board_like 테이블에서 좋아요를 가장 많이 누른 회원 TOP 3을 조회하시오.
SELECT member_id, COUNT(*) AS like_count
FROM board_like
GROUP BY member_id
ORDER BY like_count DESC
LIMIT 3;다음 SECTION에서는 JOIN을 배워서, 오늘 각각 따로 집계했던 회원별 게시글 수 + 댓글 수를 하나의 쿼리로 합쳐볼 예정입니다.
JOIN 개념 잡기 — INNER JOIN vs LEFT JOIN
국비과정 SECTION11 실습 내용 정리입니다. 지금까지 배운 것 중 체감상 가장 중요한 단원이었습니다. 게시판처럼 테이블이 여러 개로 쪼개진 서비스는 JOIN 없이는 게시글 목록에 작성자 이름 하나 띄우는 것도 안 되더라구요.
오늘 다룬 내용
- JOIN이 왜 필요한지
- INNER JOIN — 양쪽에 모두 있는 데이터만 결합
- LEFT JOIN — 왼쪽 테이블은 무조건 다 살리기
- INNER JOIN vs LEFT JOIN 차이 명확히 구분하기
- 3개 이상 테이블 JOIN
1. JOIN이 왜 필요한가?
게시글 목록을 보여줄 때는 보통 작성자 "이름"이 함께 나와야 합니다. 그런데 board 테이블에는 이름이 없고 member_id만 있습니다.
SELECT * FROM board;| id | title | member_id |
|---|---|---|
| 1 | 첫 게시글 | 1 |
| 2 | 두번째 게시글 | 2 |
"1번 회원이 누구지?"를 알려면 member 테이블을 또 조회해야 합니다.
SELECT * FROM member WHERE id = 1;매번 두 번 조회하는 건 비효율적입니다. JOIN은 두 테이블을 연결해서 한 번의 쿼리로 양쪽 컬럼을 동시에 가져오는 방법입니다.
SELECT
board.title,
member.member_id,
member.username
FROM board
JOIN member ON board.member_id = member.member_id;| title | name |
|---|---|
| 첫 게시글 | 홍길동 |
| 두번째 게시글 | 김철수 |
게시글 제목과 작성자 이름이 한 줄로 합쳐졌습니다.
2. INNER JOIN — 양쪽에 모두 있는 데이터만
JOIN이라고만 쓰면 기본적으로 INNER JOIN을 의미합니다. 두 테이블에서 연결 조건이 일치하는 행만 결과로 가져옵니다.
기본 문법
SELECT 컬럼들
FROM 테이블A
INNER JOIN 테이블B ON 테이블A.연결컬럼 = 테이블B.연결컬럼;게시글 + 작성자 이름
SELECT b.id, b.title, m.name AS writer_name
FROM board b
INNER JOIN member m ON b.member_id = m.id;포인트: 테이블명에 b, m 같은 짧은 별칭(alias)을 붙이면 컬럼을 적을 때마다 긴 테이블명을 반복하지 않아도 됩니다.
INNER JOIN의 핵심 동작 원리
member 테이블 board 테이블
id=1 홍길동 member_id=1 (게시글A)
id=2 김철수 member_id=1 (게시글B)
id=3 이영희 member_id=2 (게시글C)
member_id=99 (게시글D) ← 99번 회원은 없음!INNER JOIN 결과:
| 게시글 | 작성자 |
|---|---|
| 게시글A | 홍길동 |
| 게시글B | 홍길동 |
| 게시글C | 김철수 |
게시글D는 결과에서 사라집니다. member_id=99인 회원이 member 테이블에 없기 때문입니다. INNER JOIN은 양쪽 테이블 모두에 존재하는, 즉 "교집합"에 해당하는 데이터만 보여줍니다.
💡 실무에서는 FK 제약조건 덕분에 이런 상황이 잘 안 생기지만, "왜 일부 데이터가 안 보이지?" 하는 버그를 디버깅할 때는 INNER JOIN의 이 특성을 꼭 기억해야 합니다.
카테고리까지 함께 JOIN (3개 테이블)
SELECT b.title, m.name AS writer_name, c.name AS category_name
FROM board b
INNER JOIN member m ON b.member_id = m.id
INNER JOIN category c ON b.category_id = c.id;JOIN은 필요한 만큼 계속 이어붙일 수 있다는 걸 확인했습니다.
3. LEFT JOIN — 왼쪽 테이블은 무조건 다 보여주기
LEFT JOIN은 FROM 뒤에 먼저 적은(왼쪽) 테이블 데이터는 모두 유지하고, 오른쪽 테이블에 연결되는 데이터가 없으면 NULL로 채웁니다.
기본 문법
SELECT 컬럼들
FROM 테이블A
LEFT JOIN 테이블B ON 테이블A.연결컬럼 = 테이블B.연결컬럼;댓글이 없는 게시글도 포함해서 조회하기
게시글 목록에는 "댓글이 0개인 게시글도" 나와야 합니다. INNER JOIN을 쓰면 어떻게 될까요?
-- ❌ INNER JOIN: 댓글이 하나도 없는 게시글은 결과에서 사라짐
SELECT b.title, c.content
FROM board b
INNER JOIN comment c ON b.id = c.board_id;-- ✅ LEFT JOIN: 댓글이 없는 게시글도 NULL로 채워져서 결과에 포함됨
SELECT b.title, c.content
FROM board b
LEFT JOIN comment c ON b.id = c.board_id;| title | content |
|---|---|
| 첫 게시글 | 환영합니다! |
| 첫 게시글 | 반갑습니다 |
| 두번째 게시글 | NULL |
"두번째 게시글"은 댓글이 없지만 LEFT JOIN 덕분에 게시글 자체는 남고 content만 NULL로 표시됩니다.
INNER JOIN vs LEFT JOIN 비교 정리
| INNER JOIN | LEFT JOIN | |
|---|---|---|
| 기준 | 양쪽 테이블에 모두 존재하는 데이터만 | 왼쪽 테이블은 무조건 전부 포함 |
| 연결 안 되는 데이터 | 결과에서 제외됨 | 오른쪽 컬럼이 NULL로 표시됨 |
| 사용 시점 | 둘 다 있는 경우만 보고 싶을 때 | 왼쪽 기준으로 빠짐없이 보고 싶을 때 |
💡 실무 판단 기준: "댓글이 없는 게시글도 목록에 보여야 하나?" → LEFT JOIN. "댓글이 있는 게시글만 보고 싶다" → INNER JOIN.
LEFT JOIN + COUNT로 댓글 수 구하기 (NULL 처리 포함)
-- 게시글별 댓글 수 (댓글이 0개인 게시글도 0으로 표시)
SELECT b.id, b.title, COUNT(c.id) AS comment_count
FROM board b
LEFT JOIN comment c ON b.id = c.board_id
GROUP BY b.id, b.title;포인트: COUNT(c.id)처럼 컬럼명을 넣으면 NULL인 행은 세지 않아서, 댓글이 없는 게시글은 자동으로 0이 됩니다. COUNT(*)를 쓰면 행 자체는 존재하므로 잘못 1로 집계될 수 있어 주의해야 합니다.
4. 실전 활용 — 게시글 목록 화면에 필요한 정보 한 번에 가져오기
SELECT
b.id,
b.title,
b.view_count,
m.name AS writer_name,
c.name AS category_name,
COUNT(cm.id) AS comment_count
FROM board b
INNER JOIN member m ON b.member_id = m.id
INNER JOIN category c ON b.category_id = c.id
LEFT JOIN comment cm ON b.id = cm.board_id
GROUP BY b.id, b.title, b.view_count, m.name, c.name
ORDER BY b.created_at DESC;해석
- 작성자(
member)와 카테고리(category)는 게시글에 항상 존재해야 하므로 INNER JOIN - 댓글(
comment)은 없을 수도 있으므로 LEFT JOIN - 댓글 수는 GROUP BY로 집계
이 한 줄의 쿼리가 실제 게시판 목록 화면에서 쓰이는 형태의 쿼리입니다.
오늘의 정리
- JOIN은 여러 테이블에 나뉜 데이터를 한 번의 쿼리로 합쳐서 가져오는 방법이다.
- INNER JOIN은 두 테이블에 모두 존재하는 데이터(교집합)만 결과로 가져온다.
- LEFT JOIN은 왼쪽 테이블 데이터는 모두 유지하고, 연결되는 데이터가 없으면 NULL로 채운다.
- "없어도 보여야 하는 데이터"는 LEFT JOIN, "둘 다 있어야만 의미 있는 데이터"는 INNER JOIN.
- LEFT JOIN + COUNT(컬럼) 조합으로 0건도 포함한 정확한 집계가 가능하다.
SQL 연습 문제 풀이
Q1. board와 member를 INNER JOIN해서, 게시글 제목과 작성자 이름을 함께 조회하시오.
SELECT b.title, m.name AS writer_name
FROM board b
INNER JOIN member m ON b.member_id = m.id;Q2. board와 category를 INNER JOIN해서, 게시글 제목과 카테고리 이름을 함께 조회하시오.
SELECT b.title, c.name AS category_name
FROM board b
INNER JOIN category c ON b.category_id = c.id;Q3. board와 comment를 LEFT JOIN해서, 댓글이 없는 게시글도 포함하여 게시글 제목과 댓글 내용을 조회하시오.
SELECT b.title, c.content
FROM board b
LEFT JOIN comment c ON b.id = c.board_id;Q4. board와 comment를 LEFT JOIN하여, 게시글별 댓글 수를 구하시오. (댓글 0개인 게시글도 0으로 표시)
SELECT b.id, b.title, COUNT(c.id) AS comment_count
FROM board b
LEFT JOIN comment c ON b.id = c.board_id
GROUP BY b.id, b.title;Q5. board, member, category 세 테이블을 모두 INNER JOIN해서, 게시글 제목, 작성자 이름, 카테고리 이름을 함께 조회하시오.
SELECT b.title, m.name AS writer_name, c.name AS category_name
FROM board b
INNER JOIN member m ON b.member_id = m.id
INNER JOIN category c ON b.category_id = c.id;Q6. member와 board_like를 LEFT JOIN하여, 좋아요를 한 번도 누르지 않은 회원도 포함해서 회원별 좋아요 누른 횟수를 조회하시오.
SELECT m.id, m.name, COUNT(bl.id) AS like_given_count
FROM member m
LEFT JOIN board_like bl ON m.id = bl.member_id
GROUP BY m.id, m.name;Q7. board, member, comment를 활용해서, 게시글 제목, 작성자 이름, 댓글 수를 함께 조회하시오. (작성자는 INNER JOIN, 댓글은 LEFT JOIN)
SELECT b.title, m.name AS writer_name, COUNT(c.id) AS comment_count
FROM board b
INNER JOIN member m ON b.member_id = m.id
LEFT JOIN comment c ON b.id = c.board_id
GROUP BY b.title, m.name;JOIN을 배우고 나니 지난 SECTION10에서 따로 집계했던 "회원별 게시글 수"와 "회원별 댓글 수"를 이제 하나의 쿼리로 합칠 수 있겠다는 감이 왔습니다. 다음 SECTION에서는 서브쿼리와 JOIN을 함께 활용하는 심화 실습을 다룰 예정입니다.
