★ OVER()함수란? OVER함수는 ORDER BY, GROUP BY 서브쿼리를 개선하기 위해 나온 함수라고 할 수 있습니다. ★ 전통 SQL 사용 SELECT YYMM, PRICE ★ OVER 함수 이용 SELECT YYMM, ★ COUNT(*)OVER() 사용 실제 데이터와 함께 해당 테이블의 전체 로우 컬럼을 쉽고 편리하게 추출할 수 있다. SELECT MENU_ID, MENU_NAME, COUNT(*) AS TOTALCOUNT 위의 쿼리를 실행하면 다음과 같은 오류 메시지가
나온다. 다음 쿼리로 쉽게 전체 카운트를 추출할 수 있다. SELECT MENU_ID, MENU_NAME, COUNT(*)OVER() AS TOTALCOUNT ★ OVER() 함수 COUNT(*)OVER() : 전체행 카운트
====================================================================== COUNT(expr) OVER(analytic_clause) - 같거나 작은 조건에 대한 갯수 반환 /* 부서번호가 50인 부서 지원에 대해 각 직원의 급여보다 같거나 적게 받는 사람에 대한 누적 합을 반환. */SELECT employee_id, salary, COUNT(*) over(ORDER BY salary) AS "Count"FROM employeesWHERE department_id = '50'; SUM(expr) OVER(analytic_clause)-- 같거나 작은 값들에 대한 누적/* 특정 값을 누적하여 결과를 보여준다. */SELECT employee_id, salary, SUM(salary) over(ORDER BY employee_id)FROM employeesWHERE department_id = '50'; /* 위 예제에 더해 부서별 누적 결과를 함께 보고자 한다. */SELECT employee_id, department_id, salary, SUM(salary) over(ORDER BY department_id, employee_id), SUM(salary) over(partition by department_id order by employee_id)FROM employees; RANK() OVER()--순위SELECT SAL_SNO, SAL_YYMM, SAL_TOTAL,RANK() OVER(ORDER BY SAL_TOTAL) AS "CONT"FROM TB_SALARYWHERE SAL_YYMM = '201101'; DENSE_RANK 함수 - 값의 그룹에서 값의 순위를 계산합니다. RANK와는 달리 같은 순위가 둘 이상 있어도 다음 순위는 1만 증가하여 반환. SELECT employee_id, department_id, salary, DENSE_RANK() over(PARTITION BY department_id ORDER BY salary DESC)FROM employeesWHERE department_id = '50' ★ ROW_NUMBER() OVER- 특정 컬럼 기준으로 순위정하기(행번호 부여하기) SELECT
T.MAIN_CODE 결과 MAIN_CODE GOODS_SEQ OFFER_MASTER_SEQ MODEL_CD RANK -------------------------------------------------------------- 1 1 1 AAA 1 1 1 2 AAA 2 1 2 4 BBB 3 1 2 5 BBB 4 2 1 1 AAA 1 2 1 4 AAA 2 2 2 5 BBB 3 2 2 6 BBB 4 2 3 7 CCC 5 2 3 9 CCC 6 ★ 특정 column의 값을 기준으로 레코드의 순서정하여 정렬하기. TEAM_CD SCORE PALY_DATE ------------------------------ AAA 90 2010/08/01 AAA 50 2010/08/02 AAA 60 2010/08/03 AAA 50 2010/08/04 BBB 50 2010/08/01 BBB 90 2010/08/02 BBB 95 2010/08/03 BBB 100 2010/08/04 방법 1. RANK() OVER SELECT T.TEAM_CD, T.SCORE, RANK() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE 결과 TEAM_CD SCORE RANK PALY_DATE ------------------------------ BBB 100 1 2010/08/04 BBB 95 2 2010/08/03 AAA 90 3 2010/08/01 BBB 90 3 2010/08/02 AAA 60 5 2010/08/03 AAA 50 6 2010/08/02 AAA 50 6 2010/08/04 BBB 50 6 2010/08/01 방법 2. ROW_NUMBER() OVER SELECT T.TEAM_CD, T.SCORE, ROW_NUMBER() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE 결과 TEAM_CD SCORE RANK PALY_DATE ------------------------------ BBB 100 1 2010/08/04 BBB 95 2 2010/08/03 AAA 90 3 2010/08/01 BBB 90 4 2010/08/02 AAA 60 5 2010/08/03 AAA 50 6 2010/08/02 AAA 50 7 2010/08/04 BBB 50 8 2010/08/01 방법 3. DENSE_RANK() OVER SELECT T.TEAM_CD, T.SCORE, DENSE_RANK() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE 결과 TEAM_CD SCORE RANK PALY_DATE ------------------------------ BBB 100 1 2010/08/04 BBB 95 2 2010/08/03 AAA 90 3 2010/08/01 BBB 90 3 2010/08/02 AAA 60 4 2010/08/03 AAA 50 5 2010/08/02 AAA 50 5 2010/08/04 BBB 50 5 2010/08/01 ★ 분석용 함수 RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용) DENSE_RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용 안함) ROW_NUMBER - 조건을 만족하는 모든 행의 번호를 제공 CUME_DIST - 분산값 PERCENT_RANK - 백분율 NTILE(n) - 전체 데이터 분포를 n-Buckets으로 나누어 표시 FIRST_VALUE - 정렬된 값중에서 첫번째 값을 반환. LAST_VALUE - 정렬된 값중에서 마지막 값을 반환. ★ OVER() 에 사용되는 OPTION 1. PARTITION BY |