[개요]
Oracle 8i 버젼부터 복잡한 비지니스 로직을 구현하고 Data Warehouse에 적용 하기 위해
Analytic Function을 사용할 수 있습니다. 오라클 8.1.6 버젼에 일부 Function이 적용되며
8.1.7 버젼에서는 현재 27개의 Function 이 지원됩니다.
기존 RDB 상에서는 칼럼과 칼럼 간의 연산, 비교, 연결은 쉬운 반면, 행과 행간의 관계를 정의
하거나, 비교, 연산하는 것을 하나의 SQL 문에서 처리하는 것이 매우 어려운 문제였기 때문에,
3 G/L프로그램을 작성하거나, 인라인 뷰를 이용해 복잡한 SQL문을 작성해야 하던 것을
Analytic Function을 활용하면 하나의 SQL문으로 쉽게 해결 할 수 있습니다.
Analytic Function은 다른 함수와는 달리 중첩(nest)해서 사용하지는 못하지만, 서브쿼리에서는
사용할 수 있습니다. 기존에 사용하던 함수(주로 그룹함수) 도 있고, 새로이 Analytic Function 전용으로 만들어진 함수도 있습니다.
* Syntax
Select Analytic_Function ( arguments )
OVER( [ Partition By 칼럼 ] [ Order By 절 ] [ Windowing 절] )
From 테이블 명;
Analytic_Function : 기존에 사용하던 함수도 있고, 새로이 A/F 용으로
만들어진 함수도 있습니다.
Arguments : 펑션에 따라 0~3개의 인수가 지정될 수 있습니다.
Partition By : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있습니다.
Order By : 어떤 항목에 대해 순위를 지정할 지 Order By 절을
기술합니다.
Windowing : 펑션의 대상이 되는 행 기준으로 범위를 강력하게 지정
할 수 있습니다.
* Windowing 절
1. ROWS/RANGE UNBOUNDED PRECEDING/
CURRENT ROW/
value_expr PRECEDING
2. ROWS/RANGE BETWEEN UNBOUNDED PRECEDING/
CURRENT ROW/
value_expr PRECEDING/FOLLOWING
AND UNBOUNDED FOLLOWING
CURRENT ROW/
value_expr PRECEDING/FOLLOWING
그룹내 데이터 순위 관련
======================
→ RANK(), DENSE_RANK(), ROW_NUMBER() 함수
SELECT DEPTNO, ENAME,SAL,
ROW_NUMBER() OVER(PARTITION BY DEPTNO
ORDER BY SAL DESC) RK1,
RANK() OVER(PARTITION BY DEPTNO
ORDER BY SAL DESC) RK2,
DENSE_RANK() OVER(PARTITION BY DEPTNO
ORDER BY SAL DESC) RK3
FROM EMP;
일반 그룹 함수 관련
==================
SELECT ENAME,SAL,
COUNT(*) OVER(ORDER BY SAL
ROWS UNBOUNDED PRECEDING) C1,
COUNT(*) OVER(ORDER BY SAL
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) C2,
COUNT(*) OVER(ORDER BY SAL
ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) C3
FROM EMP;
SELECT ENAME,SAL,
COUNT(*) OVER(ORDER BY SAL
RANGE UNBOUNDED PRECEDING) C1,
COUNT(*) OVER(ORDER BY SAL
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) C2,
COUNT(*) OVER(ORDER BY SAL
RANGE BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) C3
FROM EMP;
SELECT ENAME,SAL,
COUNT(*) OVER(ORDER BY SAL
RANGE BETWEEN 50 PRECEDING
AND 150 FOLLOWING) AS m_cnt
FROM EMP;
- RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
현재 행의 값을 기준으로 -50 에서 +150 까지의 값을 가진
파티션 내의 모든 행이 대상이 된다.
SELECT MGR,ENAME,HIREDATE,SAL,
AVG(SAL) OVER(PARTITION BY MGR
ORDER BY HIREDATE
ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) AS c_mavg
FROM EMP;
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의
한 건을 범위로 지정
SELECT MGR,EMPNO,SAL,
SUM(SAL) OVER(PARTITION BY MGR
ORDER BY EMPNO
RANGE UNBOUNDED PRECEDING) AS l_csum
FROM EMP;
- RANGE UNBOUNDED PRECEDING
현재 행을 기준으로 파티션 내이 첫 번째 행까지의 범위를 지정
SELECT MGR,ENAME,SAL,
MAX(SAL) OVER(PARTITION BY MGR) AS mgr_max
FROM EMP;
SELECT MGR,ENAME,SAL
FROM ( SELECT MGR,ENAME,SAL,
MAX(SAL) OVER(PARTITION BY MGR) AS mgr_max
FROM EMP)
WHERE SAL = mgr_max;
SELECT MGR,ENAME,HIREDATE,SAL,
MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE
RANGE UNBOUNDED PRECEDING) AS p_cmin
FROM EMP;
그룹내 데이터 순서 관련
======================
1.FIRST_VALUE
파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
SELECT DEPTNO,ENAME,SAL,
FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO
ORDER BY SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS FV
FROM EMP;
2.LAST_VALUE
파티션별 윈도우에서 가장 나중 나온 값을 구한다.
SELECT DEPTNO,ENAME,SAL,
LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO
ORDER BY SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS LV
FROM EMP;
3.LAG
파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
SELECT ENAME,TO_CHAR(HIREDATE,'YYYYMMDD')HIREDATE,
TO_CHAR(LAG(HIREDATE)
OVER(ORDER BY HIREDATE),'YYYYMMDD') AS "PrevHire"
FROM EMP;
SELECT ENAME,TO_CHAR(HIREDATE,'YYYYMMDD') HIREDATE,
TO_CHAR(LAG(HIREDATE,1,TO_DATE('99991231','YYYYMMDD'))
OVER(ORDER BY HIREDATE),'YYYYMMDD') AS "NextHire"
FROM EMP;
4.LEAD
파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.
SELECT ENAME,TO_CHAR(HIREDATE,'YYYYMMDD') HIREDATE,
TO_CHAR(LEAD(HIREDATE)
OVER(ORDER BY HIREDATE),'YYYYMMDD') AS "NextHire"
FROM EMP;
SELECT ENAME,TO_CHAR(HIREDATE,'YYYYMMDD') HIREDATE,
TO_CHAR(LEAD(HIREDATE,1,TO_DATE('99991231','YYYYMMDD'))
OVER(ORDER BY HIREDATE),'YYYYMMDD') AS "NextHire"
FROM EMP;
그룹 내 비율 관련
=================
→ RATIO_TO_REPORT 함수
EMP 테이블에서 업무별로 사원들의 업무별 급여총액을 기준으로
해당 사원의 급여액의 백분율(소수점으로)을 구함.
SELECT JOB, ENAME, SAL, RATIO_TO_REPORT(SAL)
OVER(PARTITION BY JOB) AS RR
FROM EMP
ORDER BY JOB, ENAME;
----------------------------------------------------------------
[OVER 함수를 사용하지 않은 쿼리]
SELECT YYMM, PRICE
FROM
(SELECT YYMM, SUM(TOT_PRICE) AS PRICE
GROUP BY YYMM
ORDER BY YYMM DESC
FROM TABLE1)
[OVER 함수를 사용한 쿼리]
SELECT YYMM
, SUM(TOT_PRICE) OVER (ORDER BY YYMM DESC) AS PRICE
FROM
TABLE1
[예제]
SELECT
HEAT_NO KEY1
,HMLDL_USE_SNO KEY2
,HMLDL_PURG_PLG_USE_HR2 VALUE1
,SUM(VALUE1) OVER() SUM1
,SUM(VALUE1) OVER(ORDER BY KEY1) SUM2
,SUM(VALUE1) OVER(PARTITION BY KEY2 ORDER BY KEY1) SUM3
,SUM(VALUE1) OVER(PARTITION BY KEY2) SUM4
FROM TB_TEST
ORDER BY KEY1
1. SUM1 : VALUE1 값 전체 합계를 구한다.
2. SUM2 : KEY1 값이 동일한 항목별 합계를 구한다.(ROW단위로 합계를 누계해 간다.)
3. SUM3 : KEY1 값이 동일한 항목별 합계를 KEY2값 별로 구분하여 구한다.
4. SUM4 : KEY2 값이 동일한 항목별 합계를 구한다.(ROW단위로 구분하지 않고 KEY2값이 동일한 항목별 VALUE1의 합계를 구한다.)
| KEY1 | KEY2 | VALUE | SUM1 | SUM2 | SUM3 | SUM4 |
| M00001 | 1 | 43 | 1204 | 43 | 43 | 1032 |
| M00002 | 2 | 43 | 1204 | 129 | 43 | 86 |
| M00002 | 1 | 43 | 1204 | 129 | 86 | 1032 |
| M00003 | 1 | 43 | 1204 | 215 | 129 | 1032 |
| M00003 | 3 | 43 | 1204 | 215 | 43 | 86 |
| M00004 | 1 | 43 | 1204 | 301 | 215 | 1032 |
| M00004 | 1 | 43 | 1204 | 301 | 215 | 1032 |
| M00005 | 1 | 43 | 1204 | 387 | 258 | 1032 |
| M00005 | 2 | 43 | 1204 | 387 | 86 | 86 |
| M00006 | 1 | 43 | 1204 | 473 | 301 | 1032 |
| M00006 | 3 | 43 | 1204 | 473 | 86 | 86 |
| M00007 | 1 | 43 | 1204 | 559 | 387 | 1032 |
| M00007 | 1 | 43 | 1204 | 559 | 387 | 1032 |
| M00008 | 1 | 43 | 1204 | 645 | 473 | 1032 |
| M00008 | 1 | 43 | 1204 | 645 | 473 | 1032 |
| M00009 | 1 | 43 | 1204 | 731 | 559 | 1032 |
| M00009 | 1 | 43 | 1204 | 731 | 559 | 1032 |
| M00010 | 1 | 43 | 1204 | 817 | 645 | 1032 |
| M00010 | 1 | 43 | 1204 | 817 | 645 | 1032 |
| M00011 | 1 | 43 | 1204 | 903 | 731 | 1032 |
| M00011 | 1 | 43 | 1204 | 903 | 731 | 1032 |
| M00012 | 1 | 43 | 1204 | 989 | 817 | 1032 |
| M00012 | 1 | 43 | 1204 | 989 | 817 | 1032 |
| M00013 | 1 | 43 | 1204 | 1075 | 903 | 1032 |
| M00013 | 1 | 43 | 1204 | 1075 | 903 | 1032 |
| M00014 | 1 | 43 | 1204 | 1161 | 989 | 1032 |
| M00014 | 1 | 43 | 1204 | 1161 | 989 | 1032 |
| M00015 | 1 | 43 | 1204 | 1204 | 1032 | 1032 |
[예제]
SELECT A
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
[예제1]
SELECT SUM(a) OVER (PARTITION BY iden_no
[예제2]
SELECT
BB.*
,SUM(BB.FIELD1) OVER(PARTITION BY KEY_FIELD1,KEY_FIELD2,KEY_FIELD3) FIELD1_SUM
,SUM(BB.FIELD2) OVER(PARTITION BY KEY_FIELD1,KEY_FIELD2,KEY_FIELD3) FIELD1_SUM
,SUM(BB.FIELD3) OVER(PARTITION BY KEY_FIELD1,KEY_FIELD2,KEY_FIELD3) FIELD1_SUM
FROM
(
SELECT
B.SEQ
,DECODE(B.SEQ,1,A.KEY_FIELD11,2,A.KEY_FIELD12,3,A.KEY_FIELD13) AS KEY_FIELD1
,'H' || DECODE(B.SEQ,1,A.BF_NO1,2,A.BF_NO2,3,A.BF_NO3) AS KEY_FIELD2
,DECODE(B.SEQ,1,A.KEY_FIELD31,2,A.KEY_FIELD32,3,A.KEY_FIELD33) AS KEY_FIELD3
,A.HEAT_NO
,A.FIELD1 /* -- 용선장입래들수선총량 */
,A.FIELD2 /* -- 장입래들용선수선량 */
,A.FIELD3 /* -- 장입래들Slag수선량 */
FROM TB_HM_HMTLWR A
,(SELECT 1 AS SEQ FROM DUAL UNION ALL
SELECT 2 AS SEQ FROM DUAL UNION ALL
SELECT 3 AS SEQ FROM DUAL
) B
) BB
'SQL > ORACLE함수' 카테고리의 다른 글
| Oracle | 오라클 함수 정리 ① (0) | 2014.06.11 |
|---|---|
| 특정 문자와문자 사이의 내용 짜르기 (0) | 2014.06.11 |
| start with를 사용한 순환 쿼리(Tree 구조) (0) | 2014.06.11 |
| [링크스크랩] 오라클 10g의 purge, flashback 기능 (0) | 2014.06.11 |
| ORACLE 명령어 정리 (0) | 2014.06.11 |
| [본문스크랩] 오라클 종합 요약 (0) | 2014.06.11 |
| 오라클 간단한 프로시져 CURSOR,FOR 문 사용하기 (0) | 2014.06.11 |
| [DBMS비교] Oracle함수, MS-SQL함수 비교 (0) | 2014.06.11 |
nineDeveloper
안녕하세요 현직 개발자 입니다 ~ 빠르게 변화하는 세상에 뒤쳐지지 않도록 우리모두 열심히 공부합시다 ~! 개발공부는 넘나 재미있는 것~!