SQL 집계 함수
SQL 집계 함수 (SQL aggregate functions)
집계 함수는 GROUP BY 절이 정의한 부분 집합에 대해 동작해요. GROUP BY 절이 없으면 집계 함수는 결과 집합의 모든 요소에 대해 동작해요. 집계 함수는 GROUP BY, SELECT, HAVING 절에서 사용할 수 있어요.
출처: 문서
본문
OpenSearch는 다음 집계 함수를 지원해요.
| Function | Description |
|---|---|
AVG |
결과의 평균을 반환해요. |
COUNT |
결과의 개수를 반환해요. |
SUM |
결과의 합계를 반환해요. |
MIN |
결과의 최솟값을 반환해요. |
MAX |
결과의 최댓값을 반환해요. |
VAR_POP 또는 VARIANCE |
null을 버린 후 결과의 모집단 분산(population variance)을 반환해요. 결과 행이 하나뿐이면 0을 반환해요. |
VAR_SAMP |
null을 버린 후 결과의 표본 분산(sample variance)을 반환해요. 결과 행이 하나뿐이면 null을 반환해요. |
STD 또는 STDDEV |
결과의 표본 표준편차를 반환해요. 결과 행이 하나뿐이면 0을 반환해요. |
STDDEV_POP |
결과의 모집단 표준편차를 반환해요. 결과 행이 하나뿐이면 0을 반환해요. |
STDDEV_SAMP |
결과의 표본 표준편차를 반환해요. 결과 행이 하나뿐이면 null을 반환해요. |
다음 예시는 employees 테이블을 참조해요. bulk 인덱스 연산을 사용해 다음 문서들을 OpenSearch에 인덱싱하면 예시를 직접 실행해 볼 수 있어요.
PUT employees/_bulk?refresh
{"index":{"_id":"1"}}
{"employee_id": 1, "department":1, "firstname":"Amber", "lastname":"Duke", "sales":1356, "sale_date":"2020-01-23"}
{"index":{"_id":"2"}}
{"employee_id": 1, "department":1, "firstname":"Amber", "lastname":"Duke", "sales":39224, "sale_date":"2021-01-06"}
{"index":{"_id":"6"}}
{"employee_id":6, "department":1, "firstname":"Hattie", "lastname":"Bond", "sales":5686, "sale_date":"2021-06-07"}
{"index":{"_id":"7"}}
{"employee_id":6, "department":1, "firstname":"Hattie", "lastname":"Bond", "sales":12432, "sale_date":"2022-05-18"}
{"index":{"_id":"13"}}
{"employee_id":13,"department":2, "firstname":"Nanette", "lastname":"Bates", "sales":32838, "sale_date":"2022-04-11"}
{"index":{"_id":"18"}}
{"employee_id":18,"department":2, "firstname":"Dale", "lastname":"Adams", "sales":4180, "sale_date":"2022-11-05"}
GROUP BY
GROUP BY 절은 결과 집합의 부분 집합을 정의해요. 집계 함수는 이 부분 집합에 대해 동작하며 각 부분 집합마다 하나의 결과 행을 반환해요.
GROUP BY 절에는 식별자(identifier), 서수(ordinal), 또는 표현식을 사용할 수 있어요.
GROUP BY에서 식별자 사용하기
집계할 필드 이름(열 이름)을 GROUP BY 절에 지정할 수 있어요. 예를 들어 다음 질의는 부서 번호와 각 부서의 총 판매액을 반환해요.
SELECT department, sum(sales)
FROM employees
GROUP BY department;
| department | sum(sales) |
|---|---|
| 1 | 58700 |
| 2 | 37018 |
GROUP BY에서 서수 사용하기
집계할 열 번호를 GROUP BY 절에 지정할 수 있어요. 열 번호는 SELECT 절에서 열 위치에 의해 결정돼요. 예를 들어 다음 질의는 앞의 질의와 동등해요. 부서 번호와 각 부서의 총 판매액을 반환하며, 결과 집합의 첫 번째 열(즉 department) 기준으로 그룹화해요.
SELECT department, sum(sales)
FROM employees
GROUP BY 1;
| department | sum(sales) |
|---|---|
| 1 | 58700 |
| 2 | 37018 |
GROUP BY에서 표현식 사용하기
GROUP BY 절에 표현식을 사용할 수 있어요. 예를 들어 다음 질의는 연도별 평균 판매액을 반환해요.
SELECT year(sale_date), avg(sales)
FROM employees
GROUP BY year(sale_date);
| year(start_date) | avg(sales) |
|---|---|
| 2020 | 1356.0 |
| 2021 | 22455.0 |
| 2022 | 16484.0 |
SELECT
SELECT 절에서 집계 표현식을 직접 또는 더 큰 표현식의 일부로 사용할 수 있어요. 또한 집계 함수의 인자로 표현식을 사용할 수도 있어요.
SELECT에서 집계 표현식을 직접 사용하기
다음 질의는 각 부서의 평균 판매액을 반환해요.
SELECT department, avg(sales)
FROM employees
GROUP BY department;
| department | avg(sales) |
|---|---|
| 1 | 14675.0 |
| 2 | 18509.0 |
SELECT에서 더 큰 표현식의 일부로 집계 표현식 사용하기
다음 질의는 평균 판매액의 5%로 각 부서 직원들의 평균 커미션을 계산해요.
SELECT department, avg(sales) * 0.05 as avg_commission
FROM employees
GROUP BY department;
| department | avg_commission |
|---|---|
| 1 | 733.75 |
| 2 | 925.45 |
집계 함수의 인자로 표현식 사용하기
다음 질의는 각 부서의 평균 커미션 금액을 계산해요. 먼저 각 sales 값의 커미션 금액을 sales의 5%로 계산하고, 그다음 모든 커미션 값의 평균을 구해요.
SELECT department, avg(sales * 0.05) as avg_commission
FROM employees
GROUP BY department;
| department | avg_commission |
|---|---|
| 1 | 733.75 |
| 2 | 925.45 |
COUNT
COUNT 함수는 * 같은 인자나 1 같은 리터럴을 받아요. 다음 표는 다양한 형태의 COUNT 함수가 어떻게 동작하는지 설명해요.
| Function type | Description |
|---|---|
COUNT(field) |
주어진 필드(또는 표현식)의 값이 null이 아닌 행의 수를 셉니다. |
COUNT(*) |
테이블의 총 행 수를 셉니다. |
COUNT(1) (COUNT(*)와 동일) |
null이 아닌 리터럴을 셉니다. |
예를 들어 다음 질의는 연도별 판매 횟수를 반환해요.
SELECT year(sale_date), count(sales)
FROM employees
GROUP BY year(sale_date);
| year(sale_date) | count(sales) |
|---|---|
| 2020 | 1 |
| 2021 | 2 |
| 2022 | 3 |
HAVING
WHERE와 HAVING은 모두 결과를 필터링하는 데 사용돼요. WHERE 필터는 GROUP BY 단계 전에 적용되므로 WHERE 절에서는 집계 함수를 사용할 수 없어요. 하지만 WHERE 절을 사용해 이후 집계가 적용되는 행의 수를 제한할 수는 있어요.
HAVING 필터는 GROUP BY 단계 후에 적용되므로 HAVING 절을 사용해 결과에 포함되는 그룹을 제한할 수 있어요.
GROUP BY와 함께 사용하는 HAVING
HAVING 조건에서 집계 표현식이나 SELECT 절에서 정의한 별칭(alias)을 사용할 수 있어요.
다음 질의는 HAVING 절에서 집계 표현식을 사용해요. 판매를 한 번 이상 한 각 직원의 판매 횟수를 반환해요.
SELECT employee_id, count(sales)
FROM employees
GROUP BY employee_id
HAVING count(sales) > 1;
| employee_id | count(sales) |
|---|---|
| 1 | 2 |
| 6 | 2 |
HAVING 절의 집계는 SELECT 목록의 집계와 같을 필요는 없어요. 다음 질의는 HAVING 절에서 count 함수를, SELECT 절에서 sum 함수를 사용해요. 판매를 한 번 이상 한 각 직원의 총 판매 금액을 반환해요.
SELECT employee_id, sum(sales)
FROM employees
GROUP BY employee_id
HAVING count(sales) > 1;
| employee_id | sum (sales) |
|---|---|
| 1 | 40580 |
| 6 | 18120 |
SQL 표준의 확장으로, GROUP BY 절에서 식별자만 사용해야 하는 제한은 없어요. 다음 질의는 GROUP BY 절에서 별칭을 사용하며 앞의 질의와 동등해요.
SELECT employee_id as id, sum(sales)
FROM employees
GROUP BY id
HAVING count(sales) > 1;
| id | sum (sales) |
|---|---|
| 1 | 40580 |
| 6 | 18120 |
HAVING 절에서 집계 표현식의 별칭을 사용할 수도 있어요. 다음 질의는 판매액이 $40,000를 초과하는 각 부서의 총 판매액을 반환해요.
SELECT department, sum(sales) as total
FROM employees
GROUP BY department
HAVING total > 40000;
| department | total |
|---|---|
| 1 | 58700 |
식별자가 모호한 경우(예: SELECT 별칭과 인덱스 필드로 동시에 존재하는 경우)에는 별칭이 우선해요. 다음 질의에서 식별자는 SELECT 절에서 별칭이 붙은 표현식으로 대체돼요.
SELECT department, sum(sales) as sales
FROM employees
GROUP BY department
HAVING sales > 40000;
| department | sales |
|---|---|
| 1 | 58700 |
GROUP BY 없이 사용하는 HAVING
GROUP BY 절 없이 HAVING 절을 사용할 수 있어요. 이 경우 전체 데이터 집합이 하나의 그룹으로 간주돼요. 다음 질의는 department 열에 값이 두 개 이상 있으면 True를 반환해요.
SELECT 'True' as more_than_one_department FROM employees HAVING min(department) < max(department);
| more_than_one_department |
|---|
| True |
employee 테이블의 모든 직원이 같은 부서에 속했다면 결과에는 행이 0개 포함될 거예요.
| more_than_one_department |
|---|