01. SQL의 집계함수

---

집계함수란 여러 조건을 입력해 하나의 결과를 반환하는 함수를 말한다.

💡 이때 주의할 점은 집계 함수는 SELECT절이나 HAVING절에서만 호출된다.

① AVG : 평균값 반환

전달된 값들의 평균 값을 반환하는데, 이때 주의할 점은 부동 소수점 값을 반환한다는 것이다.

ROUND()함수를 이용해 특정한 소수점 값으로 반환할 수 있다.

💡 ROUND() 함수

첫 번째 인자로는 반올림할 값을 받고, 두 번째 인자로는 반올림할 자리수를 받는다.

SELECT ROUND(AVG(replacement_cost), 2) 
FROM film;

② COUNT : 개수를 반환

SELECT count(*)
FROM film;

③ MAX/MIN : 최대값 / 최소값 반환

SELECT MAX(replacement_cost), MIN(replacement_cost)
FROM film;

④ SUM : 모든 값의 합 반환

SELECT SUM(replacement_cost)
FROM film;

02. GROUP BY 구문

---

GROUP BY는 일정 카테고리별로 열을 집계한다.

❇︎ 기본 문법

SELECT category_col, 집계함수(data_col) 
FROM table
WHERE category_col != 'A'
GROUP BY category_col;

👉 GROUP BY문은 FROM문 바로 뒤 또는 WHERE문 바로 뒤에 위치해야 한다.

🚨 반드시 지켜야할 규칙

① GROUP BY문에서 선택한 열은 반드시 SELECT 문에도 포함해야 한다.

단 집계함수의 인자로 전달된 열은 예외이다.

② WHERE문에는 집계 함수(SUM 등)를 사용할 수 없다.

WHERE문이 실행된 후에 집계함수가 실행되기 때문이다.
👉 이 경우 결과를 필터링하는 HAVING문을 이용해 해결할 수 있다

❇︎ 예제

① 가장 구매 금액이 많은 customer_id순으로 나열해라.

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

👉 결과

② customer_id별/staff_id별 구매 금액의 합계를 오름차순으로 정렬해라.

SELECT customer_id, staff_id, SUM(amount) 
FROM payment
GROUP BY staff_id, customer_id
ORDER BY customer_id;

👉 결과

이때 GROUP BY에서 열의 순서는 중요하지 않다. 하지만 보통 SELECT문과 순서를 맞춘다.

❇︎ DATE 함수

---

DATE 함수는 타임스탬프 정보 중 날짜 부분만 추출하는 함수이다.

③ 결제 금액이 많은 순으로 날짜를 정렬해라.

SELECT DATE(payment_date), SUM(amount) 
FROM payment
GROUP BY DATE(payment_date)
ORDER BY SUM(amount) DESC;

👉 결과

03. HAVING

---

HAVING절은 집계가 이미 수행된 결과를 필터링하는 역할로, GROUP BY 호출 뒤에 위치한다.

💡실행 순서

---

• WHERE문 필터링 → GROUP BY 실행 → 집계 함수 실행 → HAVING절을 통해 집계된 결과를 필터링

• 따라서 집계 함수 결과에 대해선 WHERE문으로 필터링 할 수 없기 떄문에 → 이때 HAVING절을 사용한다.

❇︎ 예제

① 총 판매액이 1000달러가 넘는 회사를 필터링하여라(단 'Google' 제외)

SELECT company, SUM(sales)
FROM finance_table
WHERE company != "Google"
GROUP BY company
HAVING SUM(sales) > 1000;

② 매장 총 판매건수가 300건 이상인 매장만 필터링하여라(단 store_id가 '2'인 매장 제외)

SELECT store_id, COUNT(*)
FROM customer
WHERE store_id NOT IN(2)
GROUP BY store_id
HAVING COUNT(*) > 300;

04. Advanced 문제

---

① staff중 처리한 결제건수가 많은 순으로 staff_id를 정렬해라.

SELECT staff_id, COUNT(*)
FROM payment
GROUP BY staff_id
ORDER BY COUNT(*) DESC;

👉 결과

② 영화 등급별 평균 교체 비용을 구하라.

SELECT rating, ROUND(AVG(replacement_cost), 2) 
FROM film
GROUP BY rating;

👉 결과

③ 총 구매금액을 기준으로, 상위 5명의 고객 id를 구하여라.

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
LIMIT 5;

👉 결과

④ 결제 건수가 40건 이상인 고객을 구하여라(오름차순)

SELECT customer_id, COUNT(*)
FROM payment
GROUP BY customer_id
HAVING COUNT(*) >= 40;

👉 결과

⑤ 직원id가 '2'인 직원과 거래한 총 금액이 100달러 초과인 고객 id를 구하여라

SELECT customer_id, SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY  customer_id
HAVING SUM(amount) > 100

👉 결과

⑥ 'J'로 시작하는 영화는 몇 편인지 구하여라.

SELECT COUNT(*)
FROM film
WHERE title LIKE 'J%';

👉 결과

⑦ 이름이 ‘E’로 시작하고 주소 ID가 500 미만인 고객 중, ID 번호가 가장 높은 고객은 누구인지 구하여라.

SELECT first_name, last_name 
FROM customer
WHERE first_name LIKE 'E%' 
AND address_id <500
ORDER BY customer_id DESC
LIMIT 1;

👉 결과