가장 기본적인 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
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곱하면 됩니다.