Updated OVER Clause (Transact-SQL) SQL Server 2012


Definition from Books Online:

The OVER clause defines a window or user-specified set of rows within a query result set.
A window function then computes a value for each row in the window.
You can use the OVER clause with functions to compute aggregated values such as moving averages,
cumulative aggregates, running totals, or a top N per group results.

The extended functions of OVER can simplify the calculation of aggregated value in query.

 
Syntax:

OVER (
       [ <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 frame extent> ::=
{   <window frame preceding>
  | <window frame between>
}

…..
http://msdn.microsoft.com/en-us/library/ms189461(v=sql.110).aspx

One example:

SELECT BusinessEntityID, TerritoryID
    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
    ,DATEPART(yy,ModifiedDate) AS SalesYear
    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                             ORDER BY DATEPART(yy,ModifiedDate)
                                             ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s