دستور OVER PARTITION در SQL

SELECT TOP 100 AVG(Quantity) AS Quantity,
               MAX(Price)    AS Price
FROM [dbo].Decision;

که خروجی آن بصورت زیر است:

Quantity	Price
30289	5000000000

حال اگر بخواهیم شناسه کاربران را هم به خروجی اضافه کنیم با خطا زیر مواجه می‌شویم:

[S0001][8120] Line 1: Column 'oms.tse.new.Decision.CustomerId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT TOP 100 CustomerId,
               AVG(Quantity) AS Quantity,
               MAX(Price)    AS Price
FROM [oms.tse.new].Decision;

در این مواقع می‌توانیم از دستور OVER استفاده کنیم:

SELECT TOP 100 CustomerId,
               AVG(Quantity) OVER (PARTITION BY CustomerId)     AS Quantity,
               MAX(Price) OVER (PARTITION BY CustomerId)        AS Price,
               COUNT(CustomerId) OVER (PARTITION BY CustomerId) AS Count
FROM [dbo].Decision;

SELECT TOP 100 CustomerId,
               AVG(Quantity) OVER (PARTITION BY CustomerId)                AS AvgQuantity,
               MAX(Price) OVER (PARTITION BY CustomerId)                   AS MaxPrice,
               COUNT(CustomerId) OVER (PARTITION BY CustomerId)            AS Count,
               ROW_NUMBER() OVER (PARTITION BY Channel ORDER BY Id ASC) AS RowNumber
FROM [dbo].Decision;

البته استفاده خیلی نتیجه مفیدی ندارد و روش استفاده بصورت زیر نتیجه مفیدی می‌دهد.