행위

분석함수

DB CAFE

Dbcafe (토론 | 기여)님의 2022년 9월 2일 (금) 22:39 판
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)
thumb_up 추천메뉴 바로가기


1 윈도우 함수 / 분석 함수[편집]

  1. 분석 함수는 행 그룹을 기반으로 집계 값을 계산합니다.
  2. 각 그룹에 대해 여러 행을 반환한다는 점에서 집계 함수와 다릅니다.
  3. 행 그룹을 윈도우(Window) 라고하며 analytic_clause에 의해 정의됩니다.
  4. 각 행에 대해 행의 슬라이딩 윈도우가 정의됩니다.
  5. 윈도우는 현재 행에 대한 계산을 수행하는 데 사용되는 행 범위를 결정합니다.
  6. 윈도우 크기는 물리적 행 수 또는 시간과 같은 논리적 간격을 기반으로 할 수 있습니다.
  7. 분석 함수가 처리되기 전에 모든 조인과 모든 WHERE, GROUP BY 및 HAVING 절이 완료됩니다.




1.1 OVER[편집]

  • oracle 12c syntax

analytic_function.gif

analytic_clause.gif

query_partition_clause.gif

order_by_clause.gif

windowing_clause.gif


 notifications_activeOVER 절
  1. 누적 집계 등를 구하기 위해 분석함수 OVER절 사용
  2. ORDER BY 절 뒤로 WINDOWING 절을 사용
  3. WINDOWING절에서는 분석함수의 대상이 되는 행들의 범위 조정이 가능.
  4. 누적 합계를 구하기 위해서는 첫번째 행부터 읽고 있는 행까지(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설명
  1. ROWS 는 WINDOW의 범위를 정의할 때 물리적인 행을 지정.
  2. RANGE 는 WINDOW의 범위를 논리적 행을 지정
    어떤 행에서 시작해서 어떤 행 까지가 하나의 WINDOW 영역으로 정의 할지 범위를 BETWEEN 을 통하여 정의 함.
  3. UNBOUNDED PRECEDING 은 시작점(첫 번째 행)을 가리키며 UNBOUNDED FOLLOWING 은 끝점(마지막 행)을 의미.
    1. UNBOUNDED PRECEDING 은 윈도우 파티션의 시작점 사양이며 끝점 사양으로 사용할 수 없음.
  4. CURRENT ROW 는 현재 행을 의미.
  5. order_by_clause를 지정하지 않으면 이 절을 지정할 수 없음.
  6. 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설명
  1. 물리적 범위는 행을 기준으로 범위를 지정하는 것.
  2. 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설명
  1. 논리적 범위는 값을 기준으로 범위를 지정하는 것
  2. 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.사원번호
;