Window Function?

• 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수
  • 기존의 SQL 언어는 컬럼과 컬럼 간의 연산, 비교, 집계에 특화되어 있었음
    • 하나의 SQL문으로 행과 행 간의 관계를 정의하거나 비교, 연산하는 것이 어려웠다!
  • 이를 쉽게 처리하기 위해 생겨난 것이 윈도의 함수

윈도우 함수의 생김새

SELECT
  WINDOW_FUNCTION(ARGUMENTS) OVER (
    PARTITION BY 컬럼명
    ORDER BY 정렬조건
    WINDOWING clause
  ) 

• ARGUMENT(인수)
  • 윈도우 함수에 따라 0~N개의 인수를 설정
• PARTITION BY
  • 전체 집합을 기준에 의해 소그룹으로 나눔
• ORDER BY
  • 어떤 항목에 대해 정렬
• WINDOWING
  • 행 기준 범위를 정함
  • ROW
    • 물리적 결과의 행 수
  • RANGE
    • 논리적인 값에 의한 범위

→ 적용할 함수와 OVER절은 윈도우 함수에서 필수적으로 사용되며, 어떤 결과를 만들지에 따라 PARTITION BY, ORDER BY, WINDOWING clause 사용

WINDOWING

구조	설명
ROWS	부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
RANGE	논리적 주소에 의해 행 집합을 지정
BETWEEN AND	윈도우의 시작과 끝 위치를 지정
UNBOUNDED PRECEDING	윈도우 시작 위치가 첫 번째 행임을 의미
UNBOUNDED FOLLOWING	윈도우 마지막 위치가 마지막 행임을 의미
CURRENT ROW	윈도우 시작 위치가 현재 행임을 의미
	(데이터가 인출된 현재 행을 의미)

예시

• 전체 합계

SELECT EMPNO, ENAME, SAL
SUM(SAL) OVER(ORDER BY SAL
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING) TOTSAL
FROM EMP;

→ UNBOUNDED PRECEDING: 처음 행을 의미
→ UNBOUNDED FOLLOWING: 마지막 행을 의미
→ TOTSAL의 처음부터 마지막까지의 합계(SUM(SAL))를 계산

• 누적합계

SELECT EMPNO, ENAME, SAL
SUM(SAL) OVER(ORDER BY SAL
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)TOTSAL
FROM EMP;

→ 처음부터 CURRENT ROW까지의 합계를 계산. 즉, 누적합계
→ 첫 번째 행의 값이 1이었으면 1, 두 번째 행의 값이 2였으면 1+2=3, 세 번째 행의 값이 3이었으면 1+2+3=4

※ 더 많은 내용은 여기와 여기, 그리고 여기

GROUP BY와의 차이점

	GROUP BY	WINDOW FUNCTION
기능	자르기+집약	자르기
특징	1. GROUP BY에 지정된 컬럼으로 데이터 자름	1. PARTITION BY에 지정된 컬럼으로 데이터 자름
	2. 집계 함수를 이용해 집약
차이점	행의 수가 줄어듦	행의 수가 그대로 유지됨

• 가장 큰 차이는 '집약'의 과정 존재 여부

윈도우 함수를 잘 사용하는 법

• 윈도우 함수를 사용하면 간단하게 쿼리를 작성할 수 있지만 윈도우 함수는 행과 행 간의 관계를 다루는 함수이기 때문에 기본적으로 정렬(Sort)의 과정이 생김
  • 정렬이 발생한다 == SQL의 성능이 저하된다
• 따라서 테이블 스캔 횟수가 적다는 이유로 무조건 윈도우 함수를 사용한다는 건 잘못된 결론
• 불필요한 정렬을 줄이고 JOIN이나 Subquery를 통해 스캔을 해야 할 행의 수(레코드 수)를 줄인 후 윈도우 함수를 사용하면 좋음
• 윈도우 함수는 성능적으로 좋지 않기 때문에 성능을 생각한다면 사용할 때 주의해야 함

정리

1. 윈도우 함수는 복잡한 쿼리를 간단하게 작성할 수 있고 테이블 스캔 횟수도 줄어듦
2. 하지만 윈도우 함수를 사용하면 정렬(Sort)을 하게 되어 성능적으로 많은 실행 비용이 발생할 수 있음
3. 따라서 윈도우 함수를 사용할 때는 사전에 레코드 수를 줄이는 것이 좋음

WINDOWING clause에서 ROWS와 RANGE의 차이

• windowing clause 사용 가능한 함수

  • SQL Reference 에 함수 뒤에 * 표시를 포함하고 있는 함수
    • 적용되는 함수 : MAX,MIN,COUNT,FIRST_VALUE,.....
    • 적용되지 않는 함수 : RANK, ROW_NUMBER,LEAD,LAG,FIRST,...

• ROWS 와 RANGE

  • ROW 개수를 사용 → ROWS, ORDER BY 대상인 컬럼의 값을 사용 → RANGE
  • ROWS의 경우 현재 로우로부터 몇 개의 로우 범위에 있는 것이냐를 따짐
    • 현재 행을 기준으로 몇개의 행을 포함하는지를 명시
  • RANGE의 경우 그 값과 얼마 차이가 나는지에 대한 것을 따짐
    • 현재 행을 기준으로 어떤 값의 범위를 포함하는지를 명시

ROWS specifies the window in physical units (rows).
RANGE specifies the window as a logical offset.

• UNBOUNDED

  • 한계를 두지 않고, 해당 파티션의 끝까지를 의미

• PRECEDING, FOLLOWING

  • 현재 로우에서 앞쪽으로인지 뒤쪽으로인지의 방향성을 나타냄
    • 예를 들어 3 PRECEDING만 기술할 경우 현재 행부터 앞쪽 3행을 범위로 하여 함수가 적용된다.

• DEFAULT

  • windowing_clase 를 적지 않았을 경우 기본적으로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 가 있는 것과 같음

• 예시

WITH SUNG AS (
SELECT '20080105' T_DATE , '김철수' S_NAME,   '1학년1반' S_CLASS,  '수학' SUBJECT,   '92' SCORE FROM DUAL UNION ALL
SELECT '20080305', '김철수',   '1학년1반',   '수학',   '95' FROM DUAL UNION ALL
SELECT '20080505', '김철수',   '1학년1반',   '수학',   '94' FROM DUAL UNION ALL
SELECT '20080101', '박은혜',   '1학년3반',   '국어',   '82' FROM DUAL UNION ALL
SELECT '20080201', '박은혜',   '1학년3반',   '국어',   '85' FROM DUAL UNION ALL
SELECT '20080301', '박은혜',   '1학년3반',   '국어',   '81' FROM DUAL UNION ALL
SELECT '20080201', '윤태영',   '3학년1반',   '물리',   '63' FROM DUAL UNION ALL
SELECT '20080301', '윤태영',   '3학년1반',   '물리',   '69' FROM DUAL UNION ALL
SELECT '20080401', '윤태영',   '3학년1반',   '물리',   '52' FROM DUAL
)
SELECT t.*
     , SCORE - MAX(SCORE) OVER(PARTITION BY S_CLASS, SUBJECT
                             ORDER BY T_DATE
                             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                            ) AS 점수등락
FROM SUNG t
``