1. Introduction:
window functions in SQL Server 2005 help you quickly see the different levels of aggregation, which makes it easy to accumulate totals, move averages, and perform other calculations.
The window functions are very powerful and easy to use. You can use this technique to get a large amount of statistical value immediately.
A window is a set of rows specified by the user. The open Window function calculates the values of each row in a result set that derives from a window.
2. Scope of application:
Rank open window function and Aggregate open window function.
This means that the window function is combined with the Rank open window function or the Aggregate open window function.
The over clause must precede the rank function or the aggregate function
3. Example:
Copy Code code as follows:
--Create order Form
CREATE TABLE SalesOrder (
OrderID int,--Order ID
OrderQty Decimal (18,2)--Quantity
)
Go
--Inserting data
INSERT INTO SalesOrder
Select 1,2.0
UNION ALL
Select 1,1.0
UNION ALL
Select 1,3.0
UNION ALL
Select 2,6.0
UNION ALL
Select 2,1.1
UNION ALL
Select 3,8.0
UNION ALL
Select 3,1.1
UNION ALL
Select 3,7.0
Go
--Query with the following results
SELECT * FROM SalesOrder
Go
OrderID OrderQty
----------- ------------
1 2.00
1 1.00
1 3.00
2 6.00
2 1.10
3 8.00
3 1.10
3 7.00
The total number of totals is required to display, whenever the percentage, the number of grouped totals, each in the proportion of each group, the required format is as follows:
OrderID OrderQty Summary per single proportional grouping per single ratio in each group
1 2.00 29.20 0.0685 6.00 0.3333
1 1.00 29.20 0.0342 6.00 0.1667
1 3.00 29.20 0.1027 6.00 0.5000
2 6.00 29.20 0.2055 7.10 0.8451
2 1.10 29.20 0.0377 7.10 0.1549
3 8.00 29.20 0.2740 16.10 0.4969
3 1.10 29.20 0.0377 16.10 0.0683
3 7.00 29.20 0.2397 16.10 0.4348
Copy Code code as follows:
--By using window function and aggregation open window function, this requirement can be realized quickly
Select Orderid,orderqty,
SUM (OrderQty) over () as [total],
Convert (Decimal (18,4), Orderqty/sum (OrderQty) over ()) as [per single share],
SUM (OrderQty) over (PARTITION by OrderID) as [group totals],
Convert (Decimal (18,4), Orderqty/sum (OrderQty) over (PARTITION by OrderID)) as [% of each group]
From SalesOrder
ORDER BY OrderID
Window functions are sql2005 new additions, let's look at how to achieve the above results in sql2000:
SQL2000 the implementation of the step is more troublesome, first calculate the total, and then grouped calculated totals, the final connection to get the results
Copy Code code as follows:
--sql2000
DECLARE @sum decimal (18,2)
Select @sum =sum (OrderQty)
From SalesOrder
--Press OrderID to calculate the totals for each group, and then insert a temporary table
Select Orderid,sum (OrderQty) as Su
Into #t
From SalesOrder
GROUP BY OrderID
--Connect temporary tables, get results
Select S.orderid,s.orderqty,
@sum as [totals],
Convert (Decimal (18,4), s.orderqty/@sum) as [percentage per single],
T.su as [group totals],
Convert (Decimal (18,4), s.orderqty/t.su) as [percentage of each group per order]
From SalesOrder s join #t t
On T.orderid=s.orderid
ORDER BY S.orderid
drop table #t
Go
The above demo is the window function and the use of the Aggregate open window function, it and the ranking window function see the following example:
Copy Code code as follows:
--with the Rank open window function using
Select Orderid,orderqty,
Rank () over (PARTITION by Orde RID order by OrderQty) as [group rank],
rank () over (order by OrderQty) as [ranked]
from SalesOrder
?? orde RID ASC
--query results as follows
OrderID OrderQty Group rank
1 2.00 2 4
1 3.00 3 5
1 1.00 1 1
2 1.10 1 2
2 6.00 2 6
3 7.00 2 7
3 8.00 3 8
3 1.10 1 2
