7월 18, 2020

SQLShack (한국어)

이 기사에서는 SQL ROW_NUMBER 함수. 이것은 SQL 필수 시리즈의 연속입니다. 이 가이드에서는 윈도우 함수가 무엇인지 설명하고 SQL ROW_NUMBER 함수의 개념을 이해하기위한 샘플 예제를 볼 수 있습니다.

소개

가장 많이 SQL Server에서 일반적으로 사용되는 함수는 SQL ROW_NUMBER 함수입니다. SQL ROW_NUMBER 함수는 SQL Server 2005 이상 버전에서 사용할 수 있습니다.

ROW_NUMBER는 결과 표에 고유 한 증가 번호를 추가합니다. 행 번호가 적용되는 순서는 ORDER BY 표현식에 의해 결정됩니다. 대부분의 경우 ORDER BY 표현식에 하나 이상의 열이 지정되지만 더 복잡한 표현식이나 하위 쿼리를 사용할 수도 있습니다. 따라서 계속 증가하는 정수 값을 생성하고 항상 1에서 시작하고 후속 행은 다음으로 높은 값을 얻습니다.

PARTITION BY 절과 함께 사용할 수도 있습니다. 그러나 파티션 제한 또는 경계를 넘으면 카운터를 재설정하고 1부터 시작합니다. 따라서 파티션은 1, 2, 3 등의 값을 가질 수 있으며 두 번째 파티션은 다시 1, 2, 3에서 카운터를 시작합니다. 기타 등등.

기본 :

  1. SQL ROW_NUMBER 함수는 임시 값 시퀀스의 비 영구 생성이며 쿼리가 실행될 때 동적으로 계산됩니다.
  2. SQL ROW_NUMBER 함수를 사용하는 SQL 쿼리에서 반환 된 행이 각 실행에서 정확히 동일하게 정렬된다는 보장은 없습니다.
  3. ROW_NUMBER 및 RANK 함수는 유사합니다. ROW_NUMBER의 출력은 1부터 1 씩 증가하는 일련의 값이지만 RANK 함수는 값도 1 씩 증가하지만 값은 동점에 대해 반복됩니다.
  4. 오라클에 대한 경험이라면 ROWNUM이 더 친숙합니다. 의사 열입니다. 1부터 시작하여 테이블 끝까지 1 씩 증가합니다.
  5. SQL ROW_NUMBER 함수는 본질적으로 동적이며 PARTITION BY 절을 사용하여 값을 재설정 할 수 있습니다.
  6. 쿼리의 ORDER BY 절과 OVER의 ORDER BY 절 절은 서로 관련이 없습니다.

구문

1
2

ROW_NUMBER ()
OVER (] order_by_clause col1, col2 ..)

ROW_NUMBER

ROW_NUMBER 다음에 OVER 함수 그런 다음 괄호 안에 ORDER BY 절을 사용하십시오. 결과 집합에 대해 일종의 순서를 적용하려면 ORDER BY 절을 사용해야합니다.

OVER

OVER 절은 창 또는 창에서 행 집합을 정의합니다. 기능이 작동하므로 이해하는 것이 정말 중요합니다. OVER 절의 가능한 구성 요소는 ORDER BY 및 PARTITION BY입니다.

OVER 절의 ORDER BY 표현식은 함수가 작동하기 위해 특정 방식으로 행을 정렬해야 할 때 지원됩니다.

1

값 _ 표현식 1 별 파티션

PARTITION BY

Partition By 절은 선택 사항입니다. 값을 지정할 때 FROM 절에 의해 생성 된 결과 집합을 SQL ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다. PARTITION 절에 지정된 값은 결과 집합의 경계를 정의합니다. PARTITION BY 절이 지정되지 않은 경우 OVER 절은 결과 집합의 모든 행에서 단일 데이터 집합으로 작동합니다. 이 절은 하나 이상의 열, 더 복잡한 표현식 또는 하위 쿼리로 구성 될 수 있습니다.

order_by_clause

Order by 절은 필수 절입니다. 지정된 파티션의 행에 대한 임시 값의 순서 및 연관을 결정합니다. ORDER BY 절은 OVER 절의 표현식이며 함수에 대해 특정 방식으로 행을 정렬해야하는 방법을 결정합니다.

데모

이 섹션에서는 SQL ROW_NUMBER 함수를 살펴 보겠습니다. 전체 데모에서는 AdventureWorks2016 데이터베이스를 사용했습니다.

SQL 쿼리에서 ROW_NUMBER를 사용하는 방법

다음 예에서는 OVER 절의 사용을 볼 수 있습니다.

SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue 및 RowNum과 같은 열을 프로젝션하여 모든 고객 목록을 가져 오겠습니다. Row_Number 함수는 CustomerID 열의 순서로 적용됩니다.임시 값은 CustomerID의 순서에 따라 할당 된 1부터 시작하며 값은 테이블의 마지막 행까지 계속됩니다. 쿼리에 ORDER BY 절을 지정하지 않았기 때문에 CustomerID의 순서가 보장되지 않습니다.

1
2
3
4
5
6
7
8
9
10
11

AdventureWorks2016 사용;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Order by 절 사용 방법

다음 예에서는 쿼리에서 ORDER BY 절을 사용합니다. SalesOrderID 열에 적용된 쿼리의 ORDER BY 절입니다. 출력의 행이 여전히 정렬되고 반환되는 것을 볼 수 있습니다. Row_Number는 여전히 CustomerID에 적용됩니다. 출력은 쿼리의 ORDER BY 및 OVER 절의 ORDER BY가 출력과 독립적임을 나타냅니다.

1
2
3
4
5
6
7
8
9
10
11
12

AdventureWorks2016 사용;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

OVER 절에 여러 열을 사용하는 방법

다음 예에서는 ORDER BY 절에 customerID 및 OrderDate를 나열했음을 알 수 있습니다. 이렇게하면 전체 결과 집합에 할당 된 일련의 번호와 함께 최신 주문 세부 정보가 포함 된 고객 세부 정보가 제공됩니다.

1
2
3
4
5
6
7
8
9
10

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (ORDER BY CustomerID, OrderDate DESC) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader

PARTITION과 함께 SQL ROW_NUMBER 함수를 사용하는 방법

다음 예제에서는 CustomerID 및 OrderDate 필드에 PARTITION BY 절을 사용합니다. 출력에서 고객 11019가 2014-6 월에 3 개의 주문을 가지고 있음을 알 수 있습니다. 이 경우 파티션은 둘 이상의 열에서 수행됩니다.

파티션은 OrderDate와 CustomerID의 조합입니다. Row_Number는 OrderDate 및 CustomerID의 고유 한 각 조합에 대해 다시 시작됩니다. 이렇게하면 같은 날에 두 개 이상의 주문을 한 고객을 쉽게 찾을 수 있습니다.

1
2
3
4
5
6
7
8
9
10
11
12

AdventureWorks2016 사용;
GO
SELECT ROW_NUMBER () OVER (PARTITION BY CustomerID,
DATEADD (MONTH, DATEDIFF (MONTH, 0, OrderDate), 0)
ORDER BY SubTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
부분 합계,
TotalDue
FROM Sales.SalesOrderHeader;

CTE 및 ROW_NUMBER를 사용하여 행의 하위 집합을 반환하는 방법

다음 예에서는 SalesOrderHeader를 분석하여 각 고객이 매번 주문한 상위 5 개 주문을 표시합니다. 달. Month 함수를 사용하여 orderDate 열을 조작하여 월 부분을 가져옵니다.이러한 방식으로 고객 (CustomerID)과 함께 특정 월 (OrderDate)에 해당하는 매출이 분할됩니다.

각 고객에 대해 매달 가장 큰 주문 5 개를 나열하기 위해 CTE가 사용됩니다. 파티션 데이터에 창이 생성되고 값이 할당 된 다음 가장 큰 주문을 가져 오기 위해 CTE가 호출됩니다.

1
2
3
4
5
6
7
8
9
10
11
12

WITH cte
AS (SELECT ROW_NUMBER OVER (PARTITION BY customerID, MONTH (OrderDate) ORDER BY SubTotal DESC, TotalDue DESC) AS ROW_NUM,
CustomerID,
MONTH (OrderDate) 월,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5

요약

지금까지 SQL ROW_NUMBER 함수를 자세히 검토했습니다. 간단한 것부터 복잡한 것까지 몇 가지 예를 논의했습니다. 또한 CTE (Common Table Expressions)와 함께 SQL ROW_NUMBER 함수를 사용하는 방법에 대해 설명했습니다. 대부분의 경우 모든 창 함수에 항상 over 절이 표시됩니다.

over 절은 각 행에 표시되는 창을 정의합니다. over 절에는 파티션 by가 있으며, 다시 모든 창 함수에서 지원하고 order by 절이 뒤 따릅니다. 지금은 여기까지입니다… 기사를 즐겁게 읽으 셨기를 바랍니다.

  • 작성자
  • 최근 게시물
저는 데이터베이스 기술에 대해 11 년 이상의 풍부한 실무 경험을 가진 데이터베이스 기술자입니다. 저는 Microsoft Certified Professional이며 컴퓨터 응용 프로그램 석사 학위를 받았습니다.
저의 전문 분야는 고 가용성 솔루션과 크로스 플랫폼 DB 마이그레이션을 구현하는 & 설계에 있습니다. 현재 작업중인 기술은 SQL Server, PowerShell, Oracle 및 MongoDB입니다.
Prashanth Jayaram의 모든 게시물보기

Prashanth Jayaram의 최신 게시물 (전체보기)
  • SQL의 데이터베이스 감사에 대한 간략한 개요-2021 년 1 월 28 일
  • Azure SQL 데이터베이스와 온-프레미스 SQL Server간에 Azure 데이터 동기화를 설정하는 방법-2021 년 1 월 20 일
  • PowerShell을 사용하여 Azure SQL 데이터베이스 가져 오기 / 내보내기 작업을 수행하는 방법-1 월 14 일 , 2021

admin
0

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다

보관함

최신 글