SQL 의 ROW_NUMBER() , 오라클에서 ROWNUM 사용하기

https://www.experts-exchange.com/articles/32791/Partition-By-Clause-in-Oracle.html

 

SQL 의 ROW_NUMBER() 함수

 

SQL에서 ROW_NUMBER() 함수는 결과 집합의 각 행에 순차적인 번호를 할당하는 데 사용됩니다. 일반적으로 ORDER BY 절과 함께 사용되며, 결과를 정렬하는 기준으로 사용됩니다.

ROW_NUMBER() 함수는 정렬된 결과 집합에서 각 행의 고유한 번호를 생성합니다. 이 번호는 일반적으로 1부터 시작하여 결과 집합의 첫 번째 행에 할당되며, 다음 행부터는 1씩 증가합니다.

예를 들어, 다음과 같은 Employees 테이블이 있다고 가정해봅시다.

 

다음 쿼리는 ROW_NUMBER() 함수를 사용하여 직원의 번호를 할당합니다.

SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNumber, EmployeeName, Department
FROM Employees

 

위의 예제에서는 EmployeeID에 따라 결과를 정렬하고 번호를 할당했습니다. 하지만 ORDER BY 절에 다른 열을 사용하여 다른 정렬 기준을 지정할 수도 있습니다.

ROW_NUMBER() 함수는 주로 결과 집합의 행에 고유한 식별자를 할당하거나, 순위를 매기거나, 페이지네이션 등의 작업에 사용됩니다.

 

 

오라클에서 ROWNUM 사용하기

 

오라클에서 ROW_NUMBER() 함수와 유사한 기능을 수행하기 위해 사용할 수 있는 기능은 "ROWNUM"입니다. ROWNUM은 결과 집합에서 각 행에 할당되는 일련번호를 나타내는 가상 열입니다.

ROWNUM은 일반적으로 정렬된 결과에 번호를 할당하기 위해 사용됩니다. 그러나 ROWNUM은 쿼리의 WHERE 절에서 필터링 조건으로 사용될 때 주의해야 합니다. ROWNUM은 행이 결과 집합에 반환되기 전에 할당되므로 WHERE 절에서 ROWNUM을 사용하여 조건을 설정하면 원하는 결과를 얻을 수 없을 수 있습니다.

다음은 ROWNUM을 사용하여 오라클에서 ROW_NUMBER() 함수와 유사한 동작을 구현하는 예시입니다.

 

SELECT ROWNUM AS RowNumber, EmployeeName, Department
FROM (
    SELECT EmployeeName, Department
    FROM Employees
    ORDER BY EmployeeID
)
WHERE ROWNUM <= 10;

 

위의 예시에서는 Employees 테이블에서 EmployeeID에 따라 정렬된 결과를 가져오고, 이 결과에 ROWNUM을 할당하여 첫 10개의 행만 선택합니다.

주의할 점은 위의 예시에서 ROWNUM이 WHERE 절에서 필터링 조건으로 사용되었다는 것입니다. 이렇게 사용하면 ROWNUM이 결과 집합에 반환되기 전에 필터링되므로 원하는 결과를 얻을 수 있습니다.

오라클에서는 다양한 윈도우 함수를 사용하여 ROW_NUMBER()과 유사한 작업을 수행할 수도 있습니다. 윈도우 함수를 사용하면 정렬, 분석, 순위 등 다양한 작업을 보다 유연하게 수행할 수 있습니다.

 

 

SQL에서 PARTITION BY 구문 사용하기

 

SQL에서 PARTITION BY 구문은 윈도우 함수와 함께 사용되며, 결과 집합을 파티션으로 나누는 데 사용됩니다. 각 파티션은 고유한 그룹을 형성하고, 해당 그룹 내에서 윈도우 함수가 독립적으로 작동하도록 합니다. PARTITION BY를 사용하여 데이터를 그룹화하고 각 그룹에 대한 계산을 수행할 수 있습니다.

PARTITION BY 구문의 기본 구조는 다음과 같습니다.

 

SELECT column1, column2, ..., aggregate_function(column)
OVER (PARTITION BY partition_column1, partition_column2, ...)
FROM table_name

 

위의 구문에서 PARTITION BY 절은 partition_column1, partition_column2 등과 같은 열을 지정하여 데이터를 파티션으로 나눕니다. 이렇게 지정된 열은 그룹화의 기준이 됩니다.

다음은 PARTITION BY를 사용하여 각 부서별로 평균 연봉을 계산하는 예시입니다.

 

SELECT Department, EmployeeName, Salary,
       AVG(Salary) OVER (PARTITION BY Department) AS AvgSalary
FROM Employees

 

위의 예시에서는 Employees 테이블에서 각 직원의 부서별 평균 연봉을 계산합니다. PARTITION BY Department 구문을 사용하여 부서별로 데이터를 그룹화하고, AVG(Salary) 함수를 사용하여 각 그룹의 평균 연봉을 계산합니다. 결과에는 원래의 열과 함께 각 직원의 평균 연봉이 포함됩니다.

PARTITION BY는 다양한 윈도우 함수와 함께 사용될 수 있으며, 그룹별 계산, 순위 매기기, 누적 합계 등 다양한 분석 작업을 수행할 때 유용합니다.

 

 

SQL에서 PARTITION BY + ROW_NUMBER 활용하기

 

SELECT
  ROW_NUMBER() OVER (PARTITION BY Department ORDER BY EmployeeName) AS RowNumber,
  Department,
  EmployeeName,
  Salary,
  AVG(Salary) OVER (PARTITION BY Department) AS AvgSalary
FROM
  Employees;

 

Employees 테이블에서 데이터를 조회하고, 각 부서별로 ROW_NUMBER() 열을 할당하여 순차적인 번호를 부여합니다. PARTITION BY Department로 부서별로 그룹화되며, ORDER BY EmployeeName을 사용하여 직원 이름을 기준으로 정렬합니다. Department 열은 부서 이름을, EmployeeName 열은 직원 이름을, Salary 열은 직원의 연봉을 나타냅니다. AvgSalary 열은 AVG(Salary) OVER (PARTITION BY Department) 구문에 따라 각 부서별 평균 연봉을 계산합니다.

쿼리를 실행하면 결과로 표 형태로 정렬된 부서별 직원 데이터와 부서별 평균 연봉이 포함된 결과 집합이 반환됩니다. RowNumber 열은 각 행에 할당된 순차적인 번호를 나타냅니다.

 

 

 

참고 사이트

 

https://gent.tistory.com/378

[MSSQL] ROWNUM 사용법 정리 (조회 순번, 조회 개수)