[SQLD] 윈도우 함수, 범위 지정

ANSI/ISO SQL 표준에서 정의한 함수 

 

1. AGGREGATE FUNCTION 

GROUP AGGREGATE FUNCTION 이라고도 부릅니다.

COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수들이 포함됩니다.

 

2. GROUP FUNCTION

집계함수를 제외하고, 소그룹 간의 소계를 계산하는 ROLLUP, GROUP BY 항목들간 다차원적인 소계를 계산하는 CUBE, 특정항목에 대한 소계를 계산하는 GROUPING SETS 함수가 있습니다.

 

3. WINDOW FUNCTION

분석함수(ANALYTIC FUNCTION)나 순위함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전된 기능입니다.

 

그중에서 오늘은 window함수의 범위를 다뤄보자

 

Window function에 자주 쓰이는 함수로  순위함수, 집계함수, 비율함수 등이 있다.  

윈도우 함수종류	함수명	사용가능 DBMS
순위(RANK) 관련	RANK, DENSE_RANK, ROW_NUMBER	대부분 지원
집계(AGGREGATE) 관련	SUM, MAX, MIN, AVG, COUNT	SQL Server 경우 Over절 내  Order by 지원 못함
순서 관련 함수	FIRST_VALUE, LAST_VALUE, LAG,  LEAD	ORACLE 만 지원
그룹 내 비율 관련 함수	CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT	PERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS에서 지원하고 있으며, NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원하고 있다. RATIO_TO_REPORT 함수는 Oracle에서만 지원되는 함수(현업에서 유용).
선형분석을 포함한 통계분석 함수	CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY	특화되어있으므로 생략

 

 

 

 

윈도우함수 사용법

 

윈도우함수(args) OVER (
[ PARTITION BY column ]
[ ORDER BY column [ASC | DESC] ]
[ [ROWS|RANGE] BETWEEN UNBOUNDED PRECEDING[CURRENT ROW]
      AND UNBOUNDED FOLLOWING[CURRENT ROW] ]
)

 

1. 범위지정

범위지정은 over() 안에서 [ RANGE / ROWS ] + BETWEEN 를 지정하여 할 수 있다.

  특정 물리적인 행에 대해서 범위를 지정하고 싶다면, ROWS + BETWEEN 을 사용하고,

  논리적인 단위로 행의 범위를 지정하고 싶다면, RANGE + BETWEEN 을 사용한다.

ROWS : 물리적인 단위로 행 집합을 지정
RANGE : 논리적인 단위로 의해 행 집합을 지정

BETWEEN ~ AND : 윈도우의 시작과 끝 위치를 지정

UNBOUNDED PRECEDING : 윈도우 시작 위치가 첫 번째 로우임을 의미
UNBOUNDED FOLLOWING : 윈도우 마지막 위치가 마지막 로우임을 의미

[ROW수] PRECEDING : 윈도우 시작 위치가 ROW수만큼 이전이 시작 로우임을 의미
[ROW수] FOLLOWING : 윈도우 마지막 위치가 ROW수만큼 다음이 마지막 로우임을 의미

CURRENT ROW : 현재 로우까지를 의미

 

예제

윈도우 집계함수와

ROWS + BETWEEN 을 이용한 범위지정

SELECT empno, ename, deptno, sal,
       SUM(sal) OVER(ORDER BY deptno, empno 
                ROWS BETWEEN UNBOUNDED PRECEDING 
                         AND UNBOUNDED FOLLOWING) sal1,
       SUM(sal) OVER(ORDER BY deptno, empno 
                ROWS BETWEEN UNBOUNDED PRECEDING 
                         AND CURRENT ROW) sal2,
       SUM(sal) OVER(ORDER BY deptno, empno 
                ROWS BETWEEN CURRENT ROW 
                         AND UNBOUNDED FOLLOWING) sal3
  FROM emp;
 
 
-- SAL1 : 첫 번째 ROW부터 마지막 ROW까지의 급여 합계이다. 
-- SAL2 : 첫 번째 ROW 부터 현재 ROW까지의 급여 합계이다. 
-- SAL3 : 현재 ROW부터 마지막 ROW까지 급여 합계이다.
 EMPNO ENAME       DEPTNO        SAL       SAL1       SAL2       SAL3
------ ------- ---------- ---------- ---------- ---------- ----------
  7782 CLARK           10       2450      29025       2450      29025
  7839 KING            10       5000      29025       7450      26575
  7934 MILLER          10       1300      29025       8750      21575
  7369 SMITH           20        800      29025       9550      20275
  7566 JONES           20       2975      29025      12525      19475
  7788 SCOTT           20       3000      29025      15525      16500
  7876 ADAMS           20       1100      29025      16625      13500
  7902 FORD            20       3000      29025      19625      12400
  7499 ALLEN           30       1600      29025      21225       9400
  7521 WARD            30       1250      29025      22475       7800
  7654 MARTIN          30       1250      29025      23725       6550
  7698 BLAKE           30       2850      29025      26575       5300
  7844 TURNER          30       1500      29025      28075       2450
  7900 JAMES           30        950      29025      29025        950

집계함수 SUM()의 범위를 지정해주는 부분을 통해 확인해보자

SAL1은 ROWS BETWEEN UNBOUNDED PRECEDING (맨 처음) AND UNBOUNDED FOLLOWING (맨 끝)으로

물리적 ROWS가 처음부터 끝까지를 범위로 하여 합을 구해 리턴한다.

(모두 같은 범위를 가지므로 합은 단일값으로 나온다)

 

SAL2는 ROWS BETWEEN UNBOUNDED PRECEDING (맨 처음) AND CURRENT ROW (현재 함수실행되는 로우) 까지

물리적 ROW가 맨처음부터 SUM을 해주는 부분까지 누적합을 구하는 식이 되는것이다.

 

SAL2는 그와 반대로

ROWS BETWEEN CURRENT ROW (현재 함수실행되는 로우) AND UNBOUNDED FOLLOWING (맨 끝) 으로

총합에 대해서 앞부분의 값을 제외하는 형식처럼 나오게 된다.

 

 

이해가 되는가?

 

이번에는 RANGE 예를 보자!

 

 

윈도우 집계함수와

ROWS + BETWEEN 을 이용한 범위지정

WITH test AS
(
SELECT '200801' yyyymm, 100 amt FROM dual
UNION ALL SELECT '200802', 200 FROM dual
UNION ALL SELECT '200803', 300 FROM dual
UNION ALL SELECT '200804', 400 FROM dual
UNION ALL SELECT '200805', 500 FROM dual
UNION ALL SELECT '200806', 600 FROM dual
UNION ALL SELECT '200808', 800 FROM dual
UNION ALL SELECT '200809', 900 FROM dual
UNION ALL SELECT '200810', 100 FROM dual
UNION ALL SELECT '200811', 200 FROM dual
UNION ALL SELECT '200812', 300 FROM dual
)
SELECT yyyymm
     , amt
     , SUM(amt) OVER(ORDER BY TO_DATE(yyyymm,'yyyymm')
                RANGE BETWEEN INTERVAL '3' MONTH PRECEDING
                          AND INTERVAL '1' MONTH PRECEDING) amt_pre3
     , SUM(amt) OVER(ORDER BY TO_DATE(yyyymm,'yyyymm')
                RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING
                          AND INTERVAL '3' MONTH FOLLOWING) amt_fol3
  FROM test
;
 
-- AMT_PRE3 : 직전 3개월 합계
-- AMT_FOL3 : 이후 3개월 합계 
YYYYMM           AMT   AMT_PRE3   AMT_FOL3
--------- ---------- ---------- ----------
200801           100                   900
200802           200        100       1200
200803           300        300       1500
200804           400        600       1100
200805           500        900       1400
200806           600       1200       1700
200808           800       1100       1200
200809           900       1400        600
200810           100       1700        500
200811           200       1800        300
200812           300       1200 

 

RANGE에 대한 설명은 추가로 하지않아도 될것같다.

논리적인 인터발을 주거나, 값에 대한 합계를 좀더 다양하게 활용할수있다.

 

 

+

 

Q.  range / rows에 같은 범위를 주어도 차이가 없나요?

SELECT C1,C2,SUM(TEST2.C2) OVER(ORDER BY C1 range BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 합 FROM TEST2


SELECT C1,C2,SUM(TEST2.C2) OVER(ORDER BY C1 rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 합 FROM TEST2

집계함수의 기준의 되는값이 같은 값을 같는경우

rows 는 물리적으로 다른 행으로 취급하여, 순서대로 SUM함수를 태우지만,

Range는 논리적으로 기준값이 같은 Row들은 하나의 그룹으로 취급하여, SUM의 값을 반환해준다.