sql 中 sum () over 用法_sql 中 sum () over 分组求和详解-SQL-PHP中文网

sql 中 sum () over 用法_sql 中 sum () over 分组求和详解

爱谁谁

发布： 2025-07-06 13:26:01

原创

151人浏览过

sum() over() 是 sql 中的窗口函数，用于在不减少行数的前提下进行分组聚合计算。1. 它通过 partition by 定义分组，在每行保留原始明细的同时显示组内聚合值；2. 结合 order by 可实现滚动求和；3. 与 group by 的核心区别在于 sum() over() 保持行数不变并保留明细；4. 可用于复杂场景如移动平均、占比计算等；5. 使用时需注意性能问题，可通过索引、数据过滤、预聚合等方式优化。

sql 中 sum () over 用法_sql 中 sum () over 分组求和详解

SUM() OVER() 在 SQL 中，是一个非常强大的窗口函数，它允许你在一个“窗口”内对数据进行聚合计算，而这个“窗口”是基于你定义的分区（PARTITION BY）和排序（ORDER BY）来确定的。与传统的 GROUP BY 聚合不同，SUM() OVER() 不会减少你查询结果的行数，它会在每一行上都显示聚合结果，这对于需要保留原始明细数据，同时又想看到汇总信息的场景来说，简直是神来之笔。

解决方案

要深入理解 SUM() OVER()，我们不妨从最常见的应用场景入手：分组求和。它主要通过 PARTITION BY 子句来定义分组，然后在这个分组内进行 SUM 操作。

举个例子，假设我们有一个销售明细表 sales，包含 product_category（产品类别）、sale_date（销售日期）和 amount（销售金额）。如果我们想知道每个产品类别下的总销售额，并且希望在每一行销售记录旁边都显示这个总额，而不是像 GROUP BY 那样把所有记录聚合为一行，SUM() OVER() 就派上用场了。

-- 假设表结构
-- CREATE TABLE sales (
--     sale_id INT PRIMARY KEY,
--     product_category VARCHAR(50),
--     sale_date DATE,
--     amount DECIMAL(10, 2)
-- );

-- 插入一些示例数据
-- INSERT INTO sales (sale_id, product_category, sale_date, amount) VALUES
-- (1, 'Electronics', '2023-01-01', 100.00),
-- (2, 'Books', '2023-01-02', 50.00),
-- (3, 'Electronics', '2023-01-03', 150.00),
-- (4, 'Books', '2023-01-04', 75.00),
-- (5, 'Electronics', '2023-01-05', 200.00),
-- (6, 'Clothing', '2023-01-06', 120.00);

SELECT
    sale_id,
    product_category,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_category) AS total_category_sales
FROM
    sales;

登录后复制

这段 SQL 会为每一笔销售记录都计算出其所属产品类别的总销售额。你可以看到，即使是同一类别的多笔销售，它们各自的行都会被保留，但 total_category_sales 列的值对于同一类别是相同的。这在分析时非常有用，比如你想计算每笔销售占其所在类别总销售的百分比，就非常方便了。

当然，OVER() 子句里还可以加入 ORDER BY，这会使得 SUM 成为一个“滚动求和”或“累计求和”。例如，如果你想看每个产品类别每天的累计销售额：

SELECT
    sale_id,
    product_category,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_category_sales
FROM
    sales;

登录后复制

这里的 ORDER BY sale_date 意味着 SUM(amount) 会按照 sale_date 的顺序，在每个 product_category 内部进行累加。这比你手动写子查询或者用游标去实现累计求和，效率和代码简洁度都高出不止一个档次。

SUM() OVER() 与 GROUP BY 的核心区别是什么？

这是我第一次接触 SUM() OVER() 时，脑子里蹦出的第一个问题。直观感受上，它们都涉及“求和”，但实际使用场景和结果却大相径庭。

GROUP BY 是一种聚合操作，它的核心目的是将多行数据“压缩”成少量的分组行，每个分组行代表了原数据中符合某个条件的聚合结果。例如，SELECT product_category, SUM(amount) FROM sales GROUP BY product_category; 这条语句，会把所有 'Electronics' 类的销售记录合并成一行，显示 'Electronics' 的总销售额。原始的 sale_id、sale_date 等明细信息，在 GROUP BY 结果中是无法直接看到的。它改变了结果集的行数，通常是减少行数。

而 SUM() OVER()，作为窗口函数，它的哲学完全不同。它在计算聚合值时，不会“折叠”你的数据行。它就像给你的数据集打开了一个“窗口”，在这个窗口里进行计算，然后把计算结果“贴”回每一行原始数据旁边。这意味着，无论你如何定义你的 PARTITION BY 或 ORDER BY，最终查询返回的行数与你原始表的行数（在没有 WHERE 条件过滤的情况下）是一致的。每一行都有其原始的明细数据，同时附带了在特定“窗口”内计算出的聚合值。

所以，核心区别在于：

行数变化： GROUP BY 减少行数；SUM() OVER() 保持行数不变。
数据粒度： GROUP BY 聚合后丢失明细；SUM() OVER() 保留明细。
应用场景： GROUP BY 用于获取分组汇总结果；SUM() OVER() 用于在保留明细的同时，获取基于特定上下文（窗口）的聚合值，常用于排名、累计、占比等分析。

理解了这一点，你就能在不同场景下，选择最合适的工具。有时候，我甚至会先用 SUM() OVER() 得到每行的上下文聚合值，再对结果进行 GROUP BY，实现更复杂的二次聚合。

如何在 SQL 中利用 SUM() OVER() 实现复杂分组求和场景？

当我们谈论复杂场景，通常意味着不仅仅是简单的按一列分组求和。SUM() OVER() 的强大之处在于它对 PARTITION BY 和 ORDER BY 的灵活运用，以及结合窗口帧（ROWS BETWEEN 或 RANGE BETWEEN）。

一个常见的复杂场景是计算“移动平均”或“滚动总和”，比如在电商平台，你可能想看每个用户过去7天的消费总额，或者每个商品类别在特定时间段内的累计销售额。

我们来一个稍微复杂点的例子，假设我们想计算每个产品类别，每天往前3天的累计销售额（包括当天）。

SELECT
    sale_id,
    product_category,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY product_category
        ORDER BY sale_date
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS rolling_3_day_sales_in_category
FROM
    sales
ORDER BY
    product_category, sale_date;

登录后复制

这里 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW 定义了窗口帧。它告诉 SQL，对于当前行，它的窗口包括当前行以及它前面（按 sale_date 排序）的3行。这个窗口会随着当前行的移动而移动。如果前面不足3行，就只计算已有的行。

再比如，你可能需要计算每个产品类别中，每笔销售占该类别总销售额的百分比。

SELECT
    sale_id,
    product_category,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_category) AS total_category_sales,
    (amount / SUM(amount) OVER (PARTITION BY product_category)) * 100 AS percentage_of_category_sales
FROM
    sales;

登录后复制

这里我们两次使用了 SUM(amount) OVER (PARTITION BY product_category)，一次是获取类别总额，另一次是直接用于百分比计算。这种方式让代码非常简洁直观。

我个人觉得，理解 PARTITION BY 是如何定义“独立计算单元”的关键。它就像把你的数据集切分成互不干扰的小块，每个小块里再按照 ORDER BY 进行排序，最后在每个小块的每个“窗口”里执行聚合。一旦你掌握了这种思维，很多之前觉得棘手的报表和分析需求，都会变得迎刃而解。

使用 SUM() OVER() 可能遇到的性能问题及优化策略？

虽然 SUM() OVER() 强大且优雅，但它并非没有代价。尤其是在处理海量数据时，性能问题是我们需要特别关注的。我曾经在处理一个上亿行日志数据时，就因为窗口函数的使用不当，导致查询时间长得令人发指。

潜在的性能瓶颈通常在于：

大数据量下的排序和分区： PARTITION BY 和 ORDER BY 子句都需要对数据进行排序操作。当涉及的数据量非常大时，这个排序过程会消耗大量的内存和 CPU 资源，甚至可能导致磁盘溢出（tempdb 使用量暴增）。
复杂窗口帧的计算： 像 ROWS BETWEEN 这样的窗口帧，虽然灵活，但在某些数据库实现中，计算起来可能比简单的全分区聚合（不带 ORDER BY）更耗资源。

那么，该如何优化呢？

索引是你的第一道防线：
- 为 PARTITION BY 子句中使用的列创建索引。这能帮助数据库系统更快地定位和分组数据，减少全表扫描。
- 如果 OVER() 子句中还包含 ORDER BY，那么在 PARTITION BY 列和 ORDER BY 列上创建复合索引，并且 ORDER BY 的列放在索引的后面，效果会更好。例如，CREATE INDEX IX_Sales_Category_Date ON sales (product_category, sale_date);。这样，数据库在进行分区和排序时，可以直接利用索引的有序性，避免额外的排序操作。
缩小数据范围： 在执行窗口函数之前，尽可能通过 WHERE 子句过滤掉不需要的数据。数据量越小，窗口函数的计算负担就越轻。这是最直接有效的优化手段。
考虑数据预聚合或物化视图： 对于那些需要频繁查询且计算复杂的窗口函数结果，如果数据更新频率不高，可以考虑预先计算好结果并存储在一个新的表中（即数据预聚合），或者创建物化视图（Materialized View）。这样，用户查询时直接从预计算好的结果中读取，大大提升查询速度。
优化 SQL 语句结构：
- 避免在 OVER() 子句中进行复杂的表达式计算，尽量将计算移到 SELECT 列表之外，或者先计算好再作为列使用。
- 如果可以，将多个独立的窗口函数拆分为 CTE (Common Table Expressions) 或子查询，有时可以帮助优化器更好地理解和执行查询计划，但也要注意过度拆分可能带来的复杂性。
理解数据库的执行计划： 学习如何查看和理解你所用数据库的执行计划（如 SQL Server 的 Execution Plan，PostgreSQL 的 EXPLAIN ANALYZE）。通过执行计划，你可以看到哪个操作是性能瓶颈，是排序、扫描还是其他步骤，从而有针对性地进行优化。