분석함수 및 KEEP을 정리해보자.
<분석함수 버전>
<MAX KEEP 버전>
출처 : 오라클 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)
오라클 클럽 문제
[퀴즈] 분석함수의 이해
구분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
'1.DB > SQL' 카테고리의 다른 글
NULLS FIRST/LAST (0) | 2011.04.25 |
---|
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곱하면 됩니다.