[SQL] 간단한 데이터 추출하고 분석하기(2) - 집계 함수, GROUP BY, HAVING, ORDER BY

jae.y·2022년 9월 24일

mysql sql

👩🏻‍💻 SQL

목록 보기

2/2

🎯 목표 설정

집계 함수 COUNT(), SUM(), AVG(), MIN(), MAX() 이해하기
데이터를 GROUP BY, HAVING, ORDER BY를 사용해 그룹별로 요약해보기

📝 오늘 공부한 내용

1. 집계 함수

집계 함수(Aggregate Function)란?
여러 행으로부터 하나의 결괏값을 반환하는 함수입니다.
SELECT 구문에서만 사용되며, 이전에 다룬 기본 함수들이 행(row)끼리 연산을 수행했다면, 집계 함수는 열(column)끼리 수행됩니다.

집계 함수에는 COUNT(), SUM(), AVG(), MIN(), MAX() 가 있습니다.

📍집계 함수 종류

COUNT() : 특정 열(Column)의 행의 개수를 세는 함수
SUM() : 선택한 열(Column)의 합 계산
AVG() : 선택한 열(Column)의 평균 계산
MIN() : 선택한 열(Column)의 최솟값
MAX() : 선택한 열(Column)의 최댓값

📍코드로 실습하기

모든 실습은 관계형 데이터베이스의 기능을 제공하는 MySQL을 사용했습니다.
실습은 w3shcools 에서 수행했습니다.

실습 전, Products의 데이터 형태 알아보기
SELECT *
FROM products
LIMIT 5

실습1. OrderDetails의 데이터 개수 알아보기
-- COUNT 함수 이용하기
SELECT COUNT(*) AS '총 데이터 수'
FROM orderdetails
► 위 코드를 통해 OrderDetails 테이블 내 데이터(행)의 수가 518개라는 것을 알 수 있다.

실습2. 테이블 내 등록된 제품에 대한 총 가격 알아보기
-- SUM 함수 이용하기
SELECT SUM(price)
FROM products
-> 2222.71
실습3. 전 제품에 대한 평균 가격 알아보기
-- AVG 함수 이용하기
SELECT AVG(price)
FROM products
-> 28.866363636363637
실습4. Products 내에서 가장 저렴한 가격 알아보기
-- MIN 함수 이용하기
SELECT MIN(price)
FROM products
-> 2.5
실습5. Products 내에서 가장 비싼 가격 알아보기
-- MAX 함수 이용하기
SELECT MAX(price)
FROM products
-> 263.5

👩🏻‍💻 만약, 데이터에 NULL이 있다면?

NULL, NaN : Not a Number의 약자로, 문자도 아니고 숫자도 아닌 비어있는 값이다.

아래 그림의 테이블은 orderdetails이며, null값 계산 예시를 위해 임의로 데이터를 지웠습니다.
-- 전체 데이터 개수 구하기
SELECT COUNT(*)
FROM orderdetails
-> 15
-- NULL 포함 열의 데이터 개수 구하기
SELECT COUNT(ProductID)
FROM orderdetails
-> 12
► 특정 열에 대해서 COUNT 등의 집계 함수를 수행하면 해당 열이 NULL이 아닌 행에 대한 정보를 반환합니다.

2. GROUP BY

GROUP BY를 사용하면 유형별로 개수를 알고 싶을 때 컬럼에 데이터를 그룹화 할 수 있습니다.
기본 구조는 GROUP BY A 로, A는 그룹화할 컬럼을 입력하면 됩니다.
A는 SELECT 문에 넣어야 어떤 컬럼으로 그룹화되었는지 알기 좋습니다.

📍코드로 실습하기

실습 전, OrderDetails의 데이터 형태 알아보기
SELECT *
FROM orderdetails
LIMIT 5

실습1. 제품별로 구매되고 있는 평균 수량 구하기
-- GROUP BY 절 사용하기
SELECT productid, AVG(quantity)
FROM orderdetails
GROUP BY productid
► 위 결과를 보면, 제품별로 주문되고 있는 평균 수량을 알 수 있습니다.
► 1번 제품은 평균적으로 19.875개씩 주문이 들어오고 있네요.
► GROUP BY를 사용하면, 컬럼을 그룹화하여 유형별 정보를 알 수 있습니다.

3. HAVING

HAVING을 사용하면 GROUP BY 연산 결과물을 필터링할 수 있습니다. (WHERE : GROUP BY 하기 전 필터링)
기본 구조는 HAVING A 로, A에는 필터링할 조건식을 입력하면 됩니다.

📍코드로 실습하기

실습1. 구매 평균 수량이 40 이상인 제품 찾기
-- HAVING 절 사용하기
SELECT productid, AVG(quantity) AS avg_quantity
FROM orderdetails
GROUP BY productid
HAVING avg_quantity >= 40
► 위 결과를 보면, 1회 주문 시 평균 40개 이상이 판매되고 있는 제품은 3, 8번을 포함해 총 7제품이 있네요.
► HAVING을 사용하면, 그룹화된 이후의 데이터를 추가적으로 필터링할 수 있습니다.

4. ORDER BY

ORDER BY를 사용하면 주어진 기준으로 데이터를 정렬할 수 있습니다.
기본 구조는 ORDER BY A 로, A는 정렬하고자 하는 컬럼 명을 입력하면 됩니다.

📍코드로 실습하기

실습1. 제품별 판매 수가 300을 넘는 데이터를 출력해주세요. 높은 판매수부터 출력해주세요.
-- ORDER BY 절 사용하기
SELECT productid, SUM(quantity) AS sum_quantity
FROM orderdetails
GROUP BY productid
HAVING sum_quantity > 300
ORDER BY sum_quantity DESC
-- LIMIT 절 추가하여 판매수 TOP 5 구하기
SELECT productid, SUM(quantity) AS sum_quantity
FROM orderdetails
GROUP BY productid
HAVING sum_quantity > 300
ORDER BY sum_quantity DESC
LIMIT 5
► 위 결과를 보면, 31번 제품이 총 458개로 가장 많이 판매되었네요.

이렇게, 집계 함수와 GROUP BY, HAVING, ORDER BY 절을 추가하여 보다 상세한 데이터를 추출하고 분석해보는 실습을 해보았습니다.

🧨 어려웠던 내용

처음 HAVING을 배웠을 때, WHERE 절 과 유사한 기능을 하기 때문에 어떤 기준으로 나누어 사용해야될지 고민이 되었습니다. 하지만, 그룹화 하기 전, 1차적으로 필터링을 할 때 WHERE, 그룹화를 한 후 2차적으로 필터링할 땐 HAVING을 사용한다고 정의를 하니 더이상 두 기능에 대해 헷갈리지 않습니다.

HAVING, WHERE 두 절을 모두 사용한 예를 통해 이해를 높였습니다.

📍코드로 정리하기

예시) 제품번호가 30보다 작은 제품을 대상으로 하시오. 제품 총 주문량이 10개 이하인 고객을 출력하되, 하위 10개만 출력하시오.
SELECT OrderId, SUM(Quantity) AS sum_quantity
FROM OrderDetails
WHERE ProductID > 30 -- 제품 번호를 제한하여 검색 대상 제한
GROUP BY OrderID -- 고객을 기준으로 알아 봄
HAVING sum_quantity <= 10 -- 제품 총 구매량이 10 이하인 데이터로 제한
ORDER BY sum_quantity -- DESC를 작성하지 않으면 default 값으로 ASC(오름차순) 적용 됨
LIMIT 10 -- 상위 10개 항목만 출력
다음 코드를 통해, 아래 조건들을 만족한 데이터를 출력해보았다.

만약 제품 번호(ProductID)가 30 아래의 제품을 화장품 목록이라고 생각해보자.

제품을 화장품으로 제한 (WHERE ProductID > 30)

사이트에서 제품을 구매한 고객별로 화장품 총 구매량을 출력 (SELECT OrderId, SUM(Quantity), GROUP BY OrderID)

고객별 화장품 총 구매량이 10 이하인 데이터 출력 (HAVING sum_quantity <= 10)

고객ID 순이 아닌 구매량을 오름차순으로 정렬 (ORDER BY sum_quantity)

상위 10개 항목만 출력 (LIMIT 10)