[SQL] 윈도우 함수

 

 윈도우 함수 (Window Function)
 - 각 윈도우별 집합 연산을 수행한 결과를 반환하는 함수이다.
 - JOIN이나 프로그램의 Over Head를 줄인다.
 - 간결한 SQL로 복잡한 분석작업 수행이 가능하다.
 - 이해 및 활용이 용이하다.
 - 기존에 사용하던 집계 함수와 윈도우 함수 전용으로 만들어진 기능도 있다.
 - 다른 함수와는 달리 중첩해서 사용할 수 없지만 서브쿼리에서 사용이 가능하다. 

 

 

 윈도우 함수 구문

 

SELECT
    ANALYTIC_FUNCTION(arguments)
    OVER(
        [Partition By 컬럼LIST]
        [Order By 컬럼LIST]
        [Windowing 절(Rows | Range Between)]
        )
FROM 테이블명;

 

 - ANALYTIC_FUNCTION : 분석함수명(입력인자)

 - OVER : 분석함수임을 나타내는 키워드

 - Partition By : 계산 대상 그룹 결정

 - Order By : 대상 그룹에 대한 정렬 수행

 - Windowing 절 : 분석함수의 계산 대상 범위 지정

 

 

 윈도우 함수 처리 단계

 - JOINS, WHERE, GROUP BY, HAVING → 윈도우 함수 처리 → ORDER BY 

 - 대상 집합을 ANALYTIC FUNCTION이 적용되어야할 각 그룹으로 나눈다.
 - 윈도우 함수에 지정한 ORDER BY 절에 기준 자료를 정렬한다.
 - Pointer와 Off-Set 개념을 적용하여 각 ROW 간에 필요한 계산을 수행한다.

 

 

 

 윈도우 함수 종류

 RANK()
 - 각 ROW마다 순위를 매겨주는 함수이다.
 - PARTITION 내에서 ORDER BY 절에 명시된 대로 정렬한 후 순위를 부여한다.
 - 1부터 시작하여 동일한 값은 동일한 순위를 가지며 동일한 순위의 수만큼 다음 순위는 건너뛴다.

 - ex) 사원의 번호, 이름, 부서번호, 급여, 급여가 많은 사원부터 순위 조회

SELECT
    empno,
    ename,
    deptno,
    sal,
    RANK() OVER
    (ORDER BY sal DESC) "RANK"
FROM emp;

 

 DENSE_RANK()
 - RANK와 유사한 함수이다.
 - ORDER BY 절에 사용된 컬럼이나 표현식에 대하여 순위를 부여하는데 RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 증가된 값을 돌려준다. 예를 들어 1등이 2명일 경우 다음 등수는 2등이다.

 - ex) 사원의 번호, 이름, 부서번호, 급여, 급여가 많은 사원부터 순위 조회

SELECT
    empno,
    ename,
    deptno,
    sal,
    DENSE_RANK() OVER
    (ORDER BY sal DESC) "DRANK"
FROM emp;

 

 ROW_NUMBER()
 - 각 PARTITION 내에서 ORDER BY 절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수이다.

 - ex) 급여가 많은 순으로, 같은 급여를 받는 경우 입사일이 빠른 사람부터 번호 부여하여 조회

SELECT
    empno,
    ename,
    sal,
    hiredate,
    ROW_NUMBER() OVER (ORDER BY sal DESC, hiredate ASC) AS "순번"
FROM emp;

 

 NTILE()
 - PARTITION을 BUCKET이라 불리는 그룹별로 나누고 PARTITION 내의 각 ROW 등을 BUCKET에 배치하는 함수이다.
 - 각 BUCKET에는 동일한 수의 ROW가 배치된다.

 - ex) 급여가 적은 사원부터 4개로 분류해서 조회

SELECT
    ename,
    sal,
    NTILE(4) OVER (ORDER BY sal)
FROM emp;

 

 Window Aggregate Family
 - 윈도우를 근간으로 하여 각각의 ROW들에 대한 집계 값을 반환한다.

 - ex) 사원의 이름, 부서번호, 급여, 전체 급여 합계, 부서별 급여 합계 조회

SELECT
    ename,
    deptno,
    sal,
    SUM(sal) OVER() "TOTAL_SUM",
    SUM(sal) OVER(PARTITION BY deptno) "DEPT_SUM"
FROM emp;

 

 Window절 

 - ROWS 옵션을 사용해 윈도우 함수 계산 범위를 지정한다.

 - ex) 사원의 이름, 부서번호, 급여 합계를 앞줄 1개부터 뒷줄 1개까지 3줄씩 더한 결과, 이전 ROW의 누적 합계 조회

SELECT
    ename,
    deptno,
    sal,
    SUM(sal) OVER (
                ORDER BY sal ROWS
                BETWEEN 1 PRECEDING AND 1 FOLLOWING) "SUM1",
    SUM(sal) OVER (
                ORDER BY sal ROWS UNBOUNDED PRECEDING) "SUM2"
                
FROM emp;

 

 LAG()
 - PARTITION 내에서 OFFSET에 지정된 값(default = 1)만큼 상대적으로 상위에 위한 ROW를 참조하기 위해 사용된다.

 - ex) 사원의 이름, 부서번호, 급여, 본인 이전의 급여 값 조회

SELECT
    ename,
    deptno,
    sal,
    LAG(sal, 1, 0) OVER (ORDER BY sal) AS NEXT_SAL,
    LAG(sal, 1, sal) OVER (ORDER BY sal) AS SAL2
FROM emp;

 

 

 

 오늘의 한 줄 : 어서와 윈도우 함수는 처음이지?

---

 

 

 

 

 출처 : SK 동반성장 아카데미, 비전공자를 위한 SQL 입문, 「윈도우 함수」