我正在尝试按时间跨账户计算数量的滚动求和。理想情况下，我希望把当前行的数量与每个账户最近的一行数量相加。

给出以下示例输入数据

我期望得到的输出将是：

我尝试使用类似如下的窗口函数：

select ... sum(quantity) over (partition by account_id order ... rows between unbounded preceding and current row) ...

然而这只是按账户ID对每一行的数量进行滚动求和。

我也尝试使用 first_value/lag，但它们再次只与当前行的账户ID相关联。我猜这不容易被理解为窗口函数，但我还在寻找任何可用的方法。

如果我对基于row_number() 的窗口使用sum，会得到 row_number cannot be used here

解决方案

其实你并不需要滚动求和，而是截至到当前日期或当前交易（id）的所有账户余额之和。或者说“在途周转和（running turnover sum）”。

逐步说明。

1. “quantity的滚动总和”假设存在某种行顺序。
   根据你的数据示例，我们按 (id,date,account_id) 来排序。
2. 行 (id,date,account_id,quantity) 如
   (1,'2026-01-01',2,20) 和 (4, '2026-01-03',1, 5)
   这意味着从2026-01-01到 2026-02-01，账户_id=1的值为20，自 '2026-03-01' 起直到9999-12-31值为5。并且在 '2026-01-01' 之前账户的值为null（=0）。
3. 在每个时点对账户的值进行求和。
   对于日期 '2026-01-04'（id=5），账户的值为
   acc.1= 5
   acc.2=10
   acc.3=20
   总和=35

参见示例

with ranges as(
  SELECT * 
    ,row_number()over(order by id,date)rn
    ,date_add( lead(date,1,cast('9999-12-31' as date))over(partition by account_id order by date) 
    , interval -1 day) next_date
 FROM test
)
,dayRests as(
  select t1.id,t1.date,t1.account_id,t1.quantity,t1.next_date
    ,t2.id id2,t2.date date2,t2.account_id account_id2,t2.quantity quantity2,t2.next_date next_date2
  from ranges t1
  left join ranges t2 on  t1.date between t2.date and t2.next_date  and t2.rn<t1.rn 
)
select id,min(date) date,min(account_id) account_id
  ,min(quantity)+coalesce(sum(coalesce(quantity2,0)),0) tv
from dayRests
group by id
order by id;

输出为

带区间的账户值：

| id   | date       | account_id | quantity | rn | next_date  |
+------+------------+------------+----------+----+------------+
|    1 | 2026-01-01 |          2 |       20 |  1 | 2026-01-02 |
|    2 | 2026-01-01 |          3 |       30 |  2 | 2026-01-04 |
|    3 | 2026-01-02 |          2 |       10 |  3 | 9999-12-31 |
|    4 | 2026-01-03 |          1 |        5 |  4 | 9999-12-31 |
|    5 | 2026-01-04 |          3 |       20 |  5 | 9999-12-31 |

自连接

+------+------------+------------+----------+------------+------+------------+-----------+
|id| date      |account_id|quantity| next_date  |id2 | date2      |account_id2|quantity2|
+------+20------------+------------+----------+------------+------+------------+---------+
| 1| 2026-01-01|        2 |     20 | 2026-01-01 |NULL| NULL       |       NULL|   NULL|
+------+30+20------------+------------+----------+------------+------+------------+------+
| 2| 2026-01-01|        3 |     30 | 2026-01-03 |  1 | 2026-01-01 |          2|       20|
+------+10+30------------+------------+----------+------------+------+------------+------+
| 3| 2026-01-02|        2 |     10 | 9999-12-30 |  2 | 2026-01-01 |          3|       30 |
+------+5+30+10------------+------------+----------+------------+------+------------+----+
| 4| 2026-01-03|        1 |      5 | 9999-12-30 |  2 | 2026-01-01 |          3|       30 |
| 4| 2026-01-03|        1 |      5 | 9999-12-30 |  3 | 2026-01-02 |          2|       10 |
+------+20+10+5------------+------------+----------+------------+------+------------+----+
| 5| 2026-01-04|        3 |     20 | 9999-12-30 |  3 | 2026-01-02 |          2|       10 |
| 5| 2026-01-04|        3 |     20 | 9999-12-30 |  4 | 2026-01-03 |          1|        5 |

另一个示例，便于完整性说明。目标行的余额。

set @id =5;

 select @id id,min(t.date) date
   ,min(case when c.id=@id then t.account_id end)  account_id
   ,sum(t.quantity) current_balance
 from (
   select max(t2.id) id
   from test t2
   where t2.id<=@id
   group by account_id
  ) c 
 inner join test t on c.id=t.id;

输出

+------+------------+------------+-----------------+
| id   | date       | account_id | current_balance |
+------+------------+------------+-----------------+
|    5 | 2026-01-02 |          3 |              35 |
+------+------------+------------+-----------------+

Fiddle