[SQL / postgreSQL] SQL 기초 (집계함수, GROUP BY, HAVING)

집계함수란(aggregate function)?

여러 조건을 입력하여 하나의 결과를 반환하는 것 이다.  집계함수는SELECT나 HAVING절에서만 호출될 수 있다.

 

집계함수의 대표적인 예로

 

• AVG() -평균 값 출력

부동 소수점 값을 반환하기때문에  ROUND함수를 이용해 소숫점 자리를 조정할 수 있다.

SELECT ROUND(AVG(열 이름),2) FROM 테이블 명;

• COUNT() - 행의 개수 count
• MAX()
• MIN()
• SUM()

 

이 있다.

 

이밖에도 postgresSQL에는 많은 집계 함수가 있는데 아래의 링크를 참고하면 된다.

https://www.postgresql.org/docs/current/functions-aggregate.html 

 

 

집계 함수를 사용할 때 주의해야 할 점은, 집계함수는 하나의 열에 대해서만 집계 함수를 호출할 수 있기 때문에, 집계함수로 반환되는 값과, 다양한 행이 존재하는 열은 같이 호출될 수 없다.   

 

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

이렇게 되면 round(~~)값은 하나의 열만 반환되는데 film_id는 모든 열이 반환된다 (논리적으로 맞지 않는다.)

 

예시를 들면 아래와 같다.

 

ROUND(AVG)

2

film_id

1

2

3

4

 

이 두개의 열이 한번에 반환되어야하는데 sql에서는 일어날 수 없는 일이다.

 

SELECT ROUND(AVG(replacement_cost),2), MIN(film_id) FROM film

대신 MIN, MAX, COUNT 같은 다른 집계 함수를 같이 이용하는것은 가능하다.

위 명령을 출력 하면 아래와 같다.

ROUND(AVG)	MIN
2	1

 

집계 함수는 하나의 열에대해 계산을 수행하기 때문에, 항목(카테고리) 별 값을 확인하기 위해선 GROUP BY 함수를 써야 한다.

 

 

---

GROUP BY

 

SELECT 카테고리 열, 집계함수(데이터 열)
FROM 테이블 명
GROUP BY 카테고리 열;

 

일정 카테고리 별 열을 집계하는 GROUP BY는 카테고리 열을 선택 후 카테고리별로 분류를 하여 그에 맞게 데이터 값을 나눠준다. 이후 집계함수는 여러 값을 처리하여 단 하나의 값으로 줄인다.

GROUP BY절은 FROM문 또는 WHERE문 바로 뒤에 와야 한다.

 

 

 

GROUP BY의 과정을 보면 다음과 같다.

 

SELECT 날짜, SUM(매출액)
FROM 매출테이블
GROUP BY 날짜;

 

날짜	매출액
2022-02-16	3000
2022-02-16	4000
2022-02-17	1000
2022-02-17	3000
2022-02-18	2000
2022-02-18	1000

이 테이블의 날짜를 GROUP BY 하면

날짜	매출액
2022-02-16	3000
2022-02-16	4000

날짜	매출액
2022-02-17	1000
2022-02-17	3000

날짜	매출액
2022-02-18	2000
2022-02-18	1000

으로 나눠진 후 최종적으로

 

날짜	SUM(매출액)
2022-02-16	7000
2022-02-17	4000
2022-02-18	3000

이 되어 카테고리별로 집계를 할 수 있다.

 

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

+) GROUP BY나 ORDER BY의 1,2표시는 1은 customer_id, 2는 staff_id를 말하며 숫자로 편리하게 열을 표시할 수 있다.

 

 

 

---

HAVING

SELECT 열1, 집계함수(열) FROM film
WHERE 열1에대한 조건 식
GROUP BY 열1
HAVING 집계함수에 대한 조건식

집계함수의 조건절은 WHERE이 아닌 HAVING에 써야 한다.

HAVING의 위치는 집계가 이미 수행된 이후에 자료를 필터링 하기 떄문에 GROUP BY 뒤에 와야한다.

 

 

 

 

---

 

평가 시험1) ID가 2인 직원에게서 최소 110달러를 쓴 고객의 ID를 찾으시오

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

 

평가 시험2) J로 시작하는 영화는 몇 편 입니까?

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

 

평가 시험3) 이름이 'E'로 시작하는 동시에 주소 ID가 500 미만인 고객 중, ID 번호가 가장 높은 고객은 누구입니까?

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