14. 분석함수

분석함수는 집계 함수의 확장 기능으로 생각할 수 있다. 집계 함수는 행 그룹으로 값을 집계하고, 분석 함수는 파티션과 윈도우로 값을 집계한다.

집계 함수는 행 그룹 별로 단일 행을 반환하기 때문에 데이터 집합이 변경되지만, 분석 함수는 데이터 집합을 변경하지 않고 값을 집계한다. 데이터 집합이 변경되지 않기 때문에 원본 값과 집계 값을 함께 분석할 수 있는 장점이 있다.

# 집계 함수를 사용한 버전
SELECT a.empno, a.sal, b.sal AS c1, a.sal / b.sal AS c2
FROM emp a,
    (SELECT deptno, SUM(sal) AS sal FROM emp GROUP BY deptno) b
WHERE a.deptno = 10
AND b.deptno = a.deptno
ORDER BY 1;

# 분석 함수를 사용한 버전
SELECT empno, sal, SUM(sal) OVER () AS c1, RETIO_TO_REPORT(sal) OVER () AS c2
FROM emp
WHERE deptno = 10
ORDER BY 1;

14.1 기본 문법

분석 함수는 OVER 키워드를 사용한다. 집계 함수에 OVER 키워드를 기술하면 분석 함수로 동작한다.

OVER 키워드에는 ANALYTIC 절을 기술할 수 있고, 이 절은 QUERY PARTITION 절, ORDER BY 절, WINDOWING 절로 구성된다.

14.1.1 QUERY PARTITION 절

QUERY PARTITION 절은 GROUP BY 절과 유사하게 동작한다. QUERY PARTITION 절을 생략하면 전체 행이 하나의 파티션으로 동작한다.

SELECT empno, job, sal
      , SUM(sal) OVER (PARTITION BY job) AS c1 -- job별 합계
      , SUM(sal) OVER () AS c2 -- 총 합계
FROM emp
WHERE deptno = 30
ORDER BY 2, 1;

14.1.2 ORDER BY 절

SELECT empno, sal, SUM(sal) OVER (ORDER BY sa1, empno) AS c1
FROM emp
WHERE deptno = 30
ORDER BY 2, 1;

14.1.3 WINDOWING 절

WINDOWING 절로 파티션의 윈도우를 지정할 수 있다. 윈도우는 ROWS 방식이나 RANGE 방식으로 지정할 수 있다.

[윈도우 범위를 지정하는 키워드]

BETWEEN … AND … : 윈도우의 시작과 끝
UNBOUNDED PRECEDING : 앞쪽 끝
UNBOUNDED FOLLOWING : 뒤쪽 끝
CURRENT ROW : 현재 행
value_expr PRECEDING : ROWS - 현재 행에서 앞쪽으로 value_expr 만큼 이동, RANGE - 현재 값에서 value_expr 을 가감
value_exp FOLLOWING : ROWS - 현재 행에서 뒤쪽으로 value_expr 만큼 이동, RANGE - 현재 값에서 value_expr 을 가감

341~350p

14.1.4 KEEP 키워드

KEEP 키워드를 사용하면 ANALYTIC 절에 QUERY PARTITION 절만 사용할 수 있다.

# job 파티션 별 sal가 최소인 행을 대상으로 comm의 최고값을 조회
SELECT ename, job, sal, comm, MAX(comm) KEEP(DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY job) AS c1
FROM emp
WHERE deptno = 30
ORDER BY 2,3,4;

14.1.5 주의사항

RANGE 방식에 value_expr을 지정하면 ORDER BY 절에 숫자 값이나 날짜 값을 사용해야 한다
RANGE 방식에 value_expr을 지정하면 정렬 표현식을 1개만 사용할 수 있다
분석함수는 SELECT 절과 ORDET BY 절에 사용할 수 있다

# 1번 주읫사항
SELECT job, sal, sum(sal) OVER (ORDER BY job RANGE 1 PRECEDING) as c1
FROM emp
WHERE deptno = 30;

# 2번 주의사항
SELECT job, sal, SUM(sal) OVER (ORDER BY sal, comm RANGE 1 PRECEDING) as c1
FROM emp
WHERE deptno = 30;

# 3번 주의 사항
SELECT deptno, ename, sal
FROM emp
WHERE SUM(sal) OVER (PARTITION BY deptno) >= 1000;

14.2 분석 함수

분석 함수의 대부분은 집계 함수에 OVER 키워드를 기술하는 방식으로 사용한다.

14.2.1 기본 함수

COUNT 함수 : 전체 행의 개수나 expr의 개수를 반환
MIN 함수 : expr의 최저 값을 반환
MAX 함수 : expr의 최고 값을 반환
SUM 함수 : expr의 합계 값을 반환
AVG 함수 : expr의 평균 값을 반환 (358~361p)

14.2.2 통계 함수

STDDEV 함수 : expr의 표준 편차를 반환
VARIANCE 함수 : expr의 분산을 반환

이외에도 다음의 통계 함수를 분석 함수로 사용할 수 있다.

14.2.3 순위 함수

순위 함수는 정렬 조건에 따른 순위를 반환한다. 순위 집계함수는 가상의 행을 생성하지만 순위 분석 함수는 실제의 행으로 값을 계산한다.

RANK 함수 : expr이 동일하면 동순위를 부여하고 다음 순위는 동순위의 개수만큼 건너뛴다.
DENSE_RANK 함수 : 동순위가 존재하더라도 다음 순위를 이어서 부여한다.
ROW_NUMBER 함수 : 고유한 순번을 반환, 정렬 값이 동일하더라도 다른 순번을 부여하기 때문에 결과 값이 변경될 수 있다.
NTILE 함수 : order_by_clause에 따라 행을 정렬하고, expr의 개수만큼 버킷을 생성한 후 행에 해당하는 버킷 번호를 할당한다. NTILE 함수로 생성한 버킷으로 행을 집계할 수 있다. 367~369p

# 950~1250 1번 버킷, 1500~2850 2번 버킷...
SELECT sal
     , NTILE(1) OVER (ORDER BY sal) AS c1, NTILE(2) OVER (ORDER BY sal) AS c2
     , NTILE(3) OVER (ORDER BY sal) AS c3, NTILE(4) OVER (ORDER BY sal) AS c4
     , NTILE(5) OVER (ORDER BY sal) AS c5, NTILE(6) OVER (ORDER BY sal) AS c6
     , NTILE(7) OVER (ORDER BY sal) AS c7
FROM emp
WHERE deptno = 30
ORDER BY 1;

CUME_DIST 함수 : 누적 분포 값을 반환한다. (0 < y <= 1)
PERCENT_RANK 함수 : 백분위 순위 값을 반환한다. (0 <= y <= 1)
RATIO_TO_REPORT 함수 : expr의 합계에 대한 현재 expr의 비율을 반환한다.

14.2.4 분포 함수

분포 함수는 분포 모형에 따른 분포 값을 반환한다.

PERCENTILE_COUNT 함수 : 연속 분포 모델에서 expr에 지정한 백분위 값에 해당하는 값을 반환한다.
PERCENTILE_DISC 함수 : 이산 분포 모델에서 expr에 지정한 백분위 값에 해당하는 값을 반환한다.
MEDIAN 함수 : 연속 분포 모형을 가진 중앙값을 반환한다.

14.2.5 순차 함수

순차에 해당하는 값을 가져오는 함수다.

FIRST_VALUE 함수 : 윈도우 첫 행의 expr을 반환한다.
LAST_VALUE 함수 : 윈도우 끝 행의 expr을 반환한다.
NTH_VALUE 함수 : 윈도우 n번째 행의 measure_expr을 반환한다.

14.2.6 기타 함수

LAG 함수 : 현재 행에서 offset 이전 행의 value_expr을 반환한다.
LEAD 함수 : 현재 행에서 offset 이후 행의 value_expr을 반환한다.

LAG 함수와 LEAD 함수는 행 기준으로 동작하므로 분석 함수의 정렬 값이 고유하지 않으면 값이 무작위로 변경될 수 있다.

LISTAGG 함수 : measure_expr을 order_By_clause로 정렬한 후 delimiter로 구분하여 연결한 값을 반환한다.

14.3 활용 예제

선분 이력 전환 1

점 이력을 선분 이력으로 전환해보자

-- LEAD 함수를 사용하면 점 이력을 선분 이력으로 변환할 수 있다.
SELECT cd, dt AS bg_dt
      , LEAD(dt - 1, 1, DATE, '9999-12-31') OVER (ORDER BY dt) AS ed_dt, v1
FROM t1
ORDER BY 1, 2;

선분 이력 전환 2

월별 이력을 선분 이력으로 전환해보자

-- ROW_NUMBER 함수로 생성한 순번의 차를 이용하여 행 그룹을 생성
SELECT cd, MIN(ym) AS bg_ym
      , CASE WHEN MAX(r1) = MAX(cn) THEN '999912' ELSE MAX(ym) END AS ed_ym, v1
FROM (SELECT a.*
        , COUNT(*) OVER (PARTITION BY a.cd) AS cn
        , ROW_NUMBER() OVER (PARTITION BY a.cd ORDER BY a.ym) AS r1
        , ROW_NUMBER() OVER (PARTITION BY a.cd, a.v1 ORDER BY a.ym) AS r2
        FROM t1 a)
GROUP BY cd, v1, r1 - r2
ORDER BY 1, 2;

선분 이력 병합

SELECT cd, MIN(bg) AS bg, MAX(ed) AS ed, yn
FROM(SELECT a.*
      , ROW_NUMBER() OVER (PARTITION BY cd ORDER BY bg) AS r1
      , ROW_NUMBER() OVER (PARTITION BY cd, yn ORDER BY bg) AS r2
      FROM t1 a)
GROUP BY cd, yn, CASE WHEN yn = 'N' THEN r1 - r2 ELSE r1 END
ORDER BY 1, 2;

행 패턴 검색

WITH w1 AS (
  SELECT a.*
      , NVL (SIGN (v1 - LAG(v1) OVER (PARTITION BY cd ORDER BY dt)), 0) AS df
  FROM t1 a)
  SELECT a.*
      , ROw_NUMBER() OVER (PARTITION BY cd ORDER BY dt) AS r1
      , ROw_NUMBER() OVER (PARTITION BY cd, df ORDER BY dt) AS r2
  FROM w1 a
ORDER BY 1m 2;

선형 보간

행 복제 기법을 사용하여 데이터가 존재하지 않는 구간의 행을 생성하고, LEAD 함수를 사용하여 보간 값을 계산한다.

SELECT a.dt + b.lv - 1 AS dt,
      ROUND (a.v1 + (a.v1_df / a.dn) * (b.lv - 1), 2) AS v1
FROM (SELECT a.*,
        NVL(LEAD(a.v1) OVER (ORDER BY a.dt) - a.v1, 0) AS v1_df,
        NVL(LEAD(a.dt) OVER (ORDER BY a.dt) - a.dt, 0) AS dn
      FROM t1 a) a,
      (SELECT LEVEL AS 1v FROM DUAL CONNECT BY LEVEL <=10)b
WHERE b.1v <= a.dn
ORDER BY 1,