집합 연산·뷰·윈도 함수
집합 연산은 동일한 열 구조의 조회 결과를 행 방향으로 결합한다. 일반 뷰는 조회 정의를 저장하며 갱신과 보안에는 별도의 조건이 따른다. 윈도 함수는 개별 행을 유지하면서 PARTITION BY·ORDER BY·프레임을 기준으로 계산한다. UNION과 UNION ALL, RANK와 DENSE_RANK의 차이를 작은 데이터로 계산할 수 있어야 한다.
집합 연산·뷰·윈도 함수의 역할
집합 연산은 여러 조회 결과의 행을 결합하고, 뷰는 조회 정의에 이름을 붙이며, 윈도 함수는 행을 유지한 채 관련 행들의 순위나 집계값을 계산한다. JOIN은 관련 행의 열을 연결하지만 집합 연산은 서로 대응하는 열 위치에 행을 쌓는 방식이다.
| 개념 | 핵심 처리 | 결과 해석 |
|---|---|---|
| 집합 연산 | 행 집합 결합 | 중복 제거와 차집합 방향 확인 |
| 뷰 | SELECT 정의의 재사용 | 기본 테이블과 권한·갱신 가능성 확인 |
| 윈도 함수 | 각 행에 관련 행의 계산 결과 추가 | PARTITION BY와 ORDER BY 확인 |
집합 연산과 중복 개수
왼쪽 A·A·B와 오른쪽 A·C의 UNION은 A·B·C, UNION ALL은 A·A·A·B·C, INTERSECT는 A, EXCEPT는 B다. 대응 열 수와 자료형을 확인하며 나열 순서는 반환 순서를 보장하지 않는다. 일반 뷰는 질의 정의를 저장한다.
집합 연산
결합하는 질의는 열 수가 같고 각 위치의 자료형이 호환되어야 한다. 열 이름이 아니라 위치가 대응하며 결과의 열 이름은 일반적으로 첫 질의를 따른다.
| 연산 | 의미 | 중복 처리 |
|---|---|---|
| UNION | 양쪽에 있는 모든 행 | 중복 제거 |
| UNION ALL | 양쪽의 모든 행을 그대로 결합 | 중복 유지 |
| INTERSECT | 양쪽에 공통으로 있는 행 | 중복 제거 |
| EXCEPT | 왼쪽에 있고 오른쪽에는 없는 행 | 중복 제거 |
차집합을 MINUS로 표현하는 방언도 있다. 문제에 제시된 DBMS 문법을 따르되 연산 의미는 왼쪽에서 오른쪽을 빼는 것으로 읽는다.
왼쪽 한 열의 값이 {A,A,B,C,NULL}, 오른쪽이 {A,B,B,D,NULL}이라고 하자.
| 연산 | 결과값과 개수 |
|---|---|
| UNION | A·B·C·D·NULL, 총 5행 |
| UNION ALL | A 3개·B 3개·C 1개·D 1개·NULL 2개, 총 10행 |
| INTERSECT | A·B·NULL, 총 3행 |
| 왼쪽 EXCEPT 오른쪽 | C, 총 1행 |
| 오른쪽 EXCEPT 왼쪽 | D, 총 1행 |
이 표의 나열은 결과값 설명이지 정렬 보장이 아니다. 일반 비교의 NULL = NULL은 UNKNOWN이지만 집합 연산의 중복 판정에서는 두 NULL을 같은 값처럼 취급한다.
SELECT code FROM left_code
UNION
SELECT code FROM right_code
ORDER BY code;
ORDER BY는 최종 결과의 순서를 정한다. UNION ALL이라고 입력 순서를 자동 보장하지 않는다. 여러 집합 연산을 섞을 때에는 괄호로 범위를 명확히 한다. 중복 제거가 없는 UNION ALL은 의미가 맞을 때 사용하며 항상 더 빠르다고 단정하지 않는다.
뷰의 정의와 특징
일반 뷰는 하나 이상의 테이블이나 다른 뷰를 조회하는 정의를 저장하는 가상 테이블이다. 보통 결과 행 자체를 별도 저장하지 않으므로 기본 데이터가 바뀌면 트랜잭션 가시성 규칙에 따라 조회 결과도 바뀐다.
CREATE VIEW active_employee AS
SELECT employee_id, employee_name, department_id, salary
FROM employee
WHERE employment_state = 'ACTIVE';
뷰의 장점은 복잡한 조회의 단순화, 논리적 데이터 독립성, 필요한 행·열만 노출하는 정보 은닉이다. 다만 원본 테이블에 직접 접근할 권한이 있으면 제한을 우회할 수 있으므로 뷰를 만드는 것만으로 보안이 완성되지는 않는다.
일반 뷰는 결과를 미리 계산해 저장한 캐시가 아니므로 항상 조회를 빠르게 하는 것도 아니다. 물리화 뷰는 계산 결과를 저장한다는 점이 다르지만 별도의 새로 고침과 공간이 필요하다.
단순 뷰는 DBMS 규칙에 따라 INSERT·UPDATE·DELETE가 가능하다. 집계·DISTINCT·집합 연산 등이 포함된 복잡한 뷰는 갱신에 제약이 생긴다. '모든 뷰는 갱신 가능'도 '모든 뷰는 읽기 전용'도 부정확하다.
WITH CHECK OPTION은 뷰를 통해 입력·수정하는 행이 뷰 조건을 벗어나지 않도록 검사한다. 뷰가 ACTIVE 직원만 보여 준다면 해당 뷰를 통한 INACTIVE 행 입력·변경을 제한하는 의미다. 뷰 삭제는 정의 삭제이며 기본 테이블 자체를 삭제하는 것과 다르다.
윈도 함수와 OVER
윈도 함수는 여러 행을 계산에 이용하되 개별 행을 유지한다. 일반적인 GROUP BY가 부서당 한 행을 만든다면, 윈도 집계는 직원별 행에 부서 평균을 붙인다.
SELECT employee_id, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employee;
| 구성 요소 | 역할 |
|---|---|
| PARTITION BY | 계산 그룹을 나눔. 생략하면 전체가 하나의 그룹 |
| 윈도 ORDER BY | 그룹 안에서 계산할 순서·동점을 정함 |
| 윈도 프레임 | 현재 행을 기준으로 계산에 포함할 범위를 정함 |
윈도 내부 ORDER BY는 계산 기준이지 최종 출력 정렬의 보장이 아니다. 출력 순서는 바깥 SELECT의 ORDER BY로 정한다.
순위 함수
같은 부서에 직원 (1,6000)·(2,6000)·(3,4000)이 있다고 하자. 급여 내림차순 순위는 다음과 같다.
| 직원 | 급여 | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| 1 | 6000 | 1 | 1 | 1 |
| 2 | 6000 | 2 | 1 | 1 |
| 3 | 4000 | 3 | 3 | 2 |
ROW_NUMBER는 행마다 다른 연속 번호를, RANK는 동점 뒤 순위를 건너뛰는 순위를, DENSE_RANK는 동점 뒤에도 연속인 순위를 준다. 위 ROW_NUMBER는 직원번호를 보조 기준으로 추가한 경우다.
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS rn,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employee;
RANK의 정렬에도 고유 직원번호를 추가하면 서로 동점이 아니게 된다. 어떤 열 조합으로 동점을 판단하는지 확인한다. NTILE(n)은 순서대로 행을 가능한 한 균등한 n개 구간에 배분하는 함수다.
누계와 행 이동 함수
SELECT sale_id, amount,
SUM(amount) OVER (
ORDER BY sale_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sale
ORDER BY sale_id;
sale_id 순서의 금액이 100,200,50이면 누계는 100,300,350이다. 명시된 ROWS 프레임은 처음부터 현재 행까지를 뜻한다. 프레임을 생략했을 때의 기본 범위를 무조건 파티션 전체로 가정하지 않는다.
LAG는 정렬상 앞선 행, LEAD는 뒤의 행을 참조한다. FIRST_VALUE와 LAST_VALUE는 지정한 프레임의 첫 값·마지막 값을 구한다. LAST_VALUE의 마지막은 함수가 참조하는 범위의 끝이지 언제나 전체 테이블의 마지막이 아니다.
같은 질의 블록의 WHERE는 윈도 함수보다 먼저 처리되므로 윈도 결과를 직접 필터링하지 않는다. 번호가 2 이하인 직원은 다음처럼 바깥 질의로 고른다.
SELECT employee_id, salary
FROM (
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS rn
FROM employee
) ranked
WHERE rn <= 2;
윈도 함수의 동점과 행 유지
급여 6000·6000·4000을 내림차순으로 비교하면 RANK는 1·1·3, DENSE_RANK는 1·1·2다. ROW_NUMBER에 ID를 추가 정렬 키로 쓰면 1·2·3을 구분한다. GROUP BY는 그룹당 한 행, OVER는 입력 행에 계산값을 더한다. 윈도 정렬은 최종 출력 정렬과 다르다.
중복 개수와 윈도 프레임의 경계
ALL을 지원하는 집합 연산에서는 값별 중복 개수를 따로 계산한다. 왼쪽에서 같은 행이 m번, 오른쪽에서 n번 나타나면 UNION ALL은 m+n번, INTERSECT ALL은 min(m,n)번, EXCEPT ALL은 max(m-n,0)번 남긴다. 지원 구문은 DBMS마다 다르며 SQLite에서 INTERSECT ALL·EXCEPT ALL을 직접 실행할 수는 없다.
LAG(value,1,default)는 앞선 행이 없을 때 default를 사용한다. 앞선 행은 존재하되 그 value가 NULL인 경우에는 그 NULL이 결과가 된다. NULL값 자체를 바꾸려면 별도의 COALESCE 등이 필요하다.
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW는 현재 정렬 위치에서 앞선 한 행과 현재 행을 계산 범위로 삼는다. 첫 행에서는 앞선 행이 없으므로 현재 행 하나만 이용한다. 금액이 정렬 순서대로 10·30·20이라면 이동 합계는 10·40·50이다. 이는 처음부터 현재 행까지의 누계 10·40·60과 다르다.
집계 뷰의 '부서별 평균' 한 행을 UPDATE한다고 해서 원본 직원들 중 어느 급여를 어떻게 바꿀지가 유일하게 정해지지 않는다. 이 때문에 집계·DISTINCT·집합 연산 등이 있는 뷰는 자동 갱신에 제약이 생긴다. 제품별 별도 트리거 기능까지 모든 뷰의 일반적인 자동 갱신 가능성으로 해석하지 않는다.