SQL累积求和如何实现_SQL累积求和聚合计算方法

SQL累积求和最核心的实现方式是窗口函数SUM() OVER(ORDER BY)，可结合PARTITION BY按组计算，确保顺序唯一性并优化索引以提升性能，相比自连接、子查询等传统方法，窗口函数在效率、可读性和标准性上优势显著。

SQL累积求和，或者说聚合计算中的“跑动总和”（Running Total），最核心、最现代的实现方式就是利用SQL的窗口函数（Window Functions），尤其是

SUM() OVER()

ORDER BY

解决方案

要实现SQL累积求和，我们主要依赖

SUM() OVER()

SUM(expression) OVER (PARTITION BY column_name ORDER BY column_name)

这里面的关键点有：

• SUM(expression)

  登录后复制
  ：这是你想要累积求和的列。

• OVER()

  登录后复制
  ：这表明你正在使用一个窗口函数。

• PARTITION BY column_name

  登录后复制
  （可选）：如果你想在不同的分组（例如，按产品ID、用户ID）内独立进行累积求和，就使用它。如果没有

  PARTITION BY

  登录后复制
  ，累积求和将作用于整个结果集。

• ORDER BY column_name

  登录后复制
  ：这是累积求和的核心。它定义了计算的顺序。累积求和会根据这个顺序，逐行将当前行的值与之前行的值相加。

示例：计算每日销售额的累计总和

假设我们有一个

sales

CREATE TABLE sales (
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 150.00),
('2023-01-03', 200.00),
('2023-01-04', 50.00),
('2023-01-05', 300.00);

要计算每日销售额的累计总和，我们可以这样做：

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS cumulative_amount
FROM
    sales
ORDER BY
    sale_date;

结果：

在这个例子中，

SUM(amount) OVER (ORDER BY sale_date)

amount

sale_date

sale_date

amount

如果你需要按不同的产品或区域进行分组累积，比如

product_id

SELECT
    sale_date,
    product_id,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_amount_per_product
FROM
    product_sales
ORDER BY
    product_id, sale_date;

这样，每个

product_id

设计师AI工具箱

最懂设计师的效率提升平台，实现高效设计出图和智能改图，室内设计，毛坯渲染，旧房改造 ，软装设计

124

查看详情

SQL累积求和在实际业务场景中的应用有哪些？

SQL累积求和在数据分析和报表生成中简直是无处不在，我个人觉得，它解决了很多“看趋势”的需求，而不是仅仅“看当下”。

• 财务分析与报告： 这是最常见的应用。比如，计算公司从年初至今的累计营收、累计利润，或者每个月的累计成本。这能帮助管理层快速了解经营状况的整体走势，而不是只盯着某个单月数据。我做过的一个项目，老板就特别喜欢看“年度累计销售额”，这样他能直观地看到离年度目标还有多远。
• 库存管理： 追踪特定商品的累计入库量、累计出库量，或者计算某个时间段内的实时库存变化。这对于优化供应链、避免缺货或积压非常关键。
• 用户行为分析： 统计用户从注册开始的累计活跃天数、累计消费金额，或者网站的累计访问量。这些指标能帮助产品经理更好地理解用户生命周期价值和产品黏性。
• 项目进度追踪： 在项目管理中，可以计算每个阶段的累计完成任务数或累计投入工时，从而评估项目整体进度是否符合预期。
• 销售业绩追踪： 销售团队经常需要查看每个销售人员或每个区域的累计销售额，这有助于评估业绩表现和制定激励计划。

没有累积求和，很多时候我们只能看到一个个孤立的点，而累积求和则把这些点连成了线，展现了变化和趋势，这对于决策者来说，价值远超单点数据。

使用窗口函数实现累积求和时，有哪些常见陷阱和性能考量？

窗口函数虽然强大，但在实际使用中，确实有一些需要注意的地方，否则可能会踩坑或者遇到性能瓶颈。

• ORDER BY

  登录后复制
  子句的精确性： 这是最最关键的一点。如果

  ORDER BY

  登录后复制
  指定的列值有重复，而你又没有提供一个足够唯一的排序依据（比如再加一个主键），那么在相同排序值下的行的处理顺序可能是不确定的，这会导致每次查询结果可能略有不同。例如，同一天有两笔销售，如果只按

  sale_date

  登录后复制
  排序，这两笔销售的先后顺序不确定，累积结果也会受影响。我的经验是，在

  ORDER BY

  登录后复制
  中尽量包含一个能保证唯一性的字段，比如时间戳或主键ID。

• PARTITION BY

  登录后复制
  的正确使用： 忘了

  PARTITION BY

  登录后复制
  ，你的累积求和就会作用于整个数据集，这往往不是你想要的。反之，如果你想全局累积，却错误地使用了

  PARTITION BY

  登录后复制
  ，结果又会被切分成多个独立计算的块。理解你的业务需求，明确是在全局还是在分组内累积，是避免这个问题的关键。
• 大数据量下的性能： 窗口函数，尤其是涉及到

  ORDER BY

  登录后复制
  的，通常需要数据库对数据进行排序。当处理的行数非常庞大时，这个排序操作会消耗大量的CPU和内存资源，导致查询变慢。
  • 索引优化： 确保

    ORDER BY

    登录后复制
    和

    PARTITION BY

    登录后复制
    子句中涉及的列都有合适的索引。这能显著加快排序速度，减少数据库的负担。我曾经遇到过一个几千万行的大表，没加索引的窗口函数查询能跑几分钟，加了索引后瞬间降到几秒。
  • 内存与磁盘： 如果数据量太大，排序操作可能无法完全在内存中完成，需要将临时数据写入磁盘，这会进一步降低性能。
  • 选择合适的数据库： 不同的数据库在窗口函数的实现和优化上有所差异。例如，一些高性能的OLAP数据库（如ClickHouse、Snowflake）在这方面表现会更好。
• 资源消耗： 累积求和需要在内存中维护一个“状态”，即当前累积到的总和。对于非常大的数据集，这可能导致内存使用量增加。

所以，在编写累积求和的SQL时，我都会先思考数据量级、排序字段的唯一性以及是否有合适的索引，这些往往是决定查询效率的关键。

除了窗口函数，还有其他方法可以实现SQL累积求和吗？它们的优缺点是什么？

当然有，但在现代SQL实践中，它们大多被窗口函数取代了。了解它们主要是为了兼容老旧系统、理解历史背景，或者在极少数特定场景下作为备选。

• 自连接 (Self-Join)：

  • 实现方式： 将表与自身进行连接，通过

    WHERE

    登录后复制
    条件限制连接的行，使得每一行都与所有“之前”的行关联起来，然后对这些关联的行进行求和。
  • 示例：

    SELECT
        s1.sale_date,
        s1.amount,
        SUM(s2.amount) AS cumulative_amount
    FROM
        sales s1
    JOIN
        sales s2 ON s2.sale_date <= s1.sale_date
    GROUP BY
        s1.sale_date, s1.amount
    ORDER BY
        s1.sale_date;

    登录后复制
  • 优点： 兼容性好，几乎所有支持SQL的数据库都支持。在不支持窗口函数的老版本数据库中是主要实现方式。
  • 缺点： 性能极差，尤其是在大数据量下。它会导致笛卡尔积的中间结果，然后进行过滤和聚合，计算量呈平方级增长。代码可读性也相对较差。我个人是极力避免这种写法的，除非真的没有其他选择。

• 相关子查询 (Correlated Subquery)：

  • 实现方式： 在主查询的

    SELECT

    登录后复制
    子句中嵌入一个子查询，这个子查询会根据主查询的每一行数据来计算其对应的累积和。
  • 示例：

    SELECT
        sale_date,
        amount,
        (SELECT SUM(amount) FROM sales WHERE sale_date <= s.sale_date) AS cumulative_amount
    FROM
        sales s
    ORDER BY
        sale_date;

    登录后复制
  • 优点： 逻辑相对直观，易于理解。
  • 缺点： 性能同样极差。子查询会为外层查询的每一行执行一次，如果外层查询有N行，子查询就会执行N次，导致N*M（M为子查询行数）的计算复杂度。在大数据量下，这几乎是不可用的。

• 变量法 (Session Variables - 仅限特定数据库，如MySQL、SQL Server)：

  • 实现方式： 利用数据库的会话变量来存储和更新累积值。这种方法不是标准SQL，依赖于特定数据库的实现。
  • MySQL 示例：

    SET @cumulative_sum := 0;
    SELECT
        sale_date,
        amount,
        (@cumulative_sum := @cumulative_sum + amount) AS cumulative_amount
    FROM
        sales
    ORDER BY
        sale_date;

    登录后复制
  • 优点： 在某些特定数据库（如MySQL）中，对于某些场景，其性能可能优于自连接和相关子查询。逻辑相对清晰。
  • 缺点： 不具备SQL标准通用性，代码可移植性差。不同数据库的语法和行为可能完全不同。依赖于会话状态，可能在并发环境下引发问题（尽管累积求和通常在单次查询中完成）。

总结来说，窗口函数是现代SQL实现累积求和的最佳实践。它在性能、可读性和SQL标准支持方面都远超其他方法。除非有非常特殊的技术限制（比如数据库版本过老），否则，我都会毫不犹豫地选择窗口函数。其他方法更多是作为一种历史回顾或者在极端情况下迫不得已的备选方案。

以上就是SQL累积求和如何实现_SQL累积求和聚合计算方法的详细内容，更多请关注php中文网其它相关文章！