-- 분석 함수
-- RANK OVER()
-- ROW_NUMBER() OVER() ** 중요 제일 많은 씀 **
-- 순위를 구함.
-- RANK() OVER()는 동일 순위인 경우 1,1,3, 형식으로 출력하지만
-- ROW_NUMBER() OVER()는 동일 순위인 경우 1,2,3, 형식으로 출력
-- DENSE_RANK() OVER()는 동일 순위인 경우 1,1,2 형식으로 출력
-- ROW_NUMBER() OVER()는 매우매우매우 중요한 함수이므로 반드시 알아 둘것 !!!!!!!!!!!!!!!!!!!!!!!
--기본급 내림차순으로 순위를 구하기
SELECT name, basicpay, RANK() OVER(ORDER BY basicpay DESC) 순위 FROM insa; -- 비교데이타가 똑같으면 순위를 건너뜀
SELECT name, basicpay, ROW_NUMBER() OVER(ORDER BY basicpay DESC) 순위 FROM insa; -- 비교데이타가 똑같아도 순위를 차례대로 매김
-- 기본급 내림차순으로 순위. 단, 기본급이 같으면 수당 내림차순.
SELECT name, basicpay, sudang, RANK() OVER(ORDER BY basicpay DESC, sudang DESC) 순위 FROM insa;
SELECT name, basicpay, sudang, ROW_NUMBER() OVER(ORDER BY basicpay DESC, sudang DESC) 순위 FROM insa;
-- 부서별 기본급의 내림차순 순위 구하기
SELECT name, basicpay, buseo,
RANK() OVER(PARTITION BY buseo ORDER BY basicpay DESC, sudang DESC) 순위
FROM insa;
SELECT name, basicpay, buseo,
ROW_NUMBER() OVER(PARTITION BY buseo ORDER BY basicpay DESC, sudang DESC) 순위
FROM insa;
-- 출신도별 부서별 순위(학년의 반별)
SELECT name, basicpay, city, buseo,
RANK() OVER(PARTITION BY city ORDER BY basicpay DESC, sudang DESC) 순위
FROM insa;
-- 여자 인원수가 가장 많은 부서는?
SELECT buseo, COUNT(*) FROM insa
WHERE SUBSTR(ssn,8,1) IN (2,4,6)
GROUP BY buseo;
SELECT buseo FROM(
SELECT buseo, COUNT(*) cnt, RANK() OVER(ORDER BY COUNT(*) DESC) 순위
FROM insa WHERE SUBSTR(ssn,8,1) IN (2,4,6) GROUP BY buseo
) WHERE 순위 =1;
-- 기본급여 많이 받는 1~10등 까지 출력(name, basicpay)
SELECT name, basicpay,RANK() OVER(ORDER BY basicpay DESC) 순위 FROM(
SELECT name, basicpay, RANK() OVER(ORDER BY basicpay DESC) 순위 FROM insa
) WHERE 순위 <=10;
-- 기본급 상위 10% 출력(name, basicpay)
SELECT name, basicpay FROM(
SELECT name, basicpay, RANK() OVER(ORDER BY basicpay DESC) 순위 FROM insa
)WHERE 순위<=TRUNC((SELECT COUNT(*) FROM insa) *0.1);
-- 기본급 하위 10% 출력(
SELECT name, basicpay FROM(
SELECT name, basicpay, RANK() OVER(ORDER BY basicpay) 순위 FROM insa
)WHERE 순위<=TRUNC((SELECT COUNT(*) FROM insa) *0.1);
-- 부서별 기본급여 가장 높은 사람들 출력( name, buseo, jikwi)
SELECT name, buseo,basicpay, RANK() OVER(PARTITION BY buseo ORDER BY basicpay DESC) 순위
FROM insa; -- 일단 부서별 순위를 매기는 쿼리
SELECT name, buseo,basicpay FROM(
SELECT name, buseo,basicpay,
RANK() OVER(PARTITION BY buseo ORDER BY basicpay DESC) 순위
FROM insa
) WHERE 순위=1;
-- ***** 나중에 게시판 작성 할 때 사용하는 쿼리 형식이므로 반드 암기 해야함. *****
-- ROW_NUMBER()를 이용한 쿼리가 아래 ROWNUM을 사용한 쿼리보다 우수
SELECT num,name,basicpay FROM(
SELECT num, name, basicpay, ROW_NUMBER() OVER(ORDER BY num DESC) rnum FROM insa
) WHERE rnum>= 10 AND rnum <=15 ORDER BY num DESC;
SELECT num,name,basicpay FROM(
SELECT ROWNUM rnum, num, name, basicpay FROM(
SELECT num, name, basicpay FROM insa ORDER BY num DESC
)
)WHERE rnum>=10 AND rnum<=15;
출처 : http://tibang.tistory.com/
