Microsoft SQL Server
OVER 절

매개 변수

비고

OVER와 함께 집계 함수 사용

Cars 표를 사용하여 각 costumer가 소비 한 돈의 합계, 최대, 최소 및 평균 금액을 계산하고 수리를 위해 차량을 가져온 횟수 (COUNT)를 계산합니다.

Id CustomerId MechanicId 모델 상태 총 비용

SELECT CustomerId,  
       SUM(TotalCost) OVER(PARTITION BY CustomerId) AS Total,
       AVG(TotalCost) OVER(PARTITION BY CustomerId) AS Avg,
       COUNT(TotalCost) OVER(PARTITION BY CustomerId) AS Count,
       MIN(TotalCost) OVER(PARTITION BY CustomerId) AS Min,
       MAX(TotalCost) OVER(PARTITION BY CustomerId) AS Max
  FROM CarsTable
 WHERE Status = 'READY'

이 방식으로 OVER를 사용하면 반환 된 행을 집계하지 않습니다. 위 쿼리는 다음을 반환합니다.

중복 된 행은보고 목적으로 유용하지 않을 수 있습니다.

데이터를 단순히 집계하려면 적절한 집계 함수 Eg와 함께 GROUP BY 절을 사용하는 것이 좋습니다.

SELECT CustomerId,  
       SUM(TotalCost) AS Total,
       AVG(TotalCost) AS Avg,
       COUNT(TotalCost) AS Count,
       MIN(TotalCost) AS Min,
       MAX(TotalCost)  AS Max
  FROM CarsTable
 WHERE Status = 'READY'
GROUP BY CustomerId

누적 합계

품목 판매 표를 사용하여 품목을 통한 판매가 날짜를 통해 어떻게 증가하는지 알아 봅니다. 그렇게하기 위해 우리는 판매 주문에 의한 품목 주문 당 총 판매의 누적 합계 를 계산할 것입니다.

SELECT item_id, sale_Date 
       SUM(quantity * price) OVER(PARTITION BY item_id ORDER BY sale_Date ROWS BETWEEN UNBOUNDED PRECEDING) AS SalesTotal
  FROM SalesTable

집계 기능을 사용하여 가장 최근의 레코드 찾기

라이브러리 데이터베이스를 사용하여 저자별로 데이터베이스 에 추가 된 마지막 책을 찾습니다. 이 간단한 예제에서 우리는 추가 된 각 레코드에 대해 항상 증가하는 Id를 가정합니다.

SELECT MostRecentBook.Name, MostRecentBook.Title
FROM ( SELECT Authors.Name,
              Books.Title,
              RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.Id DESC) AS NewestRank
       FROM Authors
       JOIN Books ON Books.AuthorId = Authors.Id
     ) MostRecentBook
WHERE MostRecentBook.NewestRank = 1

RANK 대신 두 가지 다른 기능을 사용하여 주문할 수 있습니다. 이전 예에서 결과는 동일하지만 순서에 따라 각 순위에 대해 여러 행이 제공되는 경우 결과가 다릅니다.

• RANK() : 동일한 순위를 얻는 중복, 다음 순위는 이전 순위의 중복 횟수를 고려합니다.
• DENSE_RANK() : 동일한 순위를 얻으면 중복되며, 다음 순위는 항상 이전 순위보다 하나 더 높습니다.
• ROW_NUMBER() : 각 행에 고유 한 '순위'를 부여하고 무작위로 '순위 지정'합니다.

예를 들어, 테이블에 고유하지 않은 CreationDate 열이 있고 그 순서에 따라 정렬이 수행 된 경우 다음 쿼리가 수행됩니다.

SELECT Authors.Name,
       Books.Title,
       Books.CreationDate,
       RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS RANK,
       DENSE_RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS DENSE_RANK,
       ROW_NUMBER() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS ROW_NUMBER,
FROM Authors
JOIN Books ON Books.AuthorId = Authors.Id

결과 :

NTILE을 사용하여 균등 분할 된 버킷으로 데이터 나누기

여러 시험의 시험 점수가 있으며 시험 당 4 분위로 나누기를 원한다고 가정 해 봅시다.

-- Setup data:
declare @values table(Id int identity(1,1) primary key, [Value] float, ExamId int)
insert into @values ([Value], ExamId) values
(65, 1), (40, 1), (99, 1), (100, 1), (90, 1), -- Exam 1 Scores
(91, 2), (88, 2), (83, 2), (91, 2), (78, 2), (67, 2), (77, 2) -- Exam 2 Scores

-- Separate into four buckets per exam:
select ExamId, 
       ntile(4) over (partition by ExamId order by [Value] desc) as Quartile, 
       Value, Id 
from @values 
order by ExamId, Quartile

ntile 은 실제로 많은 수의 버킷이 필요하고 각각이 거의 같은 레벨로 채워질 때 유용합니다. 단순히 ntile(100) 사용하여 이러한 점수를 백분위 ntile(100) 나누는 것은 매우 간단합니다.