SQL활용 - 윈도우 함수 활용(WINDOW FUNCTION SYSTAX, WINDOWING, 오라클 RANK, DENSE RANK, ROW

WINDOW FUNCTION(윈도우 함수)는 레코드 사이의 관계를 쉽게 정의하기위해 정의됐습니다. WINDOW FUNCTION을 통해 많은 프로그램과 튜닝 팁을 대체할 수 있습니다. 복잡하거나 자원을 많이 사용하는 튜닝 기법들을 대체할 수 있는 함수는 튜닝 관점에서도 이미 최적화된 기법이므로 적극적으로 활용해야합니다. 윈도우 함수는 다른 함수와는 달리 중첩해서 사용할 수는 없지만, 서브 쿼리에서는 사용할 수 있습니다.

WINDOW FUNCTION SYSTAX

윈도우 함수에는 OVER 키워드가 필수로 포함됩니다. WINDOW_FUNCTION은 기존에 사용하던 함수와 새롭게 WINDOW 함수용으로 추가된 함수를 포함하는 개념입니다. 함수의 종류에 따라 0~N 개의 ARGUMENT가 지정될 수 있습니다. PARTITION BY 키워드를 통해 전체 집합을 기준에 의해 소그룹으로 나눌 수 있습니다. 어떤 항목에 대해 순위를 지정할지 ORDER BY 절을 기술합니다. WINDOWING 절을 통해 함수의 대상이 되는 행 기준의 범위를 강력하게 지정합니다.

WINDOW FUNCTION은 아래와 같은 형태로 정의됩니다.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ([PARTITION BY COLUMN][ORDER BY][WINDOWING])
FROM TABLE_NAME;

WINDOWING 절

범위를 지정하는 방식에는 두가지가 있습니다. ROWS는 행의 위치를 기준으로 범위를 지정하고, RANGE는 행의 값을 기준으로 범위를 지정합니다. 둘 중 하나를 선택해서 사용합니다.

BETWEEN

BETWEEN 사용 타입과 BETWEEN 미사용 타입에 따라 달라집니다. BETWEEN 사용타입은 사용자가 지정한 두 범위 키워드 사이의 레코드를 대상으로 하고 BETWEEN 미사용타입은 현재 위치를 기준으로 사용자가 지정한 하나의 범위 키워드 사이의 레코드를 대상으로 합니다.

범위타입 키워드

RANGE : 행의 값을 범위의 기준으로 한다
ROWS : 행의 위치를 범위의 기준으로 한다.

범위 키워드

CURRENT ROW : 현재 레코드
UNBOUNDED PRECEDING / FOLLOWING : 맨위 레코드 / 맨아래 레코드
N_PRECEDING / FOLLWING : 현재 위치 에서 N개 위 / 아래 번째 레코드

-- ROWS BETWEEN 사용 타입
-- 현제 레코드에서 맨 마지막 레코드까지 대상
ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW 
-- 현재 레코드와 다음 레코드 대상
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
-- ROWS BETWEEN 미사용 타입
-- 맨 처음 레코드부터 현재 레코드 까지 범위 지정
ROWS UNBOUNDED PRECEDING 

-- RANGE BETWEEN 사용 타입
-- 현재 레코드의 값을 기준으로 -100 / +300 까지의 범위를 갖는 레코드 대상
RANGE BETWEEN 100 PRECEDING AND 300 FOLLOWING)

그룹 내 순위 함수

RANK 함수

RANK는 ORDER BY를 포함한 쿼리문에서 특정 항목에 대한 순위를 구하는 함수입니다. 이때 특정 PARTITION 내의 순위, 전체 데이터에 대한 순위를 구할 수 있습니다. 동일한 값에 대해서는 동일한 순위를 부여합니다. 앞선 중복 순위 레코드의 수를 고려해 다음 순위를 부여합니다.

-- 사원 데이터에 1.전체 사원중 급여가 높은 순서와 2.JOB 별로 급여가 높은 순서를 출력합니다.
-- 업무 구분이 없는 ALL_RK 칼럼에서 동일한 급여는 같은 순위를 부여합니다.
-- 업무를 PARTITION 으로 구분한 JOB_RK는 같은 업무 내 범위내에서 급여 순위를 구합니다.
-- ORDER BY SALARY DESC 조건과 PARTITION BY JOB_ID 조건이 충돌하면 JOB을 기준으로 정렬되지 않고 SALARY DESC 조건으로 정렬합니다

SELECT JOB_ID, EMPLOYEE_ID , SALARY
, RANK () OVER ( ORDER BY SALARY DESC) AS ALL_RK
, RANK () OVER ( PARTITION BY JOB_ID ORDER BY SALARY DESC ) AS JOB_RK
FROM HR.EMPLOYEES;

-- 전체 SALARY 순위를 구하는 ALL_RK 칼럼은 제외하고, JOB_ID를 기준으로 SALARY 순서를 구하는 JOB_RK 만 알아본다.
-- JOB_RK만 사용한 경우 파티션의 기준이 된 JOB_ID 과 SALARY별로 정렬됩니다.

SELECT JOB_ID, EMPLOYEE_ID , SALARY
, RANK () OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS JOB_RK
FROM HR.EMPLOYEES ;

DENSE_RANK 함수

RANK 함수와 유사하나 동일한 순위를 하나로 취급합니다. 앞선 중복 순위 레코드의 수를 고려하지 않고 다음 순위를 부여합니다.

SELECT JOB_ID EMPLOYEE_ID , SALARY
, RANK () OVER ( ORDER BY SALARY DESC) AS RK
, DENSE_RANK () OVER ( ORDER BY SALARY DESC) AS DR
FROM HR.EMPLOYEES ;

ROW_NUMBER 함수

ROW_NUMBER 함수는 RANK 나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순서를 부여합니다. 동일한 값에대한 순서를 관리하기 위해 ORDER BY 절에 정렬기준칼럼을 추가해야 합니다.

-- 사원 데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력한다.
-- 동일한 순위를 배제하기 위해 유니크한 순위를 정한다.
-- 동일 값에 대한 순서를 관리하고 싶다면 ROW_NUMBER() OVER (ORDER BY SAL DES, ENAME) 과 같이 ORDER BY 정을 이용해 추가적인 정렬 기준을 정의한다.

SELECT JOB_ID EMPLOYEE_ID , SALARY
, RANK () OVER ( ORDER BY SALARY DESC) AS RK
, ROW_NUMBER () OVER ( ORDER BY SALARY DESC) AS DR
FROM HR.EMPLOYEES ;

일반 집계함수

SUM 함수

파티션 별 윈도우의 합을 구합니다.

-- 사원들의 급여와 같은 매니저를 두고있는 사원들의 전체 SALARY 합을 구한다.
-- PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화 한다.

SELECT MANAGER_ID , EMPLOYEE_ID, SALARY
, SUM(SALARY) OVER (PARTITION BY MANAGER_ID) AS SAL_SUM
FROM HR.EMPLOYEES

-- OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 첫 레코드부터 현재 레코드까지 SALARY 데이터의 누적값을 출력합니다.
-- RANGE UNBOUNDED PRECEDING 은 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 의미합니다.

SELECT MANAGER_ID , EMPLOYEE_ID, SALARY
, SUM(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY SALARY RANGE UNBOUNDED PRECEDING) AS SAL_SUM
FROM HR.EMPLOYEES;

MAX 함수

파티션 별 윈도우의 최댓값을 구합니다.

-- 사원들의 급여와 같은 매니저를 두고 있는 사원 전체의 SALARY 중 최댓값을 구합니다.

SELECT MANAGER_ID , EMPLOYEE_ID, SALARY
, MAX(SALARY) OVER(PARTITION BY MANAGER_ID) AS MAX_SAL
FROM HR.EMPLOYEES

INLINE VIEW 를 이용해 파티션별 최댓값을 가진 행만 추출합니다.

-- 실행 결과를 보면 파티션별 같은 최댓값을 칼럼의 수만큼 출력된다.

SELECT MANAGER_ID , EMPLOYEE_ID, SALARY
FROM (
    SELECT MANAGER_ID , EMPLOYEE_ID, SALARY
    , MAX(SALARY) OVER(PARTITION BY MANAGER_ID) AS MAX_SAL
    FROM HR.EMPLOYEES
)
WHERE SALARY = MAX_SAL;

단, 직전 예제는 비효율적입니다. 파티션별 최댓값을 가진 행만 추출할때는 순위 함수를 사용하는 것이 바람직합니다.

SELECT MANAGER_ID , EMPLOYEE_ID, SALARY
FROM (
    SELECT MANAGER_ID , EMPLOYEE_ID, SALARY
    ,RANK() OVER(PARTITION BY MANAGER_ID ORDER BY SALARY DESC) AS SAL_RK
    FROM HR.EMPLOYEES
)
WHERE SAL_RK = 1;

MIN 함수

MIN 함수를 이용해 파티션별 윈도우의 최솟값을 구합니다.

-- 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자 기준으로 정렬하고, SALARY 최솟값을 함께 구한다.

SELECT MANAGER_ID , EMPLOYEE_ID, HIRE_DATE, SALARY
, MIN(SALARY) OVER(PARTITION BY MANAGER_ID ORDER BY HIRE_DATE) AS MIN_SAL
FROM HR.EMPLOYEES

AVG 함수

AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값 구합니다.

-- 같은 매니저를 두고 있는 사원들의 평균 급여를 구하는데, 같은 매니저 내에서 자기 바로 앞의 사번과 바로 두읭 사번인 직원만을 대상으로 한다.
-- ROWS BETWEEN 1 PRERCEDING AND 1FOLLOWING은 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다.

SELECT MANAGER_ID , EMPLOYEE_ID, HIRE_DATE, SALARY
, ROUND(
    AVG(SALARY) OVER (
        PARTITION BY MANAGER_ID ORDER BY HIRE_DATE
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
) AS AVG_SAL
FROM HR.EMPLOYEES

COUNT 함수

COUNT 함수와 파티션별 RANGE WINDOWING을 통해 원하는 조건에 맞는 데이터에 대한 통계를 구할 수 있습니다.

-- 급여를 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 출력합니다.
-- RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING 은 현재 행의 급여값을 기준으로 급여가 -50 ~ + 150의 범위 안에 포함된 모든 행을 대상으로 합니다.

SELECT EMPLOYEE_ID, SALARY
,COUNT(*) OVER(ORDER BY SALARY RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS EMP_CNT
FROM HR.EMPLOYEES

그룹 내 행 순서 함수

FIRST_VALUE 함수

파티션별 정렬 후 가장 먼저 나온 값을 출력합니다. 유니크한 정렬을 위해 ORDER BY 절에 칼럼을 추가할 수 있습니다.

-- 부서별 직원들을 연봉이 높은 순서로 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력합니다.
-- RANGE UNBOUNDED PRECEDING 은 현재 행을 기준으로 파티션 내 첫 번째 행까지의 범위를 지정합니다

SELECT DEPARTMENT_ID , EMPLOYEE_ID , SALARY
, FIRST_VALUE (EMPLOYEE_ID) OVER (
    PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC
    RANGE UNBOUNDED PRECEDING
) AS ENAME_FV
FROM HR.EMPLOYEES

-- 앞의 SQL 문장에서 같은 값을 가진 FIRST_VALUE를 처리하기 위해 ORDER BY 정렬 조건을 추가한다.
-- 부서내 최고 급여를 받는 사람이 둘이 있는 경우를 대비해 정렬에 두 번째 조건을 추가한다.

SELECT DEPARTMENT_ID , EMPLOYEE_ID , SALARY
, FIRST_VALUE (EMPLOYEE_ID) OVER (
    PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC , EMPLOYEE_ID
    ROWS UNBOUNDED PRECEDING
) AS ENAME_FV
FROM HR.EMPLOYEES

LAST_VALUE 함수

파티션별 정렬 후 가장 나중에 나온 값을 출력합니다. 유니크한 정렬을 위해 ORDER BY 절에 칼럼을 추가할 수 있습니다.

-- 부서별 직원들을 연봉이 높은 순서로 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력한다.
-- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLOOWING은 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.
-- 만일 공동 등수가 있고, 결과를 의도적으로 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW 를 사용하거나 OVER() 내의 ORDER BY 조건에 칼럼을 추가해야한다.

SELECT DEPARTMENT_ID , EMPLOYEE_ID , SALARY
, LAST_VALUE(EMPLOYEE_ID) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS ENAME_LV
FROM HR.EMPLOYEES

LAG 함수

LAG 함수를 이용해 파티션별 윈도우에서 현재행 기준 앞선 몇 번째 행의 값을 가져올 수 있습니다. 함수의 2번째 인자로 몇 번째 앞의 행을 가져올지 정하고 세 번째 인자에는 가져온 값이 NULL 인 경우 사용할 기본값을 지정합니다.

-- 직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 하나 앞에 있는 앞선 사원의 급여를 본인의 급여와 함께 출력한다.
SELECT EMPLOYEE_ID , HIRE_DATE ,SALARY
, LAG(SALARY) OVER (ORDER BY HIRE_DATE) AS LAG_SAL
FROM HR.EMPLOYEES

-- LAG 함수는 3개의 ARGUMENTS 까지 사용할 수 있다. 부 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고,
-- 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어온다. 이 경우 다른 값으로 바꿔 줄 수 있다.

SELECT EMPLOYEE_ID , HIRE_DATE ,SALARY
, LAG(SALARY, 2, 0) OVER (ORDER BY HIRE_DATE) AS LAG_SAL
FROM HR.EMPLOYEES

LEAD 함수

LEAD 함수를 이용해 파티션별 윈도우에서 현재 행 기준 뒤따르는 몇 번째 행의 값을 가져옵니다. 함수의 2번째 인자로 몇 번째 앞의 행을 가져올지 정하고 세 번째 인자에는 가져온 값이 NULL 인 경우 사용할 기본값을 지정합니다.

-- 직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 바로 다음 입사한 인력의 입사일자를 출력한다.
-- LAG 함수는 3개의 ARGUMENTS 까지 사용할 수 있다. 부 번째 인자는 몇 번째 후의 행을 가져올지 결정하는 것이고,
-- 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어온다.
-- 이 경우 다른 값으로 바꿔 줄 수 있다.

SELECT EMPLOYEE_ID , HIRE_DATE
, LEAD(HIRE_DATE, 1) OVER (ORDER BY HIRE_DATE) AS LEAD_HIREDATE
FROM HR.EMPLOYEES

그룹 내 비율 함수

RATIO_TO_REPORT 함수

파티션 내 전체 SUM 값에서 레코드의 값이 차지하는 비율을 구합니다.

-- JOB_ID 가 'IT_PROG' 인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력한다.
SELECT EMPLOYEE_ID , SALARY
, ROUND(RATIO_TO_REPORT(SALARY) OVER (), 2) AS SAL_RR
FROM HR.EMPLOYEES
WHERE JOB_ID  = 'IT_PROG'

PERCENT_RANK 함수

파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 해, 값이 아닌 행의 순서별 백분율을 구합니다.

-- 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 면 번째 위치에 있는지 0~1사이의 값으로 출력한다.

SELECT DEPARTMENT_ID , EMPLOYEE_ID , SALARY
, PERCENT_RANK(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS PR
FROM HR.EMPLOYEES

CUME_DIST 함수

파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대해 누적백분율을 구합니다.

-- 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 면 번째 위치에 있는지 0~1사이의 값으로 출력한다.

SELECT DEPARTMENT_ID , EMPLOYEE_ID , SALARY
,CUME_DIST () OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS CD
FROM HR.EMPLOYEES

NTILE 함수

파티션별 전체 건수를 argument 값으로 N등분 한 결과를 구합니다.

-- 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다.
SELECT  EMPLOYEE_ID , SALARY
,NTILE(4) OVER (ORDER BY SALARY DESC) AS NT
FROM HR.EMPLOYEES

'DB > SQL 활용' 카테고리의 다른 글

SQL활용 - 계층형 질의와 셀프 조인 활용법 (1)	2023.06.16
SQL활용 - 부분범위 처리를 위한 TOP N 쿼리 작성방법(ROWNUM, ROW LIMITING 절) (1)	2023.06.16
SQL활용 - 그룹 함수를 활용한 데이터 집계방법(ROLLUP, CUBE ,GRUOPING SET) (0)	2023.06.16
SQL활용 - 다수의 결과집합을 결합하는 집합연산자(UNION, UNION ALL, INTERSECT, MINUS) (0)	2023.06.16
SQL활용 - 서브쿼리관련 핵심내용 정리(단일행/다중행/다중컬럼 서브쿼리, 중첩서브쿼리, 연관서브쿼리, 스칼라서브쿼리, 인라인뷰, 뷰) (0)	2023.06.16

이것은 변곡점

SQL활용 - 윈도우 함수 활용(WINDOW FUNCTION SYSTAX, WINDOWING, 오라클 RANK, DENSE RANK, ROW_NUMBER, FIRST VALUE, LAST VALUE, LAG)

WINDOW FUNCTION SYSTAX

WINDOWING 절

BETWEEN

범위타입 키워드

범위 키워드

그룹 내 순위 함수

RANK 함수

DENSE_RANK 함수

ROW_NUMBER 함수

일반 집계함수

SUM 함수

MAX 함수

MIN 함수

AVG 함수

COUNT 함수

그룹 내 행 순서 함수

FIRST_VALUE 함수

LAST_VALUE 함수

LAG 함수

LEAD 함수

그룹 내 비율 함수

RATIO_TO_REPORT 함수

PERCENT_RANK 함수

CUME_DIST 함수

NTILE 함수

'DB > SQL 활용' 카테고리의 다른 글

티스토리툴바

SQL활용 - 윈도우 함수 활용(WINDOW FUNCTION SYSTAX, WINDOWING, 오라클 RANK, DENSE RANK, ROW_NUMBER, FIRST VALUE, LAST VALUE, LAG)

WINDOW FUNCTION SYSTAX

WINDOWING 절

BETWEEN

범위타입 키워드

범위 키워드

그룹 내 순위 함수

RANK 함수

DENSE_RANK 함수

ROW_NUMBER 함수

일반 집계함수

SUM 함수

MAX 함수

MIN 함수

AVG 함수

COUNT 함수

그룹 내 행 순서 함수

FIRST_VALUE 함수

LAST_VALUE 함수

LAG 함수

LEAD 함수

그룹 내 비율 함수

RATIO_TO_REPORT 함수

PERCENT_RANK 함수

CUME_DIST 함수

NTILE 함수

'DB > SQL 활용' 카테고리의 다른 글

관련글

티스토리툴바