Wednesday, August 6, 2014

Over Clause

OVER Clause (Transact-SQL)

SQL Server 2008 R2
Other Versions

Determines the partitioning and ordering of the rowset before the associated window function is applied.
Applies to:
Ranking window functions
Aggregate window functions. For more information, see Aggregate Functions (Transact-SQL).
Topic link iconTransact-SQL Syntax Conventions
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifSyntax
Copy

Ranking Window Functions
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
           <ORDER BY_Clause> )

Aggregate Window Functions
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifArguments
PARTITION BY
Divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
value_expression
Specifies the column by which the rowset produced by the corresponding FROM clause is partitioned. value_expression can only refer to columns made available by the FROM clause. value_expression cannot refer to expressions or aliases in the select list. value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.
<ORDER BY Clause>
Specifies the order to apply the ranking window function. For more information, see ORDER BY Clause (Transact-SQL).
Important noteImportant
When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifRemarks
Window functions are defined in the ISO SQL standard. SQL Server provides ranking and aggregate window functions. A window is a user-specified set of rows. A window function computes a value for each row in a result set derived from the window.
More than one ranking or aggregate window function can be used in a single query with a single FROM clause. However, the OVER clause for each function can differ in partitioning and also ordering. The OVER clause cannot be used with the CHECKSUM aggregate function.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifExamples

A. Using the OVER clause with the ROW_NUMBER function

Each ranking function, ROW_NUMBER, DENSE_RANK, RANK, NTILE uses the OVER clause. The following example shows using the OVER clause with ROW_NUMBER.
Copy
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Person p
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address a
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO

B. Using the OVER clause with aggregate functions

The following examples show using the OVER clause with aggregate functions. In this example, using the OVER clause is more efficient than using subqueries.
Copy
USE AdventureWorks2008R2;
GO
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 Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO
Here is the result set.
SalesOrderID
 ProductID
 OrderQty
 Total
 Avg
 Count
 Min
 Max
43659
776
1
26
2
12
1
6
43659
777
3
26
2
12
1
6
43659
778
1
26
2
12
1
6
43659
771
1
26
2
12
1
6
43659
772
1
26
2
12
1
6
43659
773
2
26
2
12
1
6
43659
774
1
26
2
12
1
6
43659
714
3
26
2
12
1
6
43659
716
1
26
2
12
1
6
43659
709
6
26
2
12
1
6
43659
712
2
26
2
12
1
6
43659
711
4
26
2
12
1
6
43664
772
1
14
1
8
1
4
43664
775
4
14
1
8
1
4
43664
714
1
14
1
8
1
4
43664
716
1
14
1
8
1
4
43664
777
2
14
1
8
1
4
43664
771
3
14
1
8
1
4
43664
773
1
14
1
8
1
4
43664
778
1
14
1
8
1
4
The following example shows using the OVER clause with an aggregate function in a calculated value.
Copy
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)
        *100 AS DECIMAL(5,2))AS 'Percent by ProductID'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO
Here is the result set. Notice that the aggregates are calculated by SalesOrderID and the Percent by ProductID is calculated for each line of each SalesOrderID.
SalesOrderID
 ProductID
 OrderQty
 Total
 Percent by ProductID
43659
776
1
26
3.85
43659
777
3
26
11.54
43659
778
1
26
3.85
43659
771
1
26
3.85
43659
772
1
26
3.85
43659
773
2
26
7.69
43659
774
1
26
3.85
43659
714
3
26
11.54
43659
716
1
26
3.85
43659
709
6
26
23.08
43659
712
2
26
7.69
43659
711
4
26
15.38
43664
772
1
14
7.14
43664
775
4
14
28.57
43664
714
1
14
7.14
43664
716
1
14
7.14
43664
777
2
14
14.29
43664
771
3
14
21.43
43664
773
1
14
7.14
43664
778
1
14
7.14

 

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)