SQL Server Open Window function

　

Turn from http://jimshu.blog.51cto.com/3171847/1376637/

The open window function is defined in the ISO standard. SQL Server provides ranking window functions and aggregate windowing functions.

Before the window function appears, there are many problems that can be solved by SQL statements, many of which are done through complex correlated subqueries or stored procedures. SQL Server 2005 introduces window-opening functions so that these classic puzzles can be easily resolved.

A window is a set of rows that a user specifies. The window function calculates the values of the rows in the result set derived from the window. The window functions are applied to each partition separately, and the calculation is restarted for each partition.

The over clause is used to determine the partitioning and ordering of the rowset before the associated window function is applied. PARTITION by divides the result set into multiple partitions.

First, the ranking window function

1. Syntax

Ranking Window Functions < Over_clause >:: = Over ([PARTITION by Value_expression, ... [N]] <order by_clause>)

Note the ORDER by clause specifies the column on which the rowset generated by the corresponding FROM clause is partitioned. Value_expression can only reference columns that are available through the FROM clause. Value_expression cannot refer to an expression or alias in the select list. A value_expression can be a column expression, a scalar subquery, a scalars function, or a user-defined variable.

2. Example

Refer to http://jimshu.blog.51cto.com/3171847/1176067

Second, the aggregation window function

1. Syntax

Aggregate Window Functions < Over_clause >:: = Over ([PARTITION by Value_expression, ... [n]])

2. Example

The following example partitions according to SalesOrderID and then counts the sum, AVG, COUNT, MIN, MAX for each partition separately.

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 SalesOrderDetail WHERE SalesOrderID in (43659,43664);

The following example is first aggregated by the SalesOrderID partition and calculates the percentage of ProductID per row for each SalesOrderID.

SELECT SalesOrderID, ProductID, OrderQty , SUM (OrderQty) over (PARTITION by SalesOrderID) as ' total ' , CAST (1.0 * orderqty/sum (OrderQty) over (PARTITION by SalesOrderID) *100 as DECIMAL (5,2)) as ' Percent by ProductID ' From SalesOrderDetail WHERE SalesOrderID in (43659,43664);

3. SQL Server 2012 Add features

SQL Server 2012 provides window sorting and framework support for aggregate functions, and you can use the over clause with functions to calculate various aggregated values, such as moving averages, cumulative aggregations, running totals, or the top N results for each group of results.

For more information, please refer to http://technet.microsoft.com/zh-cn/library/ms189461 (v=sql.110). aspx

Iii. Analysis of the window-opening function

Refer to http://jimshu.blog.51cto.com/3171847/1176191

Four, NEXT VALUE for function

By using the OVER clause in the next value for call, the next value for function supports the generation of ordered sequence values. By using the OVER clause, you can guarantee to the user that the returned value is generated in the order of the By clause of the over clauses.

For example:

SELECT NEXT VALUE for test.countby1 through (ORDER by LastName) as Listnumber, FirstName, LastName From Person.Contact;

For more information, please refer to http://technet.microsoft.com/zh-cn/library/ff878370.aspx

SQL Server Open Window function