"분석함수"의 두 판 사이의 차이
DB CAFE
(새 문서: = 분석 함수 = ----------------------------------------------------- == 분석함수 일반 == ----------------------------------------------------- === ROW_NUMBER 함수 === SELE...) |
|||
| 4번째 줄: | 4번째 줄: | ||
== 분석함수 일반 == | == 분석함수 일반 == | ||
----------------------------------------------------- | ----------------------------------------------------- | ||
| + | |||
| + | === CUME_DIST === | ||
| + | 값 집합에서 값의 누적 분포 계산 | ||
| + | === DENSE_RANK === | ||
| + | 순위 값에 간격이없는 순서가 지정된 행 집합에서 행의 순위를 계산합니다. | ||
| + | === FIRST_VALUE === | ||
| + | 지정된 창 프레임에서 첫 번째 행의 값을 가져옵니다. | ||
| + | === LAG === | ||
| + | 셀프조인을 사용하지 않고 현재 행 앞에 오는 지정된 물리적 오프셋에서 행에 대한 액세스를 제공합니다. | ||
| + | |||
| + | === LAST_VALUE === | ||
| + | 지정된 창 프레임에서 마지막 행의 값을 가져옵니다. | ||
| + | === LEAD === | ||
| + | 셀프조인을 사용하지 않고 현재 행을 따르는 주어진 물리적 오프셋에서 행에 대한 액세스를 제공 | ||
| + | === NTH_VALUE === | ||
| + | 값 집합에서 N 번째 값을 가져옵니다. | ||
| + | |||
| + | === NTILE === | ||
| + | 순서가 지정된 행 집합을 여러 버킷으로 나누고 각 행에 적절한 버킷 번호를 할당 | ||
| + | === PERCENT_RANK === | ||
| + | 일련의 값에서 값의 순위를 계산 | ||
| + | === RANK === | ||
| + | 일련의 값에서 값의 순위를 계산 | ||
| + | === ROW_NUMBER === | ||
| + | 파티션에서 또는 전체 결과에서 1부터 시작하여 고유 한 순차 정수를 각 행에 할당 | ||
| + | |||
=== ROW_NUMBER 함수 === | === ROW_NUMBER 함수 === | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
2020년 7월 25일 (토) 23:15 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 분석 함수[편집]
1.1 분석함수 일반[편집]
1.1.1 CUME_DIST[편집]
값 집합에서 값의 누적 분포 계산
1.1.2 DENSE_RANK[편집]
순위 값에 간격이없는 순서가 지정된 행 집합에서 행의 순위를 계산합니다.
1.1.3 FIRST_VALUE[편집]
지정된 창 프레임에서 첫 번째 행의 값을 가져옵니다.
1.1.4 LAG[편집]
셀프조인을 사용하지 않고 현재 행 앞에 오는 지정된 물리적 오프셋에서 행에 대한 액세스를 제공합니다.
1.1.5 LAST_VALUE[편집]
지정된 창 프레임에서 마지막 행의 값을 가져옵니다.
1.1.6 LEAD[편집]
셀프조인을 사용하지 않고 현재 행을 따르는 주어진 물리적 오프셋에서 행에 대한 액세스를 제공
1.1.7 NTH_VALUE[편집]
값 집합에서 N 번째 값을 가져옵니다.
1.1.8 NTILE[편집]
순서가 지정된 행 집합을 여러 버킷으로 나누고 각 행에 적절한 버킷 번호를 할당
1.1.9 PERCENT_RANK[편집]
일련의 값에서 값의 순위를 계산
1.1.10 RANK[편집]
일련의 값에서 값의 순위를 계산
1.1.11 ROW_NUMBER[편집]
파티션에서 또는 전체 결과에서 1부터 시작하여 고유 한 순차 정수를 각 행에 할당
1.1.12 ROW_NUMBER 함수[편집]
SELECT A.부서코드
,A.사원번호
,ROW_NUMBER() OVER (PARTITION BY A.부서코드
ORDER BY A.사원번호) 사원순번
FROM 사원 A WHERE 부서코드 IN ('10', '20') ORDER BY A.부서코드
,A.사원번호
1.1.13 ROW_NUMBER VS RANK[편집]
SELECT A.부서코드
,A.월급여
,A.사원번호
,ROW_NUMBER() OVER (PARTITION BY A.부서코드 ORDER BY A.월급여) 사원순번1
,RANK() OVER (PARTITION BY A.부서코드 ORDER BY A.월급여) 사원순번2
,ROW_NUMBER() OVER (PARTITION BY A.부서코드 ORDER BY A.월급여,A.사원번호) 사원순번3
,RANK() OVER (PARTITION BY A.부서코드 ORDER BY A.월급여,A.사원번호) 사원순번4
FROM 사원 A WHERE 부서코드 IN ('10', '20') ORDER BY A.부서코드
,A.월급여
,A.사원번호
-- 2) COUNT 함수와 SUM 함수
1.1.14 COUNT[편집]
SELECT A.부서코드
,A.사원번호
,A.퇴사일자
,A.직급
,COUNT(*) OVER () 전체사원수
,COUNT(*) OVER (PARTITION BY A.부서코드) 부서별사원수
,COUNT(A.퇴사일자) OVER (PARTITION BY A.부서코드) 부서별퇴사자수
,COUNT(DISTINCT A.직급) OVER (PARTITION BY A.부서코드) 부서별직급수
,COUNT(*) OVER (PARTITION BY A.부서코드
ORDER BY A.사원번호) 부서별사원수누계
FROM 사원 A WHERE 부서코드 IN ('10', '20') ORDER BY A.부서코드
,A.사원번호
1.1.15 SUM[편집]
SELECT A.부서코드
,A.사원번호
,A.월급여
,SUM(A.월급여) OVER () 전체급여합
,SUM(A.월급여) OVER (PARTITION BY A.부서코드) 부서별급여합
,SUM(A.월급여) OVER (PARTITION BY A.부서코드 ORDER BY A.월급여 ) 부서별급여누계1
,SUM(A.월급여) OVER (PARTITION BY A.부서코드 ORDER BY A.월급여,A.사원번호) 부서별급여누계2
FROM 사원 A WHERE 부서코드 IN ('10', '20') ORDER BY A.부서코드
,A.월급여
,A.사원번호
1.1.16 LAG 함수와 LEAD 함수[편집]
SELECT A.사원번호
,A.월급여
,LAG (A.월급여) OVER (ORDER BY A.월급여 DESC,A.사원번호) 이전급여
,LEAD(A.월급여) OVER (ORDER BY A.월급여 DESC,A.사원번호) 다음급여
,LAG (A.월급여) OVER (ORDER BY A.월급여 DESC,A.사원번호) - A.월급여 이전급여와의차이
,A.월급여 - LEAD(A.월급여) OVER (ORDER BY A.월급여 DESC,A.사원번호) 다음급여와의차이
FROM 사원 A WHERE A.부서코드 = '20' ORDER BY A.월급여 DESC
,A.사원번호
1.2 분석함수의 활용[편집]
1.2.1 1:M 관계의 조인에서 부모테이블이 기준집합[편집]
SELECT A.사원번호
,A.사원명
,B.발령일자
,B.직급
,B.부서코드
FROM 사원 A
,(
SELECT B.사원번호
,B.발령일자
,B.직급
,B.부서코드
,ROW_NUMBER() OVER (PARTITION BY B.사원번호 ORDER BY B.이력순번 DESC)
RN
FROM 발령이력 B
) B
WHERE B.사원번호 = A.사원번호 AND B.RN = 1
1.2.2 집계 연산[편집]
SELECT A.부서코드
,A.사원번호
,A.월급여
,ROUND(A.월급여/A.부서급여합, 2) 급여비중
,ROUND(RATIO_TO_REPORT(A.월급여) OVER (PARTITION BY A.부서코드), 2)
급여비중2
FROM (
SELECT A.부서코드
,A.사원번호
,A.월급여
,SUM(A.월급여) OVER (PARTITION BY A.부서코드) 부서급여합
FROM 사원 A
WHERE A.부서코드 IN ('10', '20')
) A
ORDER BY A.부서코드
,A.월급여 DESC
,A.사원번호
SELECT A.부서코드
,A.사원번호
,A.월급여
,ROUND(A.월급여/B.부서급여합, 2) 급여비중
FROM 사원 A
,(
SELECT B.부서코드
,SUM(B.월급여) 부서급여합
FROM 사원 B
WHERE B.부서코드 IN ('10', '20')
GROUP BY B.부서코드
) B
WHERE A.부서코드 IN ('10', '20') AND B.부서코드 = A.부서코드 ORDER BY A.부서코드
,A.월급여 DESC
,A.사원번호
-- 실습문제 10.1 : 점이력을 선분이력 형태로 표현
-- * 문제 SQL SELECT A.사원번호
,A.이력순번
,A.발령일자
FROM 발령이력 A WHERE A.사원번호 BETWEEN 102 AND 104 ORDER BY A.사원번호
,A.이력순번
-- * 답안 SQL SELECT A.사원번호
,A.이력순번
,A.발령일자 발령시작일자
,NVL(A.다음발령일자 - 1, TO_DATE('99991231','YYYYMMDD')) 발령종료일자
FROM (
SELECT A.사원번호
,A.이력순번
,A.발령일자
,LEAD(A.발령일자) OVER (PARTITION BY A.사원번호 ORDER BY A.이력순번)
다음발령일자
FROM 발령이력 A
WHERE A.사원번호 BETWEEN 102 AND 104
) A
ORDER BY A.사원번호
,A.이력순번
-----------------------------------------------------
-- 실습문제 10.2 : 데이터를 특정기준에 따라 할당
-----------------------------------------------------
-- * 문제 SQL
SELECT A.부서코드
,A.사원번호
,A.월급여
FROM 사원 A
ORDER BY A.부서코드
,A.사원번호
;
SELECT A.부서코드
,A.사원번호
,A.월급여
,A.급여비중
,A.부서성과급 부서성과급
,TRUNC(A.급여비중*A.부서성과급, -3) 개별성과급
,SUM(TRUNC(A.급여비중*A.부서성과급, -3)) OVER (PARTITION BY A.부서코드
ORDER BY A.월급여 DESC
,A.사원번호)
개별성과급누계
FROM (
SELECT A.부서코드
,A.사원번호
,A.월급여
,ROUND(A.월급여/A.급여합, 4) 급여비중
,1000000 부서성과급
FROM (
SELECT A.부서코드
,A.사원번호
,A.월급여
,SUM(A.월급여) OVER (PARTITION BY A.부서코드) 급여합
FROM 사원 A
) A
) A
ORDER BY A.부서코드
,A.월급여 DESC
,A.사원번호
;
-- * 답안 SQL
SELECT A.부서코드
,A.사원번호
,A.월급여
,CASE WHEN A.부서사원수 = A.부서사원순번 --> 부서별로 최종 데이터인 경우
THEN A.개별성과급 + --> 개별성과급에
(A.부서성과급 - A.개별성과급누계) --> 자투리를 더해 줌
ELSE A.개별성과급
END 성과급
,A.급여비중
,A.부서성과급
,A.개별성과급
,A.개별성과급누계
FROM (
-- 개별성과급누계와 부서사원수, 부서사원순번을 구함
SELECT A.부서코드
,A.사원번호
,A.월급여
,A.급여비중
,A.부서성과급 부서성과급
,A.개별성과급
,SUM(A.개별성과급) OVER (PARTITION BY A.부서코드
ORDER BY A.월급여 DESC
,A.사원번호) 개별성과급누계
,COUNT(*) OVER (PARTITION BY A.부서코드) 부서사원수
,ROW_NUMBER() OVER (PARTITION BY A.부서코드
ORDER BY A.월급여 DESC
,A.사원번호) 부서사원순번
FROM (
-- (급여비중*부서성과급)으로 개별성과급을 구함
SELECT A.부서코드
,A.사원번호
,A.월급여
,A.급여비중
,A.부서성과급 부서성과급
,TRUNC(A.급여비중*A.부서성과급, -3) 개별성과급
FROM (
-- 급여비중과 부서성과급 포함
SELECT A.부서코드
,A.사원번호
,A.월급여
,ROUND(A.월급여/A.급여합, 4) 급여비중
,1000000 부서성과급
FROM (
-- 부서별급여합
SELECT A.부서코드
,A.사원번호
,A.월급여
,SUM(A.월급여) OVER (PARTITION BY A.부서코드) 급여합
FROM 사원 A
) A
) A
) A
) A
ORDER BY A.부서코드
,A.월급여 DESC
,A.사원번호
;
-- LAST_VALUE 이용
SELECT A.부서코드
,A.사원번호
,A.월급여
,CASE WHEN A.사원번호 = LAST_VALUE(A.사원번호) OVER (PARTITION BY A.부서코드)
--> 부서별로 최종 데이터인 경우
THEN A.개별성과급 + --> 개별성과급에
(A.부서성과급 - A.개별성과급누계) --> 자투리를 더해 줌
ELSE A.개별성과급
END 성과급
,A.급여비중
,A.부서성과급
,A.개별성과급
,A.개별성과급누계
FROM (
-- 개별성과급누계와 부서사원수, 부서사원순번을 구함
SELECT A.부서코드
,A.사원번호
,A.월급여
,A.급여비중
,A.부서성과급 부서성과급
,A.개별성과급
,SUM(A.개별성과급) OVER (PARTITION BY A.부서코드
ORDER BY A.월급여 DESC
,A.사원번호) 개별성과급누계
FROM (
-- (급여비중*부서성과급)으로 개별성과급을 구함
SELECT A.부서코드
,A.사원번호
,A.월급여
,A.급여비중
,A.부서성과급 부서성과급
,TRUNC(A.급여비중*A.부서성과급, -3) 개별성과급
FROM (
-- 급여비중과 부서성과급 포함
SELECT A.부서코드
,A.사원번호
,A.월급여
,ROUND(A.월급여/A.급여합, 4) 급여비중
,1000000 부서성과급
FROM (
-- 부서별급여합
SELECT A.부서코드
,A.사원번호
,A.월급여
,SUM(A.월급여) OVER (PARTITION BY A.부서코드) 급여합
FROM 사원 A
) A
) A
) A
) A
ORDER BY A.부서코드
,A.월급여 DESC
,A.사원번호
;