4.1 집계 함수란
4.1.1 집계 함수의 개념
집계 함수란 특정 칼럼 값을 입력받아 통계적 계산을 해 주는 함수
최댓값, 최솟값, 합계, 평균 등을 구할 때 사용
4.1.2 최댓값, 최솟값 구하기
이번 실습은 burgers 테이블을 이용
-- mapdonalds DB 진입
USE mapdonalds;
-- 가장 비싼 버거와 가장 싼 버거의 price 조회
SELECT MAX(price), MIN(price)
FROM burgers;4.1.3 튜플의 개수 세기
-- 무게가 240을 초과하는 버거의 개수 세기
SELECT COUNT(*)
FROM burgers
WHERE gram > 240;- COUNT(*): NULL 값을 포함한 전체 튜플의 수를 센다.
- COUNT(칼럼명): 지정된 칼럼에서 NULL 값을 제외한 튜플의 수를 센다.
4.1.4 합계 구하기
-- 합계 구하기 (숫자형 칼럼에만 이용 가능)
SELECT SUM(price)
FROM burgers;4.1.5 평균 구하기
-- 평균 구하기 (숫자형 칼럼에만 이용 가능)
SELECT AVG(price)
FROM burgers;4.2 집계 함수 실습: 은행 DB
4.2.1 데이터셋 만들기
- DECIMAL: 소수점을 포함한 고정 길이의 숫자를 나타내는 자료형
칼럼명 DECIMAL (P, S)
P(Precision, 정밀도)는 소수점을 포함한 전체 자릿수
S(Scale, 스케일)는 소수점 이하 자릿수
- DATETIME: 날짜와 시간을 나타내는 자료형
칼럼명 DATETIME
YYYY-MM-DD hh:mm:ss 형식으로 값 저장
연-월-일 시:분:초
-- bank DB 생성 및 진입
CREATE DATABASE bank;
USE bank;
-- transactions 테이블 생성
CREATE TABLE transactions (
id INTEGER, -- 아이디
amount DECIMAL(12, 2), -- 거래 금액
msg VARCHAR(15), -- 거래처
created_at DATETIME, -- 거래 일시
PRIMARY KEY (id) -- 기본키 지정: id
);
-- transactions 데이터 삽입
INSERT INTO transactions (id, amount, msg, created_at)
VALUES
(1, -24.20, 'Google', '2024-11-01 10:02:48'),
(2, -36.30, 'Amazon', '2024-11-02 10:01:05'),
(3, 557.13, 'Udemy', '2024-11-10 11:00:09'),
(4, -684.04, 'Bank of America', '2024-11-15 17:30:16'),
(5, 495.71, 'PayPal', '2024-11-26 10:30:20'),
(6, 726.87, 'Google', '2024-11-26 10:31:04'),
(7, 124.71, 'Amazon', '2024-11-26 10:32:02'),
(8, -24.20, 'Google', '2024-12-01 10:00:21'),
(9, -36.30, 'Amazon', '2024-12-02 10:03:43'),
(10, 821.63, 'Udemy', '2024-12-10 11:01:19'),
(11, -837.25, 'Bank of America', '2024-12-14 17:32:54'),
(12, 695.96, 'PayPal', '2024-12-27 10:32:02'),
(13, 947.20, 'Google', '2024-12-28 10:33:40'),
(14, 231.97, 'Amazon', '2024-12-28 10:35:12'),
(15, -24.20, 'Google', '2025-01-03 10:01:20'),
(16, -36.30, 'Amazon', '2025-01-03 10:02:35'),
(17, 1270.87, 'Udemy', '2025-01-10 11:03:55'),
(18, -540.64, 'Bank of America', '2025-01-14 17:33:01'),
(19, 732.33, 'PayPal', '2025-01-25 10:31:21'),
(20, 1328.72, 'Google', '2025-01-26 10:32:45'),
(21, 824.71, 'Amazon', '2025-01-27 10:33:01'),
(22, 182.55, 'Coupang', '2025-01-27 10:33:25'),
(23, -24.20, 'Google', '2025-02-03 10:02:23'),
(24, -36.30, 'Amazon', '2025-02-03 10:02:34'),
(25, -36.30, 'Notion', '2025-02-03 10:04:51'),
(26, 1549.27, 'Udemy', '2025-02-14 11:00:01'),
(27, -480.78, 'Bank of America', '2025-02-14 17:30:12');4.2.2 거래 금액의 합계 구하기
-- Google과 거래한 금액의 합계 구하기
SELECT SUM(amount)
FROM transactions
WHERE msg = 'Google';4.2.3 거래 금액의 최댓값/최솟값 구하기
-- PayPal과 거래한 금액의 최댓값/최솟값 구하기
SELECT MAX(amount), MIN(amount)
FROM transactions
WHERE msg = 'PayPal';4.2.4 거래 횟수 세기
- IN 연산자
IN 연산자는 어떤 칼럼의 값이 주어진 목록 값 중 하나에 해당하는지 찾아 준다.
칼럼A IN (값1, 값2);
-- Coupang 및 Amazon과 거래한 횟수 세기 (OR 연산자)
SELECT COUNT(*)
FROM transactions
WHERE msg = 'Coupang' OR msg = 'Amazon';
-- Coupang 및 Amazon과 거래한 횟수 세기 (IN 연산자)
SELECT COUNT(*)
FROM transactions
WHERE msg IN ('Coupang', 'Amazon');4.2.5 입금 금액의 평균 구하기
-- Google 및 Amazon에서 입금받은 금액의 평균 구하기
SELECT AVG(amount)
FROM transactions
WHERE msg IN ('Google', 'Amazon') AND amount > 0;4.2.6 거래처 목록 조회하기
-- msg 목록 조회
SELECT msg
FROM transactions;위와 같이 조회하면 칼럼이 모두 조회돼 거래처가 중복으로 나옴
이러한 중복을 없애고 싶을 때 DISTINCT 키워드를 사용
DISTINCT 키워드: 쿼리 실행 결과에서 중복된 값을 제거하고 고유한 값만 반환할 때 사용
SELECT DISTINCT 칼럼명
FROM 테이블명;
-- 중복을 제거한 msg 목록 조회
SELECT DISTINCT msg
FROM transactions;
-- 중복을 제거한 msg 수 세기
SELECT COUNT(DISTINCT msg)
FROM transactions;4.3 셀프체크
다음 데이터를 employees(직원) 테이블에 담아 관리하려고 합니다. 각 칼럼에 대한 설명을 참고해 다음 1~7을 수행하는 쿼리를 작성하세요.
employees
| id | name | department | salary |
|---|---|---|---|
| 101 | John | Sales | 7000 |
| 102 | Aria | IT | 5500 |
| 103 | Mike | Sales | 8000 |
| 104 | Lily | HR | 6500 |
| 105 | David | IT | 7200 |
| 106 | Emma | Sales | 6500 |
| 107 | Oliver | IT | 5900 |
| 108 | Sophia | HR | 6300 |
| 109 | Lucas | Sales | 5500 |
| 110 | Charlotte | HR | 6800 |
- id: 아이디, 기본키이며 정수
- name: 이름, 최대 50자 문자
- department: 소속 부서, 최대 50자 문자
- salary: 연봉, 단위는 '천만 원' 정수
-- 1. company DB를 생성 및 진입 후 employees 테이블 생성
CREATE DATABASE company;
USE company;
CREATE TABLE employees (
id INTEGER,
name VARCHAR(50),
department VARCHAR(50),
salary INTEGER,
PRIMARY KEY (id)
);
-- 2. employees 테이블에 직원 10명의 데이터를 삽입
INSERT INTO employees (id, name, department, salary)
VALUES
(101, 'John', 'Sales', 7000),
(102, 'Aria', 'IT', 5500),
(103, 'Mike', 'Sales', 8000),
(104, 'Lily', 'HR', 6500),
(105, 'David', 'IT', 7200),
(106, 'Emma', 'Sales', 6500),
(107, 'Oliver', 'IT', 5900),
(108, 'Sophia', 'HR', 6300),
(109, 'Lucas', 'Sales', 5500),
(110, 'Charlotte', 'HR', 6800);
-- 3. 모든 직원의 연봉 합계를 계산
SELECT SUM(salary)
FROM employees;
-- 4. Sales 부서의 평균 연봉
SELECT AVG(salary)
FROM employees
WHERE department = 'Sales';
-- 5. 부서가 모두 몇 개 있는지 조회
SELECT COUNT(DISTINCT department)
FROM employees;
-- 6. Sales 부서의 최대 연봉과 최소 연봉 차이
SELECT MAX(salary) - MIN(salary)
FROM employees
WHERE department = 'Sales';
-- 7. 가장 높은 연봉을 받는 직원은 전체 평균 연봉 대비 얼마를 더 받는가
SELECT MAX(salary) - AVG(salary)
FROM employees;집계 함수 연습 문제
-- orders 테이블 생성
CREATE TABLE orders (
id INTEGER, -- ID
customer_name VARCHAR(50), -- 고객명
product VARCHAR(50), -- 제품명
quantity INTEGER, -- 주문 수량
price DECIMAL(10, 2), -- 단가
order_date DATE, -- 주문 날짜
region VARCHAR(20), -- 고객이 사는 지역
PRIMARY KEY (id)
);
-- orders 데이터 삽입
INSERT INTO orders (id, customer_name, product, quantity, price,order_date, region)
VALUES
(1, '김철수', '노트북', 2, 1500000, '2023-11-01', '서울'),
(2, '박영희', '스마트폰', 1, 900000, '2023-11-02', '부산'),
(3, '이민호', '청소기', 1, 250000, '2023-11-03', '서울'),
(4, '최수진', '냉장고', 1, 1200000, '2023-11-04', '대구'),
(5, '정하늘', '노트북', 1, 1500000, '2023-11-05', '부산'),
(6, '홍길동', '스마트폰', 3, 900000, '2023-11-06', '서울'),
(7, '오준수', '에어컨', 2, 800000, '2023-11-07', '대구'),
(8, '서지우', '청소기', 1, 250000, '2023-11-08', '서울'),
(9, '이은지', '냉장고', 2, 1200000, '2023-11-09', '부산'),
(10, '안현준', '스마트폰', 1, 900000, '2023-11-10', '대구');
-- 연습 문제 1. 모든 주문의 총 매출액을 계산
SELECT SUM(quantity*price) AS total_sales
FROM orders;
-- 연습 문제 2. 단가가 1,000,000원 이상인 제품의 주문 건수를 계산
SELECT COUNT(*) AS expensive_orders
FROM orders
WHERE price >= 1000000;
-- 연습 문제 3. 주문 수량이 2개 이상이면서 단가 1,000,000원 이하인 제품의 총 매출액 계산
SELECT SUM(quantity*price) AS total_sales
FROM orders
WHERE quantity >= 2 AND price <= 1000000;
-- 연습 문제 4. 고객이 사는 지역의 개수를 출력
SELECT COUNT(DISTINCT region) AS unique_regions
FROM orders;
-- 연습 문제 5. 주문 날짜가 2023-11-01과 2023-11-05 사이에 해당하는 주문의 총 수량을 계산
SELECT SUM(quantity) AS total_quantity
FROM orders
WHERE order_date >= '2023-11-01' AND order_date <= '2023-11-05';
-- 연습 문제 6. 고객 이름이 '김철수'이거나 '대구' 사는 고객이 주문한 제품명 조회
SELECT product AS unique_products
FROM orders
WHERE customer_name = '김철수' OR region = '대구';
-- 연습 문제 7. '스마트폰'이 총 몇 대 팔렸는지 조회
SELECT SUM(quantity)
FROM orders
WHERE product = '스마트폰';
hi!