[DataBase] 03. SQL(Structured Query Language)(2)

 

 

SQL(Structured Query Language)(2)

#Database/Concepts/Temp

(10/10) 굉장히 공들여서 작성한 글이여서 원래 동아리 스터디 용도로 일부 공개 했었는데, 더 많은 분들이 봤으면 하는 마음에 오픈!

게시글 길이 제한으로 글을 분리해서 업로드하는 점 양해 바랍니다

SQL에서의 집합 연산 (SET Operations in SQL)

위에서는 특정 릴레이션들에서 특정 조건을 만족하는 튜플, 또는 그 튜플 중 원하는 attribute를 선택하는(= 쿼리) 구문에 대해 배웠다.
여기서부터는 두 개 이상의 쿼리 결과를 조합해서 하나의 결과 집합을 만들어내는 집합 연산에 대해 알아본다. (쿼리 + 쿼리)

• SQL은 테이블 데이터에 대해 수행될 수 있는 집합 연산을 지원합니다.
  • 이는 테이블에 저장된 데이터로부터 특별한 조건 하에 의미 있는 결과를 얻기 위해 사용됩니다.
• 집합 연산의 종류는 다음과 같다.
  • UNION (합집합)
  • UNION ALL
  • INTERSECT (교집합)
  • INTERSECT ALL
  • EXCEPT (차집합)
  • EXCEPT ALL
• 집합 연산 UNION (합집합), INTERSECT (교집합), EXCEPT (차집합)
  • 위의 각 연산은 자동으로 중복을 제거합니다.
• 연산자 뒤에 ALL 키워드를 붙이면 모든 중복을 유지할 수 있다.
  • UNION ALL (모든 합집합)
  • INTERSECT ALL (모든 교집합)
  • EXCEPT ALL (모든 차집합)

Union 연산 (Union operation)

우리가 흔히 알고있는 합집합에 대응되는 연산이다.

• UNION은 두 개 이상의 SELECT 문 결과를 결합하는 데 사용된다.
• 그러나 결과 집합에서 중복 행을 제거한다.
• UNION을 적용하는 테이블의 열 수(attribute의 수)와 데이터(도메인) 유형은 동일해야 한다.

 

UNION ALL 연산 (UNION ALL operation)

• 이 연산은 UNION과 거의 같다.
• 한 가지 차이점은 중복 행도 표시한다는 것. (중복을 제거하지 않는다)

 

UNION에서 중복을 포함하는 ALL 을 다뤘으므로 앞으로 나올 Intersect, Except 연산에서는 따로 다루지 않고 넘어간다.

Intersect 연산 (Intersect operation)

우리가 흔히 알고있는 교집합에 해당하는 연산이다

• Intersect 연산은 두 SELECT 문을 결합하는 데 사용되며, 두 SELECT 문에서 공통된 레코드만 반환합니다.
• Intersect의 경우 열 수(attribute 개수)와 데이터(도메인) 유형이 동일해야 합니다.

 

Except 연산 (Except operation)

• Except 연산은 두 SELECT 문 결과를 결합하여 첫 번째 집합에 속하는 결과만 반환합니다.

 

집합 연산의 예 (Example of Set Operations)

• 2017년 가을 또는 2018년 봄에 열린 강좌 찾기

• 2017년 가을과 2018년 봄에 열린 강좌 찾기

• 2017년 가을에 열렸지만 2018년 봄에는 열리지 않은 강좌 찾기

NULL 값 (Null Values)

데이터베이스에서 NULL 값은 데이터베이스 내에서 특정 attribute(column)에 값이 없거나 알 수 없음을 나타낸다.

• 튜플의 일부 attribute는 null 값을 가질 수 있다.
• null은 알 수 없는 값 또는 값이 존재하지 않음을 나타냅니다.
• null을 포함하는 모든 산술 표현의 결과는 null입니다.
  • 예) 5 + null의 결과는 null입니다.
  • 주의! NULL = NULL의 결과는 TRUE가 아니라 NULL이다.
    • 마찬가지로 NULL > 5 과 같은 비교 연산의 결과 또한 NULL이다.
• is null 조건자는 null 값을 확인하는 데 사용할 수 있습니다.
  • 예) 급여가 null인 모든 강사를 찾습니다.
  • 이런식으로 is null 조건자는 값이 아직 지정되지 않은 레코드를 찾는데 유용하게 사용된다.
• 반대로 null 값이 아님을 확인하기 위해 is not null 조건자를 사용할 수 있다.

예시

select name
from instructor
where salary is null

집계 함수 (Aggregate Functions)

지금까지 배운 내용으로 한 번 교수님들의 평균 연봉을 구하고 싶어서 쿼리를 작성한다고 해보자.

select salary
from instructor
where salary is not null

여기까지 작성하면 다음과 같은 고민이 들 것이다.
“여러 행의 연봉 Attirbute 데이터를 가져왔는데, 평균은 어떻게 구해야 할 까?”

이러한 고민을 해결해줄 수 있는 함수가 sql에 존재한다. Aggregate Function(집계 함수) 이다.

집계 합수는 여러 행의 데이터를 한 번에 처리하여 요약된 결과를 도출하는 함수이다.
주로 테이블, 쿼리 결과에서 특정 열(Attribute)의 값을 요약하거나 집계할 때 사용한다.
위에서 예로 든 평균을 구하는 경우 외에도 최소, 최대, 총 합, 값의 수를 구하는 데에도 사용할 수 있다.

• avg: 평균 값
• min: 최소 값
• max: 최대 값
• sum: 값의 합
• count: 값의 수
• 이러한 함수들은 릴레이션의 열 값의 다중 집합(multiset)에서 작동하며 값을 반환합니다.
  • 다중 집합 —> C++에서 multiset과 유사한 개념이라고 생각하면 된다.
    • 중복된 요소를 허용하는 집합이다.
  • 위 말을 쉽게 풀어서 이야기 하자면, 쿼리로 얻은 특정 열(Attribute) 데이터에서 중복된 값이 있더라도 집계 함수가 정상적으로 동작한다는 말이다.

예시: 컴퓨터공학과 강사의 평균 급여 찾기

select avg (salary)
from instructor
where dept_name = 'Comp. Sci.';

예시: 컴퓨터공학과 모든 강사의 총 급여의 합 구하기

select sum (salary)
from instructor
where dept_name = 'Comp. Sci.';

예시: 컴퓨터공학과 강사 급여 중 최소 급여 구하기

select min (salary)
from instructor
where dept_name = 'Comp. Sci.';

예시: 2018년 봄 학기에 강의를 진행한 강사의 총 수 찾기

select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;

예시: course 릴레이션(테이블)의 튜플(레코드) 수 찾기

select count (*)
from course;

그룹화 (Group By)

집계 함수는 보통 Group By 절과 함께 사용된다. Group By는 특정 Attribute를 기준으로 데이터를 그룹화하여 각 그룹에 대해 집계 결과를 계산한다. 말이 어려우니 아래 예시를 통해 확인하자

예시: 각 학과의 강사 평균 급여를 찾기
한 학교의 모든 강사의 평균 급여를 구해봤지만, 학과별 교수 평균 급여를 구하고 싶다고 해보자. 이 때 교수를 “학과”를 기준으로 묶어서 그룹 내 급여 평균을 구한 뒤 하나의 레코드로 표현하면 되지 않을까?
—> Group By를 사용한다.

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;

• Group By는 특정 Attribute를 기준으로 데이터를 그룹화한다고 했다.
• 위 예시에서 각 학과를 기준으로 하나의 레코드로 묶인 것을 확인할 수 있다.

참고) as 키워드가 등장하는데, as는 테이블 또는 열(Attribute)에 임시 이름을 부여하는 데 사용된다. 위 예시에서는 학과 기준으로 그룹화여 교수 개개인의 연봉이 아닌 학과 평균 연봉을 저장하므로 salary 대신 avg_salary라는 이름을 사용하는 것이 적합하다. 따라서 as 키워드를 사용하였다.

주의) Aggregate function의 대상이 되는 attribute를 제외한 나머지 select 절에 있는 attribute는 group by 목록에 반드시 나타나야 한다.

오류가 있는 예시

/* 오류가 있는 쿼리 */
select dept_name, ID, avg (salary)
from instructor
group by dept_name;

학과별 교수 연봉 평균을 구하기 위해서 학과를 기준으로 그룹화를 했는데, 교수 ID를 선택하는 쿼리는 논리적으로도 문제가 있는 쿼리이다. dept_name으로 그룹화하였기 때문에 select 절에는 [집계 함수의 대상이 되는 attribute(salary), group_by의 대상이 되는 attribute(dept_name)]를 제외하고 다른 attribute가 올 수 없는 것이다.

Aggregate Functions – Having 절

HAVING 절은 GROUP BY로 그룹화된 데이터에 대해 조건을 적용할 때 사용된다. 이 절은 WHERE 절과 유사하지만, GROUP BY 결과에 필터를 적용한다는 점에서 차이가 있다.

• HAVING 절은 결과에 표시될 그룹을 필터링하는 조건을 지정할 수 있습니다.

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

주의: HAVING 절의 조건자는 그룹 형성 후 적용되며, WHERE 절의 조건자는 그룹 형성 전에 적용된다.

WHERE 절을 통해서 조건을 만족하지 않는 레코드를 미리 제외시키고 그룹 형성을 해야 연산 횟수가 줄어 효율적이기 때문이다.

참고) SQL 쿼리의 실행 순서

1. FROM 절
2. WHERE 절
3. GROUP BY 절
4. 집계 함수
5. HAVING 절
6. SELECT 절
7. ORDERED BY 절
8. LIMIT/OFFSET(Optional)

NULL 값과 Aggregate

만약 집계 함수가 계산하려는 데이터 중 NULL 값이 존재하면 어떻게 동작할까?
아래 예시를 통해서 확인해보자

예시: 모든 급여 합산

select sum (salary)
from instructor

• 위 문장은 null 금액을 무시합니다.
• non-null 금액이 없으면(모두 null 값이면) 결과는 null입니다.

Aggregate 함수 파트 도입 부문에 적었던 sql을 가져와보겠다.

select salary
from instructor
where salary is not null

where salary is not null 라고 적었던 걸 볼 수 있는데, 여기서 집계 함수를 사용하면 is not null은 필요 없게 된다.

주의) count(*)를 제외한 모든 집계 연산은 집계 속성의 null 값을 무시합니다.

즉 count만 null을 고려한다고 보면 된다.

select count (*)
from course;

• 이 쿼리는 NULL 값을 포함하여 결과 집합에 반환된 모든 레코드의 총 수를 반환한다.