In SQL SERVER 2005/2008, two types of ranking window functions and clustered window functions are supported.
For example, in SQL Server, the order number is listed in chronological order.
With OrderInfo as
(
SELECT row_number () over (ORDER by OrderDate) as number,
Orderid,customerid, employeeid,orderdate from Orders (NOLOCK)
)
SELECT Number,orderid,customerid, EmployeeID, OrderDate
From OrderInfo WHERE number between 0 and 10
where Row_number () is the ranking function, over () is the window function.
The window function over () specifies a set of rows and the window function calculates the values of the rows in the result set from the window function output.
The window function does not need to use Group by to group the data, but it can also return the columns and aggregate columns of the underlying row.
1. Ranking Window function
Row_number, Dense_rank, rank, ntile belong to the ranking function.
The Rank window function can be used by either the order BY statement alone or with partition by.
PARTITION by is used to group the result set, and the window function is applied to each group.
ODER by specifies the order of the ranking window functions. The order BY statement must be used in the ranking window function.
For example, query each employee's order and sort by time
With OrderInfo as
(
SELECT row_number () over (PARTITION by EmployeeID ORDER by OrderDate) as number,
Orderid,customerid, employeeid,orderdate from Orders (NOLOCK)
)
SELECT Number,orderid,customerid, EmployeeID, OrderDate
From OrderInfo WHERE number between 0 and 10
The window function groups data rows by employee ID according to the partition by statement, and then sorts by the order by statement, and the Rank function row_number () generates a sequence number starting with 1 for each group of data.
Row_number () generates a unique ordinal number for each group of rows sequentially
RANK () also generates a sequence number for each group of rows, unlike row_number () if the order by is ordered by, if the same value produces the same ordinal, and the next sequence number is not sequential. For example, two identical lines generate sequence number 3, then the next generation is ordinal 5.
Dense_rank () is similar to RANK (), and the difference is that if you have the same ordinal number, the next sequence number will not be interrupted. That is, if two identical lines generate sequence number 3, then the next generated sequence number is 4.
NTILE (integer_expression) groups The data by a specified number and generates a sequence number for each group.
2. Aggregate open Window functions
Many aggregation functions can be used as operations for window functions, such as sum,avg,max,min.
The aggregate window function can only be used with the partition by clause or without any statements, and ORDER by cannot be used with the aggregate window function.
For example, query the total number of orders and order information for an employee
With OrderInfo as
(
SELECT COUNT (OrderID) over (PARTITION by EmployeeID) as Totalcount,orderid,customerid, employeeid,orderdate from Orders ( NOLOCK)
)
SELECT Orderid,customerid, EmployeeID, Orderdate,totalcount
From OrderInfo ORDER by EmployeeID
If the window function does not use the partition by statement, then the data is not grouped, and the aggregate function calculates the values of all rows.
With OrderInfo as
(
SELECT COUNT (OrderID) over () as Count,orderid,customerid, employeeid,orderdate from Orders (NOLOCK)
)
Introduction to the "Go" SQL SERVER open Window function