[OracleDB] 윈도우(Window) 함수

윈도우 (Window) 함수

OracleDB의 윈도우 함수는 행과 행 간의 관계를 정의하기 위해 사용할 수 있다. 지금까지 사용했던 집계(Aggregate) 함수는 여러 개의 행을 묶어 하나의 값을 냈다면, 윈도우 함수는 여러 개의 행의 값을 묶거나 비교하는 건 비슷하지만 각 행 마다 하나의 값을 만들어낼 수 있다.

 

윈도우 함수에서는 집계 함수 뿐만 아니라 순위 함수를 사용할 수 있고 그 이외에도 LAG, LEAD와 같은 앞뒤 행의 값을 가리키는 함수 등을 제공한다.

 

 

구성

SELECT 함수명() OVER([PARTITION BY 컬럼명] ORDER BY [컬럼명] [[ROWS|RANGE] BETWEEN 시작행 AND 마지막행])
  FROM 테이블명;

 

 

여러가지 함수

집계 함수

COUNT(*)	null 값을 포함한 행의 수
COUNT(컬럼명 or 표현식)	null 값을 제외한 행의 수
SUM(컬럼명 or 표현식)	합계
AVG(컬럼명 or 표현식)	평균
MAX(컬럼명 or 표현식)	최대
MIN(컬럼명 or 표현식)	최소
STDDEV(컬럼명 or 표현식)	표준 편차
VARIRAN(컬럼명 or 표현식)	분산

 

순위 함수

RANK()	같은 순위에서 중복된 수만큼 순위가 증가한다. 예) 1 - a 1 - b 1 - c 4 - d
DENSE_RANK()	같은 순위가 중복되어도 순위는 차례대로 증가한다. 예) 1 - a 1 - b 1 - c 2 - d
ROW_NUMBER()	행 순번을 출력한다.

 

그외

LAG(컬럼명[, offset][, default])	이전 행의 특정 컬럼값을 가져온다. offset : 이전 행의 범위 지정 (기본값 : 1) default : 값이 없을 경우 사용할 기본값
LEAD(컬럼명[, offset][, default])	다음 행의 특정 컬럼값을 가져온다. offset : 이전 행의 범위 지정 (기본값 : 1) default : 값이 없을 경우 사용할 기본값

 

select e.first_name || ' ' || e.last_name as employee_name, e.salary,
       LAG(e.salary) OVER(ORDER BY e.salary) AS lag,
       LEAD(e.salary) OVER(ORDER BY e.salary) AS lead
  from employees e
 where e.salary BETWEEN 2000 AND 2400;

 

 

 

WINDOWING

OVER() 안에 WINDOWING 절을 작성하여 파티션 내에서 선택하는 행의 범위를 지정할 수 있다.

 

ROWS : 물리적인 행

RANGE : 논리적인 행 (같은 값을 하나의 행으로 봄)

 

이전 행의 급여와 현재 행의 급여 값의 합계 구하기

1. ROWS

select e.first_name || ' ' || e.last_name as employee_name,
       e.salary,
       SUM(e.salary) OVER(ORDER BY e.salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS windowing_sum
  from employees e;

 

 

2. RANGE

select e.first_name || ' ' || e.last_name as employee_name,
       e.salary,
       SUM(e.salary) OVER(ORDER BY e.salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS windowing_sum
  from employees e;

 

 

* 범위의 선택

UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫 번째 ROW
UNBOUNDED FOLLOWING	윈도우의 마지막 위치가 마지막 ROW
CURRENT ROW	현재 ROW

 

select e.first_name || ' ' || e.last_name as employee_name, e.salary,
       -- 시작 행부터 현재 행까지 합 구하기
       SUM(e.salary) OVER(ORDER BY e.salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS preceding,
       -- 현재 행에서 마지막 행까지 합 구하기
       SUM(e.salary) OVER(ORDER BY e.salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS following,
       -- 시작 행부터 마지막 행까지 합 구하기
       SUM(e.salary) OVER(ORDER BY e.salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS unbounded
  from employees e;

 

 

 

 

https://ribo.dev/59

[ORACLE] Window 함수, 분석함수