SQL Server 窗口（排序）row_number()、rank()、dense_rank()、ntile(n) 函数总结

一、窗口函数语法

<窗口函数> OVER ([PARTITION BY <分组列> [, <分组列>...]]
                     [ORDER BY <排序列> [ASC | DESC] [, <排序列> [ASC | DESC]]...]
                     [<rows or range clause>])

其中：

<窗口函数> : 定义要在窗口中计算的聚合函数或其它分析函数，如COUNT、RANK、SUM等。
OVER : 窗口函数的核心关键字。
PARTITION BY : 定义要用来分组的一组列名。
ORDER BY : 定义用来排序的一组列名。
<rows or range clause> : 定义窗口的行集合。默认为 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，表示窗口包括从窗口开始到当前行的所有行。

二、准备数据源

创建表：a_jxl_pos_test

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[a_jxl_pos_test]') AND type IN ('U'))
	DROP TABLE [dbo].[a_jxl_pos_test]
GO

CREATE TABLE [dbo].[a_jxl_pos_test] (
  [storeName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [barcode] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [quantity] int  NULL,
  [amount] decimal (20,2),
  [year] int  NULL,
  [month] int  NULL,
  [name] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

数据源如下：

三、窗口（排序）函数介绍
SQL Server中的排序函数有四个：row_number()，rank()，dense_rank()及ntile()函数；

1、row_number()函数
特点： row_number()函数可以为每条记录添加递增的顺序数值序号，即使值完全相同也依次递增序号，不会重复。

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

PARTITION BY子句将结果集划分为分区。 ROW_NUMBER()函数分别应用于每个分区，并重新初始化每个分区的行号。PARTITION BY子句是可选的。如果未指定，ROW_NUMBER()函数会将整个结果集视为单个分区。

ORDER BY子句定义结果集的每个分区中的行的逻辑顺序。 ORDER BY子句是必需的，因为ROW_NUMBER()函数对顺序敏感。

1)、PARTITION BY 划分规则未指定即不分组，按金额从大到小排序

select row_number() OVER(ORDER BY amount desc) no,* from a_jxl_pos_test

2)、PARTITION BY 划分规则根据门店分组，按金额从大到小排序

select row_number() OVER(PARTITION BY storeName ORDER BY amount desc) no,* from a_jxl_pos_test

3)、找出各分组的前3名

select * from(select row_number() OVER(PARTITION BY storeName ORDER BY amount desc) no,* from a_jxl_pos_test)a where no <= 3

2、rank()函数

特点：rank()函数也是返回每条记录的排名序号，但当值相同时，序号也将相同，同时跳跃排序

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

PARTITION BY子句划分应用该函数的结果集分区的行。
ORDER BY子句指定应用该函数每个分区中行的逻辑排序顺序。

select rank() OVER(ORDER BY quantity desc),* from a_jxl_pos_test

3、dense_rank()函数

特点：dense_rank()函数进行排序时，也会将值相同的数据赋予同一个序号，但与rank()函数不同的是，dense_rank()函数为连续排序

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

dense_rank()函数以ORDER BY子句定义的指定顺序应用于PARTITION BY子句定义的每个分区的行。它会在划分分区边界时重置等级。
PARITION BY子句是可选的。如果省略它，该函数会将整个结果集视为单个分区。

select dense_rank() OVER(ORDER BY quantity desc),* from a_jxl_pos_test

4、ntile(n)函数
特点： ntile(n)函数会将数据分为n组，自动进行分组（每组数量大致相等，若无法均分为n组，则每组的记录数不能大于它上一组的记录数），每组将会分配同一个序号（组号为1-n）。

NTILE(integer_expression) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

integer_expression为数据行划分的组数。
PARTITION BY子句将结果集的行分配到应用了NTILE()函数的分区中。
ORDER BY子句指定应用NTILE()的每个分区中行的逻辑顺序。

select ntile(4) OVER(ORDER BY quantity desc),* from a_jxl_pos_test