[DB/Postgres] 집계 함수(Aggregate Function) 이해 및 활용방법

해당 글에서는 데이터베이스 PostgreSQL을 기반으로 집계함수에 대해 알아봅니다.

1) 집계함수(Aggregate Function)

---

💡 집계함수

- 데이터베이스 내의 ‘데이터를 집계’하여 ‘통계 정보’를 제공하는 함수들을 의미합니다. 이러한 집계 함수를 사용하여 특정 열의 평균, 합계, 최대값, 최소값, 개수 등을 계산할 수 있습니다.

 
 
 

2) 집계함수의 구조 : GROUP BY ~ HAVING

---

💡 일반적인 집계함수를 사용하려면 GROUP BY 절을 통해서 데이터를 그룹화하여 집계 함수를 사용해야 합니다.

SELECT {column1}, {column2}, ..., {aggregate function(column)}
FROM {table}
GROUP BY {column1}, {column2}, ...
HAVING {condition}

SQL 절	설명
SELECT	출력할 열(column) 이름을 지정하거나 집계 함수를 이용하여 지정합니다.
FROM	조회할 테이블 이름을 지정합니다.
WHERE	데이터를 조회할 때 필요한 조건을 지정합니다.
GROUP BY	그룹화 할 열(column) 이름을 지정합니다.
HAVING	그룹화 된 결과 데이터에 대한 조건을 지정합니다.

 
 

3) 집계 함수의 종류 -1 : 기본

---

1. 집계 함수 요약

---

💡 기본적으로 사용되는 집계함수에 대해서 확인합니다.

집계 함수	설명	Syntax
COUNT	주어진 열의 행 수를 계산합니다.	COUNT([DISTINCT] column_name)
SUM	주어진 열의 합계를 계산합니다.	SUM([DISTINCT] column_name)
AVG	주어진 열의 평균값을 계산합니다.	AVG([DISTINCT] column_name)
MAX	주어진 열의 최대값을 계산합니다.	MAX(expression)
MIN	주어진 열의 최소값을 계산합니다.	MIN(expression)
ARRAY_AGG	주어진 열의 값을 배열로 반환합니다.	ARRAY_AGG(expression [ORDER BY [sort_expression {ASC
STRING_AGG	주어진 열의 값을 문자열로 반환합니다.	STRING_AGG ( expression, separator [order_by_clause] )

 

2. COUNT 함수

---

💡 COUNT 함수

- 테이블에서 ‘특정 열의 행 수’를 반환하는 함수입니다.

- 기본적으로 NULL 값을 포함하며, 중복된 값을 포함합니다.
- 이를 방지하려면 DISTINCT를 사용합니다.
- 예를 들어, 테이블의 특정 열의 값을 계산하거나 테이블의 전체 행 수를 계산하는 데 사용할 수 있습니다.

-- 구문1 : 테이블의 전체 행 수를 계산합니다. (NULL 값을 포함하여 중복된 값을 모두 포함합니다.)
SELECT COUNT(*) 
FROM TABLE1;

-- 구문2 : 이 구조는 특정 열에서 NULL이 아닌 값을 계산합니다.
SELECT COUNT(column_name) 
FROM TABLE1;

-- 구문3 :  이 구조는 특정 열에서 중복되지 않은 값을 계산합니다. NULL 값을 제외합니다.
SELECT COUNT(DISTINCT column_name) 
FROM TABLE1;

-- 구문4 : 이 구조는 특정 열에서 중복되지 않은 값을 계산합니다. NULL 값을 제외하고, 해당 열을 기준으로 그룹화합니다.
SELECT COUNT(DISTINCT column_name), column_name 
FROM TABLE1 
GROUP BY column_name;

-- 구문5 : 이 구조는 특정 열에서 중복되지 않은 값을 계산합니다. NULL 값을 제외하고, 해당 열을 기준으로 그룹화하고, 해당 열의 개수가 40보다 큰 경우 필터링합니다.
SELECT COUNT(DISTINCT column_name), column_name 
FROM TABLE1 
GROUP BY column_name
HAVING COUNT(colmn_name) > 40;

 

PostgreSQL COUNT Function: Counting Rows That Satisfy a Condition

 [ 더 알아보기 ]

💡 DISTINCT 함수

- 특정 컬럼에서 중복된 값을 제거하고 각각 고유한 값을 반환하는 역할을 합니다. 즉, 중복된 값을 제거하고 고유한 값을 추출해 내는 데 사용됩니다. 값 중에 NULL이 있으면 함께 제거하여 값으로 반환합니다.

 
 

3. SUM 함수

---

💡 SUM 함수

- 테이블에서 ‘특정 열의 값을 합산’하여 반환하는 함수입니다.

- DISTINCT 중복 제거 사용 가능

-- Syntax
SUM([DISTINCT] expression)

-- 구문 1 : orders 테이블에서 order_amount 열의 값을 모두 더한 값을 계산합니다.(중복값 포함)
SELECT SUM(order_amount) 
FROM orders;

-- 구문 2 : orders 테이블에서 order_amount 열의 값을 모두 더한 값을 계산합니다.(중복값 제외)
SELECT SUM(DISTINCT order_amount) 
FROM orders;

-- 구문 3 : payment 테이블에서 customer_id가 2000인 행들에서 amount 열의 합계를 계산합니다. (* COALESCE는 NULL의 값을 0으로 대체합니다)
SELECT COALESCE(SUM(amount),0) AS total
FROM payment
WHERE customer_id = 2000;

-- 구문 4 : orders 테이블에서 user_id별 order_amount 열의 합계를 계산하고, user_id 열로 그룹화합니다. 
SELECT user_id, SUM(order_amount) 
FROM orders 
GROUP BY user_id;

-- 구문 5 : orders 테이블에서 user_id별 order_amount 열의 합계를 계산하고, user_id 열로 그룹화하고 합계가 1000보다 큰 user_id에 대해 주문 금액을 출력합니다
SELECT user_id, SUM(order_amount) 
FROM orders 
GROUP BY user_id 
HAVING SUM(order_amount) > 1000;

 

PostgreSQL SUM Function

 
 

4. AVG 함수

---

💡 AVG 함수

- 테이블에서 ‘선택한 열의 모든 값의 합을 그 열의 행의 개수로 나눈 값을 반환’하는 함수입니다.
- DISTINCT 중복 제거 사용 가능

-- Syntax
AVG([DISTINCT] column)

-- 구문 1 : 이 쿼리는 payment 테이블의 amount 열의 평균 값을 계산합니다.
SELECT AVG(amount)
FROM payment;

-- 구문 2 : 이 쿼리는 payment 테이블의 amount 열의 평균 값을 계산하며, 중복 값을 제외합니다.
SELECT AVG(DISTINCT amount)
FROM payment;

-- 구문 3 : 이 쿼리는 payment 테이블의 amount 열의 평균 값을 계산하고, 출력을 소수점 이하 2자리까지, 소수점 이전 10자리까지의 최대 자릿수로 지정합니다.
SELECT AVG(amount)::numeric(10,2) 
FROM payment;

-- 구문 4 :  이 쿼리는 payment 테이블의 amount 열의 평균 값을 계산하며, 중복 값을 제외하고 amount 열을 기준으로 결과를 그룹화합니다.
SELECT AVG(DISTINCT amount), amount
FROM payment
GROUP BY amount;

-- 구문 5 : 그룹 조건절 : 이 쿼리는 payment 테이블의 amount 열의 평균 값을 계산하며, 중복 값을 제외하고 amount 열을 기준으로 결과를 그룹화합니다. 그 후, 평균 금액이 40보다 큰 행만 결과로 필터링합니다.
SELECT AVG(DISTINCT amount), amount
FROM payment
GROUP BY amount
HAVING AVG(amount) > 40;

 

[ 더 알아보기 ]

💡 평균을 내는 집계를 수행할 때 컬럼 값이 NULL인 경우 어떻게 처리되는가?

- NULL의 값은 무시되고 평균 값이 구해집니다.

PostgreSQL AVG function

 
 
 

5. MAX/MIN 함수

---

💡 MAX 함수

- 테이블의 ‘특정 열에서 가장 큰 값을 반환’하는 함수입니다.
- DISTINCT 중복 제거 사용 불가


💡 MIN 함수

- 테이블의 ‘특정 열에서 가장 작은 값을 반환’하는 함수입니다.
- DISTINCT 중복 제거 사용 불가

-- 기본 구문
MAX(expression);

-- 기본 구문
MIN(expression);

-- 구조 1 : payment 테이블의 amount 열에서 가장 큰 값을 찾기 위해 MAX 집계 함수를 사용합니다.
SELECT MAX(amount)
FROM payment;

-- 구조2 : payment 테이블의 amount 열에서 가장 큰 값을 찾기 위해 MAX 집계 함수를 사용합니다. 그리고 결과는 amount 열로 그룹화됩니다.
SELECT MAX(amount), amount
FROM payment
GROUP BY amount;

-- 구조3 : payment 테이블의 amount 열에서 가장 큰 값을 찾기 위해 MAX 집계 함수를 사용합니다. 그리고 결과는 amount 열로 그룹화되고 평균 amount가 40보다 큰 그룹만 필터링됩니다.
SELECT MAX(amount), amount
FROM payment
GROUP BY amount
HAVING AVG(amount) > 40;

 
 

6. ARRAY_AGG 함수

---

💡 ARRAY_AGG 함수

- 특정 컬럼의 그룹화된 값들을 배열 형태로 반환하는 함수입니다. 이 함수를 사용하면 여러 행을 하나의 배열로 묶어서 조회할 수 있습니다

ARRAY_AGG(expression [ORDER BY [sort_expression {ASC | DESC}], [...])

SELECT category, ARRAY_AGG(name) as items
FROM table_name
GROUP BY category;

| category | items                 |
|----------|----------------------|
| fruit    | {apple, banana}       |
| vegetable| {carrot, celery, onion}|

 
 

7. STRING_AGG 함수

---

💡 STRING_AGG 함수

- 여러 행의 값을 하나의 문자열로 연결해 주는 문자열 집계 함수입니다.
- 이 함수는 PostgreSQL 및 다른 SQL 데이터베이스에서 사용할 수 있습니다.

STRING_AGG ( expression, separator [order_by_clause] )

SELECT STRING_AGG(name, ', ') as all_names
FROM employees;

| all_names           |
|---------------------|
| John, Jane, Mike, Ed |

 
 
 

4) 집계함수 종류 - 더 알아보기

---

1. 함수 요약

---

집계 함수	설명
GREATEST	주어진 값 중 가장 큰 값을 반환합니다.
LEAST	주어진 값 중 가장 작은 값을 반환합니다.
COALESCE	NULL이 아닌 첫 번째 인수를 반환합니다.

 

2. GREATEST 함수

---

 💡 GREATEST 함수

- 주어진 값 중 가장 큰 값을 반환하는 함수입니다.

- 예를 들어, GREATEST(2, 4, 6)는 6을 반환합니다.

-- 테이블 구조
CREATE TABLE ranks (
	user_id INT PRIMARY KEY,
	rank_1 int4 NOT NULL,
	rank_2 int4 NOT NULL,
	rank_3 int4 NOT NULL
);

-- 테이블 데이터 생성
INSERT INTO ranks
VALUES
	(1, 6, 3, 5),
	(2, 2, 8, 5),
	(3, 5, 9, 8);

-- rank_1, rank_2, rank_3 중 가장 큰 값을 반환
SELECT
	user_id,
	GREATEST (rank_1, rank_2, rank_3) AS largest_rank
FROM
	ranks;

 

반환 결과

user_id	largest_rank
1	6
2	8
3	9

 
 
 

3. LEAST 함수

---

💡 LEAST 함수

- 주어진 값 중 가장 작은 값을 반환하는 함수입니다.

- 예를 들어, LEAST(2, 4, 6)는 2를 반환합니다.

-- 테이블 구조
CREATE TABLE ranks (
	user_id INT PRIMARY KEY,
	rank_1 int4 NOT NULL,
	rank_2 int4 NOT NULL,
	rank_3 int4 NOT NULL
);

-- 테이블 데이터 생성
INSERT INTO ranks
VALUES
	(1, 6, 3, 5),
	(2, 2, 8, 5),
	(3, 5, 9, 8);

-- rank_1, rank_2, rank_3 중 가장 작은 값을 반환
SELECT
	user_id,
	LEAST (rank_1, rank_2, rank_3) AS lowest_rank
FROM
	ranks;

수행결과

user_id	lowest_rank
1	3
2	2
3	5

 
 

4. COALESCE 함수

---

💡 COALESCE 함수

- SQL에서 둘 이상의 표현식 중에서 NULL 값을 반환하지 않는 첫 번째 표현식을 반환합니다. 만약 모든 표현식이 NULL 값을 반환한다면, COALESCE 함수는 NULL 값을 반환합니다.

# Syntax
COALESCE (argument_1, argument_2, …);

-- 1 반환
SELECT COALESCE (1, 2);

-- 2 반환
SELECT COALESCE (NULL, 2 , 1);

 

 

 

 

오늘도 감사합니다. 😀