[SQLP] SQL함수 - 복수행 함수

복수행 함수

 

그룹함수의 종류

 

COUNT : 행의 개수 출력 - 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수이다. COUNT(*)는 NULL을 가진 행과 중복되는 행을 모두 포함한 행의 수이고, COUNT(expresstion)은 NULL을 가진 행을 제외한 행의 개수를 계산하여 반환한다.

 

MAX : NULL을 제외한 모든 행에서 최대 값

MIN : NULL을 제외한 모든 행에서 최소 값

SUM : NULL을 제외한 모든 행의 합계

AVG : NULL을 제외한 모든 행의 평균 값

STDDEV : NULL을 제외한 모든 행의 표준편차

VARIANCE : NULL을 제외한 모든 행의 분산 값

GROUPING : 해당 컬럼의 그룹에 사용되었는지 여부를 1 또는 0으로 반환

GROUPING SETS : 한 번의 질의로 여러 개의 그룹화 가능

 

 

ROLLUP : GROUP BY 절에서 다양한 형태의 그룹조합을 생성하기 위한 연산자 이다. ROLLUP연산자는 GROUP BY 절에서 그룹 조건에 따라 전체행을 그룹화 하고 각 그룹에 대해 부분합을 구하는 연산자이다.

ROLLUP에 의해 만들어지는 그룹핑 조합은 n+1개이다

CUBE : ROLLUP 연산자와 동일하게 GROUP BY 절에서 사용하는 연산자이며 ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자 이다.

CUBE에 의해 만들어 지는 그룹핑 조합은 2n개 이다.

 

ROLLUP은 소 그룹간의 합계를 구하는 함수이다

예시로

SELECT DEPT_NO, POSITION, COUNT(*), SUM(SAL)
FROM TEST
GROUP BY ROLLUP(DEPT_NO, POSITION);

 

작성하면 

 

위와 같이 나온다.

5 ROW는 DEPT_NO = 101에 대한 부분합계

8 ROW는 DEPT_NO = 102에 대한 부분합계

11 ROW는 DEPT_NO = 103에 대한 부분합계

12 ROW는 전체의 합계이다

 

그리고

SELECT DEPT_NO, POSITION, AGE, COUNT(*), SUM(SAL)
FROM TEST
GROUP BY ROLLUP(DEPT_NO, POSITION, AGE);

이렇게 작성하면

위와 같이 결과가 나오는데

3 ROW는 DEPT_NO = 101 AND POSITION = 교수 인 부분합계

6 ROW는 DEPT_NO = 101 AND POSITION = 부교수 인 부분합계

8 ROW는 DEPT_NO = 101 AND POSITION = 전임강사 인 부분합계

..

..

이런식으로 GROUP BY 절에 ROLLUP함수의 그룹핑에 따라 결과물이 달라진다.

 

 

CUBE는 항목별 다 차원적인 소계를 계산한다.

SELECT DEPT_NO, POSITION, COUNT(*), SUM(SAL)
FROM TEST
GROUP BY CUBE(DEPT_NO, POSITION);

 

5 ROW는 DEPT_NO = 101에 대한 소계

8 ROW는 DEPT_NO = 102에 대한 소계

11 ROW는 DEPT_NO = 103에 대한 소계

12 ROW는 POSITION = 교수 에 대한 소계

13 ROW는 POSITION = 부교수 에 대한 소계

14 ROW는 POSTITION = 전임강사 에 대한 소계

15 ROW는 POSTITION = 조교수 에 대한 소계

16 ROW는 전체 합계이다.

 

CUBE의 그룹핑 컬럼을 하나 더 추가하게 된다면

SELECT DEPT_NO, POSITION, AGE,COUNT(*), SUM(SAL)
FROM TEST
GROUP BY CUBE(DEPT_NO, POSITION, AGE);

 

이렇게 위와 같이 결과가 나오며

 

DEPT_NO 와 POSITION 에 대한 각각의 소계

DEPT_NO와 AGE에 대한 각각의 소계

POSITION과 AGE에 대한 각각의 소계가 나오고 마지막으로

모든 합계까지 나온다.

 

GROUPING 함수는 ROLLUP이나 CUBE 연산자와 함께 사용하는 함수이다. 이 함수는 인수로 지정된 컬럼이 ROLLUP이나 CUBE 연산자로 생성된 그룹 조합에서 사용되었는지 여부를 1 또는 0으로 반환한다. 만일 해당 컬럼이 그룹 조합에 사용되었으면 0, 아니면 1을 반환한다.

 

SELECT DEPT_NO, POSITION, COUNT(*),
GROUPING(DEPT_NO) AS GRP_DEPT_NO,
GROUPING(POSITION) AS GRP_POSI,
SUM(SAL)
FROM TEST
GROUP BY ROLLUP(DEPT_NO, POSITION);

 

5 ROW 에서는 POSITION이 사용되지 않았으므로 GRP_POSI = 1 이다

8 ROW 에서도 POSITION이 사용되지 않았으므로 GRP_POSI = 1 이다

11 ROW도 동일하며

12 ROW는 둘다 사용되지 않았으므로 GRP_DEPT_NO, GRP_POSI 가 둘다 1이다

 

GROUPING SETS 함수는 GROUP BY 절에서 그룹 조건을 여러개 지정할 수 있는 함수이다. GROUPING SETS 함수의 결과는 각 그룹 조건에 대해 별도로 GROUP BY 한 결과를 UNION ALL 한 결과와 동일하다.

GROUPING SETS함수는 하나의 SQL 명령문에 의해 여러개의 그룹 조건을 한꺼번에 지정하여 복잡한 그룹 처리 과정을 단순하게 구성할 수 있다.

 

GROUP BY 

GROUPING SET(A,B,C) 

==>

GROUP BY A UNION ALL

GROUP BY B UNION ALL

GROUP BY C

 

위 처럼 GROUPING SETS (A,B,C)는  GROUP BY A, GROUP BY B, GROUP BY C 를 UNION ALL 한거와 결과가 동일하다

 

GROUP BY

GROUPING SETS(A, B, (B,C))

==>

GROUP BY A UNION ALL

GROUP BY B UNION ALL

GROUP BY B,C

 

위에 문구는 동일한 결과다

 

GROUP BY 

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

==>

GROUP BY A UNION ALL

GROUP BY ROLLUP(B, C)

 

GROUP BY 

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

==>

GROUP BY A UNION ALL

GROUP BY CUBE(B,C)