분석함수
DB CAFE
notifications_active 데이터베이스 전문기업 안내
- 데이터 품질 전문기업
http://empathydata.co.kr/
목차
1 윈도우 함수 / 분석 함수[편집]
1.1 OVER[편집]
- oracle 12c syntax
notifications_activeOVER 절
- 누적 집계 등를 구하기 위해 분석함수 OVER절 사용
- ORDER BY 절 뒤로 WINDOWING 절을 사용
- WINDOWING절에서는 분석함수의 대상이 되는 행들의 범위 조정이 가능.
- 누적 합계를 구하기 위해서는 첫번째 행부터 읽고 있는 행까지(ROWS UNBOUNDED PRECEDING)를 분석함수의 범위 대상으로 지정.
- WINDOWING절에서는 물리적(ROWS), 논리적(RANGE) 범위를 지정 가능.
1.1.1 ROWS UNBOUNDED PRECEDING[편집]
SELECT DEPTNO
, EMPNO
, ENAME
, SAL
, SUM(SAL) OVER(ORDER BY DEPTNO
ROWS UNBOUNDED PRECEDING
) AS SUM_SAL
FROM EMP;1.1.2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW[편집]
notifications_active설명
- ROWS 는 WINDOW의 범위를 정의할 때 물리적인 행을 지정.
- RANGE 는 WINDOW의 범위를 논리적 행을 지정
- 어떤 행에서 시작해서 어떤 행 까지가 하나의 WINDOW 영역으로 정의 할지 범위를 BETWEEN 을 통하여 정의 함.
- UNBOUNDED PRECEDING 은 시작점(첫 번째 행)을 가리키며 UNBOUNDED FOLLOWING 은 끝점(마지막 행)을 의미.
- UNBOUNDED PRECEDING 은 윈도우 파티션의 시작점 사양이며 끝점 사양으로 사용할 수 없음.
- CURRENT ROW 는 현재 행을 의미.
- order_by_clause를 지정하지 않으면 이 절을 지정할 수 없음.
- RANGE 절로 정의 된 일부 윈도우 바운더리(경계)를 사용하면 order_by_clause에서 하나의 표현식 만 지정할 수 있음.
SELECT
DEPTNO
,EMPNO
,ENAME
,SAL
,SUM(SAL) OVER(
ORDER BY DEPTNO
ROWS BETWEEN UNBOUNDED PRECEDING -- 윈도우 범위를 첫행 부터 ~ 현재 행까지
AND CURRENT ROW
) AS SUM_SAL
FROM EMP;1.1.3 WINDOW 물리적 범위[편집]
notifications_active설명
- 물리적 범위는 행을 기준으로 범위를 지정하는 것.
- ROWS를 사용해서 행으로 부터 전(PRECEDING)과 후(FOLLOWING)의 범위를 지정
- 누적 합계를 구하는 예시
SELECT
DEPTNO
,EMPNO
,ENAME
,SAL
,ROUND(
AVG(SAL) OVER(
ORDER BY DEPTNO
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
) AS AVG_SAL
FROM EMP;- 사원의 정보를 부서 번호 기준으로 정렬 했을때 바로 전과 후 행의 급여평균 값과 함께 출력.
1.1.4 WINDOW 논리적 범위[편집]
notifications_active설명
- 논리적 범위는 값을 기준으로 범위를 지정하는 것
- RANGE를 사용해서 해당 행의 값을 기준으로 전(PRECEDING)과 후(FOLLOWING)의 범위를 지정
- 예시) 2번째 행에서 급여 4000의 1000 PRECEDING과 1000 FOLLOWING 범위는 3000 ~ 5000까지의 값을 분석함수에 적용하는 것.
SELECT DEPTNO
, EMPNO
, ENAME
, SAL
, ROUND(
AVG(SAL) OVER(
ORDER BY SAL --어떤 컬럼으로 정렬하느냐에 따라서 논리적인 값이 변경된다.
RANGE BETWEEN 1000 PRECEDING
AND 1000 FOLLOWING
)
) AS AVG_SAL
FROM EMP;- 사원의 정보를 부서 번호 기준으로 정렬 했을때 기준 행의 -1000 ~ 1000에 해당하는 값들의 급여평균 값과 함께 출력.
1.2 CUME_DIST[편집]
값 집합에서 값의 누적 분포 계산
1.3 DENSE_RANK[편집]
순위 값에 간격이없는 순서가 지정된 행 집합에서 행의 순위를 계산합니다.
1.4 FIRST_VALUE[편집]
지정된 창 프레임에서 첫 번째 행의 값을 가져옵니다.
1.5 LAG[편집]
셀프조인을 사용하지 않고 현재 행 앞에 오는 지정된 물리적 오프셋에서 행에 대한 액세스를 제공합니다.
1.6 LAST_VALUE[편집]
지정된 창 프레임에서 마지막 행의 값을 가져옵니다.
1.7 LEAD[편집]
셀프조인을 사용하지 않고 현재 행을 따르는 주어진 물리적 오프셋에서 행에 대한 액세스를 제공
1.8 NTH_VALUE[편집]
값 집합에서 N 번째 값을 가져옵니다.
1.9 NTILE[편집]
순서가 지정된 행 집합을 여러 버킷으로 나누고 각 행에 적절한 버킷 번호를 할당
1.10 PERCENT_RANK[편집]
일련의 값에서 값의 순위를 계산
1.11 RANK[편집]
일련의 값에서 값의 순위를 계산
1.12 ROW_NUMBER[편집]
파티션에서 또는 전체 결과에서 1부터 시작하여 고유 한 순차 정수를 각 행에 할당
1.13 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.14 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.15 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.16 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.17 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.사원번호
;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.사원번호
;