순위함수(분석함수)
특정 컬럼을 기준으로 크기에 따른 순위를 구하는 함수
RANK() OVER, DENSE_RANK() OVER, ROW_NUMBER() OVER
그룹내에서 순위는 RANK() OVER(PARTITION ~) 함수 사용
1) RANK() OVER
. 순위부여시 중복값(같은값)이 발생되면 중복 값의 갯수만큼 건너 뛰고 다음 순위 부여
ex) 90,80,80,80,70 =>(1,2,2,2,5..)
. SELECT 절에 사용
(사용형식)
RANK() OVER(ORDER BY 컬럼명 (ASC|DESC)) (AS 별칭)
사용예) 사원테이블에서 80번 부서직원 중 입사년도가 가장 빠른 직원부터 순위를 부여하여 조회하시오
Alias는 사원번호, 사원명, 입사일자, 순위
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일자,
RANK() OVER(ORDER BY HIRE_DATE ASC) AS 순위
FROM HR.EMP
WHERE DEPARTMENT_ID=80;
2) DENSE_RANK() OVER
. 중복순위 발생 후 다음 순위를 연속된 값을 부여
ex) 90,80,80,80,70 =>1,2,2,2,3...
사용예) 상품테이블에서 매출가격 순으로 등수를 DENSE_PARK() 함수 형식으로 부여하시오
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_PRICE AS 판매가,
RANK() OVER(ORDER BY PROD_PRICE DESC)AS 순위1,
DENSE_RANK() OVER(ORDER BY PROD_PRICE DESC) AS 순위2
FROM PROD;
3) ROW_NUMBER() OVER
젤많이씀
중복값에 관계없이 SEQUENCE(순차적인 순위 값) 값을 반환
ex)90,80,80,80,70 => 1,2,3,4,5,....
사용예) 상품테이블에서 매출가격 순으로 등수를 DENSE_PARK() 함수 형식으로 부여하시오
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_PRICE AS 판매가,
RANK() OVER(ORDER BY PROD_PRICE DESC)AS 순위1,
DENSE_RANK() OVER(ORDER BY PROD_PRICE DESC) AS 순위2,
ROW_NUMBER() OVER(ORDER BY PROD_PRICE DESC) AS 순위3
FROM PROD;
4) 그룹내 순위
. 그룹별로 순위를 구하기 위해 사용되는 순위함수
(사용형식)
RANK() OVER(PARTITION BY 컬렴명1[,컬렴명2,...]
ORDER BY 컬럼명11[,컬럼명12,...][ASC|DESC])
-'컬럼명1[,컬럼명2,...]' : 그룹화된 기준 컬럼명
-'컬럼명11[,컬럼명12,...]': 정렬의 기준 컬럼명
사용예) 상품테이블에서 분류별로 상품의 판매가격 순으로 등수를 부여하시오
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_LGU AS 분류코드,
PROD_PRICE AS 가격,
RANK() OVER(PARTITION BY PROD_LGU
ORDER BY PROD_PRICE DESC) 순위
FROM PROD;