상위 문서: SQL
개요
(집계 함수)'Aggregate function은 set 또는 multiset[1] 입력으로 받아서, 하나의 값을 반환하는 함수이다. SQL은 다음과 같은 5가지 표준 내장 집계 함수로 평균(avg), 최솟값(min), 최댓값(max), 합계(sum), 개수(count)를 제공한다. 이때 sum과 avg의 입력값은 숫자들의 집합이어야 하지만, 나머지 연산자들(min, max, count)은 문자열과 같이 숫자가 아닌 데이터 타입에도 사용할 수 있다.
Basic Aggregation
“컴퓨터 과학과(Computer Science department)에 속한 교수들의 평균 급여를 구하라.”라는 질의는 아래와 같이 작성된다. 이때 평균 급여에 avg_salary와 같이 이름을 부여한다.
select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp. Sci.';
위 예제에서는 중복값을 유지하여 평균값을 계산한다. 중복값이 제거된다면 잘못된 값이 계산되기 때문이다. 하지만 중복값이 제거되어야 올바른 값을 구할 수 있는 경우도 존재한다. 이 경우, 집계 함수에 distinct 키워드를 작성한다. 예를 들어, 어떤 교수가 어떤 수업을 했는지 나타내는 relation이 teaches라고 할때, “2018년 봄 학기에 과목을 가르친 교수들의 총 수를 구하라.”와 같은 질의는 아래와 같이 나타내진다.
select count(distinct ID) --distinct 사용!
from teaches
where semester = 'Spring' and year = 2018;
집계 함수 count를 사용하여 relation의 튜플 수를 계산하고 싶을 때에는 아래와 같은 문법을 사용한다.
select count(*) from course; --(*)기호를 이용해 모든 속성을 선택한다.
이때, SQL에서는 count(*)와 함께 distinct를 사용할 수 없다. 또한, max와 min에서는 distinct를 사용하는 것이 허용되지만, 결과는 변하지 않는다.
Aggregation with Grouping
경우에 따라 우리는 집계 함수를 하나의 튜플 집합 전체에만 적용하는 것이 아니라, 튜플 집합을 여러 그룹으로 나누어서 각각의 그룹에 대해 적용하고자 할 때가 있다. SQL에서는 이를 group by절을 사용하여 지정하며, 지정된 하나 이상의 속성(attribute)은 그룹을 형성하는 기준이 된다. 즉, group by 절의 모든 속성에 대해 같은 값을 가지는 튜플들은 하나의 그룹으로 묶인다. 예를 들어, 각 학과의 평균 급여를 구하라.”라는 질의는 다음과 같이 작성된다.
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;
Figure 1은 instructor 릴레이션에 있는 튜플들을 dept_name 속성에 따라 그룹으로 묶은 것을 보여준다. 그 다음에는, 지정된 집계 함수(avg)가 각 그룹에 대해 계산된다. 그 계산 결과는 figure 2와 같다.
집계 함수를 사용할 때에는 다음과 같은 주의해야할 사항이 있다. select 절에서 사용된 속성 중 집계 함수의 인자로 사용되지 않은 속성은 반드시 group by 절에 사용되어야 한다. group by 절이 있으면, SQL은 각 그룹별로 하나의 결과 튜플만 출력해야 한다. 그런데 select 절에서 집계 함수 없이 속성을 사용하면, 해당 그룹 안의 여러 튜플 중 어떤 값을 출력해야 할지 알 수 없기 때문에 오류가 생긴다. 예를 들어 아래와 같은 질의는 오류에 해당한다.
/* erroneous query */
select dept_name, ID, avg(salary) --오류!
from instructor
group by dept_name;
해당 질의에서는 dept_name에 따라 그룹이 형성되었지만, ID는 group by 절에 포함되지 않았고, 또한 집계 함수로 묶이지도 않았다. 그러므로, 한 그룹(같은 dept_name 값 안)에 여러 명의 교수(ID)가 있을 수 있는데, 그 중 어떤 ID를 출력해야 할지 알 수 없기 때문에, SQL은 이런 질의를 허용하지 않는다.
The Having Clause
때로는 개별 튜플이 아니라, 그룹 전체에 적용되는 조건을 명시하고자 할 수 있으며, 이를 위해 SQL은 having 절을 지원한다. Where 절이 그룹을 만들기 전, 모든 튜플에 대해 조건을 바탕으로 필터링을 하는 것과는 달리, having 절은 group by로 묶인 그룹 각각에 대해 조건을 바탕으로 필터링을 한다는 점에서 where 절과 구분된다. 예를 들어, "교수 들의 평균 급여가 $42,000을 초과하는 학과를 찾기"라는 질의는 아래와 같이 작성된다.
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
select 절의 경우와 마찬가지로, having 절에서 사용되는 속성 중 집계 함수의 인자로 사용되지 않는 속성은 반드시 group by 절에 있어야 하며, 그렇지 않으면 그 질의는 오류가 된다. group by 절이 있으면, SQL은 각 그룹별로 하나의 결과 튜플만 출력해야 한다. 그런데 having에서 집계 함수 없이 속성을 사용하면, 해당 그룹 안의 여러 튜플 중 어떤 값을 써야 할지 정할 수 없기 때문에 오류가 생긴다. 예를 들어 아래와 같은 질의는 오류에 해당한다.
/* erroneous query */
select dept_name, avg(salary)
from instructor
group by dept_name
having ID = '10101'; --오류!
aggregation, group by, having 절을 포함하는 질의의 의미는 다음과 같은 연산 순서로 정의된다:
- aggregation이 없는 질의와 마찬가지로, 우선 from 절이 평가되어 하나의 릴레이션이 생성된다.
- where 절이 존재하는 경우, 그 안의 조건(predicate)이 from 절의 결과 릴레이션에 적용된다.
- where 조건을 만족하는 튜플들이 group by 절이 있으면 그에 따라 그룹으로 나뉘게 된다.
- group by 절이 없다면, 조건을 만족하는 모든 튜플이 하나의 그룹으로 간주된다.
- having 절이 존재하면, 이 절은 각 그룹에 대해 적용되며, having 조건을 만족하지 않는 그룹은 제거된다.
- select 절은 남아 있는 그룹들을 사용하여 결과 튜플들을 생성하며, 집계 함수는 각 그룹마다 하나의 결과 튜플을 얻기 위해 적용된다.
where 절과 having 절을 동시에 사용하는 예시로, “2017년에 개설된 각 과목 섹션에 대해, 그 섹션에 등록된 학생들의 평균 총 이수 학점(tot_cred)을 구하되, 해당 섹션에 최소 두 명의 학생이 등록된 경우만 포함하라.”과 같은 질의는 아래와 같이 나타내어진다.
select course_id, semester, year, sec_id, avg(tot_cred)
from student, takes
where student.ID = takes.ID and year = 2017
group by course_id, semester, year, sec_id
having count(ID) >= 2;
각주
- ↑ 중복 원소를 허용하는 집합