Introduction to the "Go" SQL SERVER open Window function

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