오라클 - 윈도우(Window) 함수

 

 

윈도우 함수

• 윈도우 함수는 그룹 함수들에 대해서 데이터 처리를 간단하게 하기 위한 함수이다.
• 윈도우 함수는 중첩해서 사용할 수 없다.
• 윈도우 함수는 서브 쿼리에서 사용할 수 있다.
• 윈도우 함수는 'OVER' 구문이 필수로 포함되어야 한다.

 

 

윈도우 함수 종류

1. 순위 관련 : RANK, DENSE_RANK, ROW_NUMBER
2. 집계 관련 : SUM, MAX, MIN, AVG, COUNT
3. 순서 관련 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
4. 그룹 내 비율 관련 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 
5. 선형 분석을 포함한 통계분석 관련
   : 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

 

 

윈도우 함수 형식

기본 형식

 

• WINDOW_FUNCTION 자리에는 앞서 말한 윈도우 함수를 넣어준다.
• PARTITION BY 절에 입력한 칼럼에 의해 값이 분할된다.
• ORDER BY 절에서는 DATA SET을 어떤 순서로 정렬할지 결정한다.
  - ASC/DESC 외에도 NULLS FIRST, NULLS LAST와 같은 예약어를 사용할 수 있다.
  - NULLS FIRST : NULL이 포함된 ROW가 제일 앞에 위치
  - NULLS LAST : NULL이 포함된 ROW가 제일 마지막에 위치
• WINDOWING 절에서는 함수의 대상이 되는 행 기준의 범위를 지정할 수 있다.
  - ROWS|RANGE : 자료의 물리적 순서를 이용(ROWS)할지 논리적 순서(RANGE)를 이용할지 결정
  - BETWEEN.. AND .. : 자료의 범위를 결정
  - UNBOUNDED PRECEDING : 지정된 값 이전의 모든 ROW를 포함
  - UNBOUNDED FOLLOWING : 지정된 값 이후의 모든 ROW를 포함
  - CURRENT ROW : 현재 ROW를 시작 값 또는 마지막 값으로 이용할 때 사용 

 

 

 

윈도우 함수 사용법 예제

1. 순위 함수 사용법

• TEMP 테이블을 이용해 SALARY 값으로 순위를 부여하는 SQL 만들기
  
  
  

  

  실행 결과

• DENSE_RANK()와 RANK() 함수는 동순위 발생 시
  DENSE_RANK()는 다음 함수에 그대로 +1을 더해주고
  RANK() 함수는 동순위 발생 수만큼 +N을 해준다는 차이가 존재한다.
• 기본 정렬 형식은 ASC 방식이다.

 

2. PARTITION BY 사용해보기

• TEMP 테이블의 직원들을 부서별로 급여 순위를 부여해보자
  
  

  

  실행 결과

• RANK() 함수를 사용해 순위를 부여해줬다.
• PARTITION BY 절을 사용해, DEPT_CODE, 즉 부서별로 순위를 매길 구역을 나눠주었다.
• ORDER BY 절을 사용해 급여가 높은 순위로 정렬해주었다.

 

 

3. ROLLUP 함수 사용해보기

• ROLLUP이나 CUBE와 같은 함수는 SUBTOTAL(소계)을 구할 수 있는 함수이다.
• 다음 예제를 통해 확인해보자
  
  

실행문

 

실행 결과

 

• ROLLUP 함수를 사용할 때는 ROLLUP하는 컬럼을 PARTITION BY 절에서 그룹핑해준다.
• 부서별 연봉의 합계와 총합계의 결과를 얻을 수 있다.
• 부서별 연봉의 순위를 구한 후 SUBTOTAL을 계산해준다
  
  
• ROLLUP 함수를 CUBE 함수로 바꾸면 다음과 같이 결과가 변한다.
  
  

  

  실행문

  
  

  

  

  실행 결과

• ROLLUP 함수와 마찬가지로 우선 부서별 연봉 순위를 보여준다.
• ROLLUP 함수와 마찬가지로 부서별 연봉 합계를 보여준다.
• ROLLUP 함수와 달리 EMP_ID 별 연봉 합계를 다시 보여준다
  (본 예시에는 EMP_ID가 중복되지 않아 합계처럼 보이지 않습니다.😭)
• ROLLUP 함수와 같이 총합계를 보여준다.

 

 

4. SALE_HIST 테이블을 이용해 '01' 사업장의 품목별 당일 판매액과 당일 누적 판매액을 구해보자

• 다음 예제를 통해 UNBOUNDED PRECENDING을 활용할 수 있다.
  
  

  

  실행 결과

• PARTITION BY 절을 사용해 품목별로 구역을 나누고 ORDER BY 절로 품목별로 정리하였다.
• WHERE 절을 통해 매출 사업장 코드가 '01'번인 사업장만 조회하였다.
• UNBOUNDED PRECEDING 절은 지정된 값 이전의 모든 값을 참조하기 때문에
  판매액을 더할 때(SUM(SALE_AMT)) 더하는 시점 이전의 모든 값을 더해 누적 값을 구해준다.
  
  

 

5. SALE_HIST 테이블을 이용해 일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균을 구해보자

• 다음 예제를 통해 RANGE 범위를 이해할 수 있다.
  
  

  

  실행 결과

• 판매장소별, 판매일자별로 GROUPING을 해주었다.
• 판매장소와 일자별 판매액의 합계를 구해주었다.
• RANGE INTERVAL '2'는 참조할 범위를 나타낸다.
• DAY PRECEDING은 기준일을 기점으로 하여 이전일을 참조한다는 것이다.
• 따라서 기준일 기준하여 2일 전까지 값을 참조하여 평균값을 구한다는 것이다.

 

 

6. TEMP 테이블을 활용해 자신의 급여와 이전 사번 3명의 급여의 합계와 평균을 구해보자

• 다음 예제를 통해 ROWS [N] PRECEDING의 사용법을 이해할 수 있다.
  
  

  

  실행 결과

• ROWS [N] PRECEDING은 N개만큼 자신 이전의 ROW를 참조한다는 것이다.
• 따라서 자신을 기점으로 이전 3개의 ROW의 합계와 평균을 구한 것이다.

 

 

7. SALE_HIST 테이블을 이용해 각 ROW의 판매액, 같은 날 같은 품목의 최대 판매액,
   최대 판매액 사업장, 해당 사업장의 최소 판매액, 최소 판매액 사업장을 구해보자

• 다음 예제를 통해 FIRST_VALUE, LAST_VALUE의 사용법을 알 수 있다.
  
  

  

  실행문

  
  

  

  실행 결과

• FIRST_VALUE는 PARTITION BY에 의해 분류된 범위 내에서 ORDER BY에 의해 정렬을 한 후
  ROWS 또는 RANGE에 의해 범위가 지정된 값 중 가장 앞에 위치하는 ROW의 값을 읽어올 때 사용
• LAST_VALUE는 PARTITION BY에 의해 분류된 범위 내에서 ORDER BY에 의해 정렬을 한 후
  ROWS 또는 RANGE에 의해 범위가 지정된 값 중 가장 뒤에 위치하는 ROW의 값을 읽어올 때 사용
• FIRST_VALUE와 LAST_VALUE는 정렬된 ROW의 순서에 의해 값이 결정
• 판매액을 기준으로 정렬했기 때문에 FIRST_VALUE와 LAST_VALUE를 사용하면
  최대 판매액, 최대 판매액 사업장, 최소 판매액, 최소 판매액 사업장을 구할 수 있다.

 

8. SALE_HIST 테이블을 이용해 사업장별 품목의 매출액과 함께 동일 사업장, 동일 품목의
   전일 매출액과 다음날 매출액을 구해보자.

• 다음 예제를 통해 LAG와 LEAD의 사용법을 이해할 수 있다.
  
  

  

  실행 결과

• A컬럼에서는 전날 매출액이 나오는 것을 확인할 수 있다.
• B컬럼에서는 다음날 매출액이 나오는 것을 확인할 수 있다.
• LAG 함수는 이전 값을 구하는 함수로 LAG(컬럼명, 'N')으로 사용한다.
  컬럼명의 행을 중심으로 N번째 전에 있는 값을 구한다.
• LEAD 함수는 다음 값을 구하는 함수로 LEAD(컬럼명, 'N')으로 사용한다.
  컬럼명의 행을 중심으로 N번째 후에 있는 값을 구한다.
• 'N'의 인자 값으로 음수는 올 수 없다.

 

 

 

---

본 글은 다음 블로그 '일상 흔적' 님의 오라클 실습 편을 참조하여 작성되었습니다.