9. Window Function

Window Function

window function은 전체 테이블이 아닌 분석하고 싶은 작은 테이블을 연산의 대상으로 삼는다 (어느 특정 컬럼 안에서 특정를 연산)

* 전체 테이블에서도 사용 가능하지만, 보통의 경우 전체 테이블을 사용하고 싶으면 집계 함수를 바로 쓰는게 일반적

윈도우 함수 지정 범위에게 윈도우 함수를 적용하겠다

SELECT  WINDOW함수 (컬럼)  OVER  (<행을 분할> <행을 정렬> <대상 행 지정>)
                   윈도우 함수            ~에게                윈도우 함수 지정 범위

* <> 부분은 생략 가능
* OVER : 적용 대상을 정해줌

• 행을 분할 : PARTITION BY            (Group By와 같은 역할)
• 행을 정렬 : ORDER BY                   (Order By 역할)
• 행을 지정 : ROWS 또는 RANGE     (Where의 역할)

 

Group By와의 차이

기준으로 나누어 함수를 적용한다는 점에서 Group by와 비슷하지만, 차이점이 있는 것이

Group by는 계산한 값이 기준값을 기준으로 한 행으로 나오는 반면, Window Function은 기준에 대해 각 행마다 계산한 값을 보여준다는 것이다.

예를 들어, Group By는 김씨 5, 이씨 3, 최씨 4 이런식으로 나온다면

                 Window Function은 김씨 5, 김씨 5, 김씨 5, 이씨 3, 이씨 3, 최씨 4, 최씨 4, 최씨 4, 최씨 4 이런식으로 결과를 출력한다.

즉, Group By를 하면 행 수가 전에 비해 줄어들지만 Window Function은 전과 같은 행의 수를 갖는다

 

 

Window Function 종류

• 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER
• 윈도우 집계 함수 :  SUM, MAX, MIN, AVG, COUNT
• 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 비율 함수 : RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTITLE

[SQLD] 윈도우 함수(Window Function) - 자주 사용하는 함수 모음

윈도우 함수(WINDOW FUNCTION)

구루비에 예시랑 설명 다 나와있음

 

---

대상 행 지정 : ROWS & RANGE

* 해당 문법은 PostgreSQL, Oracle, MSSQL에서는 지원되고, MySQL에서는 8.0 버전 이상부터만 지원되는 거 같다.

ROWS (행)

행의 수를 선택할 때 사용한다

• UNBOUNDED PRECEDING: 맨 위의 행
• PRECEDING  : 이전의 행
• CURRENT ROW : 현재 행
• FOLLOWING : 다음의 행
• UNBOUNDED FOLLOWING : 맨 아래의 행

현재 행을 기준으로 한칸 위는 1 PRECEDING, 한 칸 아래는 1 FOLLOWING 이라고 부른다.

 

ROWS

만약, ROWS에 시작점만 적고 끝점을 안적었을 때에는 CURRENT ROW까지 자동으로 연산이 된다 (디폴트가 CURRENT ROW)

• ROWS UNBOUNDED PRECEDING : 연산을 맨 위 부터 현재 행까지
• ROWS UNBOUNDED FOLLOWTING: 연산을 현재 행 부터 맨 아래까지
• ROWS 1 PREDECING: 연산을 한칸 위 행 부터 현재 행까지
• ROWS 2 FOLLOWING: 연산을 현재 행 부터 두 칸 아래 행까지

 

 

 

ADAMS의 경우 950 + 1100이 되어 2050

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ROWS BETWEEN  A   AND  B

연산의 시작이나 끝이 CURRENT ROW가 아닌 경우이다, 이런 경우 BETWEEN A AND B를 이용해 연산의 시작점과 끝점을 정해준다.

• ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING : 맨 위부터 현재 행보다 2칸 아래 행까지
• ROWS BETWEEN 2 PRCEDING AND CURRENT ROW : 현재 행보다 2칸 위에 행부터 현재 행까지
  (CURRENT ROW을 생략해도 같은 값 얻을 수 있다)
• ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING: 현재 행보다 1칸 아래 행부터 현재 행 보다 3칸 아래 행까지

 

 

 

JAMES의 경우 현재행을 기준으로 시작 행이 1개 전이고 끝행이 1개 후니까

SMITH부터 JAMES 사이의 있는 값을 모두 더한 2850 (800 + 950 + 1100)

 

 

 

 

 

 

 

 

 

 

 

 

---

RANGE (컬럼 값)

컬럼의 값을 기준으로 연산에 참여할 행을 선택한다

• RANGE 150 PRECEDING : 현재 칼럼 값을 기준으로 작은 값에서 150 이하로 차이가 나는 행들 선택
• RANGE 150 FOLLOWING : 현재 칼럼 값을 기준으로 큰 값에서 150 이하로 차이가 나는 행들 선택
• RANGE UNBOUNDED PRECEDING : 현재 칼럼을 기준으로 작은 값들을 모두 선택

 

 

TURNER의 경우 1500보다 작은 값 중에 150

이하로 차이나는 값 없음, 즉 그대로 1500

 

 

 

 

 

 

 

---

 

예시

SELECT JOB, ENAME, SAL,
       SUM(SAL) OVER (PARTITION BY JOB
                      ORDER BY SAL
                      ROWS UNBOUNDED PRECEDING)  # 파티션 맨 위에 위치해 있는 행부터 현재 행까지 연산 실시
       AS CUM_SAL
FROM EMP

 

각 부서별 SAL의 누적 합을 구하는 컬럼을 생성하는 쿼리이다.

 

CUM_SAL 컬럼을 만들기 위해 WINDOW FUNCTION을 사용했다

1. JOB컬럼을 기준으로 행을 분할

2. 분할한 행 안에서 SAL을 정렬

3. 분할한 행 안에서 맨 위부터 현재 행까지 연산 실시

• FORD : 3000
• SCOTT : 3000 + 3000 = 6000
• JAMES : 1300 + 1100 + 950 = 3350

 

 

 

출처

• 쏭즈캠퍼스

오늘 처음 봤지만 비전공자 중 SQLD를 취득하고자 하는 사람들에게 도움이 많이 될 거 같다. 

 

참고

[SQL] 윈도우 함수(Window Function)의 소중함을 느껴보자

엄청 잘 나와있다!! 특히 WINDOW FUNCION의 기본 설계에 대해서 알기 좋음