sql的四大分析函数




2018-07-15

blog_main_img

在 SQL Server 里，窗口函数是处理“分组内排序、排名、分页、Top N、分桶分析”最顺手的工具之一。其中最常用的 4 个排名相关函数是： ROW_NUMBER() RANK() DENSE_RANK() NTILE()

它们看起来很像，但语义差异非常关键。实际开发里，很多查询结果“不对劲”，本质上就是函数选错了。

这篇文章围绕这 4 个函数展开，重点讲清楚：

  • 每个函数的含义
  • 它们之间的核心区别
  • 典型业务场景
  • 常见坑和性能注意点

1. 先理解窗口函数的基本写法

这 4 个函数都属于窗口函数，通常写法如下：

函数名() OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列
)

其中：

  • PARTITION BY：表示先分组，再在组内计算；不写就表示对整个结果集计算
  • ORDER BY：表示窗口内部的排序规则；对于本文这 4 个函数来说，通常必须写

可以把它理解为：

  1. 先按 PARTITION BY 切分数据
  2. 再按 ORDER BY 给每一组排顺序
  3. 最后根据具体函数规则生成编号、名次或分桶结果

2. 准备一份测试数据

下面用一张销售表来说明问题：

CREATE TABLE Sales (
    Id INT PRIMARY KEY,
    DeptName NVARCHAR(50),
    EmployeeName NVARCHAR(50),
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (Id, DeptName, EmployeeName, Amount)
VALUES
    (1, N'华北区', N'张三', 9800),
    (2, N'华北区', N'李四', 8600),
    (3, N'华北区', N'王五', 8600),
    (4, N'华北区', N'赵六', 7200),
    (5, N'华东区', N'钱七', 12000),
    (6, N'华东区', N'孙八', 11000),
    (7, N'华东区', N'周九', 11000),
    (8, N'华东区', N'吴十', 9000);

我们主要做两件事：

  • 看每个大区内部的销售排名
  • 看整体数据如何分桶

3. ROW_NUMBER()：生成唯一顺序号

3.1 基本用法

SELECT
    DeptName,
    EmployeeName,
    Amount,
    ROW_NUMBER() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS RowNum
FROM Sales;

3.2 结果特点

ROW_NUMBER() 的特点非常直接：

  • 无论排序值是否相同，每一行都会拿到一个唯一编号
  • 编号连续，不会重复，也不会跳号

比如在 华北区 中，李四 和 王五 的销售额都是 8600，但 ROW_NUMBER() 仍然会分配不同编号，例如：

张三  9800  1
李四  8600  2
王五  8600  3
赵六  7200  4

3.3 最适合的场景

ROW_NUMBER() 适合：

  • 分页查询
  • 每组取前 N 条
  • 去重保留一条记录
  • 为结果生成稳定的顺序编号

3.4 实战：每个部门取销售额最高的 2 人

WITH RankedSales AS (
    SELECT
        DeptName,
        EmployeeName,
        Amount,
        ROW_NUMBER() OVER (
            PARTITION BY DeptName
            ORDER BY Amount DESC, Id ASC
        ) AS RowNum
    FROM Sales
)
SELECT
    DeptName,
    EmployeeName,
    Amount,
    RowNum
FROM RankedSales
WHERE RowNum <= 2;

这里我额外加了 Id ASC 作为第二排序条件，原因是：

  • 如果只按 Amount DESC 排序
  • 当多个员工销售额相同，SQL Server 可以自由决定它们的先后顺序
  • 这样结果就可能不稳定

所以在生产环境中，ROW_NUMBER() 最好补一个可以打破并列的排序字段。

4. RANK()：并列同名次，后续跳号

4.1 基本用法

SELECT
    DeptName,
    EmployeeName,
    Amount,
    RANK() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS RankNo
FROM Sales;

4.2 结果特点

RANK() 的规则是：

  • 排序值相同，名次相同
  • 一旦出现并列，后面的名次会跳号

例如 华北区：

张三  9800  1
李四  8600  2
王五  8600  2
赵六  7200  4

注意最后一行是 4，不是 3。
因为前面有两个人并列第 2，所以第 3 名被“占掉了”。

4.3 最适合的场景

RANK() 适合：

  • 严格表达比赛名次
  • 需要体现并列，并接受跳号
  • 报表里强调“真实排名”的业务

比如考试、竞赛、业绩榜，很多业务语义上就是“并列第二，下一位第四”。

5. DENSE_RANK()：并列同名次，但不跳号

5.1 基本用法

SELECT
    DeptName,
    EmployeeName,
    Amount,
    DENSE_RANK() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS DenseRankNo
FROM Sales;

5.2 结果特点

DENSE_RANK() 和 RANK() 的区别只有一个：

  • 相同值仍然并列
  • 但后续名次不会跳号

例如 华北区：

张三  9800  1
李四  8600  2
王五  8600  2
赵六  7200  3

可以理解为：

  • RANK() 是“体育比赛排名”
  • DENSE_RANK() 是“去重后的层级排名”

5.3 最适合的场景

DENSE_RANK() 适合：

  • 按不同分值层级编号
  • 需要并列，但不希望名次出现空洞
  • 做分档、分层、等级划分

例如：

  • 销售额第一档、第二档、第三档
  • 不同薪资层级的编号
  • 价格分层

6. NTILE()：把数据分成 N 组

6.1 基本用法

SELECT
    DeptName,
    EmployeeName,
    Amount,
    NTILE(4) OVER (ORDER BY Amount DESC) AS BucketNo
FROM Sales;

6.2 结果特点

NTILE(N) 会把结果集尽量平均地分成 N 组，并给每一行一个组号。

比如：

  • NTILE(2)：分成 2 组
  • NTILE(4)：分成 4 组
  • NTILE(10)：分成 10 组

常见理解方式：

  • 1 组通常表示靠前的一组
  • 组号越大，表示越靠后的组

6.3 一个直观例子

SELECT
    EmployeeName,
    Amount,
    NTILE(4) OVER (ORDER BY Amount DESC) AS Quartile
FROM Sales;

如果有 8 条数据，那么大致会分成 4 组，每组 2 条。
这常用于：

  • 四分位分析
  • 十分位分析
  • 用户分层
  • 销售业绩分桶

6.4 注意事项

NTILE() 有几个容易误解的点：

  • 它是“尽量平均分配行数”，不是按数值区间均匀切分
  • 如果总行数不能整除组数，前面的组通常会多 1 行
  • 如果存在大量相同值，可能会被拆到不同桶里

也就是说，NTILE() 按“行数”分桶，不按“数值范围”分桶。

7. 四个函数放在一起比较

下面这条 SQL 可以一次性看出差异：

SELECT
    DeptName,
    EmployeeName,
    Amount,
    ROW_NUMBER() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC, Id ASC
    ) AS RowNum,
    RANK() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS RankNo,
    DENSE_RANK() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS DenseRankNo,
    NTILE(3) OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS TileNo
FROM Sales;

核心差异可以总结成一张表：

函数 并列值是否同名次 是否跳号 主要用途
ROW_NUMBER() 否 否 唯一编号、分页、Top N
RANK() 是 是 比赛式排名
DENSE_RANK() 是 否 分层排名、等级编号
NTILE(N) 不属于“排名” 不适用 分桶、分组分析

如果用一句话区分：

  • ROW_NUMBER()：强制排出唯一先后
  • RANK()：允许并列，后面跳号
  • DENSE_RANK()：允许并列，后面不跳号
  • NTILE()：不是给名次，而是给桶号

8. 业务里怎么选

8.1 需要分页，用谁？

用 ROW_NUMBER()。

WITH PagedData AS (
    SELECT
        Id,
        DeptName,
        EmployeeName,
        Amount,
        ROW_NUMBER() OVER (ORDER BY Id ASC) AS RowNum
    FROM Sales
)
SELECT *
FROM PagedData
WHERE RowNum BETWEEN 1 AND 5;

8.2 需要“成绩并列第二，下一名第四”，用谁？

用 RANK()。

8.3 需要“成绩并列第二，下一名第三”，用谁？

用 DENSE_RANK()。

8.4 需要把用户分成前 25%、中间 50%、后 25%，用谁？

用 NTILE()。

9. 典型实战场景

9.1 每个部门找出销售额最高的一条记录

WITH T AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DeptName
            ORDER BY Amount DESC, Id ASC
        ) AS RowNum
    FROM Sales
)
SELECT *
FROM T
WHERE RowNum = 1;

这个写法非常常见，比子查询 MAX() 再回表通常更灵活。

9.2 查找每个部门销售额前 3 名，允许并列

如果业务要求“并列第三也算前 3 名”，就不能用 ROW_NUMBER()，而应该用 RANK() 或 DENSE_RANK()。

WITH T AS (
    SELECT
        *,
        RANK() OVER (
            PARTITION BY DeptName
            ORDER BY Amount DESC
        ) AS RankNo
    FROM Sales
)
SELECT *
FROM T
WHERE RankNo <= 3;

如果第三名出现并列，上面这条 SQL 会一起查出来。

9.3 用户价值分层

SELECT
    EmployeeName,
    Amount,
    NTILE(4) OVER (ORDER BY Amount DESC) AS UserLevel
FROM Sales;

可以把用户分为：

  • 第 1 桶：高价值用户
  • 第 2 桶：中高价值用户
  • 第 3 桶：中低价值用户
  • 第 4 桶：低价值用户

10. 常见坑

10.1 只写了 ORDER BY Amount DESC，结果不稳定

如果排序字段有重复值，而你又使用了 ROW_NUMBER()，那么相同 Amount 的记录谁在前谁在后，数据库未必每次都一样。

建议：

  • 在 ORDER BY 里补一个唯一字段
  • 例如 ORDER BY Amount DESC, Id ASC

10.2 把 ROW_NUMBER() 当作排名来用

这是最常见的误区之一。

ROW_NUMBER() 只是编号，不考虑并列名次。
如果业务语义是“排名”，尤其涉及并列，应该优先考虑：

  • RANK()
  • DENSE_RANK()

10.3 误以为 NTILE() 是按数值范围切分

不是。
NTILE(4) 只是把数据行尽量平均分成 4 组，不表示金额区间是平均的。

比如：

  • 第一组可能是 12000、11000
  • 第二组可能是 11000、9800

这并不意味着每组金额跨度一致。

10.4 忘了 PARTITION BY

如果你想看“每个部门内部排名”，却没写 PARTITION BY DeptName，那结果就是“全表统一排名”。

这个错误非常隐蔽，SQL 能执行，但业务含义已经变了。

11. 性能注意点

排名函数本身不神秘，性能瓶颈通常在排序上。

需要注意：

  • OVER (...) ORDER BY ... 往往意味着排序成本
  • 数据量大时，排序列和分组列的索引会影响性能
  • 如果查询里还伴随大范围筛选、关联、聚合，需要结合执行计划分析

一个常见优化思路是：

  • 先尽量过滤数据
  • 再做窗口函数计算

例如先用 WHERE SaleDate >= '2018-01-01' 缩小数据范围，再排名，通常会更合理。

12. 一段记忆口诀

可以这样记：

  • ROW_NUMBER()：一行一个号，绝不重复
  • RANK()：并列同名次，后面会跳号
  • DENSE_RANK()：并列同名次，后面不跳号
  • NTILE()：不是排第几名，而是分到第几桶

13. 总结

这 4 个函数的选择，本质上是在回答 4 个不同问题：

  • ROW_NUMBER()：我只想给每一行排个唯一顺序，谁先谁后必须明确
  • RANK()：我需要真实表达并列名次，并接受跳号
  • DENSE_RANK()：我需要并列，但不希望名次出现空缺
  • NTILE()：我不是要名次，我是想把数据按行数平均切成若干组

如果你在项目里只记住一条经验，那就是：

ROW_NUMBER() 是编号函数，RANK() 和 DENSE_RANK() 才更接近“排名函数”，而 NTILE() 更像“分桶函数”。

14. 附：一组可直接运行的完整示例

CREATE TABLE Sales (
    Id INT PRIMARY KEY,
    DeptName NVARCHAR(50),
    EmployeeName NVARCHAR(50),
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (Id, DeptName, EmployeeName, Amount)
VALUES
    (1, N'华北区', N'张三', 9800),
    (2, N'华北区', N'李四', 8600),
    (3, N'华北区', N'王五', 8600),
    (4, N'华北区', N'赵六', 7200),
    (5, N'华东区', N'钱七', 12000),
    (6, N'华东区', N'孙八', 11000),
    (7, N'华东区', N'周九', 11000),
    (8, N'华东区', N'吴十', 9000);

SELECT
    DeptName,
    EmployeeName,
    Amount,
    ROW_NUMBER() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC, Id ASC
    ) AS RowNum,
    RANK() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS RankNo,
    DENSE_RANK() OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS DenseRankNo,
    NTILE(3) OVER (
        PARTITION BY DeptName
        ORDER BY Amount DESC
    ) AS TileNo
FROM Sales;

如果后面你还想扩展，我建议继续写这几个方向：

  • OVER() 与聚合窗口函数配合使用
  • ROW_NUMBER() 做重复数据清理
  • SQL Server 分页方案对比：TOP、ROW_NUMBER()、OFFSET FETCH
  • 排名函数在报表和 BI 场景中的使用方式