분석함수
DB CAFE
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.사원번호
;1. 누적 합계를 구하기 위해 분석함수 OVER절을 사용
2. ORDER BY 절 뒤로 WINDOWING 절을 사용
3.WINDOWING절에서는 분석함수의 대상이 되는 행들의 범위 조정이 가능.
4.누적 합계를 구하기 위해서는 첫번째 행부터 읽고 있는 행까지(ROWS UNBOUNDED PRECEDING)를 분석함수의 범위 대상으로 지정.
WINDOWING절에서는 물리적(ROWS), 논리적(RANGE) 범위를 지정 가능.
SELECT DEPTNO
, EMPNO
, ENAME
, SAL
, SUM(SAL) OVER(ORDER BY DEPTNO
ROWS UNBOUNDED PRECEDING
) AS SUM_SAL
FROM EMP;SELECT
DEPTNO
,EMPNO
,ENAME
,SAL
,SUM(SAL) OVER(
ORDER BY DEPTNO
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS SUM_SAL
FROM EMP;