[ORACLE] 5일차 : SQL함수(집계 함수) - 2

 

ROLLUP절

소계를 생성하기 위해 사용한다. grouping columns의 수를 N이라고 했을때 N+1 Level의 소계가 생성된다. ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 된다.

정렬이 필요한 경우는 ORDER BY절에 명시적으로 정렬 컬럼이 표시되어야 한다. 

 

SELECT dept,pos,SUM(sal)
FROM emp
GROUP BY ROLLUP(dept,pos)
ORDER BY dept,pos;

 

 

 

 

부서별 인원수와 마지막에 전체 인원수 출

SELECT dept, count(*)
FROM emp
GROUP BY ROLLUP(dept);

 

 

 

 

 

CUBE절

CUBE는 결합 기능한 모든 값에 대하여 다차원 집계를 생성한다. 예를 들어 CUBE절에서 3(n)개의 표현식이 주어진 경우 8개의 그룹화가 이루어진다. CUBE의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔수 있어도 데이터 결과는 같다. CUBE는 결과에 대한 정렬이 필요한 경우는 ORDER BY절에 명시적으로 정렬할 컬럼을 명시한다.

 

CUBE 집계

 

SELECT dept,pos,SUM(sal)
FROM emp
GROUP BY city, CUBE(dept,pos)
ORDER BY city,dept,pos;

 

 

 

 

 

GROUPING

GROUPING 함수는 ROLLUP이나 CUBE와 함께 사용하여 GROUPING 함수에 기술된 컬럼이 ROLLUP이나 CUBE절에서 사용이 되었는지 보여주는 함수이다. GROUPING 함수를 이용할 경우 출력되는 결과 값중 NULL값이 있다면 NULL값이 ROLLUP이나 CUBE 연산의 결과로 생성된 것인지, 원래 테이블상에 NULL값으로 저장된 것인지를 확인할 수 있다. GROUPING 함수는 결과값으로 0또는 1을 리턴한다. 0값을 리턴 하는 경우, 해당 인수로 쓰인 값이 ROLLUP이나 CUBE에서 사용되어 졌음을 나타내는 것이고, 1값을 리턴하는 경우 ROLLUP이나 CUBE에 사용되지 않았음을 나타낸다. 

 

SELECT dept,pos,SUM(sal),GROUPING(dept),GROUPING(pos)
FROM emp
GROUP BY city, CUBE(dept,pos)
ORDER BY city,dept,pos;

 

 

 

 

GROUP_ID

형식 GROUP_ID()

SELECT문에서 GROUP BY로 분리되어 복제된 번호로 복제 횟수를 구분하도록 출력한다. 번호가 0부터 시작되므로 n번 복제되었으면 n-1 번호가 출력된다. GROUP BY의 확장으로 인하여 같은 grouping을 나타내는 값이 중복되어 결과 집합에 포함될 수 있으며, 이때 중복된 그룹들을 구별 하기위해 처음으로 중복되어 나타나는 행에 대하여 GROUP_ID 함수는 0을 반환하고, 나머지 경우에 대하여 1의 값을 반환한다. 

 

SELECT dept,empNO,name,SUM(sal),GROUP_ID()
FROM emp
GROUP BY dept, ROLLUP(dept,(empNO,name))
ORDER BY dept,GROUP_ID(),empNO;