SQL Server window functions

function of window function

Window functions operate on a set of values, do not need to group the data by using the GROUP BY clause, and can also return the columns and aggregate columns of the underlying row in the same row. window functions, queries for both the underlying column and the aggregate column are very simple.

Second, the syntax format

The syntax format for window functions is as follows:

Over ([PARTITION by Value_expression,.. [N< byby_clause>)    

• PARTITION: grouping;
• ORDER BY: sort;

First build a debug table as follows:

CREATETABLE[Dbo].[Xxx]([Id][Int]Null,[Name][nvarchar](50)Null,[Operate][nvarchar "(50) [score] [int null[createtime< Span style= "color: #ff0000;" >] [datetime null) on [primary           

Add the following data to the inside.

　　

third, the application scenario

　　1. Aggregate column and data column display together

　-- query name, score, and all average split CAST (decimal (5,2))' average from  xxx    

　　2, the group date of the latest

--For each person query date latest column from (as from as        C1      

　　3. Paging

Returns the number of rows in the result set, starting with 1 for each partition, and order by to determine the order in which the rows are assigned unique row_number in a particular partition.

Iv. Ranking function /c3>

　　1, Row_number ()

Returns the number of rows in the result set, each of which is calculated from 1, and order by determines the order in which the rows are assigned unique row_number in a particular partition.

    by fromxxx

The output is as follows:

　　

　　2. RANK ()

Returns the ordering of each row within the partition of the result set. The rank of the line is calculated starting from 1. If two or more lines are associated with a rank, each association row gets the same rank.

by Score), score, Name, createtime from xxx 

The following picture is a good illustration of the difference between rank and row_number.

　　

　　3, Dense_rank ()

Returns the rank of the row in the result set partition, similar to rank (), except for a slightly different processing of the juxtaposition, see example.

by Score), score, Name, createtime fromxxx 

The following example shows the difference between Rank () and Dense_rank ().

　　

　　4, NTILE ()

The Ntile function associates the rows in the result to a group and assigns each row the number of the group to which it belongs, starting with the number 1. For each row, NTILE returns the number of the group to which this row belongs.

If the number of rows in a partition cannot be integer_expression (that is, the passed-in parameter, which means that the meaning of several groups) is divisible, then one member will have two groups of different sizes. Larger groups precede smaller groups in the order specified by the OVER clause.

-- Each partition is divided into 2 groups, which is the group name that the row belongs to SELECT NTILE (by score), score, Name, createtime fromxxx   

Examples are as follows:

　　

SQL Server window functions