분석을 위한 창 함수 적용
팁 (조언)
자세한 내용은 텍스트 및 이미지 탭을 참조하세요.
분석 쿼리에는 개별 행 세부 정보를 반환하는 동시에 여러 행에 걸친 계산이 필요한 경우가 많습니다. 기존 집계 함수는 행을 그룹으로 축소하여 행 수준 정보를 손실합니다. Window 함수는 결과 집합을 축소하지 않고 현재 행과 관련된 행 집합에서 계산을 수행하여 이 문제를 해결합니다.
창 함수 구문 이해
Window 함수는 절에 정의된 OVER 행의 "창"에서 값을 계산합니다. 일반 집계 함수와 달리 창 함수는 행을 단일 출력 행으로 그룹화하지 않습니다. 대신, 결과의 모든 원래 행을 유지하면서 관련 행에서 값을 계산합니다.
창 함수의 일반적인 구문은 다음과 같습니다.
function_name(arguments) OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [ASC | DESC]]
[ROWS | RANGE frame_specification]
)
OVER 절 구성 요소는 창 정의 방법을 제어합니다.
- PARTITION BY: 계산을 위해 행을 그룹(파티션)으로 나눕니다.
- ORDER BY: 각 파티션 내의 행의 논리적 순서 결정
- ROWS/RANGE: 현재 행을 기준으로 프레임 경계를 정의합니다.
다음 쿼리는 고객당 총 주문 금액을 계산하는 간단한 창 함수를 보여 줍니다.
SELECT
CustomerID,
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
비고
프레임 사양 없이 절에서 ORDER BY 지정 OVER 하는 경우 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 집계 함수에 대한 것입니다. 이렇게 하면 누적 계산이 생성됩니다.
순위 함수 사용
순위 함수는 파티션 내의 위치에 따라 행에 순차적인 숫자를 할당합니다. SQL Server는 네 가지 순위 함수를 제공합니다. 각 함수는 서로 다른 관계를 처리합니다.
ROW_NUMBER() 는 각 행에 고유한 순차 번호를 할당하며, 연결된 값에 대해서도 중복되지 않습니다.
SELECT
ProductID,
Name,
ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
결과 집합은 다음과 같습니다.
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 2
751 Road-150 Red, 48 3578.27 3
771 Mountain-100 Silver, 38 3399.99 4
이 쿼리는 가격별로 모든 제품의 순위를 가장 높음에서 가장 낮은 값으로 지정합니다. 각 제품은 여러 제품이 동일한 가격을 공유하는지 여부에 관계없이 고유 번호를 받습니다.
RANK() 는 연결된 값에 동일한 순위를 할당한 다음, 숫자를 건너뛰어 동률을 고려합니다.
SELECT
ProductID,
Name,
ListPrice,
RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
결과 집합은 다음과 같습니다.
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 4
두 제품의 가격이 동일하면 둘 다 동일한 순위를 받습니다. 다음 제품의 순위는 순위가 높은 총 제품 수를 반영하여 시퀀스에서 차이를 만듭니다.
DENSE_RANK() 는 연결된 값에 동일한 순위를 할당하지만 숫자를 건너뛰지는 않습니다.
SELECT
ProductID,
Name,
ListPrice,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
결과 집합은 다음과 같습니다.
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 2
마찬가지로 RANK(), 연결된 값은 동일한 순위를 공유합니다. 그러나 DENSE_RANK() 다음 연속 번호로 계속 진행하므로 이를 사용하여 고유한 가격 수준을 계산할 수 있습니다.
NTILE(n) 은 행을 대략 같음 그룹의 지정된 수로 분산합니다.
SELECT
ProductID,
Name,
ListPrice,
NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;
결과 집합은 다음과 같습니다.
ProductID Name ListPrice PriceQuartile
--------- --------------------------- --------- -------------
749 Road-150 Red, 62 3578.27 1
771 Mountain-100 Silver, 38 3399.99 1
722 LL Road Frame - Black, 58 337.22 2
859 Half-Finger Gloves, S 24.49 4
이 쿼리는 가격을 기준으로 제품을 4개의 그룹으로 나눕니다. 가장 높은 가격의 제품은 사분위수 1에 있으며, 가장 낮은 가격은 사분위수 4에 있습니다.
NTILE()는 백분위수 분석이나 작업을 균등하게 분배하는 데에 사용합니다.
순위 함수와 결합하면 PARTITION BY 그룹별 순위를 사용할 수 있습니다.
SELECT
pc.Name AS Category,
p.Name AS Product,
p.ListPrice,
ROW_NUMBER() OVER (
PARTITION BY p.ProductCategoryID
ORDER BY p.ListPrice DESC
) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;
결과 집합은 다음과 같습니다.
Category Product ListPrice CategoryPriceRank
--------------- ------------------------- --------- -----------------
Road Bikes Road-150 Red, 62 3578.27 1
Road Bikes Road-150 Red, 44 3578.27 2
Mountain Bikes Mountain-100 Silver, 38 3399.99 1
Mountain Bikes Mountain-100 Black, 38 3374.99 2
이 쿼리는 각 범주 내의 제품 순위를 개별적으로 지정합니다. 순위는 각 범주에 대해 1에서 다시 시작되므로 필터링하여 CategoryPriceRank = 1각 범주에서 가장 비싼 제품을 식별할 수 있습니다.
팁 (조언)
순위당 정확히 하나의 행이 필요할 때 사용합니다 ROW_NUMBER() (예: 그룹당 상위 N개 찾기). 보고 목적으로 연결 정보를 보존해야 하는 경우 RANK() 또는 DENSE_RANK()를 사용합니다.
집계 창 함수 적용
, , SUMAVGCOUNT등의 MIN표준 집계 함수는 MAX 절을 추가하여 OVER 창 함수로 사용할 수 있습니다. 이렇게 하면 개별 행 세부 정보를 유지하면서 집계를 계산할 수 있습니다.
다음 쿼리는 실행 중인 합계 및 누적 집계를 계산하는 방법을 보여 줍니다.
SELECT
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;
결과 집합은 다음과 같습니다.
SalesOrderID OrderDate TotalDue RunningTotal RunningAverage OrderNumber
------------ ---------- --------- ------------ -------------- -----------
71774 2008-06-01 972.785 972.785 972.785 1
71776 2008-06-01 87.083 1059.868 529.934 2
71780 2008-06-01 42452.65 43512.518 14504.172 3
71782 2008-06-01 43962.79 87475.308 21868.827 4
중요합니다
ORDER BY 절 없이 OVER 집계 창 함수를 사용하는 경우, 함수는 전체 파티션에 걸쳐 계산됩니다. 추가하면 ORDER BY 파티션 시작부터 현재 행까지 실행 중인 계산이 만들어집니다.
ROWS 및 RANGE로 창 프레임을 정의하세요
창 프레임을 사용하면 현재 행을 기준으로 계산에 포함해야 하는 행을 정확하게 지정할 수 있습니다.
ROWS 절은 물리적 행 수를 계산하는 반면, RANGE는 같은 값을 가진 행들을 그룹화합니다.
프레임 경계는 다음을 사용하여 지정할 수 있습니다.
-
UNBOUNDED PRECEDING: 파티션 시작부터 -
n PRECEDING:n현재 행 앞의 행 -
CURRENT ROW: 현재 행 -
n FOLLOWING:n현재 행 뒤의 행 -
UNBOUNDED FOLLOWING: 파티션 끝으로
다음 쿼리는 마지막 세 가지 주문에 대한 이동 평균을 계산합니다.
SELECT
SalesOrderID,
OrderDate,
TotalDue,
AVG(TotalDue) OVER (
ORDER BY OrderDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
결과 집합은 다음과 같습니다.
SalesOrderID OrderDate TotalDue MovingAvg3Orders
------------ ---------- --------- ----------------
71774 2008-06-01 972.785 972.785
71776 2008-06-01 87.083 529.934
71780 2008-06-01 42452.65 14504.172
71782 2008-06-01 43962.79 28834.174
이 쿼리는 현재 행과 행 앞에 두 행을 포함하여 3차 이동 평균을 계산합니다. 첫 번째 행의 경우 하나의 값만 사용할 수 있으므로 평균은 같습니다 TotalDue. 세 번째 행으로 창에는 세 개의 행이 모두 포함됩니다.
분석 함수 사용
분석 함수를 사용하면 자체 조인 또는 하위 쿼리를 사용하지 않고도 다른 행의 데이터에 액세스할 수 있습니다. 이러한 함수는 시계열 분석, 추세 검색 및 현재 값을 기록 값 또는 미래 값과 비교하는 데 유용합니다. 요약을 계산하는 집계 창 함수와 달리 분석 함수는 창의 특정 행에서 특정 값을 검색합니다.
LAG() 및 LEAD() 다음과 같이 이전 행 또는 이후 행의 값에 액세스합니다.
SELECT
SalesOrderID,
OrderDate,
TotalDue,
LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
결과 집합은 다음과 같습니다.
SalesOrderID OrderDate TotalDue PreviousOrderTotal NextOrderTotal ChangeFromPrevious
------------ ---------- --------- ------------------ -------------- ------------------
71774 2008-06-01 972.785 0 87.083 972.785
71776 2008-06-01 87.083 972.785 42452.65 -885.702
71780 2008-06-01 42452.65 87.083 43962.79 42365.567
71782 2008-06-01 43962.79 42452.65 0 1510.14
LAG() 는 다음 행에서 검색하는 동안 LEAD() 이전 행에서 값을 검색합니다. 두 번째 매개 변수는 뒤로 또는 앞으로 살펴볼 행 수를 지정하고(기본값: 1), 행이 없는 경우(예: 첫 번째 행의 경우) 세 번째 매개 변수는 기본값을 LAG()제공합니다. 이러한 함수를 사용하여 기간별 변경 내용을 계산하거나, 추세를 식별하거나, 순차적 데이터에서 변칙을 검색합니다.
FIRST_VALUE() 및 LAST_VALUE()는 프레임의 첫 번째 또는 마지막 행에서 값을 반환합니다.
SELECT
ProductID,
Name,
ListPrice,
ProductCategoryID,
FIRST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
) AS MostExpensiveInCategory,
LAST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;
결과 집합은 다음과 같습니다.
ProductID Name ListPrice ProductCategoryID MostExpensiveInCategory LeastExpensiveInCategory
--------- ------------------------- --------- ----------------- ------------------------ ------------------------
749 Road-150 Red, 62 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
750 Road-150 Red, 44 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
722 LL Road Frame - Red, 58 337.22 5 Road-150 Red, 62 LL Road Frame - Red, 58
771 Mountain-100 Silver, 38 3399.99 6 Mountain-100 Silver, 38 Mountain-500 Black, 52
FIRST_VALUE() 는 순서가 지정된 창의 첫 번째 행에서 값을 반환하며, 이 경우 범주당 가장 비용이 많이 드는 제품입니다.
LAST_VALUE() 는 비용이 가장 적게 들지만 모든 행을 포함하려면 명시적 프레임이 필요합니다. 이러한 함수를 사용하면 각 행을 그룹에서 가장 높음, 최하위 또는 기준 값과 같은 벤치마크 값과 비교할 수 있습니다.
비고
LAST_VALUE() 에는 현재 행 뒤의 행을 포함하도록 명시적 프레임 사양이 필요합니다. 이 값이 없으면 기본 프레임에는 현재 행까지의 행만 포함되므로 LAST_VALUE() 현재 행의 값이 반환됩니다.
PERCENT_RANK() 및 CUME_DIST()는 파티션 내의 상대 위치를 계산합니다.
SELECT
Name,
ListPrice,
PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;
결과 집합은 다음과 같습니다.
Name ListPrice PercentRank CumulativeDistribution
------------------------- --------- ----------- ----------------------
Patch Kit/8 Patches 2.29 0.0 0.0081
Road Tire Tube 3.99 0.0081 0.0162
Touring Tire Tube 4.99 0.0162 0.0243
Road-150 Red, 62 3578.27 0.9919 1.0
PERCENT_RANK() 는 값이 낮은 행의 백분율을 나타내는 0에서 1 사이의 값을 반환합니다(0은 가장 낮음, 1은 가장 높음).
CUME_DIST() 는 현재 행보다 작거나 같은 값이 있는 행의 백분율을 나타내는 누적 분포를 보여 줍니다. 백분위수 분석, 이상값 식별 또는 배포 보고서 만들기에 이러한 함수를 사용합니다.
창 함수에 대한 자세한 내용은 창 함수(Transact-SQL) 및 순위 함수를 참조하세요.