理解 SQL SUM OVER 函数|极客笔记

理解 SQL SUM OVER 函数

引言

在 SQL 中，SUM 函数是用来计算指定列数据总和的聚合函数之一。而 SUM OVER 函数则是对 SUM 函数的一种扩展，可以用来在查询结果中为每一行添加一个新的列，该列的值是指定列的累积总和。本文将详细解释 SUM OVER 函数的用法和语法，并通过示例代码和运行结果展示其使用方法。

`SUM OVER` 函数语法

SUM OVER 函数主要用于计算指定列的累积总和，并返回一个结果集，其中每一行都包含了原来的列以及新的累积总和列。

SUM OVER 函数的语法如下：

SUM (col) OVER (PARTITION BY partition_col1, partition_col2... ORDER BY order_col1, order_col2...)

其中，col 为需要计算总和的列名，PARTITION BY 子句用于将结果集分组，指定按照哪些列进行分组；ORDER BY 子句用于指定按照哪些列进行排序。

`SUM OVER` 函数示例

为了更好地理解 SUM OVER 函数的用法和效果，以下将通过一个示例来演示其运行结果。

假设我们有一个名为 orders 的表，包含了客户的订单信息，结构如下：

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10, 2)
);

我们想要计算每个客户的订单金额累积总和，并将结果添加为一列。

以下是一些示例数据：

INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES (1, 1, '2021-01-01', 100.00),
       (2, 1, '2021-02-01', 200.00),
       (3, 2, '2021-03-01', 150.00),
       (4, 2, '2021-04-01', 300.00),
       (5, 2, '2021-05-01', 250.00);

现在，我们可以使用 SUM OVER 函数来计算每个客户的订单金额累积总和，并将结果添加为一列。示例代码如下：

SELECT order_id, customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_amount
FROM orders;

运行上述代码，我们将得到以下结果：

order_id | customer_id | order_date | amount | cumulative_amount
---------+-------------+------------+--------+------------------
      1  |           1 | 2021-01-01 | 100.00 |            100.00
      2  |           1 | 2021-02-01 | 200.00 |            300.00
      3  |           2 | 2021-03-01 | 150.00 |            150.00
      4  |           2 | 2021-04-01 | 300.00 |            450.00
      5  |           2 | 2021-05-01 | 250.00 |            700.00

可以看出，计算了每个客户的订单金额累积总和，并将结果添加为了新的一列 cumulative_amount。

`PARTITION BY` 子句

在上面的示例中，PARTITION BY 子句被用来将结果集按照 customer_id 进行分组。这意味着 SUM OVER 函数将根据 customer_id 对数据进行分组，并计算每个分组的累积总和。

如果没有使用 PARTITION BY 子句，则 SUM OVER 函数将计算整个结果集的累积总和。

以下是一个示例，演示如何在没有使用 PARTITION BY 子句的情况下使用 SUM OVER 函数：

SELECT order_id, customer_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS cumulative_amount
FROM orders;

运行上述代码，我们将得到以下结果：

order_id | customer_id | order_date | amount | cumulative_amount
---------+-------------+------------+--------+------------------
      1  |           1 | 2021-01-01 | 100.00 |            100.00
      3  |           2 | 2021-03-01 | 150.00 |            250.00
      2  |           1 | 2021-02-01 | 200.00 |            450.00
      5  |           2 | 2021-05-01 | 250.00 |            700.00
      4  |           2 | 2021-04-01 | 300.00 |           1000.00

可以看到，没有使用 PARTITION BY 子句时，SUM OVER 函数计算了整个结果集的累积总和。

总结

本文详细介绍了 SQL 中的 SUM OVER 函数，包括其语法和使用方法。通过示例代码和运行结果的演示，我们了解了如何使用 SUM OVER 函数计算指定列的累积总和，并将结果添加为新的一列。PARTITION BY 子句用于将结果集按照指定的列进行分组，以便对每个分组进行单独的累积总和计算。运用 SUM OVER 函数可以方便地进行累积求和操作，为我们在实际的数据分析和处理中提供了便利。