Go to SQL Server Window Function Introduction

Original: http://blog.csdn.net/liu_1983/article/details/7269312

 

In SQL Server 2005/2008, two ranking Window Function and aggregation window function are supported.

Take the SQL Server split page as an example to list order numbers in chronological order.

With orderinfo

(

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

Row_number () is the rank function, and over () is the window function.

The window function over () specifies a group of rows. The window function calculates the values of each row in the result set output from the window function.

 

The window function can group data without using group by. It can also return the columns and aggregate columns of the basic row.

 

1. Ranking Window Function

Row_number, dense_rank, rank, and ntile are ranking functions.

The ranking window function can use the order by statement or partition by statement.

Partition by is used to group result sets. The window function is applied to each group.

Oder by specifies the order of the ranking window function. The order by statement must be used in the ranking window function.

For example, query the orders of each employee and sort the orders by time.

With orderinfo

(

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 based on the partition by statement, and sorts the rows by order by statement. The rank function row_number () generates a sequence number starting from 1 for each group of data.

 

Row_number () generates a unique sequence number for each group of rows in order.

Rank () also generates an sequence number for each group of rows. Unlike row_number (), if order by is used and if there are the same values, the same sequence number is generated, in addition, the subsequent sequence numbers are non-sequential. For example, if two identical rows generate serial number 3, then serial number 5 is generated.

Dense_rank () and rank () are similar. The difference is that if the same sequence number exists, the subsequent sequence number will not be interrupted. That is to say, if two identical rows generate sequence number 3, the sequence number generated next is still 4.

Ntile (integer_expression) groups data according to the specified number and generates an Sn for each group.

 

2. Aggregate Window Function

Many Aggregate functions can be used as window functions, such as sum, AVG, Max, and Min.

The aggregate window function can only use the partition by clause or without any statements. Order by cannot be used together with the aggregate window function.

For example, query the total number of orders and order information of an employee

With orderinfo

(

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, the data is not grouped and the aggregate function calculates the values of all rows.

With orderinfo

(

Select count (orderid) over () as Count, orderid, customerid, employeeid, orderdate from orders (nolock)

)