[Database] 집합 연산자, 그룹함수 (UNION, UNION ALL, INTERSECT, MINUS, GROUPING SETS, ROLLUP, CUBE)

집합 연산자

• select 문 결과를 하나의 집합으로 간주, 그 집합에 대한 합집합, 교집합, 차집합 연산
• select 문과 select 문 사이에 집합 연산자 정의
• 두 집합의 컬럼이 동일하게 구성되어야 한다. (각 컬럼의 데이터 타입과 순서가 일치해야함)
• 전체 집합의 데이터 타입과 컬럼명은 첫번째 집합에 의해 결정됨

합집합

• 두 집합의 총 합 출력
• UNION과 UNION ALL 사용 가능

1. UNION

• 중복된 데이터는 한 번만 출력
• 중복된 데이터를 제거하기 위해 내부적으로 정렬 수행
• 중복된 데이터가 없을 경우는 UNION 대신 UNION ALL 사용(불필요한 정렬 발생할 수 있으므로)

2. UNION ALL

• 중복된 데이터도 전체 출력

출처: 홍쌤의 데이터랩

 

UNION의 경우 중복되는 20번 부서에 대한 중복 제거 결과가 나타난다.

 

 

UNION ALL 수행 결과 다음과 같이 smith가 두 번 나오는 것을 볼 수 있다.

 

주의할 점

select 시작 부터 다음 select 가 끝날 때까지 모두 하나의 쿼리기 때문에 중간에 ; 쓰지 말고

맨 마지막에 세미콜론 ;찍어주기

 

교집합

• 두 집합 사이에 INTERSECT를 적어주면 된다.
• 교집합 출력

10번 아니고 20번 아닌 거의 교집합 이니까 30번 부서만 출력되는 것을 볼 수 있다.

 

차집합

• 두 집합 사이에 MINUS를 적어주면 된다.
• 두 집합의 차집합(한 쪽 집합에만 존재하는 행) 출력
• A-B와 B-A는 다르니 순서에 주의할 것!

10번이 아닌부서 즉 20과 30번 부서에서 20번을 뺐으니 30번 부서만 출력되는 것을 확인할 수 있다.

 

집합 연산자 사용시 주의 사항

1. 두 집합의 컬럼 수 일치
2. 두 집합의 컬럼 순서 일치
3. 두 집합의 각 컬럼의 데이터 타입 일치
4. 각 컬럼의 사이즈는 달라도 됨
5. 개별 select 문에 order by 전달 불가 (group by는 전달 가능)
   • 집합 연산자는 하나의 쿼리다 즉 order by는 한 번만 들어가야 한다는 것이다.
     그런데 갑자기 쿼리 중간에 order by를 집어넣어 버리면 에러가 발생한다.
     맨 마지막 쿼리가 다 끝나고 나서 order by를 적어주면 에러는 발생하지 않는다.
     
     쿼리가 다 끝나고 나온 결과에 대해서 order by를 적용하는 것이기 때문에 에러가 발생할 이유가 없음

UNION ALL 위에다가 order by 쓰면 에러 발생함

 

그룹함수

• 숫자합수 중 여러값을 전달하여 하나의 요약값을 출력하는 다중행 함수
• 수학/통계 함수들
• group by 절에 의해 그룹별 연산 결과를 리턴 함
• 반드시 한 함수당 한 컬럼만 전달해야 한다.
• NULL은 무시하고 연산함 

COUNT

• 행의 수를 세는 함수
• 대상 컬럼은 * 또는 하나의 컬럼만 전달 가능
• * 사용 시 모든 컬럼의 값이 NULL 일때만 COUNT를 제외한다.
• 그냥 한 컬럼에 대해 사용시 값이 NULL이면 count 제외한다.
• 행의 수를 세는 경우 PK 컬럼에다 count 를 적용하는 것이 좋음 (NOT NULL이니까)

SUM

총 합을 출력하는 함수 숫자 컬럼만 전달 가능하다.

 

AVG

평균을 출력하는 함수 숫자 컬럼만 전달 가능

NULL을 제외한 대상의 평균을 리턴하므로 전체 평균 연산 시 주의가 필요함

 

MIN/MAX

최솟값 최댓값 

날짜 숫자 문자 모두 가능하다 (오름차순으로 정렬한 후 최소 최대를 출력한다.)

 

VARIANCE / STDDEV

분산과 표준편차

표준편차는 분산의 루트값

 

GROUP BY FUCTION

group by 절에 사용하는 함수

여러 group by 결과를 동시에 출력하는 기능

그룹핑 할 그룹을 정의 

 

1. grouping sets (a,b,,,)

• a별 b별 그룹 연산 결과 출력
• 나열 순서는 중요하지 않다.
• 기본 출력에 전체 총계는 출력되지 않는다.
• NULL 혹은  () 사용하여 전체 총 합 출력 가능

group by를 통해 기존엔 deptno로 요약된 정보만 확인할 수 있었다면,

 

grouping sets는 deptno와 job로 요약된 정보를 전부 보여주는 역할을 수행한다.

 

당연히 UNION ALL로도 가능하다.

다만 위에 deptno로 group by했을 시 job은 표현이 불가능하니, NULL AS job으로 (as 는 별칭임!)

job 컬럼을 null로 채워주면 UNION ALL 사용이 가능해진다.

 

다음과 같이 grouping sets(deptno, job, ())로 빈 괄호를 넣으면 전체 총계까지도 출력이 가능하다.

 

2.  ROLLUP(A,B)

• A별, (A,B)별, 전체 그룹 연산 결과 출력
  • 맨 처음 항목 A를 먼저 그룹핑 하고 그다음 A,B를 그룹핑 함
  • 즉 B,A라고 함수에 적엇으면 B를 먼저 그룹핑하고 그 다음 B,A를 그룹핑하는 것임
• 나열 대상의 순서가 중요하다
• 기본적으로 전체 총 계가 출력됨

다음과 같이 deptno 별로 그룹핑 하고 그 내부에서 job 별 그룹핑한 결과가 나오고

밑에 바로 deptno로 그룹핑한 결과가 나오는 것을 볼 수 있다.

 

맨 아래에는 기본적으로 총 계가 나온다.

 

3. CUBE(A,B)

• A 단독 B 단독 A,B 묶어서 이렇게 세 개의 결과가 출력된다.
• 그룹으로 묶을 대상의 나열 순서는 중요하지 않다.
• 기본적으로 전체 총 계가 출력된다.

정리해보자

grouping sets는 a별 b별 그룹핑 결과만 출력하는 것이고

 

roll up은 a별 (a,b) 별이다.

 

cube는 a별 b별 (a,b) 별이다.

 

roll up 과 cube는 기본적으로 전체 총 계가 출력된다. 

 

cube는 grouping sets로도 대체 가능하다

grouping sets(a,b,(a,b),()) 이렇게 하면 모든 경우의 수가 다 나오니 cube와 똑같다.

 

참고 영상 및 이미지 출처: https://www.youtube.com/watch?v=hLvv0GN0rT8&t=1168s