按天计算交易running total的SQL写法是否正确？求优化方案

你的SQL写法正确性验证 #

你写的SQL语句是完全正确的，逻辑上没有问题：

1. 先通过GROUP BY date将交易数据按日期分组，内层SUM(amount)计算出每天的交易总金额
2. 外层的SUM(...) OVER (PARTITION BY TRUE ORDER BY date)对每日总金额做累计求和：
   • PARTITION BY TRUE等价于不指定分区（所有数据在同一个分区），可以简化为仅保留ORDER BY date
   • ORDER BY date确保累计是按日期顺序进行的

对应的简化后SQL代码：

SELECT date,
  SUM(SUM(amount)) OVER (ORDER BY date) AS value
FROM transactions
GROUP BY date
ORDER BY date ASC

更优实现方式 #

虽然你的写法正确，但嵌套SUM的可读性稍弱，更直观的写法是先用子查询/CTE计算每日总金额，再基于这个结果计算累计总和，这种写法在团队协作中更易理解：

方式1：使用CTE（通用表表达式） #

WITH daily_totals AS (
  SELECT date, SUM(amount) AS daily_amount
  FROM transactions
  GROUP BY date
)
SELECT 
  date,
  SUM(daily_amount) OVER (ORDER BY date) AS value
FROM daily_totals
ORDER BY date ASC

方式2：使用子查询 #

SELECT 
  date,
  SUM(daily_amount) OVER (ORDER BY date) AS value
FROM (
  SELECT date, SUM(amount) AS daily_amount
  FROM transactions
  GROUP BY date
) AS daily_totals
ORDER BY date ASC

从性能上看，这几种写法在主流数据库（PostgreSQL、MySQL 8.0+、SQL Server等）中执行计划基本一致，差异主要在可读性。

示例验证 #

输入数据 #

date                      amount
2024-11-01 00:00:00+00    -1.9
2024-11-01 00:00:00+00    -1.9
2024-11-02 00:00:00+00    -1.9
2024-11-02 00:00:00+00     10
2024-11-05 00:00:00+00      3.9
2024-11-08 00:00:00+00    -1.95
2024-11-13 00:00:00+00    -10
2024-11-16 00:00:00+00     80
2024-11-18 00:00:00+00     85
2024-11-23 00:00:00+00    498.25
2024-11-27 00:00:00+00   -498.25
2024-11-30 00:00:00+00   -650

预期输出 #

date                      value
2024-11-01 00:00:00+00    -3.8
2024-11-2 00:00:00+00      4.3
2024-11-05 00:00:00+00     8.2
2024-11-08 00:00:00+00     6.25
2024-11-13 00:00:00+00    -3.75
2024-11-16 00:00:00+00     76.25
2024-11-18 00:00:00+00    161.25
2024-11-23 00:00:00+00    659.5
2024-11-27 00:00:00+00    161.25
2024-11-30 00:00:00+00   -488.75

内容的提问来源于stack exchange，提问作者Arnoud van der Leer