[TIL] SQL복습&심화_20일차(집계함수,GROUP BY, HAVING,SUB QUERY)

이전 시간에 이어서 튜터님께 배운 SQL 을 복습하고 실습하는 수업에 대한 정리를 하겠습니다.

 

데이터와 친해지는 SQL - 2회차

🔥 수업 목표

1. SQL 집계함수: COUNT, MAX, MIN, SUM, AVG

2. SQL 그룹화: GROUP BY, HAVING( + WHERE 과 차이점)

3. SQL : SUB QUERY 구문 (🔥중요)

4. 실습 문제

 

예제 샘플 데이터셋은 튜터님께서 제공해주셨습니다.

---

1. SQL 집계함수: COUNT, MAX, MIN, SUM, AVG

1) SELECT 문: 이를 사용하여 테이블의 값(=열, 컬럼, 필드라고도 불려요!)를 선택할 수 있습니다.

2) 아스테리크( * ) 기호를 사용하여 모든 컬럼값을 한번에 추출할 수 있어요.

3) FROM 절은 사용할 테이블의 이름을 명시합니다.

4) MySQL에서 구문 마지막은 세미콜론( ; ) 으로 끝내야 합니다. 이를 사용하면 다수의 SQL을 작성

할 때, 실행하고 싶은 SQL구문에 마우스 커서를 두고 ctrl+Enter 버튼을 누르면 해당 SQL만 실행

됩니다.

5) 중복 없이 컬럼을 조회하고 싶다면, ‘DISTINCT’ 구문을 활용합니다.

-- 모든 컬럼을 조회할 경우 
SELECT * 
FROM 테이블이름 
;
-- 특정 컬럼을 조회할 경우
SELECT 컬럼이름, 컬럼이름 
FROM 테이블이름
;
-- 특정 컬럼을 중복없이 조회할 경우
SELECT DISTINCT 컬럼이름
FROM 테이블이름 
;

 

[ 예시 ]

#1. 전체 데이터 갯수 세기
select count(*)as cnt 
from basic.theglory 

#2. 전체 데이터 평균 나이 구하기
select avg(나이)as avg_age 
from basic.theglory 

#3. 전체 데이터 최대 나이 구하기
select max(나이)as max_age 
from basic.theglory 

#4. 전체 데이터 최소 나이 구하기
select min(나이)as min_age 
from basic.theglory 

#5. 전체 데이터 나이합계 구하기
select sum(나이)as sum_age 
from basic.theglory 

#6. 여러 집계함수의 동시사용
select count(*)as cnt
, avg(나이)as avg_age 
, max(나이)as max_age
, min(나이)as min_age
, sum(나이)as sum_age
from basic.theglory

---

2. SQL 그룹화: GROUP BY, HAVING( + WHERE 과 차이점)

1) GROUP BY절 

위에서 배운 집계함수에 그룹(기준) 이 더해진 개념입니다. 특정 컬럼을 기준으로 데이터를 요약해서 비교하고 싶을 때 주로 사용합니다.

( 전체 데이터를 기준으로 조회할 때는 GROUP BY절이 필요하지 않지만, 특정 컬럼을 기준으로 데이터를 요약해서 비교하고 싶을 때 주로 사용 )

 

[ 작성 순서 ]

1️⃣ SELECT 뒤 기준 컬럼 작성

2️⃣집계함수(COUNT, MAX, MIN, AVG, SUM) 작성

3️⃣ WHERE 절 뒤 GROUP BY 기준 컬럼 작성 (WHERE 절은 생략 가능)

 

[ ⚠️주의 ⚠️ ]

1️⃣, 2️⃣번 작성 후, 3️⃣번을 작성하지 않을 경우 ⚠️에러가 발생됩니다. 따라서 반드시 GROUP BY 에 있는 기준 컬럼은 SELECT 뒤에도 있어야 합니다.

이유 : 데이터를 SELECT할 때, 2️⃣번 집계함수는 여러 데이터로부터 하나의 행을 반환하지만, 1️⃣번 기준 컬럼은 N 개의 값을 반환하기 때문에, 3️⃣번 GROUP BY 절을 사용하여 기준컬럼 당 값 1개를 반환 할 수 있도록 명시해야 합니다.

 

[🔎TIP ✨ ] SQL 의 작동순서

 FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

# 집계함수와 GROUP BY 절을 함께 사용하는 SQL문

select 기준컬럼, 집계함수(조건컬럼을 기준으로 여러개의 집계함수 동시사용 가능)   
from 테이블명
where 조건 #(생략가능)
group by 기준컬럼
;

# 주의)) 기준컬럼과 집계함수를 작성하고 GROUP BY 절을 사용하지 않을 때 에러가 발생하는 예
select 기준컬럼, 집계함수(조건컬럼을 기준으로 여러개의 집계함수 동시사용 가능)   
from 테이블명
where 조건 #(생략가능)
;

 

[ 예시 ]  성별 기준 집계함수

• 성별 기준 데이터 갯수= 성별(기준) + 데이터 갯수(집계함수)
• 성별 기준 평균 나이= 성별(기준) + 평균 나이(집계함수)
• 성별 기준 최대 나이= 성별(기준) +최대 나이(집계함수)
• 성별 기준 최소 나이= 성별(기준) + 최소 나이(집계함수)
• 성별 기준 나이 합계 = 성별(기준)+ 나이 합계(집계함수)

# 집계함수와 GROUP BY 절을 함께 사용하는 SQL문 - 실습

select 성별 # 기준 컬럼 🚩 
, count(*)as cnt # 전체 데이터 갯수 ✅
, avg(나이)as avg_age # 평균나이 ✅
, max(나이)as max_age # 최대나이 ✅
, min(나이)as min_age # 최소나이 ✅
, sum(나이)as sum_age # 나이합계 ✅
from basic.theglory 
group by 성별 # 기준 컬럼 🚩

 

 

2) HAVING ≠ WHERE (🔥중요)

두 절 모두 조건절( 필터링 )이지만 차이점이 있습니다.

2-1) Having 절은 GROUP BY에 의한 결과를 필터링 할 때 사용됩니다.

2-2) GROUP BY 절 뒤에 Having절이 위치합니다.

필터링 구문	필터링 시점
WHERE	GROUP BY 절 전 데이터 필터링
HAVING	GROUP BY 절 후 결과값을 가지고 데이터 필터링

# 집계함수, GROUP BY절, HAVING을 함께 사용하는 SQL문

select 기준컬럼, 집계함수(조건컬럼을 기준으로 여러개의 집계함수 동시사용 가능)   
from 테이블명
where 조건 #(생략가능)
group by 기준컬럼
having 조건식
;

 

[ 예시 ]

 

THEGLORY 예제 테이블을 기준으로, 차례로 아래 조건들로 작성합니다.

• 1️⃣ 나이가 31세 이상이고,
• 2️⃣ 성별을 기준으로 평균 나이를 구하고,
• 3️⃣ 평균 나이가 41초과인 경우

1️⃣은 전체 데이터에 대한 필터링이예요. 따라서, WHERE 절에 위치해야 합니다.

2️⃣는 집계함수와 GROUP BY 를 사용해서 작성해 주어야 합니다. 집계함수의 짝궁 = GROUP BY

3️⃣은 GROUP BY 된 결과값을 필터링 해야 하므로, HAVING 절에 위치해야 합니다.

# 집계함수, GROUP BY절, HAVING을 함께 사용하는 SQL문 - 실습
select 성별, avg(나이)as avg_age 2️⃣
from basic.theglory 
where 나이>=311️⃣
group by 성별 2️⃣
having AVG_AGE>41 3️⃣

 

[🔎TIP ✨ ] SQL 의 작동순서

 FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

---

3. SQL : SUB QUERY 구문 (🔥중요)

SUB QUERY 를 통해, 데이터를 구조화 할 수 있습니다.

컬럼들을 활용한 사칙연산, 집계함수, 윈도우 함수, 포맷팅 등 많은 연산을 하는 경우, SUB QUERY 는 이를 순차적(구조적)으로 기록하는 역할을 수행합니다.

복잡한 연산시, 최종 결과를 반환하기 위한 중간테이블의 역할로 이해해주세요!

 

📌 사용 이유

SELECT를 하고 결과값을 확인하고, 그 결과값을 기억했다가 또 SELECT 를 하고, 또 이러한 과정을 겪는 경우, N 번의 쿼리문을 수행해야 하는데, N 번의 쿼리문 실행을 1번의 쿼리문으로 실행하기 위해 쿼리의 결과값을 가지고 추가 연산을 하기 위해 사용됩니다.

⇒ JOIN, UNION 동작 수행을 대체 할 수 있는 방법을 제공합니다.

 

📌 실행순서

(1) 서브쿼리 실행(가장 안쪽에 위치한 쿼리) → 메인쿼리(바깥쪽에 위치한 쿼리) 실행

(2) 쿼리의 가장 안쪽부터, 바깥쪽 쿼리를 실행하며 최종 결과값을 반환

 

📌 특징

(1) () 안에 SELECT, FROM 을 반드시 명시

(2) 쿼리 마지막에 ; 기호를 사용 불가 ( 추가 연산을 하기 떄문에 )

(3) ORDER BY절을 사용 할 수 없습니다.

 

📌3가지 서브쿼리 종류

3-1) 중첩(일반) 서브쿼리

(1) WHERE 절에서 사용

(2) 서브쿼리의 결과에 따라 달라지는 조건절

# 중첩 서브쿼리 실습

select *
from basic.theglory 
where 나이 > (select 나이 from basic.theglory where 이름='문동은')
;     # 문동은의 나이보다 나이가 많은 모든 데이터 반환하기

 

3-2) 스칼라 서브쿼리

(1) SELECT 절에서 사용

(2) 하나의 컬럼처럼 사용

# 중첩 서브쿼리 실습
# theglory 의 이름과 theglory2 테이블의 이름이 일치하는 경우를 count 하여 
  #same_name_cnt 컬럼으로 반환
# theglory 의 이름과 theglory 테이블의 이름이 일치하는 경우의 결제금액을 sum 하여 
  #same_name_sumamount 컬럼으로 반환
select 이름
, 나이
, (select count(*) from theglory2 where theglory2.이름=theglory.이름) as same_name_cnt
, (select sum(결제금액) from theglory2 where theglory2.이름=theglory.이름)as same_name_sumamount
from basic.theglory
;

 

3-3) 인라인 뷰(🔥가장 많이 사용)

(1) FROM 절에서 사용

(2) 하나의 테이블처럼 사용

(3) as 구문을 사용하여 명칭을 반드시 기재해야 함

(4) 간단해 보이지만, 향후 JOIN 및 UNION 시 가장 유용하게 사용됩니다 !

# 중첩 서브쿼리 실습

select x.나이, x.직업
from(	select *
		  from basic.theglory
		  where 나이>=33
	  )as x   # 나이가 33세 이상인 모든 데이터 중 나이와 직업 컬럼 반환하기

---

4. 실습 문제

# 원본 데이터
SELECT logid, ip_addr, `date`, game_account_id,
       game_actor_id, `level`, `exp`, serverno, 
       zone_id, etc_num1, etc_num2, etc_str1, etc_num3, etc_str2
FROM basic.users;

문제1

group by 절을 사용하여, 서버별 게임캐릭터id수(중복값 허용x)와 평균 경험치를 추출해주세요.

select serverno, count(distinct(game_actor_id)), avg(`exp`)
from basic.users 
group by serverno
;

 

[🔎TIP ]

``은 예약어로 사용하지 않고 `컬럼명 또는 테이블명`으로 사용하겠다는 표시입니다. ( '따옴표'와 다름니다'' )

 

문제2

group by 와 having 절을 사용하여, 일별(yyyy-mm-dd) 게임캐릭터id수(중복값 허용x)를 구하고, 그 값이 10개를 초과하는 경우를 추출해주세요.

select `date`, count(distinct(game_actor_id)) as count_gai
from basic.users 
group by `date`
having count_gai > 10
;

 

문제3

2번 문제를 having 이 아닌 인라인 뷰 subquery 를 사용하여, 추출해주세요.

select *
from
(select `date`, count(distinct(game_actor_id)) as count_gai
from basic.users 
group by `date`) a
where count_gai > 10
;

# 튜터님 정답쿼리
# 안쪽의 서브쿼리가 마치 테이블처럼 활용된 것을 보실 수 있습니다! 
select *
from(	 select `date`, count(distinct game_actor_id)as cnt 
		   from basic.users
		   group by `date` 
	   ) as a 
where cnt>10

 

[🔎TIP ]

1. count는 null을 세지 않습니다.

2. count(*) = count(1)

3. 집계함수 뒤에는(괄호)가 꼭 있어야합니다. 하지만 distinct 뒤에는 없어도 됩니다.

4. cast type

5. MySQL_list of data fomat

MySQL_list of data fomat

 

---

SQL 을 처음부터 보고싶으시다면 여기를 클릭📣

Python을 처음부터 보고싶으시다면 여기를 클릭📣

모두 힘내세요~🌃