SELECT - WINDOW - (Transact-SQL)

아티클
05/23/2023

적용 대상: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

WINDOW 절의 명명된 창 정의는 OVER 절에서 창을 사용하는 창 함수가 적용되기 전에 행 집합의 분할 및 순서를 결정합니다.

참고

WINDOW 절에는 데이터베이스 호환성 수준 160 이상이 필요합니다. 데이터베이스 호환성 수준이 160보다 낮으면 SQL Server는 WINDOW 절을 사용하여 쿼리를 실행할 수 없습니다.

sys.databases 뷰 또는 데이터베이스 속성에서 호환성 수준을 검사 수 있습니다. 다음 명령을 사용하여 데이터베이스의 호환성 수준을 변경할 수 있습니다.

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160

Transact-SQL 구문 표기 규칙(Transact-SQL)

구문

WINDOW window_name AS (
       [ reference_window_name ]   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  
  
<PARTITION BY clause> ::=  
PARTITION BY value_expression , ... [ n ]  
  
<ORDER BY clause> ::=  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]  
  
<ROW or RANGE clause> ::=  
{ ROWS | RANGE } <window frame extent>

인수

window_name

정의된 창 사양의 이름입니다. 이 이름은 OVER 절의 창 함수가 창 사양을 참조하는 데 사용됩니다. 창 이름은 반드시 식별자에 적용되는 규칙을 준수해야 합니다.

reference_window_name

현재 창에서 참조되는 창의 이름입니다. 참조되는 창은 WINDOW 절에 정의된 창 중 하나여야 합니다.

다른 인수는 다음과 같습니다.

PARTITION BY - 쿼리 결과 집합을 파티션으로 분할합니다.
ORDER BY - 결과 집합의 각 파티션 내에서 행의 논리적 순서를 정의합니다.
ROWS/RANGE - 파티션 내의 시작점 및 끝점을 지정하여 파티션 내의 행을 제한합니다.

인수에 대한 자세한 내용은 OVER 절을 참조하세요

일반적인 설명

WINDOW 절에 둘 이상의 명명된 창을 정의할 수 있습니다.

window_name과 추가 사양을 차례로 사용하여 OVER 절의 명명된 창에 추가 구성 요소를 추가할 수 있습니다. 다만 WINDOW 절에서 지정된 속성은 OVER 절에서 다시 정의할 수 없습니다.

쿼리가 여러 창을 사용하는 경우 명명된 창이 window_name을 사용하여 다른 명명된 창을 참조할 수 있습니다. 이 경우 참조되는 window_name을 참조 창의 창 정의에서 지정해야 합니다. 한 창에서 정의된 창 구성 요소는 해당 구성 요소를 참조하는 다른 창에서 다시 정의할 수 없습니다.

창 절에서 창이 정의되는 순서에 따라 전향 및 후향 창 참조가 허용됩니다. 즉, 창은 정의되는 순서에 관계없이 창이 속한 <window_expression>에 정의된 다른 모든 창을 reference_window_name으로 사용할 수 있습니다. 순환 참조 및 단일 창에서 여러 창 참조 사용은 허용되지 않습니다.

<window_expression>에 포함된 정의된 창의 새 window_name 범위는 window 절을 포함하는 <query_specification> 또는 <SELECT statement>의 SELECT 절과 함께 <window_expression>의 일부인 모든 창 정의로 구성됩니다. <window_expression>이 단순한 테이블 쿼리인 <query_expression>의 일부인 <query_specification>에 포함된 경우 새 window_name의 범위에는 해당 <query_expression>의 <order_by_expression>(있는 경우)도 포함됩니다.

의미 체계에 기반하여 집계 및 분석 함수와 함께 OVER 절에서 창 사양을 사용하는 데 적용되는 제한 사항은 WINDOW 절에도 적용할 수 있습니다.

예제

A. window 절에 정의된 창 지정

다음 예제 쿼리는 OVER 절에서 명명된 창 사용을 보여 줍니다.

ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO

USE AdventureWorks2022;
GO

SELECT ROW_NUMBER() OVER win AS "Row Number",
    p.LastName, s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson AS s
    INNER JOIN Person.Person AS p
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
WINDOW win AS (PARTITION BY PostalCode ORDER BY SalesYTD DESC)
ORDER BY PostalCode;
GO

다음 쿼리는 위의 쿼리에서 WINDOW 절을 사용하지 않는 경우에 해당합니다.

USE AdventureWorks2022;
GO

SELECT ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName, s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson AS s
    INNER JOIN Person.Person AS p
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
ORDER BY PostalCode;
GO

결과 집합은 다음과 같습니다.

Row Number	LastName	SalesYTD	PostalCode
1	Mitchell	4251368.5497	98027
2	Blythe	3763178.1787	98027
3	Carson	3189418.3662	98027
4	Reiter	2315185.611	98027
5	Vargas	1453719.4653	98027
6	Ansman-Wolfe	1352577.1325	98027
1	Pak	4116871.2277	98055
2	Varkey Chudukatil	3121616.3202	98055
3	Saraiva	2604540.7172	98055
4	Ito	2458535.6169	98055
5	Valdez	1827066.7118	98055
6	Mensa-Annan	1576562.1966	98055
7	Campbell	1573012.9383	98055
8	Tsoflias	1421810.9242	98055

B. 여러 over 절에 단일 창 지정

다음 예제는 창 사양을 정의하고 OVER 절에서 이를 여러 번 사용하는 방법을 보여 줍니다.

ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER win AS Total
    ,AVG(OrderQty) OVER win AS "Avg"
    ,COUNT(OrderQty) OVER win AS "Count"
    ,MIN(OrderQty) OVER win AS "Min"
    ,MAX(OrderQty) OVER win AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664)
WINDOW win AS (PARTITION BY SalesOrderID);
GO

다음 쿼리는 위의 쿼리에서 WINDOW 절을 사용하지 않는 경우에 해당합니다.

USE AdventureWorks2022;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total
    ,AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg"
    ,COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Count"
    ,MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min"
    ,MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max"
    FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO

결과 집합은 다음과 같습니다.

SalesOrderID	ProductID	OrderQty	합계	Avg	개수	Min	Max
43659	776	1	26	2	12	1	6
43659	777	3	26	2	12	1	6
43659	778	1	26	2	12	1	6
43659	771	1	26	2	12	1	6
43659	772	1	26	2	12	1	6
43659	773	2	26	2	12	1	6
43659	774	1	26	2	12	1	6
43659	714	3	26	2	12	1	6
43659	716	1	26	2	12	1	6
43659	709	6	26	2	12	1	6
43659	712	2	26	2	12	1	6
43659	711	4	26	2	12	1	6
43664	772	1	14	1	8	1	4
43664	775	4	14	1	8	1	4
43664	714	1	14	1	8	1	4
43664	716	1	14	1	8	1	4
43664	777	2	14	1	8	1	4
43664	771	3	14	1	8	1	4
43664	773	1	14	1	8	1	4
43664	778	1	14	1	8	1	4

C. window 절에서 공통 사양 정의

이 예제는 창에서 공통 사양을 정의하고 이를 사용하여 OVER 절에서 추가 사양을 정의하는 방법을 보여 줍니다.

ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER win AS Total,
    AVG(OrderQty) OVER(win PARTITION BY SalesOrderID) AS Avg,
    COUNT(OrderQty) OVER(win ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%'
WINDOW win AS (ORDER BY SalesOrderID, ProductID);
GO

다음 쿼리는 위의 쿼리에서 WINDOW 절을 사용하지 않는 경우에 해당합니다.

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
    AVG(OrderQty) OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg,
    COUNT(OrderQty) OVER(ORDER BY SalesOrderID, ProductID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%';
GO

결과 집합은 다음과 같습니다.

OrderNumber	ProductID	OrderQty	합계	Avg	개수
43659	711	4	4	4	2
43659	712	2	6	3	3
43659	714	3	9	3	4
43659	716	1	10	2	5
43664	714	1	11	1	6
43664	716	1	12	1	6

D. 전향 및 후향 창 참조

이 예제는 WINDOW 절에서 새 창을 정의할 때 명명된 창을 전향 및 후향 참조로 사용하는 방법을 보여 줍니다.

ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER win2 AS Total,
    AVG(OrderQty) OVER win1 AS Avg
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%'
WINDOW win1 AS (win3),
    win2 AS (ORDER BY SalesOrderID, ProductID),
    win3 AS (win2 PARTITION BY SalesOrderID);
GO

다음 쿼리는 위의 쿼리에서 WINDOW 절을 사용하지 않는 경우에 해당합니다.

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
    AVG(OrderQty) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%';
GO                                                                                     |

결과 집합은 다음과 같습니다.

OrderNumber	ProductID	OrderQty	합계	Avg
43659	711	4	4	4
43659	712	2	6	3
43659	714	3	9	3
43659	716	1	10	2
43664	714	1	11	1
43664	716	1	12	1

SELECT - WINDOW - (Transact-SQL)

구문

인수

window_name

reference_window_name

일반적인 설명

예제

A. window 절에 정의된 창 지정

B. 여러 over 절에 단일 창 지정

C. window 절에서 공통 사양 정의

D. 전향 및 후향 창 참조

추가 정보

피드백

피드백

추가 리소스