CONNECT BY, PARTITION BY, GROUP BY in Oracl

CONNECT BY, PARTITION BY, GROUP BY 란?

 

1. CONNECT BY - 계층형으로 나누기 위해 사용하는 쿼리
예시 )
SELECT 

      EMPNO, 

      ENAME,

      MGR 

FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO='MGR'


2. PARTITION BY - 집계함수에 그룹바이와 비슷하게 묶는 역할로 사용이 가능하다.
예시 )
SELECT 

      EMPNO , 

      ENAME , 

      JOB , 

      SAL
     , SUM(SAL) OVER(PARTITION BY JOB)
 FROM EMP
 WHERE JOB IN ('MANAGER', 'SALESMAN')
 ORDER BY JOB


3. GROUP BY - 그룹바이절로 그룹을 지을 수 있으며 내부적으로 ROLLUP, CUBE, GROUPPING SETS를 사용할 수 있다.

3_1)  ROLLUP - 사용시 인자로 넣은 값을 기준으로 소계,총계가 함께 조회된다.
EX )
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);

ROLLUP함수를 쓰면 첫번째 인자(상품 ID)별 두번째 인자(월) + 첫번째 인자(상품 ID)별 두번째 인자(월)의 소계(SUBTOTAL) + 총계(GRAND TOTAL) 의 결과가 나온다.
따라서 ROLLUP함수는 인수의 순서에도 영향을 받게 된다.

EX )
SELECT 월, 상품ID, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(월, 상품ID);

아까와는 반대로
첫번째 인자(월)별 두번째 인자(상품 ID) + 첫번째 인자(월)별 두번째 인자(상품ID)의 소계(SUBTOTAL) + 총계(GRAND TOTAL) 의 결과가 나온다.


3_2) CUBE - 
EX )
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY CUBE(상품ID, 월);

ROLLUP과 같이 소계와 총계가 조회되나, 모든 경우의 수에 대하여 조회된다. 따라서 인자값이 달라도 영향이없다.


3_3) GROUPING SETS  -
EX )
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS(상품ID, 월);

첫번째 인자(상품ID)별 소계(SUBTOTAL), 두번째 인자(월)별 소계(SUBTOTAL)이 나오는 것을 확인할 수 있다.
ROLLUP과 CUBE와 달리 계층 구조가 나타나지 않으며 따라서 인자의 순서가 달라도 결과는 똑같다.
또한, GROUPING SETS 함수는 괄호로 묶은 집합별로도 집계를 구할 수 있다.
EX )
SELECT 상품ID, 월, 회사, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS((상품ID, 월), 회사);

묶여진 컬럼을 하나의 인자로 취급해 (상품ID, 월)별 소계(SUBTOTAL)과 회사별 소계(SUBTOTAL)이 나오는 걸 확인할 수 있다.