SQL 聚合函数如何结合窗口函数计算排名？

答案是：SQL中聚合函数与窗口函数结合计算排名的核心在于分工协作，前者提供汇总值，后者在保留行级数据的同时进行排序或分组。常见模式有先聚合再排名（如用CTE计算客户总消费后排名），或窗口内聚合后直接排名（如按客户分区计算每笔订单在其内部的排名）。区别在于是否保留原始行细节，窗口函数弥补了聚合函数丢失行信息的不足，支持更复杂的上下文分析。常用排名函数包括ROW_NUMBER()（唯一连续编号）、RANK()（并列同名但跳过后续名次）、DENSE_RANK()（并列同名且不跳名次）和NTILE(N)（分N等份）。性能优化需关注索引、避免COUNT(DISTINCT) OVER、减少多重嵌套、处理数据倾斜，并优先通过预聚合降低数据量。

SQL聚合函数和窗口函数结合计算排名，本质上是在处理数据时，既需要对数据进行汇总统计（聚合函数的职责），又需要保留行的独立性，并在此基础上进行排序、分组或百分比计算（窗口函数的强项）。我个人理解，这并非简单地“叠加”，而更像是“分工协作”——聚合函数可能先为排名提供一个“分数”，或者窗口函数本身就包含聚合操作，为后续的排名提供上下文。核心思想是，聚合函数可以作为窗口函数排序或分区的基础，或者窗口函数内部可以执行聚合操作来定义排名依据。

解决方案

要将SQL聚合函数与窗口函数结合计算排名，最常见的两种模式是：一是先通过聚合函数得到一个汇总值，然后对这个汇总值进行排名；二是利用窗口函数自身的聚合能力，在窗口内进行聚合计算，然后基于这个结果进行排名。

我来举个例子，假设我们有一个销售订单表

SalesOrders

CustomerID

OrderDate

Amount

场景一：先聚合，再排名

这是最直观的组合方式。我们首先需要计算每个客户的总消费额，这显然是聚合函数

SUM()

RANK()

DENSE_RANK()

WITH CustomerTotalSales AS (
    SELECT
        CustomerID,
        SUM(Amount) AS TotalAmount
    FROM
        SalesOrders
    GROUP BY
        CustomerID
)
SELECT
    CustomerID,
    TotalAmount,
    -- 这里我们对聚合后的TotalAmount进行排名
    RANK() OVER (ORDER BY TotalAmount DESC) AS SalesRank
FROM
    CustomerTotalSales;

这段代码的逻辑很清晰：

CustomerTotalSales

SELECT

RANK() OVER (ORDER BY TotalAmount DESC)

场景二：窗口函数内部包含聚合，然后直接排名

有时候，我们想在不丢失原始行信息的前提下，进行一些分组聚合，并基于这些聚合结果进行排名。比如，我们想看每个订单的金额，以及该订单在其所属客户的所有订单中的排名（按金额）。这里，窗口函数可以利用

PARTITION BY

SELECT
    CustomerID,
    OrderDate,
    Amount,
    -- 这里的SUM(Amount) OVER (PARTITION BY CustomerID) 
    -- 是一个窗口聚合，它计算了每个客户的总销售额
    -- 但与GROUP BY不同，它不会合并行，而是将总销售额显示在每一行
    SUM(Amount) OVER (PARTITION BY CustomerID) AS CustomerTotalAmount,
    -- 紧接着，我们可以在这个窗口内对Amount进行排名
    RANK() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS OrderRankWithinCustomer
FROM
    SalesOrders;

在这个例子里，

SUM(Amount) OVER (PARTITION BY CustomerID)

CustomerID

GROUP BY

RANK() OVER (PARTITION BY CustomerID ORDER BY Amount DESC)

我个人觉得，理解这两者的区别和适用场景是关键。第一种是“分步走”，先汇总再排名；第二种是“一步到位”，在保留原始行信息的同时，利用窗口的上下文进行聚合和排名。实际工作中，我发现第二种模式在处理很多业务场景时，能写出更简洁、更高效的SQL。

为什么有时候直接用聚合函数不够，非要引入窗口函数？

这问题问得挺好的，我刚开始学SQL的时候也常常困惑。其实，聚合函数（比如

SUM

AVG

COUNT

MAX

MIN

SUM(Amount)

GROUP BY CustomerID

GROUP BY

这就是窗口函数登场的地方。它就像一个“透视镜”，让你在看数据的时候，既能看到整体（或者说一个“窗口”内的整体），又能看到个体。它不会合并你的行，而是对每一行，根据你定义的“窗口”（

PARTITION BY

ORDER BY

举个例子，如果我只想知道每个客户的总消费额，

GROUP BY

1. 找出每个客户消费最高的3笔订单。

   GROUP BY

   登录后复制
   没法直接做到，因为它会把所有订单都加起来。你需要窗口函数（比如

   RANK()

   登录后复制
   或

   ROW_NUMBER()

   登录后复制
   ）在每个客户内部进行排名，然后筛选。
2. 计算每个订单的金额，以及该订单金额占其所属客户总消费额的比例。 聚合函数只能给你总消费额，但无法在不丢失订单行的情况下，把总消费额和每笔订单金额放在一起计算比例。窗口函数

   SUM(Amount) OVER (PARTITION BY CustomerID)

   登录后复制
   就能完美解决，它既给出了总额，又保留了每笔订单的独立性。
3. 计算累计销售额。 这就是典型的运行总计，

   SUM(Amount) OVER (ORDER BY OrderDate)

   登录后复制
   这种窗口函数特有的功能，

   GROUP BY

   登录后复制
   完全无法实现。

所以，我个人认为，窗口函数是SQL在处理“组内计算”和“上下文分析”时的利器。它弥补了传统聚合函数在保留行细节方面的不足，让我们可以进行更细致、更复杂的分析，而不用去写一堆子查询或者临时表，大大提高了SQL的表达能力和效率。它让数据分析变得更加灵活，能够回答那些既需要全局视角又需要个体细节的问题。

常见的排名函数有哪些？它们之间有什么细微差别？

在SQL的窗口函数家族里，排名函数是出镜率非常高的成员。我平时最常用、也觉得最有必要掌握的，主要有这几个：

ROW_NUMBER()

RANK()

DENSE_RANK()

NTILE(N)

怪兽AI数字人

数字人短视频创作，数字人直播，实时驱动数字人

44

查看详情

1. ROW_NUMBER()

   登录后复制
   • 特点： 为窗口中的每一行分配一个唯一的、连续的整数排名。即使有值相同的行（并列），它们也会得到不同的排名。
   • 何时用： 当你只需要每个分组中的“第1个”、“第2个”等，且并列时任意取一个都可以接受，或者需要强制每个行都有一个独一无二的序号时。比如，每个客户消费最高的订单，哪怕有两笔订单金额一样，我也只想要其中一笔。
   • 例子： 如果有 (100, A), (100, B), (90, C)，

     ROW_NUMBER()

     登录后复制
     可能会给出 (1, A), (2, B), (3, C) 或 (1, B), (2, A), (3, C)，顺序不确定但排名唯一。

2. RANK()

   登录后复制
   • 特点： 为窗口中的每一行分配排名，并列的值会得到相同的排名。但是，下一个非并列的值的排名会跳过并列的个数。
   • 何时用： 当你关心“并列第一”、“并列第二”这种概念，并且希望排名能反映出这种跳跃性时。比如，班级前三名，如果有两个人并列第一，那么第三名就是第三个学生，而不是第二个学生。
   • 例子： 如果有 (100, A), (100, B), (90, C)，

     RANK()

     登录后复制
     会给出 (1, A), (1, B), (3, C)。注意，这里跳过了排名2。

3. DENSE_RANK()

   登录后复制
   • 特点： 和

     RANK()

     登录后复制
     类似，并列的值会得到相同的排名。但不同的是，它不会跳过排名，而是分配连续的排名。
   • 何时用： 当你关心并列，但不希望排名有“空洞”时。比如，我想知道所有不同的销售额对应的排名，或者在并列情况下，希望下一个排名是紧接着的数字。我个人在很多业务场景下更倾向于用

     DENSE_RANK()

     登录后复制
     ，因为它给出的排名更“紧凑”，也更符合一些业务逻辑对“名次”的理解。
   • 例子： 如果有 (100, A), (100, B), (90, C)，

     DENSE_RANK()

     登录后复制
     会给出 (1, A), (1, B), (2, C)。这里没有跳过排名。

4. NTILE(N)

   登录后复制
   • 特点： 将窗口中的行分成 N 个大致相等的分组（桶），并为每个分组分配一个从 1 到 N 的整数。
   • 何时用： 当你需要进行百分位数分析，或者将数据分成若干个等级（比如，将客户分成高价值、中价值、低价值三类）时。它不直接给出“名次”，而是给出“所属等级”。
   • 例子：

     NTILE(4)

     登录后复制
     会将数据分成四等份（四分位数）。如果100行数据，第一组是1-25行，第二组是26-50行，等等。

选择哪个排名函数，真的取决于你的具体业务需求和对“排名”的定义。理解它们在处理并列值时的行为差异，是正确使用的关键。在我的经验里，经常需要根据业务方的要求，来回切换

RANK()

DENSE_RANK()

ROW_NUMBER()

在处理复杂业务逻辑时，如何避免窗口函数和聚合函数组合的性能陷阱？

在我的实际工作中，我发现窗口函数和聚合函数的组合虽然强大，但也确实是性能问题的“重灾区”。尤其是在处理大数据量时，一个不经意的写法可能就会让查询跑上好几分钟，甚至直接超时。要避免这些陷阱，我觉得有几个点是需要特别留意的：

1. 理解

   PARTITION BY

   登录后复制
   和

   ORDER BY

   登录后复制
   的开销：

   • PARTITION BY

     登录后复制
     会导致数据在内部进行分组，这通常需要排序操作。如果

     PARTITION BY

     登录后复制
     的列上没有合适的索引，或者涉及的列太多，数据库引擎可能需要将大量数据加载到内存甚至写入磁盘进行排序，这开销是巨大的。

   • ORDER BY

     登录后复制
     同样，如果没有索引支持，也会导致额外的排序操作。
   • 我的建议： 确保

     PARTITION BY

     登录后复制
     和

     ORDER BY

     登录后复制
     中使用的列都有复合索引（如果它们经常一起出现的话），或者至少是单列索引。索引能显著减少排序和分组的时间。

2. 避免在窗口函数中使用

   DISTINCT

   登录后复制
   聚合：
   • 比如

     COUNT(DISTINCT column) OVER (...)

     登录后复制
     这种写法，性能通常非常差。因为在每个窗口内，数据库都需要维护一个唯一的集合，这比普通的

     COUNT()

     登录后复制
     要复杂得多。
   • 我的建议： 如果非要计算窗口内的唯一计数，考虑是否能通过其他方式实现，例如先

     GROUP BY

     登录后复制
     得到唯一值，再进行窗口操作，或者在应用层进行处理。

3. 警惕多重窗口函数和嵌套 CTE：

   • 在一个查询中应用太多的窗口函数，或者将窗口函数的结果作为另一个窗口函数的输入，有时候会导致数据库引擎进行多次全表扫描或数据读取。
   • 我的建议： 审查执行计划。有时候，将复杂的逻辑拆分成多个简单的 CTE，或者调整 CTE 的顺序，反而能让优化器找到更优的路径。但也要注意，过多的 CTE 可能会让优化器难以全局优化。这确实是个权衡。

4. 数据倾斜问题：

   • 如果

     PARTITION BY

     登录后复制
     的某个键值对应的数据量特别大，而其他键值的数据量很小，就会出现数据倾斜。这会导致处理这个大分区的任务成为瓶颈。
   • 我的建议： 了解你的数据分布。如果某个分区特别大，考虑是否能对数据进行预处理、采样，或者调整

     PARTITION BY

     登录后复制
     的策略，使其分布更均匀。

5. 选择合适的排名函数：

   • 前面提到的

     ROW_NUMBER()

     登录后复制
     、

     RANK()

     登录后复制
     、

     DENSE_RANK()

     登录后复制
     性能上差异不大，但

     NTILE(N)

     登录后复制
     可能会有额外的计算开销，因为它需要确定每个桶的边界。
   • 我的建议： 根据实际需求选择最合适的，不要为了“看起来高级”而选择复杂的函数。

6. 优先使用子查询或 CTE 进行预聚合：

   • 在某些情况下，如果你需要基于聚合结果进行排名，像我前面“先聚合，再排名”的例子那样，先用

     GROUP BY

     登录后复制
     进行一次预聚合，把数据量减少，再对聚合后的少量数据应用窗口函数，这通常比直接在原始大表上应用复杂的窗口函数更高效。
   • 我的建议： 考虑你的业务逻辑是否允许先降维。

总的来说，避免性能陷阱的关键在于理解SQL引擎是如何处理窗口函数的，以及你的数据是如何分布的。多看执行计划，多做实验，是提升SQL性能的必由之路。有时候，一个看似微小的改动，就能带来巨大的性能提升。

以上就是SQL 聚合函数如何结合窗口函数计算排名？的详细内容，更多请关注php中文网其它相关文章！