SQL_Basic_05_Day 06. 데이터 그룹화 하기

Day 06. 데이터 그룹화 하기

---

1. 데이터 그룹화 하기

---

데이터 그룹화해서 통계내기

 

“고객 등급 별 매출 통계 뽑아주세요”
“상품 카테고리 별 실적 통계 뽑아주세요”

 

컬럼에서 동일한 값을 가지는 로우를 그룹화하는 키워드

 

GROUP BY

 

 

GROUP BY 특징

• GROUP BY [컬럼이름] 형식으로 사용.
• 주로 그룹별 데이터를 집계할때 사용하며, 엑셀의 피벗기능과 유사.
• GROUP BY가 쓰인 쿼리의 SELECT절에는 GROUP BY 대상컬럼과 그룹함수만 사용가능.
• 만약, GROUP BY 대상컬럼이 아닌 컬럼을 SELECT 하면, 에러가 발생.
• 여러 컬럼으로 그룹화도 가능하며, 키워드 뒤에 [컬럼이름]을 복수 개 입력하면 된다.
• 컬럼번호로도 그룹화가 가능
• 이때, 컬럼번호는 SELECT 절의 컬럼 이름의 순서를 의미.

 

GROUP BY 문법

 

SELECT [GROUP BY대상컬럼이름], … , [그룹함수]
FROM[테이블이름]
WHERE조건식
GROUP BY [컬럼이름];

 

 

GROUP BY 사용예제

 

SELECT type
FROM pokemon.mypokemon
GROUP BY type;

 

 

 

 

 

2. 그룹에 조건 주기

---

가져올 데이터 그룹에 조건을 지정해주는 키워드

HAVING

 

HAVING 특징

 

• HAVING 조건식 형식으로 사용.
• 조건식이 True(참)이 되는 그룹만 선택.
• HAVING 절의 조건식에서는그룹 함수를 활용

 

HAVING 문법

 

SELECT [컬럼이름], …, [그룹함수]
FROM[테이블이름]
WHERE조건식
GROUP BY [컬럼이름]
HAVING조건식

 

 

 

 

3. 다양한 그룹 함수 알아보기

---

COUNT

• 그룹의 값 수를 세는 함수
• COUNT([컬럼이름]) 형식으로 SELECT, HAVING 절에서 사용.
• 집계할 컬럼 이름은 그룹의 기준이 되는 컬럼이름과 같아도 되고, 같지 않아도 된다.
• COUNT(1)은 하나의 값을 1로 세어주는 표현으로 COUNT 함수에 자주 사용.
• GROUP BY가 없는 쿼리에서도 사용가능하며, 이때는 전체 로우에 함수가 적용.

 

 

쿼리 문법

 

SELECT [컬럼이름], …, COUNT([컬럼이름])
FROM[테이블이름]
GROUP BY [컬럼이름]
HAVING조건문;

 

 

SUM

• 그룹의 합을 계산하는 함수
• SUM([컬럼이름]) 형식으로 SELECT, HAVING 절에서 사용합니다.
• 집계할 컬럼 이름은 그룹의 기준이되는 컬럼이름과 같아도 되고, 같지 않아도 된다.
• GROUP BY가 없는 쿼리에서도 사용가능하며, 이때는 전체 로우에 함수가 적용.

 

쿼리 문법

SELECT [컬럼이름], …, SUM([컬럼이름])
FROM[테이블이름]
GROUP BY [컬럼이름]
HAVING조건문;

※ Tip! 그룹의 기준이 되는 컬럼이름을 함께 SELECT해야 의미를 파악할 수 있다!

 

 

AVG

• 그룹의 평균을 계산하는 함수
• AVG([컬럼이름]) 형식으로 SELECT, HAVING 절에서 사용.
• 집계할 컬럼이름은 그룹의 기준이 되는 컬럼이름과 같아도 되고, 같지 않아도 된다.
• GROUP BY가 없는 쿼리에서도 사용가능하며,  이때는 전체로우에 함수가 적용된다.

쿼리 문법

SELECT [컬럼이름], …, AVG([컬럼이름])
FROM[테이블이름]
GROUP BY [컬럼이름]
HAVING 조건문;

 

 

MIN

• 그룹의 최솟값을 반환하는 함수
• MIN([컬럼이름])형식으로 SELECT, HAVING 절에서 사용.
• 집계할 컬럼이름은 그룹의 기준이 되는 컬럼이름과 같아도 되고, 같지 않아도 된다.
• GROUP BY가 없는 쿼리에서도 사용가능하며,  이때는 전체로우에 함수가 적용된다.

 

쿼리 문법

SELECT [컬럼이름], …, MIN([컬럼이름])
FROM[테이블이름]
GROUP BY [컬럼이름]
HAVING 조건문;

 

MAX

• 그룹의 최댓값을 반환하는 함수
• MIN([컬럼이름])형식으로 SELECT, HAVING 절에서 사용.
• 집계할 컬럼이름은 그룹의 기준이 되는 컬럼이름과 같아도 되고, 같지 않아도 된다.
• GROUP BY가 없는 쿼리에서도 사용가능하며,  이때는 전체로우에 함수가 적용된다.

쿼리 문법

SELECT [컬럼이름], …, MAX([컬럼이름])
FROM[테이블이름]
GROUP BY [컬럼이름]
HAVING 조건문;

 

 

그룹 함수 사용 예제 (1) SELECT 절에서 사용

 

SELECT type, COUNT(*), COUNT(1), AVG(height), MAX(weight)
FROM pokemon.mypokemon
GROUP BY type;

 

 

 

 

 

그룹 함수 사용 예제 (2) HAVING 절에서 사용

 

SELECT type, COUNT(*), COUNT(1), AVG(height), MAX(weight)
FROM pokemon.mypokemon
GROUP BY type
HAVING COUNT(1) = 2;

 

 

 

 

 

 

 

4. [TIP] 쿼리 실행 순서 알아보기

---

6가지 핵심 쿼리 키워드

 

 

 

 

 

5. [실습] 데이터를 그룹화해서 통계를 내보자

실습 전 데이터 프레임 구성

 

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
	number  int,
       name	varchar(20),
       type	varchar(10),
       height	float,
       weight	float
);
INSERT INTO mypokemon (number, name, type, height, weight)
VALUES (10, 'caterpie', 'bug', 0.3, 2.9),
	   (25, 'pikachu', 'electric', 0.4, 6),
	   (26, 'raichu', 'electric', 0.8, 30),
          (125, 'electabuzz', 'electric', 1.1, 30),
	   (133, 'eevee', 'normal', 0.3, 6.5),
          (137, 'porygon', 'normal', 0.8, 36.5),
	   (152, 'chikoirita', 'grass', 0.9, 6.4),
          (153, 'bayleef', 'grass', 1.2, 15.8),
          (172, 'pichu', 'electric', 0.3, 2),
          (470, 'leafeon', 'grass', 1, 25.5);

 

MISSION (1)
포켓몬 테이블에서 이름(name)의 길이가 5보다 큰 포켓몬들을 타입(type)을 기준으로 그룹화하고,
몸무게(weight)의 평균이20이상인 그룹의 타입과, 몸무게의 평균을 가져와주세요. 이때, 결과는
몸무게의 평균을 내림차순으로 정렬해주세요.

 

SELECT type, AVG(weight)
FROM mypokemon
WHERE LENGTH(name) > 5
GROUP BY type
HAVING AVG(weight) >= 20
ORDER BY AVG(weight) DESC;

 

 

 

MISSION (2)
포켓몬테이블에서 번호(number)가 200보다 작은포켓몬들을 타입(type)을 기준으로 그룹화한 후에,
몸무게(weight)의 최댓값이 10보다 크거나같고 최솟값은 2보다 크거나같은 그룹의
타입, 키(height)의 최솟값, 최댓값을 가져와주세요. 이때, 결과는 키의 최솟값의 내림차순으로 정렬해
주시고, 만약 키의 최솟값이 같다면 키의 최댓값의 내림차순으로 정렬해주세요

 

SELECT type, MIN(height), MAX(height)
FROM mypokemon
WHERE number < 200
GROUP BY type
HAVING MAX(weight) >= 10  AND MIN(weight) >= 2
ORDER BY MIN(height) DESC, MAX(height) DESC;

 

 

6. [추가 실습] 데이터를 그룹화해서 통계를 내보자

 

MISSION (1)
포켓몬의 타입별 키의 평균을 가져와주세요.

 

SELECT AVG(height)
FROM mypokemon
GROUP BY type ;

 

MISSION (2)
포켓몬의 타입별 몸무게의 평균을 가져와주세요.
반드시 GROUP BY 사용할 시에 컬럼을 SELECT에 놓을 것

 

SELECT type, AVG(weight)
FROM mypokemon
GROUP BY type ;

 

 

MISSION (3)
포켓몬의 타입별 키의 평균과 몸무게의 평균을 함께 가져와주세요.

 

SELECT type, AVG(height), AVG(weight)
FROM mypokemon
GROUP BY type ;

 

 

MISSION (4)
키의 평균이 0.5 이상인 포켓몬의 타입을 가져와주세요.

 

SELECT type
FROM mypokemon
GROUP BY type
HAVING AVG(height) >= 0.5;

 

 

MISSION (5)
몸무게의 평균이 20이상인 포켓몬의 타입을 가져와주세요

 

SELECT type
FROM mypokemon
GROUP BY type
HAVING AVG(height) >= 0.5;

 

 

MISSION (6)
포켓몬의 타입별 번호(number)의 합을 가져와주세요.

 

SELECT type, SUM(number) 
FROM mypokemon
GROUP BY type;

 

 

MISSION (7)
키가 0.5 이상인 포켓몬이 포켓몬의 type 별로 몇개씩 있는지 가져와주세요.

 

SELECT type, COUNT(1) 
FROM mypokemon
WHERE height >= 0.5
GROUP BY type;

 

 

MISSION (8)
포켓몬 타입별 키의 최솟값을 가져와주세요.

 

SELECT type, MIN(height)
FROM mypokemon
GROUP BY type;

 

 

MISSION (9)
포켓몬 타입별 몸무게의 최댓값을 가져와주세요

 

SELECT type, MAX(weight)
FROM mypokemon
GROUP BY type;

 

 

MISSION (10)
키의 최솟값이 0.5보다 크고 몸무게의 최댓값이 30보다 작은 포켓몬타입을 가져와주세요.

 

SELECT type
FROM mypokemon
GROUP BY type
HAVING MIN(height) >= 0.5 AND MAX(weight) < 30;