- Today
- Total
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
- 재능이의 돈버는 일기
- StresslessLife
- K_JIN2SM
- 소소한 일상
- My Life Style & Memory a Box
- Blog's generation
- 공감 스토리
- 취객의 프로그래밍 연구실
- Love Me
- Dream Archive
- 세상에 발자취를 남기다 by kongmingu
- hanglesoul
- 카마의 IT 초행길
- 느리게.
- 미친듯이 즐겨보자..
- Joo studio
- Gonna be insane
- 악 다 날아갔어!! 갇대밋! 왓더...
- xopowo05
- 맑은공기희망운동
- 엔지니어 독립운동
- 혁준 블로그
- Simple in Complex with Simple
- 무의식이 의식을 지배한다
드럼치는 프로그래머
[DBMS] OVER() 함수 본문
★ OVER()함수란?
OVER함수는 ORDER BY, GROUP BY 서브쿼리를 개선하기 위해 나온 함수라고 할 수 있습니다.
★ 전통 SQL 사용
SELECT YYMM, PRICE SELECT YYMM, SUM(TOT_PRICE) AS PRICE ) |
★ OVER 함수 이용
SELECT YYMM, SUM(TOT_PRICE) OVER(ORDER BY YYMM DESC) AS PRICE |
★ COUNT(*)OVER() 사용
실제 데이터와 함께 해당 테이블의 전체 로우 컬럼을 쉽고 편리하게 추출할 수 있다.
SELECT MENU_ID, MENU_NAME, COUNT(*) AS TOTALCOUNT FROM MENU_MG |
위의 쿼리를 실행하면 다음과 같은 오류 메시지가 나온다.
ORA-00937: not a single-group group function
다음 쿼리로 쉽게 전체 카운트를 추출할 수 있다.
SELECT MENU_ID, MENU_NAME, COUNT(*)OVER() AS TOTALCOUNT FROM MENU_MG |
★ OVER() 함수
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곱하면 됩니다.
======================================================================
COUNT(expr) OVER(analytic_clause)
- 같거나 작은 조건에 대한 갯수 반환
/* 부서번호가 50인 부서 지원에 대해 각 직원의 급여보다 같거나 적게 받는 사람에 대한 누적 합을 반환. */
SELECT employee_id, salary
, COUNT(*) over(ORDER BY salary) AS "Count"
FROM employees
WHERE department_id = '50';
SUM(expr) OVER(analytic_clause)
-- 같거나 작은 값들에 대한 누적
/* 특정 값을 누적하여 결과를 보여준다. */
SELECT employee_id, salary
, SUM(salary) over(ORDER BY employee_id)
FROM employees
WHERE 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_SALARY
WHERE 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 employees
WHERE department_id = '50'
★ ROW_NUMBER() OVER- 특정 컬럼 기준으로 순위정하기(행번호 부여하기)
SELECT T.MAIN_CODE -- , (ROW_NUMBER() OVER(PARTITION BY 중복조회컬럼 ORDER BY 정렬컬럼1, 정렬컬럼2, ...)) |
결과
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
2. ORDER BY DESC
3. NULLS FIRST : NULL 데이터를 먼저 출력.
4. NULLS LAST : NULL 데이터를 나중에 출력.
[출처] 오라클 OVER() 함수 |작성자 whitefre
'★─Programing > ☆─DBMS' 카테고리의 다른 글
[DBMS] MSSQL 에서 Getdate()함수로 얻은 포맷 자유로이 변환하기 (0) | 2016.12.20 |
---|---|
[DBMS] 오라클(Oracle) 테이블 존재 여부 확인하기 (0) | 2016.07.20 |
[DBMS] 오라클 날짜 더하기 (0) | 2016.07.04 |
[DBMS] 오라클에서 날짜/시간 사용하기 (0) | 2016.05.31 |
[DBMS] NVL함수 & DECODE함수 (0) | 2016.05.25 |