"분석함수"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
− | = 분석 함수 | + | == 분석 함수 == |
− | |||
− | |||
− | |||
− | |||
=== CUME_DIST === | === CUME_DIST === | ||
값 집합에서 값의 누적 분포 계산 | 값 집합에서 값의 누적 분포 계산 | ||
+ | |||
=== DENSE_RANK === | === DENSE_RANK === | ||
순위 값에 간격이없는 순서가 지정된 행 집합에서 행의 순위를 계산합니다. | 순위 값에 간격이없는 순서가 지정된 행 집합에서 행의 순위를 계산합니다. | ||
+ | |||
=== FIRST_VALUE === | === FIRST_VALUE === | ||
지정된 창 프레임에서 첫 번째 행의 값을 가져옵니다. | 지정된 창 프레임에서 첫 번째 행의 값을 가져옵니다. | ||
+ | |||
=== LAG === | === LAG === | ||
셀프조인을 사용하지 않고 현재 행 앞에 오는 지정된 물리적 오프셋에서 행에 대한 액세스를 제공합니다. | 셀프조인을 사용하지 않고 현재 행 앞에 오는 지정된 물리적 오프셋에서 행에 대한 액세스를 제공합니다. | ||
16번째 줄: | 15번째 줄: | ||
=== LAST_VALUE === | === LAST_VALUE === | ||
지정된 창 프레임에서 마지막 행의 값을 가져옵니다. | 지정된 창 프레임에서 마지막 행의 값을 가져옵니다. | ||
+ | |||
=== LEAD === | === LEAD === | ||
셀프조인을 사용하지 않고 현재 행을 따르는 주어진 물리적 오프셋에서 행에 대한 액세스를 제공 | 셀프조인을 사용하지 않고 현재 행을 따르는 주어진 물리적 오프셋에서 행에 대한 액세스를 제공 | ||
+ | |||
=== NTH_VALUE === | === NTH_VALUE === | ||
값 집합에서 N 번째 값을 가져옵니다. | 값 집합에서 N 번째 값을 가져옵니다. | ||
23번째 줄: | 24번째 줄: | ||
=== NTILE === | === NTILE === | ||
순서가 지정된 행 집합을 여러 버킷으로 나누고 각 행에 적절한 버킷 번호를 할당 | 순서가 지정된 행 집합을 여러 버킷으로 나누고 각 행에 적절한 버킷 번호를 할당 | ||
+ | |||
=== PERCENT_RANK === | === PERCENT_RANK === | ||
일련의 값에서 값의 순위를 계산 | 일련의 값에서 값의 순위를 계산 | ||
+ | |||
=== RANK === | === RANK === | ||
일련의 값에서 값의 순위를 계산 | 일련의 값에서 값의 순위를 계산 | ||
+ | |||
=== ROW_NUMBER === | === ROW_NUMBER === | ||
파티션에서 또는 전체 결과에서 1부터 시작하여 고유 한 순차 정수를 각 행에 할당 | 파티션에서 또는 전체 결과에서 1부터 시작하여 고유 한 순차 정수를 각 행에 할당 | ||
=== ROW_NUMBER 함수 === | === ROW_NUMBER 함수 === | ||
+ | <source lang=sql> | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.사원번호 | ,A.사원번호 | ||
40번째 줄: | 45번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
+ | </source> | ||
=== ROW_NUMBER VS RANK === | === ROW_NUMBER VS RANK === | ||
+ | <source lang=sql> | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.월급여 | ,A.월급여 | ||
55번째 줄: | 62번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
+ | </source> | ||
-- 2) COUNT 함수와 SUM 함수 | -- 2) COUNT 함수와 SUM 함수 | ||
+ | |||
=== COUNT === | === COUNT === | ||
+ | <source lang=sql> | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.사원번호 | ,A.사원번호 | ||
73번째 줄: | 83번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
− | + | </source> | |
=== SUM === | === SUM === | ||
+ | <source lang=sql> | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.사원번호 | ,A.사원번호 | ||
88번째 줄: | 99번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
− | + | </source> | |
=== LAG 함수와 LEAD 함수 === | === LAG 함수와 LEAD 함수 === | ||
+ | <source lang=sql> | ||
SELECT A.사원번호 | SELECT A.사원번호 | ||
,A.월급여 | ,A.월급여 | ||
101번째 줄: | 113번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
− | + | </source> | |
----------------------------------------------------- | ----------------------------------------------------- | ||
== 분석함수의 활용 == | == 분석함수의 활용 == | ||
----------------------------------------------------- | ----------------------------------------------------- | ||
=== 1:M 관계의 조인에서 부모테이블이 기준집합 === | === 1:M 관계의 조인에서 부모테이블이 기준집합 === | ||
+ | <source lang=sql> | ||
SELECT A.사원번호 | SELECT A.사원번호 | ||
,A.사원명 | ,A.사원명 | ||
124번째 줄: | 137번째 줄: | ||
AND B.RN = 1 | AND B.RN = 1 | ||
; | ; | ||
− | + | </source> | |
=== 집계 연산 === | === 집계 연산 === | ||
+ | <source lang=sql> | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.사원번호 | ,A.사원번호 | ||
144번째 줄: | 158번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
− | + | <source lang=sql> | |
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.사원번호 | ,A.사원번호 | ||
163번째 줄: | 177번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
− | + | </source> | |
----------------------------------------------------- | ----------------------------------------------------- | ||
-- 실습문제 10.1 : 점이력을 선분이력 형태로 표현 | -- 실습문제 10.1 : 점이력을 선분이력 형태로 표현 | ||
----------------------------------------------------- | ----------------------------------------------------- | ||
− | + | ||
+ | <source lang=sql> | ||
SELECT A.사원번호 | SELECT A.사원번호 | ||
,A.이력순번 | ,A.이력순번 | ||
195번째 줄: | 210번째 줄: | ||
,A.이력순번 | ,A.이력순번 | ||
; | ; | ||
− | + | </source> | |
<source lang=sql> | <source lang=sql> | ||
----------------------------------------------------- | ----------------------------------------------------- | ||
208번째 줄: | 223번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
− | + | </source> | |
+ | <source lang=sql> | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.사원번호 | ,A.사원번호 | ||
237번째 줄: | 253번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
− | + | </source> | |
-- * 답안 SQL | -- * 답안 SQL | ||
+ | <source lang=sql> | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.사원번호 | ,A.사원번호 | ||
296번째 줄: | 313번째 줄: | ||
,A.사원번호 | ,A.사원번호 | ||
; | ; | ||
+ | </source> | ||
-- LAST_VALUE 이용 | -- LAST_VALUE 이용 | ||
+ | <source lang=sql> | ||
SELECT A.부서코드 | SELECT A.부서코드 | ||
,A.사원번호 | ,A.사원번호 |
2020년 9월 18일 (금) 09:38 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 분석 함수[편집]
1.1 CUME_DIST[편집]
값 집합에서 값의 누적 분포 계산
1.2 DENSE_RANK[편집]
순위 값에 간격이없는 순서가 지정된 행 집합에서 행의 순위를 계산합니다.
1.3 FIRST_VALUE[편집]
지정된 창 프레임에서 첫 번째 행의 값을 가져옵니다.
1.4 LAG[편집]
셀프조인을 사용하지 않고 현재 행 앞에 오는 지정된 물리적 오프셋에서 행에 대한 액세스를 제공합니다.
1.5 LAST_VALUE[편집]
지정된 창 프레임에서 마지막 행의 값을 가져옵니다.
1.6 LEAD[편집]
셀프조인을 사용하지 않고 현재 행을 따르는 주어진 물리적 오프셋에서 행에 대한 액세스를 제공
1.7 NTH_VALUE[편집]
값 집합에서 N 번째 값을 가져옵니다.
1.8 NTILE[편집]
순서가 지정된 행 집합을 여러 버킷으로 나누고 각 행에 적절한 버킷 번호를 할당
1.9 PERCENT_RANK[편집]
일련의 값에서 값의 순위를 계산
1.10 RANK[편집]
일련의 값에서 값의 순위를 계산
1.11 ROW_NUMBER[편집]
파티션에서 또는 전체 결과에서 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.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.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.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.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.사원번호
;
2 분석함수의 활용[편집]
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
;
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.사원번호
;
<source lang=sql>
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 : 점이력을 선분이력 형태로 표현
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.사원번호
;