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 ]<ORDER by By_clause>)

• PARTITION: grouping;
• ORDER BY: sort;

First build a debug table as follows:

CREATE TABLE [dbo].[XXX]([Id] [int] NULL,[Name] [nvarchar]( -)NULL,[Operate] [nvarchar]( -)NULL,[score] [int] NULL,[Createtime] [datetime] NULL)  on [PRIMARY]

Add the following data to the inside.

　　

Third, the application scenario

　　1. Aggregate column and data column display together

--　 Check your name, score, and the average of all SELECT CAST (AVGover asdecimal(5,2))  as ' Average score '  from xxx　

　　

　　2, the group date of the latest

--The latest column for everyone's query date SELECT *  from (    SELECTover by ORDER  by as From as CWHERE=1  

　　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.

Four, ranking function

　　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.

    SELECT  Over  by ORDER  by Createtime), score, Name, createtime    from xxx

The output is as follows:

　　

　　2. RANK ()

Returns the ordering of each row within the partition of the result set. The row rank is the number of rows before the related row plus 1. If two or more lines are associated with a rank, each association row gets the same rank.

SELECT  Over  by ORDER  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 ()

　　
　　4, NTILE ()

SQL Server window functions