분석함수
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 분석 함수[편집]
1.1 분석함수 일반[편집]
1.1.1 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.2 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.3 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.4 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.5 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.사원번호
;