In-depth discussion of SQL Server 2005 window functions

Source: Internet
Author: User
Tags count functions sql query window

Windows functions in SQL Server 2005 (window function) are not related to Microsoft Windows; Instead, they create data windows. Window functions help you quickly look at different levels of aggregation, which make it easy to recover cumulative totals, move averages, and perform other calculations.

A SQL Server window is a partition of the rows that are applied to a function. You use over (...) clause to specify a window, you can apply this clause to any one of the aggregate functions. Generally, you divide the data into groups, but the arguments for over () are optional.

USE AdventureWorks2;
GO
SELECT SalesOrderID, ProductID,
OrderQty AS 'Item Qty',SUM(OrderQty)
OVER(PARTITION BY SalesOrderID) AS 'Total Qty',
AVG(OrderQty) OVER(PARTITION BY SalesOrderID)
AS 'Average Qty',
COUNT(OrderQty) OVER(PARTITION BY SalesOrderID)
AS 'Total Count',
MIN(OrderQty) OVER(PARTITION BY SalesOrderID)
AS 'Minimum Count',
MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS
'Maximum Count',LineTotal,
AVG(LineTotal) OVER(PARTITION BY SalesOrderID) AS
'Average Amount',
SUM(LineTotal) OVER(PARTITION BY SalesOrderID) AS
'Total Amount',(LineTotal/SUM(LineTotal)
OVER(PARTITION BY SalesOrderID)) * 100 AS
'Percent'FROM Sales.SalesOrderDetail WHERE
SalesOrderID = 43664ORDER BY ProductID
GO

List A

List B

List A shows how to collect several aggregations at a time, or even other calculations based on aggregations. This allows you to get the result set in List B. I limit the result set to a single OrderID, then the query will produce only one window. You can modify clauses in a variety of ways, such as using an in () clause to list a specific set of orders, or using a between clause to derive a statistical value in a sequential range. Finally, each OrderID you specify gets a window.

The window function is very powerful and easy to build. You can use this technique to immediately search for a large number of statistics. The example in this tutorial shows that you can search for any collection of statistics in a single query.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.