[ 살펴보기 ] PostgreSQL - 집계 함수

집계함수를 통해 동일한 데이터를 가진 특정 column의 개수, 평균, 최대, 최소값 등을 조회할 수 있다. 예를 들어 다음과 같은 테이블과 데이터가 있다고 가정해보자.

/* 테이블 이름 */
employees

/* 테이블 데이터 */
id    name        department        salary
1    Jake        HR                50000
2    Karl        Engineering        70000
3    Charlie        HR                55000
4    David        Engineering        80000
5    Eve            Sales            60000

Count

Count function을 통해 Table의 전체 데이터 수, 혹은 특정 조건에 부합하는 데이터의 갯수를 조회한다. Table의 전체 데이터 수를 조회하고 싶으면 다음과 같이 조회할 수 있다.

SELECT COUNT(*) AS count FROM employees;

그리고 다음과 같이 특정 조건에 부합하는 데이터의 갯수를 조회할 수도 있다.

SELECT COUNT(*) AS count_hr FROM employees
WHERE department = 'HR';

SUM

Sum function을 통해 table 특정 column의 총 합을 구할 수 있다. 다음은 employee salary의 총 합을 구하는 예제다.

SELECT SUM(salary) AS sum_salary
FROM employees;

혹은 다음과 같이 특정 부서의 salary 총 합을 구할 수도 있다.

SELECT SUM(salary) AS sum_salary_hr
FROM employees
WHERE department = 'HR';

AVG

AVG function을 통해 table의 특정 column을 기준으로 평균 값을 산출할 수 있다.

다음은 employee 전체 salary의 평균 값을 산출하는 예제다.

SELECT AVG(salary) AS ave_salary
FROM employees;

혹은 다음과 같이 특정한 부서를 기준으로 평균 salary를 산출할 수도 있다.

SELECT AVG(salary) AS ave_salary
FROM employees
WHERE department = 'HR';

MIN, MAX

MIN, MAX function을 통해 table 데이터의 최소값과 최대값을 조회할 수 있다.

다음은 employees table에서 최대 salary와 최소 salary를 조회하는 예제다.

SELECT MIN(salary) AS min, MAX(salary) as max 
FROM employees;

혹은 특정 부서를 기준으로 최대 값과 최소 값을 구하고 싶으면 다음과 같이 조회할 수 있다.

SELECT MIN(salary) AS min,
       MAX(salary) AS max
FROM employees
WHERE department = 'HR';

BOOL_AND, BOOL_OR

특정 column의 데이터가 모두 true인지 혹은 일부 데이터가 true인지 조회하고 싶을 때 BOOL_AND와 BOOL_OR를 사용할 수 있다.

만약 employees table에 특정 직원이 승진 대상인지 여부를 나타내는 promoted column이 존재 한다면 다음 예제는 모든 promote column의 데이터가 true일 때 조회 결과로 true를 반환한다.

SELECT BOOL_AND(promote) AS all_promote,
FROM employees;

혹은 promote 대상인 직원이 있는지 조회를 하고자 한다면 BOOL_OR을 통해 조회할 수 있다. 다음 promote column 데이터 중 하나라도 true인 데이터가 있다면 true를 반환한다.

SELECT BOOL_OR(promote) AS all_promote,
FROM employees;

GROUP BY

데이터를 특정 group으로 나누어 집계 함수를 적용하고 싶을 때 GROUP BY를 사용할 수 있다. 예를 들어 아래의 예제는 table의 데이터를 department별로 group화 한다.

SELECT department FROM employees GROUP BY department;

 department
-------------
 Engineering
 Sales
 HR

위와 같이 group by를 통해 group화된 데이터에 집계 함수를 적용하여 필요한 데이터를 group별로 조회할 수 있다. 다음은 각 department에 소속된 인원을 조회하는 예제다.

SELECT department, COUNT(*) FROM employees 
GROUP BY department;

 department  | count
-------------+-------
 Engineering |     2
 Sales       |     1
 HR          |     2

또는 다음과 같이 부서별 최대 salary와 최소 salary를 조회할 수 있다.

SELECT department,
       MIN(salary) AS min,
       MAX(salary) AS max
FROM employees
GROUP BY department;

 department  |  min  |  max
-------------+-------+-------
 Engineering | 70000 | 80000
 Sales       | 60000 | 60000
 HR          | 50000 | 55000

다음과 같이 ARRAY_AGG function을 통해 부서별 소속된 직원의 이름을 함께 조회할 수도 있다. ARRAY_AGG function은 여러 row의 데이터를 postgresql array로 표현한다.

SELECT department, ARRAY_AGG(name) AS employees
FROM employees
GROUP BY department;

 department  |    employees
-------------+-----------------
 Engineering | {Karl,David}
 Sales       | {Eve}
 HR          | {Jake,Charlie}

집계 결과에 필터 적용

위의 예제와 같이 group화된 데이터 결과에 추가 필터를 적용할 때는 having을 통해 적용할 수 있다. 다음은 count 집계 함수를 통해 department count를 집계한 결과 중 count 결과가 2인 data만 필터한다.

SELECT department, COUNT(*) FROM employees 
GROUP BY department
HAVING COUNT(*) = 2;

 department  | count
-------------+-------
 Engineering |     2
 HR          |     2

혹은 emplyees table에 지출 내역을 관리하는 expenditure column이 존재한다면 다음 예제와 같이 부서별 평균 지출이 특정 지출 금액 이상인 부서를 filter할 수 있다.

SELECT department, AVG(expenditure) FROM employees 
GROUP BY department
HAVING AVG(expenditure) > 100000;