2025.02.24 본_캠프 5일차

[예제로 익히는 SQL] - 3회차 요약

본 강의는 MySQL 기준으로 진행됩니다.

---

01. SQL 집계함수 (COUNT, MAX, MIN, SUM, AVG)

개념

• SQL 집계함수: 여러 행 또는 열로부터 하나의 결과값을 반환
• 집계함수 종류
  • COUNT 행 수 반환
  • SUM 열 합계 반환
  • AVG 열 평균 반환
  • MIN 최소값 반환
  • MAX 최대값 반환

예제

# 1. 전체 데이터 개수 세기
SELECT COUNT(*)  cnt FROM basic.theglory;

# 2. 평균 나이 구하기
SELECT AVG(나이)  avg_age FROM basic.theglory;

# 3. 최대 나이 구하기
SELECT MAX(나이)  max_age FROM basic.theglory;

# 4. 최소 나이 구하기
SELECT MIN(나이)  min_age FROM basic.theglory;

# 5. 나이 합계 구하기
SELECT SUM(나이)  sum_age FROM basic.theglory;

# 6. 여러 집계함수 사용
SELECT COUNT(*)  cnt, AVG(나이)  avg_age, 
       MAX(나이)  max_age, MIN(나이)  min_age, SUM(나이)  sum_age 
FROM basic.theglory;

---

02. SQL 그룹화 (GROUP BY, HAVING)

개념

• GROUP BY: 특정 컬럼을 기준으로 데이터를 요약

• HAVING: GROUP BY의 결과값을 필터링 (WHERE와 차이점 존재)
  필터링 구문 필터링 시점

  • WHERE GROUP BY 전 데이터 필터링
  • HAVING GROUP BY 후 결과값 필터링

예제

# 성별을 기준으로 집계
SELECT 성별, COUNT(*)  cnt, AVG(나이)  avg_age, 
       MAX(나이)  max_age, MIN(나이)  min_age, SUM(나이) sum_age 
FROM basic.theglory 
GROUP BY 성별;

# 나이가 31세 이상인 데이터 중, 성별별 평균 나이 구하기 (평균 나이가 41초과인 경우만 필터링)
SELECT 성별, AVG(나이)  avg_age 
FROM basic.theglory 
WHERE 나이 >= 31
GROUP BY 성별
HAVING AVG(나이) > 41;

---

03. SQL 서브쿼리 (SUBQUERY)

개념

• 서브쿼리: 쿼리 내부에서 또 다른 쿼리를 실행하는 것

• 서브쿼리 종류
  서브쿼리 유형 설명

  • 중첩(일반) 서브쿼리 : WHERE 절에서 사용
  • 스칼라 서브쿼리 : SELECT 절에서 사용 (하나의 컬럼처럼 활용)
  • 인라인 뷰 : FROM 절에서 사용 (하나의 테이블처럼 활용)

예제

# 1. 중첩 서브쿼리 - 문동은보다 나이가 많은 사람 조회
SELECT * 
FROM basic.theglory 
WHERE 나이 > (SELECT 나이 FROM basic.theglory WHERE 이름 = '문동은');

# 2. 스칼라 서브쿼리 - theglory2 테이블과 매칭하여 이름별 결제금액 합산
SELECT 이름, 나이,
       (SELECT COUNT(*) FROM theglory2 WHERE theglory2.이름 = theglory.이름)  same_name_cnt,
       (SELECT SUM(결제금액) FROM theglory2 WHERE theglory2.이름 = theglory.이름)  same_name_sumamount
FROM basic.theglory;

# 3. 인라인 뷰 - 나이가 33세 이상인 사람들의 직업 조회
SELECT x.나이, x.직업
FROM (SELECT * FROM basic.theglory WHERE 나이 >= 33)  x;

---

SQL 문제 & 정답 & 해석 정리

---

문제 1 - 서버별, 월별 게임계정 ID 수

문제 설명

• 조건1) 서버별, 월별 게임계정 ID 수를 중복값 없이 추출
• 조건2) 월은 첫 접속일자(first_login_date)를 기준으로 계산
• 조건3) 월은 yyyy-mm 형식으로 출력

정답 SQL

SELECT serverno,
       SUBSTR(first_login_date,1,7)  month,  
       COUNT(DISTINCT game_account_id)  cnt
FROM users
GROUP BY serverno, SUBSTR(first_login_date,1,7)
ORDER BY serverno, month;

해석

1. SUBSTR(first_login_date,1,7) → 첫 접속일자에서 연-월(yyyy-mm) 형식으로 잘라서 월을 추출
2. COUNT(DISTINCT game_account_id) → 중복되지 않는 게임계정 ID 개수 계산
3. GROUP BY serverno, month → 서버별 & 월별로 그룹화
4. ORDER BY serverno, month → 결과를 서버번호와 월 기준으로 정렬

---

문제 2 - 첫 접속일자별 게임캐릭터 ID 수 (HAVING 활용)

문제 설명

• 조건1) GROUP BY를 사용하여 first_login_date별 게임캐릭터 ID 개수를 중복 없이 구하기
• 조건2) HAVING 절을 사용하여 10개를 초과하는 경우만 추출

정답 SQL

SELECT first_login_date,
       COUNT(DISTINCT game_actor_id)  cnt 
FROM users
GROUP BY first_login_date
HAVING COUNT(DISTINCT game_actor_id) > 10
ORDER BY first_login_date;

해석

1. COUNT(DISTINCT game_actor_id) → 중복 없이 게임캐릭터 ID 개수를 계산
2. GROUP BY first_login_date → 첫 접속일자 기준으로 그룹화
3. HAVING COUNT(DISTINCT game_actor_id) > 10 → HAVING을 사용하여 10개 초과하는 경우만 필터링
4. ORDER BY first_login_date → 결과를 날짜순으로 정렬

---

문제 3 - 서버별, 기존/신규 유저 구분 및 평균 레벨

문제 설명

• 조건1) GROUP BY를 사용하여 서버별, 기존/신규 유저의 게임캐릭터 ID 개수를 중복 없이 구하기
• 조건2) 기존/신규 유저 구분 기준
  • 첫 접속일자(first_login_date)가 2024-01-01 미만이면 기존유저, 그렇지 않으면 신규유저
• 조건3) 서버별 평균 레벨을 함께 출력

정답 SQL

SELECT serverno,
       IF(first_login_date < '2024-01-01', '기존유저', '신규유저') AS user_data,
       COUNT(DISTINCT game_actor_id) AS user_cnt,
       AVG(level) AS avg_level
FROM users
GROUP BY serverno, user_data
ORDER BY serverno, user_data;

해석

1. IF(first_login_date < '2024-01-01', '기존유저', '신규유저') → 첫 접속일자 기준으로 기존/신규 유저 구분
2. COUNT(DISTINCT game_actor_id) → 중복 없는 게임캐릭터 ID 개수를 계산
3. AVG(level) → 평균 레벨 계산
4. GROUP BY serverno, user_data → 서버별 & 기존/신규 유저별로 그룹화
5. ORDER BY serverno, user_data → 서버번호와 유저 구분을 기준으로 정렬

---

문제 4 - HAVING 없이 인라인 뷰 서브쿼리 활용

문제 설명

• 조건1) HAVING을 사용하지 않고 인라인 뷰 서브쿼리를 활용하여 문제 2의 결과를 추출

정답 SQL

SELECT first_login_date, cnt
FROM (
    SELECT first_login_date, COUNT(DISTINCT game_actor_id) AS cnt
    FROM users 
    GROUP BY first_login_date
) a
WHERE cnt > 10
ORDER BY first_login_date;

해석

1. 서브쿼리 (인라인 뷰) 활용
   • COUNT(DISTINCT game_actor_id) AS cnt → first_login_date별 게임캐릭터 ID 개수 계산
   • GROUP BY first_login_date → 날짜별 그룹화
2. HAVING 없이 WHERE 사용
   • WHERE cnt > 10 → 10개 초과하는 경우만 필터링
3. ORDER BY first_login_date → 날짜순 정렬

---

문제 5 - SubQuery 활용하여 캐릭터 수 별 게임계정 개수

문제 설명

• 조건1) 레벨이 30 이상인 캐릭터를 기준으로, 게임계정 별 캐릭터 수를 중복 없이 추출
• 조건2) HAVING을 사용하여 캐릭터 수가 2개 이상인 게임계정만 추출
• 조건3) 인라인 뷰 서브쿼리를 활용하여 캐릭터 수 별 게임계정 개수를 중복 없이 추출

정답 SQL

SELECT g_a_cnt, COUNT(DISTINCT game_account_id) AS a_cnt
FROM (
    SELECT game_account_id, COUNT(DISTINCT game_actor_id) AS g_a_cnt
    FROM users 
    WHERE level >= 30
    GROUP BY game_account_id
    HAVING COUNT(DISTINCT game_actor_id) >= 2
) a
GROUP BY g_a_cnt
ORDER BY g_a_cnt;

해석

1. 서브쿼리에서 게임계정별 캐릭터 수 계산

   • COUNT(DISTINCT game_actor_id) AS g_a_cnt → 각 게임계정이 보유한 고유 캐릭터 수 계산
   • WHERE level >= 30 → 레벨 30 이상 캐릭터만 대상
   • GROUP BY game_account_id → 게임계정별 그룹화
   • HAVING COUNT(DISTINCT game_actor_id) >= 2 → 캐릭터가 2개 이상인 계정만 필터링

2. 외부 쿼리에서 캐릭터 수 별 게임계정 개수 구하기

   • SELECT g_a_cnt, COUNT(DISTINCT game_account_id) AS a_cnt
   • GROUP BY g_a_cnt → 캐릭터 개수(g_a_cnt) 기준으로 그룹화
   • ORDER BY g_a_cnt → 캐릭터 개수 기준으로 정렬