오라클의 그룹 함수들, GROUP BY ,CUBE

유동현·2022년 10월 10일
groupbysql함수
0

오라클

목록 보기
9/18
post-thumbnail

그룹함수

  • ※오라클은 NULL을 가장 큰값으로 간주한다.

  • (ORACLE 9i 까지는 NULL이 가장 작은값이었다.)

  • MSSQL 은 NULL을 가장 작은 값으로 간주한다.

  • ✏️그룹 함수의 가장 큰 특징

  • 처리해야 할 데이터들 중 NULL이 존재한다면(포함되어 있다면)

  • 이 NULL은 제외한 상태로 연산을 수행한다는 것이다.

  • 즉,NULL은 연산의 대상에서 제외된다.

SUM() 합

--○SUM() 합
-- EMP 테이블을 대상으로 전체 사원들의 급여 총합을 조회한다.
SELECT SUM(SAL)
FROM EMP;
--29025 총합

SELECT SUM(COMM) --NULL+300+500.... (x)
FROM EMP;
--==>2200 NULL을 제외하고 연산한다.

COUNT() 카운트

--○COUNT() 행(레코드)의 갯수 조회 -> 데이터가 몇 건인지 확인...
SELECT COUNT(ENAME)
FROM EMP;
--==>>14

SELECT COUNT(COMM)
FROM EMP;
--==>>4 NULL을 세지 않음

SELECT COUNT(*)
FROM EMP;
--특정 컬럼을 명시하지않고 보통 이렇게 쓴다.

AVG() 평균

--○AVG() 평균 반환
SELECT AVG(SAL) "COL"
       ,SUM(SAL) / COUNT(SAL) "COL2"
FROM EMP;
--2073.214285714285714285714285714285714286	
--2073.214285714285714285714285714285714286

SELECT AVG(COMM) "COL1"
      ,SUM(COMM) / COUNT(COMM) "COL2"
      FROM EMP;

VARIENCE() 분산, STDDEV()표준편차

  • ✏️표준편차의 제곱이 분산, 분산의 제곱근이 표준편차다.

SELECT VARIANCE(SAL), STDDEV(SAL)
FROM EMP;
--==>>1398313.87362637362637362637362637362637
--==>>1182.503223516271699458653359613061928508
SELECT POWER(STDDEV(SAL),2) "COL1"   --표준 편차 제곱
       ,VARIANCE(SAL) "COL2"
FROM EMP;
--1398313.87362637362637362637362637362637
--1398313.87362637362637362637362637362637


SELECT SQRT(VARIANCE(SAL)) "COL1"
    ,   STDDEV(SAL) "COL2"
FROM EMP;
--==>>
/*
1182.503223516271699458653359613061928508
1182.503223516271699458653359613061928508
*/

MAX() 최대값, MIN() 최소값

--   최대값 / 최소값 반환

SELECT MAX(SAL) "COL1"
       ,MIN(SAL) "COL2"
FROM EMP;
--5000	800

⚠️주의

SELECT ENAME, SUM(SAL)
FROM EMP;
--ORA-00937: not a single-group group function

ENAME는 여러행이나 SUM은 그룹함수로서 한개의 행만을 반환한다.

이를위해 사용하는것이 GROUP BY다.


🧐 GROUP BY

  • 🧐 GROUP BY는 SELECT 구문에서 3번째로 실행되는 구문으로 WHERE절에 의해 걸러져 메모리에 로드된 데이터들을 사용자가 정의하는 방식대로 묶어주는 구문이다.
SELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;
/*
10	8750
20	10875
30	9400
	8700    --부서번호가 NULL인 사원들의 급여합
*/
  • DEPTNO 즉 부서번호로 묶겠다고 지정했으므로 데이터는 각 부서번호를 기준으로 묶어지게 되고(그룹화) 이후 SUM에 의해 각 부서별 봉급(SAL)값의 총합을 구해 출력하게된다.
  • GROUP BY 에서는 NULL인것도 NULL값으로 묶어 구별해서 출력한다.

ROLL UP

ELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);

/*
10	8750
20	10875
30	9400
	8700
	37725   --모든부서 직원들의 급여합
*/
  • 위와같이 ROLL UP을 사용하면 전체부서들의 합을 같이 출력해준다.
  • 허나 모든부서와 부서번호가 NULL인것과 급여합이 구분이 안되므로
SELECT NVL2(DEPTNO, TO_CHAR(DEPTNO),'모든부서')"부서번호",SUM(SAL)"급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
/*
10	8750
20	10875
30	9400
모든부서	8700
모든부서	37725
*/

이와같이 쓸수있다 허나 이것또한 구분이 힘드므로 사용하는것이 GROUPING이다

GROUPING

  • ROLLUP 이 수행된 값과 아닌값을 구분할수 있게 해준다.
SELECT GROUPING(DEPTNO) "GROUPING", DEPTNO "부서번호", SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
/*
GROUPING  부서번호  급여합
0	        10      	8750
0	        20      	10875
0	        30      	9400
0		   (null)   8700
1	       (null) 	37725
*/

--GROUPING 반환값이 1인 것은 ROLLUP이 수행된것

위의 내용을 CASE WHEN NVL을 통해

SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')  
            ELSE '모든부서'
        END "부서"
            ,SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
/*
10	8750
20	10875
30	9400
인턴	8700
모든부서	37725
*/

아래와 같이 바꿀수 있다.

실습(인라인쿼리, Group by)

--○TBL_SAWON 테이블을 대상으로
--다음과 같이 조회될 수있도록 쿼리문 작성

----------------------------
성별           급여합
-----------------------------
남             xxxx
여             xxxxx
모든사원        xxxx
SELECT  CASE GROUPING(T.성) WHEN 0 THEN T.성
        ELSE '모든사원'
        END "성별"
        ,SUM(T.급여) "급여합"
FROM(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남'
            WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여'
            ELSE '성별확인안됨'
        END "성"
        ,SAL "급여"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.성);

or 

SELECT  NVL(T.성별,'모든사원') 
        ,SUM(T.급여) "급여합"
FROM(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남'
            WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여'
            ELSE '성별확인안됨'
        END "성별"
        ,SAL "급여"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.성별);

실습2(인라인쿼리,GroupBy,View)

--○TBL_SAWON 테이블을 대상으로
--다음과 같이 조회될 수있도록 쿼리문 작성
/*
----------------------------
연령대           인원수
-----------------------------
10               x
20             x
30              x
50              x
전체             16
*/

인라인쿼리

SELECT NVL(T2.나이대,'전체') "연령대"  --CASE GROUPING(T.나이대) WHEN 0 TO_CHAR(T.나이대) ELSE '전체' END
       ,COUNT(*) "인원"
FROM(
SELECT CASE WHEN T1.나이>=50 THEN '50'
            WHEN T1.나이>=30 THEN '30'
            WHEN T1.나이>=20 THEN '20'
            WHEN T1.나이>=10 THEN '10'
            ELSE '확인필요'
        END "나이대"
FROM(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)
            ELSE TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999)
            END "나이"
FROM TBL_SAWON
)T1
)T2
GROUP BY ROLLUP(나이대);

or

----------------나이대 붙이기 GROUPING(숫자형) GROUP BY ROLLUP(숫자형) 이라는걸 기억하자.
SELECT CASE GROUPING(T1.나이) WHEN 0 THEN T1.나이||'대'
            ELSE '전체' END "나이대" ,COUNT(*)
FROM(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TRUNC(EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(JUBUN,1,2))+1899),-1)
            WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TRUNC(EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(JUBUN,1,2))+1999),-1)
            ELSE -1
            END "나이"
FROM TBL_SAWON
)T1  --T1
GROUP BY ROLLUP(T1.나이);

View

CREATE OR REPLACE VIEW VIEW_SAWONTEMP
AS
SELECT CASE WHEN T.나이>=50 THEN '50'
            WHEN T.나이>=30 THEN '30'
            WHEN T.나이>=20 THEN '20'
            WHEN T.나이>=10 THEN '10'
            ELSE '확인필요'
        END "나이대"
FROM(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)
            ELSE TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999)
            END "나이"
FROM TBL_SAWON
)T;

이후

SELECT NVL(나이대,'전체') "나이대", COUNT(나이대)
FROM VIEW_SAWONTEMP
GROUP BY ROLLUP(나이대);

RollUp 보충

--ROLLUP은 하나만 묶을수 있는 애가 아니다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY 1,2;

/*
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	5600
*/




SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
/*
10	    CLERK	    1300   --10번 부서 CLERK 직종의 급여합
10	    MANAGER	    2450   --10번 부서 MANAGER 직종의 급여합
10	    PRESIDENT	5000   --10번 부서 PRESIDENT 직종의 급여합
10	    (null)	    8750   --10번 부서 모든 직종의 급여합
20	    ANALYST     	6000
20	    CLERK	    1900
20	    MANAGER 	2975
20	    (null)	    10875  --20번 부서 모든 직종의 급여합
30	    CLERK	    950
30	    MANAGER	    2850
30	    SALESMAN    	5600
30	    (null)  	9400   --30번 부서 모든 직종의 급여합
(null)	(null)  	29025  --모든 부서 모든 직종의 급여합

2개묶었을땐 이렇게 사용한다.

SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
       ELSE '전체부서'
    END "부서번호"
       , CASE GROUPING(JOB) WHEN 0 THEN JOB
          ELSE '전체직종'
        END "직종"
        , SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1, 2;
/*
10	    CLERK	    1300
10	    MANAGER 	2450
10	    PRESIDENT	5000
10	    전체직종    	8750
20	    ANALYST	    6000
20	    CLERK	    1900
20	    MANAGER 	2975
20	    전체직종    	10875
30      	CLERK	    950
30	    MANAGER 	2850
30	    SALESMAN	    5600
30	    전체직종    	9400
인턴  	CLERK	    3500
인턴	    SALESMAN	    5200
인턴	    전체직종	    8700
전체부서	전체직종	    37725
*/

Cube

ROLLUP()보다 더 자세한 결과를 반환받는다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY 1,2;

/*
10	    CLERK	    1300
10	    MANAGER	    2450
10	    PRESIDENT	5000
10	    (null)  	8750
20	    ANALYST 	    6000
20	    CLERK	    1900
20	    MANAGER	    2975
20	    (null)  	10875
30	    CLERK   	950
30	    MANAGER	    2850
30	    SALESMAN	    5600
30	    (null)  	9400
(null)	ANALYST	    6000 --모든 부서 ANALYST 직종의 급여합  --추가
(null)	CLERK	    4150 --모든 부서 CLERK 직종의 급여합    --추가
(null)	MANAGER	    8275 --모든 부서 MANAGER 직종의 급여합  --추가
(null)	PRESIDENT	5000 --모든 부서 PRESIDENT 직종의 급여합 --추가
(null)	SALESMAN	    5600 --모든 부서 SALESMAN 직종의 급여합  --추가
(null)	(null)  	29025
*/

SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
       ELSE '전체부서'
    END "부서번호"
       , CASE GROUPING(JOB) WHEN 0 THEN JOB
          ELSE '전체직종'
        END "직종"
        , SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY 1, 2;
/*
10	    CLERK	    1300
10	    MANAGER	    2450
10	    PRESIDENT	5000
10	    전체직종	    8750
20	    ANALYST     	6000
20	    CLERK	    1900
20	    MANAGER	    2975
20	    전체직종	    10875
30	    CLERK	    950
30   	MANAGER 	2850
30	    SALESMAN    	5600
30	    전체직종    	9400
인턴	    CLERK	    3500
인턴	    SALESMAN	    5200
인턴	    전체직종	    8700

전체부서	ANALYST	    6000
전체부서	CLERK	    7650
전체부서	MANAGER	    8275
전체부서	PRESIDENT	5000
전체부서	SALESMAN	    10800

전체부서	전체직종	    37725
*/
profile
유동현
이전 포스트

CASE WHEN , DECODE, 뷰 , 서브쿼리, RANK

다음 포스트

Grouping Set

0개의 댓글