창 함수는 특정 창(행 집합)에 대한 집계 및 순위 함수에 적용됩니다. OVER 절은 해당 창을 정의하기 위해 창 함수와 함께 사용됩니다. OVER 절은 두 가지 작업을 수행합니다.

• 행을 분할하여 행 집합을 형성합니다. (PARTITION BY 절이 사용됨)
• 해당 파티션 내의 행을 특정 순서로 정렬합니다. (ORDER BY 절이 사용됨)

메모: 파티션이 완료되지 않은 경우 ORDER BY는 테이블의 모든 행을 정렬합니다.

통사론:

SELECT coulmn_name1,   window_function(cloumn_name2)  OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column FROM table_name;       window_function=   any aggregate or ranking function    column_name1  = column to be selected   coulmn_name2=   column on which window function is to be applied   column_name3  = column on whose basis partition of rows is to be done   new_column=   Name of new column   table_name=   Name of table>

집계 창 함수
특정 창(행 집합)에 적용되는 SUM(), COUNT(), AVERAGE(), MAX(), MIN() 등 다양한 집계 함수를 집계 창 함수라고 합니다.

다음을 고려하세요 직원 테이블 :

예 -
부서별 직원들의 평균 연봉을 구하고, 해당 부서 내 직원들의 연령순으로 정렬합니다.

SELECT Name, Age, Department, Salary,   AVG(Salary) OVER( PARTITION BY Department) AS Avg_Salary  FROM employee>

그러면 다음이 출력됩니다.

특정 창의 모든 평균 급여가 어떻게 동일한 값을 갖는지 확인하세요.

또 다른 경우를 고려해 봅시다:

SELECT Name, Age, Department, Salary,   AVG(Salary) OVER( PARTITION BY Department ORDER BY Age) AS Avg_Salary  FROM employee>

여기서는 파티션 내의 레코드를 연령 값별로 정렬하므로 평균 값은 정렬된 순서에 따라 변경됩니다.
위 쿼리의 출력은 다음과 같습니다.

따라서 집계가 포함된 창 함수에 order by 절을 추가하는 동안 주의해야 합니다.

순위창 기능 :
순위 함수는 RANK(), DENSE_RANK(), ROW_NUMBER()입니다.

• 순위() –
  이름에서 알 수 있듯이 순위 함수는 모든 파티션 내의 모든 행에 순위를 할당합니다. 첫 번째 행에 1순위를 부여하고 동일한 값을 갖는 행에는 동일한 순위를 부여하도록 순위를 부여한다. 동일한 순위 값 두 개 이후의 다음 순위에서는 순위 값 하나를 건너뜁니다. 예를 들어 두 행이 순위 1을 공유하는 경우 다음 행은 순위 2가 아닌 3을 얻습니다.
  
• DENSE_RANK() –
  파티션 내의 각 행에 순위를 할당합니다. 순위 함수와 마찬가지로 첫 번째 행에는 순위 1이 할당되고 동일한 값을 갖는 행에는 동일한 순위가 지정됩니다. RANK()와 DENSE_RANK()의 차이점은 DENSE_RANK()에서는 동일한 순위 두 개 이후의 다음 순위에 대해 연속된 정수를 사용하고 순위를 건너뛰지 않는다는 점입니다.
  
• ROW_NUMBER() –
  ROW_NUMBER()는 각 행에 고유 번호를 부여합니다. 1행부터 전체 행까지 행 번호를 매깁니다. 행은 해당 값을 기준으로 그룹에 배치됩니다. 각 그룹을 파티션이라고 합니다. 각 파티션에서 행은 차례로 숫자를 얻습니다. 파티션에는 두 행의 번호가 동일하지 않습니다. 이로 인해 ROW_NUMBER()가 RANK() 및 DENSE_RANK()와 다릅니다. ROW_NUMBER()는 순차적인 정수 번호로 모든 행을 고유하게 식별합니다. 이는 다양한 종류의 데이터 분석에 도움이 됩니다.

메모 -
순위창 함수를 사용할 때에는 ORDER BY()를 반드시 지정해야 합니다.

예 -
사원의 행번호, 직급, 밀집순위를 계산하여 각 부서 내 급여에 따른 사원 테이블입니다.

SELECT   ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS emp_row_no,   Name,   Department,   Salary,  RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_rank,  DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_dense_rank FROM   employee;>

위 쿼리의 출력은 다음과 같습니다.

따라서 ROW_NUMBER() 정의에서 언급했듯이 행 번호는 각 파티션 내에서 연속된 정수임을 알 수 있습니다. 또한, Dense Rank에서는 Rank 값 사이에 간격이 없는 반면, Rank를 반복한 후에는 Rank 값에 간격이 있는 Rank와 Dense Rank의 차이를 확인할 수 있습니다.