sql - 数据库表上的高效滑动窗口总和

Question

一个数据库有一个 transactions带列的表:account_id , date , transaction_value (有符号整数)。另一个表( account_value )存储了每个账户的当前总值，即所有 transaction_value 的总和。每个帐户。它通过 transactions 上的触发器更新表(即，插入、更新和删除到 transactions 触发触发器以更改 account_value 。)

一项新要求是仅计算过去 365 天内帐户的总交易值(value)。只需要当前的运行总数，而不是以前的总数。这个值会经常被请求，几乎和 account_value 一样频繁。 .

你将如何有效地实现这个“滑动窗口总和”？一张新 table 没问题。有没有办法避免每次都对一年的范围进行求和？

Answer 1

这可以通过标准的窗口函数来完成:

SELECT account_id,
       sum(transaction_value) over (partition by account_id order by date)
FROM transactions

order by内 over() claues 使总和成为“滑动总和”。

对于“仅过去 356 天”，您需要第二个查询来限制 WHERE 子句中的行。

以上适用于 PostgreSQL、Oracle、DB2 和(我认为)Teradata。 SQL Server 不支持窗口定义中的 order by(即将推出的 Denali 版本将 AFAIK)