오라클 분석함수, PARTITION BY, ORDER BY, WINDOW구, MAX, MIN, SUM, AVG, DENSE RANK FIRST/LAST, KEEP, OVER

오라클 분석함수, PARTITION BY, ORDER BY, WINDOW구, MAX, MIN, SUM, AVG, DENSE RANK FIRST/LAST, KEEP, OVER

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=627

 

오라클 분석함수, PARTITION BY, ORDER BY, WINDOW구, MAX, MIN, SUM, AVG, DENSE RANK FIRST/LAST, KEEP, OVER

오라클 분석함수, PARTITION BY, ORDER BY, WINDOW구, MAX, MIN, SUM, AVG, DENSE RANK FIRST/LAST, KEEP, OVER7. 오라클 분석함수(Analytic Function) 및 SQL 활용 7.1 분석함수란? 분석함수란? 테이블의 로우(행, 레코드)를 그룹핑 하여 집계를 하는 기능을 하는 함수인데 일반 …

ojc.asia

7. 오라클 분석함수(Analytic Function) 및 SQL 활용

7.1 분석함수란?

분석함수란? 테이블의 로우(행, 레코드)를 그룹핑 하여 집계를 하는 기능을 하는 함수인데 일반 집계함수(MAX, MIN, COUNT등)와 다른 점은 다중 행(Multiple Rows)을 리턴하는 것이다. (일반 집계함수가 그룹당 하나의 결과를 리턴하지만 분석함수는 행당 하나의 결과를 리턴)

분석을 위한 행(로우)들의 그룹을 윈도우(window)라 부르며 analytic_clause(OVER절)에서 정의한다. window는 현재 함수가 적용되는 행에 대해 분석을 위해 집계를 할 행들의 범위를 결정하는 역할을 하며 다양한 분석함수의 제공으로 서브쿼리등 불필요한 쿼리 사용을 줄일 수 있으며 성능에도 이점이 있다.

집계함수(Aggregate Function)뒤에 Analytic구를 두어 행그룹의 정의를 범위(Window)를 지정하고 각 그룹당 결과값을 반복하여 출력하는 형태로 구성되며, 윈도우를 통해 집계를 하기위한 타겟 레코드의 범위를 결정하게 된다. 분석함수는 조인, WHERE, GROUP BY, HAVING등과 함게 쓰이는 경우 가장 마지막에 집계연산을 수행하며 SELECT절과 ORDER BY절에서 사용 가능하다.

-- 분석함수를 사용할 때와 안할 때 비교 SQL> set pagesize 30 SQL> select empno, ename, sal, deptno, 2 max(sal) over(partition by deptno) max_sal, 3 min(sal) over(partition by deptno) min_sal 4 from emp; EMPNO ENAME SAL DEPTNO MAX_SAL MIN_SAL ---------- ---------- ---------- ---------- ---------- ---------- 7782 CLARK 2450 10 5000 1300 7839 KING 5000 10 5000 1300 …… 7900 JAMES 950 30 2850 950 7698 BLAKE 2850 30 2850 950 7654 MARTIN 1250 30 2850 950 14 개의 행이 선택되었습니다. SQL> select e1.empno, e1.ename, e1.sal, e1.deptno, e2.max_sal, e2.min_sal 2 from emp e1, ( select deptno, max(sal) max_sal, min(sal) min_sal 3 from emp 4 group by deptno) e2 5 where e1.deptno = e2.deptno; EMPNO ENAME SAL DEPTNO MAX_SAL MIN_SAL ---------- ---------- ---------- ---------- ---------- ---------- 7900 JAMES 950 30 2850 950 7844 TURNER 1500 30 2850 950 …… 7934 MILLER 1300 10 5000 1300 7839 KING 5000 10 5000 1300 7782 CLARK 2450 10 5000 1300 14 개의 행이 선택되었습니다.

7.2 분석함수 기본형식(PARTITION BY, ORDER BY, WINDOW구, MAX, MIN, SUM, AVG, DENSE RANK FIRST/LAST, KEEP, OVER)

[분석함수 기본형식]

analytic_function([ arguments ]) OVER (analytic_clause)

Analytic clause

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

Query partiotion clause ​ PARTITION BY { value_expr[, value_expr ]... | ( value_expr[, value_expr ]... ) }

n PARTITON BY구는 하나 또는 여러행을 그룹핑 하기 위하여 사용한다. GROUP BY절과 동일한 기능을 수행하는데 Group By절을 사용하지 않고 행들을 그룹핑 하는 것이다.

Order by clause ​ ORDER [ SIBLINGS ] BY { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]...

n Partition By로 정의된 윈도우내에서 행들의 정렬 순서를 지정한다.

n ORDER BY절의 ASC|DESC는 결과에 대해 오름차순 또내림차순으로 정렬할 때 사용한다.

n ORDER BY절의 NULLS FIRST|NULL LAST구는 NULL값을 먼저 보일것인지 나중에 보일 것인지를 결정한다.

---

Windowing_clause ​ { ROWS | RANGE } { BETWEEN { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } | { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING } }

n ROWS 와 RANGE : ROWS는 현재 행을 기준으로 어떠한 행을 포함하는지, RANGE는 현재 행을 기준으로 어떤 범위를 포함하는지, 논리적인 범위를 명시하며 ORDER BY 절에 DATE형이나 표현식 등이 올 때 사용가능 하다.

n BETWEEN ... AND절은 분석을 위한 행(로우)들의 그룹의 시작점과 끝점을 이야기 한다.

n UNBOUNDED PRECEDING 구문은 window가 해당 파티션의 처음부터 시작된다는 것을 의미한다.

n UNBOUNDED FOLLOWING 구문은 window의 끝이 해당 파티션의 마지막 행임을 의미한다.

n UNBOUNDED는 한계를 두지 않고 해당 파티션의 끝까지를 의미한다.

n CURRENT ROW 구문은 해당 window의 시작이 현재행 임을 의미 한다.

n PRECEDING과 FOLLOWING은 현재 ROW에서 앞쪽인지 뒤쪽인지 방향성을 나타낸다. 만약 5 PRECEDING이라고 기술할 경우 현재행부터 앞5행을 범위로 해서 함수가 적용됨을 의미한다.

FIRST : 주어진 ORDER BY절에 의한 값중 첫번째를 가리킨다.

LAST : 주어진 ORDER BY절에 의한 값중 마지막을 가리킨다.

aggregate_function

KEEP

(DENSE_RANK FIRST[LAST] ORDER BY

expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ]

[, expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ]

]...

)

[ OVER query_partition_clause ]

n 윈도우 구에서 FIRST/LAST로 하나만 추출하는 경우에 사용하며 ORDER BY로 정렬을 시키고 DENSE_RANK FIRST/LAST구문으로 처음또는 마지막 한건을 가져온다. KEEP구는 FIRST, LAST분석함수가 오직 집계함수의 첫번째값 또는 마지막 값만을 리턴한다는 것을 의미한다.

n DENSE_RANK FIRST or DENSE_RANK LAST : 집계함수가 최소값(FIRST), 최대값(LAST)을 반환하는데 같은 값인 경우 동등한 순위를 반환한다. (동일한 값 다음 값은 동일한 값의 순위는 동일한 값을 가진행이 몇 개이든 관계없이 다음 순위가 된다. 만약 RANK라면 다음값의 순위는 동일한 값을 가진 값의 수에 따라 달라진다.)

-- EMP 테이블에서 수당(COMM) 내림차순으로 순위를 매기는데 1등이 몇 명이라도 다음 순위는 2등이 되도록 하라. (NULL값은 정렬시 가장 큰 값이 된다) SQL> SELECT EMPNO, ENAME, SAL, COMM, DENSE_RANK() OVER (ORDER BY COMM DESC) D_RANK FROM EMP; EMPNO ENAME SAL COMM D_RANK --------------------------------------------- 7369 SMITH 800 1 7782 CLARK 2450 1 …… 7839 KING 5000 1 7654 MARTIN 1250 1400 2 7521 WARD 1250 500 3 7499 ALLEN 1600 300 4 7844 TURNER 1500 0 5 -- 위예문에서 ORDER BY 기본 설정에 따라 NULL값이 맨 앞으로 왔는데 COMM 내림차순으로 하고맨 뒤로 보낼려면 NULLS LAST 구문을 사용하면 된다. SQL> SELECT EMPNO, ENAME, SAL, COMM, DENSE_RANK() OVER (ORDER BY COMM DESC NULLS LAST) D_RANK FROM EMP; EMPNO ENAME SAL COMM D_RANK --------------------------------------------- 7654 MARTIN 1250 1400 1 7521 WARD 1250 500 2 7499 ALLEN 1600 300 3 7844 TURNER 1500 0 4 7788 SCOTT 3000 5 …… 7369 SMITH 800 5 7782 CLARK 2450 5 -- 모든 사원출력 하면서 자신이 속한 부서에서 수당을 가장많이 받는 사원의 이름을 같이출력 SELECT EMPNO, ENAME, DEPTNO, MAX(ENAME) KEEP(DENSE_RANK FIRST ORDER BY comm DESC) OVER(partition by deptno) SAL FROM EMP; 7934 MILLER 10 MILLER 7782 CLARK 10 MILLER 7566 JONES 20 SCOTT 7788 SCOTT 20 SCOTT 7902 FORD 20 SCOTT 7876 ADAMS 20 SCOTT …… -- 부서별로 그룹핑해서 부서내에서 수당 가장많이 받는 사원명 출력, partiotion by를 사용하지 -- 않고 from절 다음에 group by를 사용하면 출력되는 결과 자체가 그룹핑 된다. SELECT DEPTNO, MAX(ENAME) KEEP(DENSE_RANK FIRST ORDER BY comm DESC) SAL FROM EMP GROUP BY DEPTNO; 10 MILLER 20 SCOTT 30 JAMES -- EMP 테이블에서 부서별로 급여의 최대값, 최대급여사원번호, 최소값, 최소급여사원번호를 출력 SELECT deptno, MAX(SAL) max_sal, MAX(empno) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_empno, MIN(sal) min_sal, MIN(empno) KEEP(DENSE_RANK FIRST ORDER BY sal Asc) as min_empno FROM emp GROUP BY DEPTNO ORDER BY DEPTNO DEPTNO MAX_SAL MAX_EMPNO MIN_SAL MIN_EMPNO -------------------------------------------------------------------------- 10 5000 7839 1300 7934 20 3000 7902 800 7369 30 2850 7698 950 7900 -- 부서별로 그룹핑 하여 출력하는데, 입사한지 가장 오래된 직원의 급여, 가장 최근에 입사한 직원의 급여를 출력 SELECT deptno, MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY hiredate) "Worst", MAX(sal) KEEP (DENSE_RANK LAST ORDER BY hiredate) "Best" FROM emp GROUP BY deptno ORDER BY deptno, "Worst", "Best"; DEPTNO Worst Best ---------- ---------- ---------- 10 2450 1300 20 800 3000 30 1600 950 -- 모든 사원들을 대상으로 그 사원이 속한 부서의 급여 최소, 최대, 급여합, 급여순위를 출력 SELECT ename, deptno, sal, MIN(sal) OVER (PARTITION BY deptno) "Worst", MAX(sal) OVER (PARTITION BY deptno) "Best", SUM(sal) OVER (PARTITION BY deptno) "SUM(SAL)", AVG(sal) OVER (PARTITION BY deptno) "AVG(SAL)", RANK() OVER (PARTITION BY deptno ORDER BY SAL DESC) "RANK" FROM emp ENAME DEPTNO SAL Worst Best SUM(SAL) AVG(SAL) RANK ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- KING 10 5000 1300 5000 8750 2916.66667 1 CLARK 10 2450 1300 5000 8750 2916.66667 2 …… MARTIN 30 1250 950 2850 9400 1566.66667 4 WARD 30 1250 950 2850 9400 1566.66667 4 JAMES 30 950 950 2850 9400 1566.66667 -- MYEMP1에서 직무(JOB)가 ‘CLERK’ 인 사원을 출력하면서 사번, 이름, 급여, 그 사원이 속한 부서의 급여 평균을 같이 출력하라.(급여 평균은 소수이하 첫째자리에서 절삭하세오) SELECT EMPNO, ENAME, SAL, TRUNC(AVG(SAL)) OVER (PARTITION BY DEPTNO) AVG_SAL FROM MYEMP1 WHERE JOB = 'CLERK'; Execution Plan ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 3906K| | 26294 (2)| 00:05:16 | | 1 | WINDOW SORT | | 100K| 3906K| 5528K| 26294 (2)| 00:05:16 | |* 2 | TABLE ACCESS FULL| MYEMP1 | 100K| 3906K| | 25264 (2)| 00:05:04 | ------------------------------------------------------------------------------------- -- MYEMP1, MYDEPT1에서 ‘1’번 부서의 사원을 출력하면서 사번, 이름, 급여, 부서명, 사원이 속한 부서의 급여 평균을 같이 출력하라.(급여 평균은 소수이하 첫째자리에서 절삭하세오) SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME, TRUNC(AVG(E.SAL) OVER (PARTITION BY E.DEPTNO)) AVG_SAL FROM MYEMP1 E, MYDEPT1 D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = '1'; EMPNO ENAME SAL DNAME AVG_SAL ---------------------------------------------------------------------------------------------- 541 홍길동541 541 개발2팀 547 545 가길동545 545 개발2팀 547 549 나길동549 549 개발2팀 547 553 홍길동553 553 개발2팀 547 …… ​ Execution Plan ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2510K| 100M| 52440 (1)| 00:10:30 | | 1 | WINDOW BUFFER | | 2510K| 100M| 52440 (1)| 00:10:30 | | 2 | NESTED LOOPS | | 2510K| 100M| 25232 (1)| 00:05:03 | |* 3 | TABLE ACCESS FULL| MYDEPT1 | 1 | 14 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| MYEMP1 | 2510K| 67M| 25229 (1)| 00:05:03 | ------------------------------------------------------------------------------- -- MYEMP1 테이블에서 가장 오래전에 입사한 사원중 급여를 가장 많이 받는 사원을 출력하시오. (아래 예문에서 ROW_NUMBER()대신 RANK()를 사용해도 된다) SELECT EMPNO, ENAME, SAL, HIREDATE FROM ( SELECT EMPNO, ENAME, SAL, HIREDATE, ROW_NUMBER() OVER (ORDER BY HIREDATE ASC, SAL DESC) RNUM FROM MYEMP1 ) WHERE RNUM = 1; 경 과: 00:00:15.67 EMPNO ENAME SAL HIREDATE ---------- -------------------- ---------- -------- 9999000 박길동9999000 3999000 15/05/01 Execution Plan ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10M| 953M| | 118K (1)| 00:23:44 | |* 1 | VIEW | | 10M| 953M| | 118K (1)| 00:23:44 | |* 2 | WINDOW SORT PUSHED RANK| | 10M| 333M| 459M| 118K (1)| 00:23:44 | | 3 | TABLE ACCESS FULL | MYEMP1 | 10M| 333M| | 25271 (2)| 00:05:04 | ------------------------------------------------------------------------------------------- -- 아래는 Range와 Row 차이에 관한 예문이다. -- Emp 테이블의 모든 사원을 출력하는데 해당 레코드의 입사일자를 기준으로 3개월전, 후의 데이터를 윈도우로 해서 분석하는 예문이다. Range는 OREDER BY 절에 DATE형이나 표현식이 올 때 사용가능하다. select empno, ename, hiredate, sal, avg(sal) over(order by hiredate range between interval'3' month preceding and interval '3' month following) sal_avg from emp; 7499 ALLEN 81/02/20 14639.54 6249.515 7521 WARD 81/02/22 1830.13 6249.515 7566 JONES 81/04/02 4355.7 5717.022 7698 BLAKE 81/05/01 4172.69 5717.022 7782 CLARK 81/06/09 3587.05 3577.8975 -- 아래는 입사일자 기준으로 90일 이전, 이후의 데이터를 분석을 위한 범위로 두었다. select empno, ename, hiredate, sal, avg(sal) over (order by hiredate range between 90 PRECEDING and 90 following) sal_avg from emp; 7499 ALLEN 81/02/20 14639.54 6249.515 7521 WARD 81/02/22 1830.13 6249.515 7566 JONES 81/04/02 4355.7 5717.022 7698 BLAKE 81/05/01 4172.69 5717.022 7782 CLARK 81/06/09 3587.05 4038.48 -- Rows를 사용하면 물리적인 행이 기준이 된다. 즉 앞뒤로 90개의 행이 윈도우가 된다. Emp 테이블은 전체 레코드가 14건이므로 모두 같은 같을 가지게 된다. select empno, ename, hiredate, sal, avg(sal) over (order by hiredate rows between 90 PRECEDING and 90 following) sal_avg from emp; 7499 ALLEN 81/02/20 14639.54 5025.5707 7521 WARD 81/02/22 1830.13 5025.5707 7566 JONES 81/04/02 4355.7 5025.5707 7698 BLAKE 81/05/01 4172.69 5025.5707 ……

7.3 분석함수(LISTAGG)

LISTAGG() : String Aggregation 함수로 하나의 칼럼의 값을 그룹핑하고 결합시키는 함수(오라클11g R2 이후 가능). 칼럼의 데이터를 특정칼럼을 기준으로 그룹핑하여 WITHIN GROUP절에서 정의한 순서대로 하나의 로우로 생성한다.

[기본 형식]

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

여러 행의 값을 그룹핑 하고 결합시키는 것이다. 예를 들면 아래와 같은 데이터를

DEPTNO ENAME

--------- ----------

10 CLARK

10 KING

10 MILLER

20 ADAMS

20 FORD

20 JONES

Deptno에 따라 그룹핑 하고 결합시킨다면 아래와 같이 될 것이다.

DEPTNO AGGREGATED_ENAMES

--------- -------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES

--ENAME을 나열하는데 ENAME 순서대로 DEPTNO별로 그룹핑 하여 출력 SQL> column employees format a50 SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD --EMP 테이블의 데이터를 출력하면서 같은 부서 사원들을 EMPLOYEES 칼럼에 보여주는 예 SQL> set pagesize 50 SQL> column employees format a40 SQL> SELECT deptno, ename, hiredate, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY hiredate) OVER (PARTITION BY deptno) AS employees FROM emp; DEPTNO ENAME HIREDATE EMPLOYEES ---------- ---------- -------- ---------------------------------------- 10 CLARK 81/06/09 CLARK,KING,MILLER 10 KING 81/11/17 CLARK,KING,MILLER …… 30 MARTIN 81/09/28 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 JAMES 81/12/03 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 14 개의 행이 선택되었습니다.

​