sql-server - SQL Server RANK() 用法

Question

我自己总结了一下——SQL Server中的窗口函数。

但这对我来说也非常重要 - 不仅仅是知道如何编写函数，而且 - 在什么场景中我应该使用它们。

好吧，我可以诚实地说，对于 Rank 函数，我没有答案，在什么情况下我应该使用 DENSE_RANK 是非常清楚的:

示例:有 5 名参赛者，其中 2 同时完成了终点线 - 因此两者都获得相同的排名(具有 DENSE_RANK) - 都将获得 1。其他 3 参赛者(也同时完成)将获得排名 2(等等...)

问题:

在什么场景下我应该使用Rank函数，我知道数字是如何生成的，但我没有看到任何使用它的场景

可视化示例:

DECLARE @t TABLE(NAME NVARCHAR(MAX),val money)

insert INTO @t SELECT 'a',100
insert INTO @t SELECT 'a',100
insert INTO @t SELECT 'a',100
insert INTO @t SELECT 'a',100
insert INTO @t SELECT 'b',200
insert INTO @t SELECT 'b',200
insert INTO @t SELECT 'd',400
insert INTO @t SELECT 'e',500
insert INTO @t SELECT 'f',600
select  Name,
        val,
        ROW_NUMBER() OVER (PARTITION BY NAME  ORDER BY name),
        val/ SUM(val) OVER(PARTITION BY NAME )  AS '1AgainstTotalHimself',
        val/ SUM(val) OVER( )  AS '1AgainstOthers' , 
        NTILE(2) OVER ( PARTITION BY NAME ORDER BY name) AS 'ntile2' ,
        NTILE(2) OVER ( ORDER BY name) AS 'ntile' , -- ( 9%2=1  , so group #1 will get more number) 
        RANK( ) OVER ( ORDER BY name  ) AS  Rank,
        DENSE_RANK( ) OVER (ORDER BY name) AS DENSERANK

from @t

Answer 1

RANK() 可以看作是 TOP (n)WITH TIES 的等价物，除了后者只会在整个集合中获取指定的行，而前者也可以通过分区来实现。

因此，如果您需要选择每组前五个结果，但包括与每组中第 5 行相关的所有行(如果有)，RANK() 可以解决此问题为您服务:

WITH ranked AS (
  SELECT
    EmployeeID,
    FirstName,
    LastName,
    DepartmentID,
    Salary,
    rnk = RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC)
  FROM
    dbo.Employee
)
SELECT
  EmployeeID,
  FirstName,
  LastName,
  DepartmentID,
  Salary
FROM
  ranked
WHERE
  rnk <= 5
;