분석함수 및 KEEP을 정리해보자.

가장 기본적인 EMP테이블에서 부서별로 MAX와 MIN 값을 구해보자

<분석함수 버전>

SELECT A.deptno, A.empno, A.sal, B.empno, B.sal

  FROM (SELECT t.*,

  ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rn

      FROM EMP T

) A,

(SELECT t.*,

  ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal ASC) AS rn

      FROM EMP T

) B

 WHERE A.deptno = B.deptno

  AND A.rn = 1

  AND B.rn = 1

10 7839 5000 7934 1300

20 7788 3000 7369 800

30 7698 2850 7900 950

<MAX KEEP 버전> 

SELECT deptno,

MAX(empno) KEEP(DENSE_RANK FIRST  ORDER BY sal desc) as empno,

MAX(sal) AS max_sal,

MIN(empno) KEEP(DENSE_RANK FIRST  ORDER BY sal Asc) as empno,

min(sal) AS min_sal

  FROM EMP

 GROUP BY deptno

10 7839 5000 7934 1300

20 7902 3000 7369 800

30 7698 2850 7900 950

9001 9000

출처 : 오라클 KEEP
         (http://tortoiselfish.springnote.com/pages/5897823?print=1) 

keep(dense_rank {last|first} order by <>) , over(partition by XX)

 먼저 keep(dense_rank first order by )함수

A 컬럼 기준 정렬 후 지정 된 행의 B 컬럼 값 추출 하기

first나 last로 order by 한 로우에서 첫번째 혹은 마지막로우를 가져온다

 

MIN(B) (DENSE_RANK FIRST ORDER BY A [ASC|DESC])

MAX(B) (DENSE_RANK LAST ORDER BY A [ASC|DESC])

 

오라클 클럽에서 발췌한 퀴즈를 풀면서 알게 되었다는... 스칼라서브쿼리를 생각했었는데 저런 방법도 있었구나.. ㅎ

참 배울게 많다

 

--참고자료(http://3030.tistory.com/88)
구문
【형식】

집합함수 KEEP (
 DENSE_RANK FIRST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)

 

field1    field2
1    c
2    b
3    a

SQL> select MIN(field1), MIN(field2) KEEP(DENSE_RANK FIRST ORDER BY field1) AS field2 from table1
결과>1, c

SQL> select MAX(field1), MAX(field2) KEEP(DENSE_RANK LAST ORDER BY field1) AS field2 from table1
결과> 3 , a

 

 

--참고자료(http://tong.nate.com/rahxephon/35087891)

COUNT(*) OVER () : 전체행 카운트

COUNT(*) OVER (PARTITION BY 컬럼) : 그룹단위로 나누어 카운트

MAX(컬럼) OVER() : 전체행 중에 최고값

MAX(컬럼) OVER(PARTITION BY 컬럼) : 그룹내 최고값

MIN(컬럼) OVER () : 전체행 중에 최소값

MIN(컬럼) OVER (PARTITION BY 컬럼) : 그룹내 최소값

SUM(컬럼) OVER () : 전체행 합

SUM(컬럼) OVER (PARTITION BY 컬럼) : 그룹내 합

AVG(컬럼) OVER () : 전체행 평균

AVG(컬럼) OVER (PARTITION BY 컬럼) : 그룹내 평균

STDDEV(컬럼) OVER () : 전체행 표준편차

STDDEV(컬럼) OVER (PARTITION BY 컬럼) : 그룹내 표준편차

RATIO_TO_REPORT(컬럼) OVER () : 현재행값/SUM(전체행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.

RATIO_TO_REPORT(컬럼) OVER (PARTITION BY 컬럼) : 현재행값 / SUM(그룹행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.

 

오라클 클럽 문제

[퀴즈] 분석함수의 이해

구분1, 구분2, 날짜, 값으로 구성된 테이블이 있습니다.
구분1, 구분2 그룹으로 최종날짜와 금액합계를 구하고,
구분1별 최종날짜와 그에 해당하는 구분2값과 금액합계를 구하시오.

WITH t AS
(
SELECT 'A' gb1, '01' gb2, '20100601' dt, 10 v FROM dual
UNION ALL SELECT 'A', '01', '20100604', 20 FROM dual
UNION ALL SELECT 'A', '02', '20100603', 30 FROM dual
UNION ALL SELECT 'A', '03', '20100602', 40 FROM dual
UNION ALL SELECT 'B', '01', '20100601', 50 FROM dual
UNION ALL SELECT 'B', '02', '20100605', 60 FROM dual
UNION ALL SELECT 'B', '03', '20100603', 70 FROM dual
UNION ALL SELECT 'B', '04', '20100602', 80 FROM dual
)
SELECT * FROM t
;

 

 

 

 

==정답

SELECT gb1
    , gb2
    , MAX(dt) dt
    , SUM(v) v
    , MAX(MAX(dt)) OVER(PARTITION BY gb1) max_dt
    , MAX(gb2) KEEP(DENSE_RANK LAST ORDER BY MAX(dt)) OVER(PARTITION BY gb1) max_dt_gb2
    , MAX(SUM(v)) KEEP(DENSE_RANK LAST ORDER BY MAX(dt)) OVER(PARTITION BY gb1) max_dt_v
  FROM t
 GROUP BY gb1, gb2
 ORDER BY gb1, gb2
;

 

with t as
(
select 'A' bg1, '01' gb2 , '20100601' dt, 10 v from dual
union all select 'A','01','20100604', 20 from dual
union all select 'A','02','20100603', 30 from dual
union all select 'A','03','20100602', 40 from dual
union all select 'B','01','20100601', 50 from dual
union all select 'B','02','20100605', 60 from dual
union all select 'B','03','20100603', 70 from dual
union all select 'B','04','20100602', 80 from dual
)
select bg1, gb2, max(dt), sum(v),
(select max(dt) from t where bg1 =a.bg1) max_dt,
max(max(dt)) over(partition by bg1),
min(gb2) keep(dense_rank last order by max(dt)) over(partition by bg1) max_dt_gb2,
max(sum(v)) keep(dense_rank last order by max(dt)) over(partition by bg1) max_dt_v,
(select sum(v) from t where dt = (select max(dt) from t where bg1 =a.bg1)) nax_dt_v from t a
group by  bg1, gb2