SQLD 공부 11일 차!

이번 글에는 집합 연산자와 그룹 함수와 관련하여 글을 작성하도록 하겠습니다.

---

1. 집합 연산자

- 집합 연산자는 두 테이블에 대한 집합 연산(합집합, 교집합, 차집합 등)을 수행하는 연산자이다.

- 특정한 기중키 없이 두 테이블에 대한 집합 연산을 수행하므로 스키마가 동일해야한다.

 

1) UINON ALL / UNION(합집합)

- 두 테이블에 포함된 레코드들을 모두 포함시킨다

 

형태	종류	설명
UNION		중복된 레코드들 중 하나만 포함 시키는 것
UNION ALL		중복 레코드들의 중복된 개수만큼 그대로 포함시키는 것

 

작성 방법

[SQL문]
SELECT * FROM LeftTable
       UNION ALL SELECT * FROM RightTable ;

 

2) INTERSECT(교집합)

- 공통적으로 포함하는 레코드만 포함

형태	종류	설명
INTERSECT		공통적으로 포함된 값들만 반환

 

작성 방법

[SQL문]
SELECT * FROM LeftTable
       INTERSECT SELECT * FROM RightTable ;

 

3) MINUS / EXCEPT(차집합)

- 기준이 되는 테이블에서 공통된 결과를 제외한 값들만 포함한다

형태	종류	설명
MINUS / EXCEPT		기준이 되는 테이블에서 공통인 내용을 제외한 내용만 포함

 

작성 방법

[SQL문]
SELECT * FROM LeftTable
       MINUS / EXCEPT SELECT * FROM RightTable ;

---

2. 그룹함수

- 그룹합수는 GROUP BY절의 결과에 대해서 그룹별로 별도로 연산을 수행하는 함수이다

 

 

1) ROLLUP

- 칼럼을 대상으로 하위 그룹핑을 수행하는 함수

- 그룹핑 순서 

GROUP BY ROLLUP(날짜, 이름) => (날짜, 이름) -> 날짜 -> 전체

 

에시) 볼트수 별로 그룹핑하여 각 그룹의 필요한 볼트 수를 세는 쿼리

 

[SQL문]
SELECT Bolts, COUNT(*) From Product
        GROUP BY ROLLUP(Bolts)
                   ORDER BY Bolts desc;

 

 

 

2) CUBE

- 가능한 모든 경우로 그룹핑을 진행한다

- 그룹핑 순서

GROUP BY ROLLUP(날짜, 이름) => (날짜, 이름) -> 날짜 -> 이름 -> 전체

 

에시) 볼트수와 넛트수 별로 그룹핑하여 각 그룹의 필요한 볼트 수를 세는 쿼리

 

[SQL문]
SELECT Bolts, COUNT(*) From Product
        GROUP BY ROLLUP(Bolts)
                   ORDER BY Bolts desc;

 

3) GROUPING SETS

- 그룹핑할 대상을 지정하는 함수

- 입력된 인자에 대해서만 소계를 진행

GROUP BY GROUPING SETS(A) -> A에 대해서 소계 진행

GROUP BY GROUPING SETS(B) -> B에 대해서 소계 진행

GROUP BY GROUPING SETS(A,B) -> A와 B에 대해서 소계 진행

GROUP BY GROUPING SETS((A,B)) -> (A,B)에 대해서 소계 진행

GROUP BY GROUPING SETS(()) -> 전체에 대해서 소계 진행

 

ROLLUP(A,B) = GROUPING SETS(A, (A,B), ())

CUBE(A,B) = GROUPING SETS(A, B, (A,B), ())

 

 

4) GROUPING

- 소계에 해당하는 행과 그렇지 않은 행을 구분하기 위해 사용

---

3. 윈도우 함수

- 행과 행간의 관계를 나타내는 연산을 쉽게하기 위한 함수

- 행의 기존 구성을 유지한 상태로 새로운 값을 추가하거나 기존 값을 변경함

- OVER 키워드와 함께 사용

 

1) 순위함수

이름	설명	예
RANK	- 동일 순위는 같은 순위값을 가진다. - 순위는 누적 순위로 한다	1,1,3,4,5,5,5,8...
DENSE_RANK	- 동일 순위는 같은 순위값을 가진다. - 순위는 앞 순위 +1이다	1,1,2,3,4,4,5,6...
ROW_NUMBER	- 동일 순위더라도 값이 다르다 - 각 행별로 순위를 부여한다	1,2,3,4,5,6,7,8...

 

2) 집계함수

이름	설명
COUNT	- 값이 Null인 행을 제외한 파티션 별 개수를 반환한다.
SUM	- 입력된 칼럼에 대한 파티션 별 합계를 반환한다
AVG	- 입력된 칼럼에 대한 파티션 별 평균를 반환한다
MIN	- 입력된 칼럼에 대한 파티션 내 최솟값을 반환한다
MAX	- 입력된 칼럼에 대한 파티션 내 최댓값을 반환한다

 

 

3) 행 순서 함수

이름	설명
FIRST_VALUE	- 파티션의 첫 값을 반환한다
LAST_VALUE	- 파티션의 마지막 값을 반환한다
LAG	- 입력된 인자 만큼 이전 값을 반환한다
LEAD	- 입력된 인자 만큼 이후 값을 반환한다

 

4) 비율 함수

이름	설명
CUME_DIST	- 전체 개수에 대한 누적 백분율을 소수점 단위로 계산하여 반환한다 - 마지막 행의 값 1
PERCENT_RANK	- 순서별로 백분율을 반환한다 - 첫 행의 값 0, 마지막 행의 값 1
NTILE	- 입력된 인자로 파티션의 총 개수를 N등분하여 등급을 반환한다 - 인자로 나눈 값이 나머지가 생긴다면 앞 등급들에 차례로 1씩 부여한다
RATIO_TO_REPORT	- 파티션별 합계에 대한 비율을 제공한다

 

5) TOP N 쿼리

- 상위 N번째 값을 추출하는 쿼리

- ROWNUM또는 순위 함수를 활용

 

- TOP N 사용시 가능 비교 연산자(<, <=)

---