[교육] SQL초보에서실전전문가까지 ORACLE - 10

--// 일반적인 GROUP BY

SELECT B.DNAME, A.JOB, COUNT(*) AS TOTALEMPL, SUM(A.SAL) AS TOTALSAL

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY B.DNAME, A.JOB;

--// ROLLUP GROUP BY 중간합과 총합까지 나오는 함수

SELECT B.DNAME, A.JOB, COUNT(*) AS TOTALEMPL, SUM(A.SAL) AS TOTALSAL

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY ROLLUP(B.DNAME, A.JOB);

MSSQL 에서는 WITH ROLLUP 구문으로 사용할 수 있다.

--// GROUPING 그룹화 하는데 해당 컬럼이 사용됐는지 확인 

--// 0이면 사용 1이면 아님

--// 주로 확인 할 때 사용함; 그리드에서 통계정보 보일때 유용할 듯

SELECT B.DNAME, 

        GROUPING(B.DNAME),

        A.JOB, 

        GROUPING(A.JOB), 

        COUNT(*) AS TOTALEMPL, 

        SUM(A.SAL) AS TOTALSAL

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY ROLLUP(B.DNAME, A.JOB);

--// DECODE + GROUPING

SELECT  DECODE(GROUPING(B.DNAME),'1','ALL DEPT',B.DNAME),

        A.JOB, 

        DECODE(GROUPING(A.JOB),'1','ALL JOB',A.JOB), 

        COUNT(*) AS TOTALEMPL, 

        SUM(A.SAL) AS TOTALSAL

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY ROLLUP(B.DNAME, A.JOB);

--// CUBE

--// JOB 별로의 결과도 추가로 보여줌

SELECT B.DNAME, 

        GROUPING(B.DNAME),

        A.JOB, 

        GROUPING(A.JOB), 

        COUNT(*) AS TOTALEMPL, 

        SUM(A.SAL) AS TOTALSAL

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY CUBE(B.DNAME, A.JOB);

--// 실행계획 보기

SET AUTOTRACE ON

--// 튜닝이 필요한 사례

SELECT 

    TO_CHAR(COUNT(ENAME)) NUM,

    DNAME

FROM

    EMP A

    INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY DNAME

-----------

UNION ALL

-----------

SELECT

    TO_CHAR(SUM(NUM)), 'TOTAL'

FROM 

    (

    SELECT 

        TO_CHAR(COUNT(ENAME)) NUM,

        DNAME

    FROM

        EMP A

        INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

    GROUP BY DNAME

    );

    

    

    

--// ROLLUP 활용하여 튜닝

SELECT TO_CHAR(COUNT(ENAME)) NUM, DECODE(DNAME,'', 'TOTAL', DNAME)

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY ROLLUP(DNAME);

--// TO_CHAR 빼고 테스트 (동일함)

SELECT COUNT(ENAME) NUM, DECODE(DNAME,'', 'TOTAL', DNAME)

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY ROLLUP(DNAME);

--// GROUPING SET

--// JOB 과 MGR을 같은 레벨로 보게 해줌

SELECT B.DNAME, 

        GROUPING(B.DNAME),

        A.JOB, 

        GROUPING(A.JOB), 

        A.MGR,

        COUNT(*) AS TOTALEMPL, 

        SUM(A.SAL) AS TOTALSAL

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY ROLLUP(B.DNAME, (A.JOB, A.MGR));

--// GROUPING SETS 

--// 여러 그룹핑 기준을 한꺼번에 볼 수 있음

SELECT 

    DNAME,

    JOB,

    MGR,

    SUM(SAL) "TOTAL SAL"

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO

GROUP BY GROUPING SETS((DNAME, JOB, MGR),(DNAME, JOB), (JOB, MGR));

--// SUM(DECODE()) 활용

SELECT

    NVL(SUM(DECODE(DNAME,'ACCOUNTING',SAL)),0) "ACCOUNTING",

    NVL(SUM(DECODE(DNAME,'RESEARCH',SAL)),0) "RESEARCH",

    NVL(SUM(DECODE(DNAME,'SALES',SAL)),0) "SALES",

    NVL(SUM(DECODE(DNAME,'OPERATIONS',SAL)),0) "OPERATIONS"

FROM EMP A

INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO;   

--// RANK OVER 함수와  PARTITION BY

SELECT

    SAL, DEPTNO,

    RANK() OVER(ORDER BY SAL) RANK,

    RANK() OVER(PARTITION BY SAL ORDER BY DEPTNO) SAL_RANK

FROM EMP; 

--// DENSE_RANK OVER 함수와  PARTITION BY 

--// 중복 순위를 하나로 취급

SELECT

    SAL, DEPTNO,

    DENSE_RANK() OVER(ORDER BY SAL) RANK,

    DENSE_RANK() OVER(PARTITION BY SAL ORDER BY DEPTNO) SAL_RANK

FROM EMP; 

--// ROW_NUMBER()

SELECT

    SAL, DEPTNO,

    RANK() OVER(ORDER BY SAL) RANK,

    ROW_NUMBER() OVER(ORDER BY SAL) ROW_NUMBER

FROM EMP; 

--// 자기를 기준으로 위에 1개 아래 1개와 합의 평균

--// 최근 평균 온도 활용에 좋을 듯

SELECT MGR, ENAME, HIREDATE, SAL, 

    AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE

        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS C_MAVG 

    --// 자기를 기준으로 위에 1개 아래 1개와 합의 평균

FROM EMP;

--// 부서별 총 월급의 평균이 큰 순서대로 하면서 인별

SELECT 

    MGR, ENAME, SAL

FROM 

    (

    SELECT MGR, ENAME, SAL,

    MAX(SAL) OVER (PARTITION BY MGR) AS MAX_SAL

    FROM EMP

    )

WHERE SAL = MAX_SAL;

SELECT ENAME, SAL,

COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS MOV_CNT

--// 자기 급여보다 50작고 150 큰 범위 안에 있는 인원의 수

FROM EMP;

--// FIRST_VALUE : PARTITION 내에서 가장 먼저 나오는 값

SELECT

    DEPTNO, EMPNO, ENAME, SAL, FIRST_VALUE(ENAME)

    OVER (ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS RICH_EMP

FROM EMP;

--// LAST_VALUE : PARTITION 내에서 가장 먼저 나오는 값

SELECT

    DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(ENAME)

    OVER (ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS RICH_EMP

FROM EMP;

--// LAG , LEAD

SELECT

    ENAME, HIREDATE, SAL,

    LAG(SAL) OVER(ORDER BY HIREDATE) AS PREV_SAL --// 기본 이전 1번째것

FROM EMP

WHERE JOB='SALESMAN';

SELECT

    ENAME, HIREDATE, SAL,

    LAG(SAL,2,0) OVER(ORDER BY HIREDATE) AS PREV_SAL --// 이전 2번째것

FROM EMP

WHERE JOB='SALESMAN';

--가져올게 없으면 0처리

SELECT

    ENAME, HIREDATE, SAL,

    LEAD(SAL,2,0) OVER(ORDER BY HIREDATE) AS PREV_SAL --// 다음 2번째것

FROM EMP

WHERE JOB='SALESMAN';

--// PERCENT_RANK() 

SELECT DEPTNO, ENAME, SAL,

PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS PR

FROM EMP;

--// CUME_DIST()

--// RATIO_TO_REPORT

SELECT ENAME, SAL, RATIO_TO_REPORT(SAL) OVER() AS RR

FROM EMP

WHERE JOB='SALESMAN';

--// NTILE 전체를 몇으로 나눠서 순번을 표시해준다.

SELECT

    ENAME, SAL,

    NTILE(5) OVER(ORDER BY SAL DESC) AS QUARTILE

FROM EMP;