OVER함수 PARTITION BY 절 조사

COD기준으로 DPT의 순위, 누적합, 최소값, 최대값을 표기, 바로상위행 NAM값, 바로하위행 NAM값을
COD, NAMS, DPT, RankV, MinV, MaxV, URowV, DRowV으로 작성

with TB AS (
SELECT 'DDDD' AS COD, 'aaaa' AS NAM, null AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'bbbb' AS NAM, '200' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'cccc' AS NAM, '300' AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'dddd' AS NAM, '100' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'eeee' AS NAM, '600' AS DPT
FROM DUAL
UNION ALL
SELECT 'CCCC' AS COD, 'ffff' AS NAM, '300' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'gggg' AS NAM, '400' AS DPT
FROM DUAL
UNION ALL
SELECT 'CCCC' AS COD, 'hhhh' AS NAM, '900' AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'iiii' AS NAM, null AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'jjjj' AS NAM, '800' AS DPT
FROM DUAL
)
SELECT *
FROM TB
order by 1,2,3

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

* 쿼리

WITH TB AS (
SELECT 'DDDD' AS COD, 'AAAA' AS NAM, NULL AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'BBBB' AS NAM, '200' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'CCCC' AS NAM, '300' AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'DDDD' AS NAM, '100' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'EEEE' AS NAM, '600' AS DPT
FROM DUAL
UNION ALL
SELECT 'CCCC' AS COD, 'FFFF' AS NAM, '300' AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'GGGG' AS NAM, '400' AS DPT
FROM DUAL
UNION ALL
SELECT 'CCCC' AS COD, 'HHHH' AS NAM, '900' AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'IIII' AS NAM, NULL AS DPT
FROM DUAL
UNION ALL
SELECT 'BBBB' AS COD, 'JJJJ' AS NAM, '800' AS DPT
FROM DUAL
)
SELECT COD, NAM, DPT
     , ROW_NUMBER() OVER (PARTITION BY COD ORDER BY DPT DESC) AS RANKV --그룹별 순위
     , MIN(DPT) OVER (PARTITION BY COD) AS MINV                                      --그룹별 최소값
     , MAX(DPT) OVER (PARTITION BY COD) AS MAXV                                    --그룹별 최대값    
     , SUM(DPT) OVER (PARTITION BY COD ORDER BY DPT DESC) AS SUMS        -- 그룹별 누적합
     , FIRST_VALUE(NAM) OVER (PARTITION BY COD) AS UROWV                     --그룹별 첫번째 값을 조회
     , LAST_VALUE(NAM) OVER (PARTITION BY COD) AS DROWV                     --그룹별의 마지막 값을 조회
     , LAG(NAM, 1, NULL) OVER (PARTITION BY COD ORDER BY COD) AS LAGV     --상위행 NAM 값을 조회
     , LEAD(NAM, 1, NULL) OVER (PARTITION BY COD ORDER BY COD) AS LEADV     --하위행 NAM 값을 조회
FROM TB;

*결과값

> OVER 이후 스크립트를 WINODW절 이라고 한다.

> 쿼리를 실행하면, ORACLE 내부적으로
SELECT값을 View메모리에 올리고,
그중에 OVER로 정의된 부분(WINDOW절의 조건 결과값)을 별도의 메모리영역에 올려둔 상태로
행결과값을 표현시, 다시 WINODW절의 조건에 맞는 처리된 결과값을 보인다.

> OVER는 기본형식은 OVER (order by ~) 이다.
> OVER (order by ~) 정의된 영역을
재구역을 할당 할 필요가 있을때,  PARTITION BY ~
행단위 할당이 필요할때,  ROW ~ BETWEEN ~ AND ~
범위단위 할당이 필요할때,  RANGE ~BETWEEN ~ AND ~를 사용한다.