SQL每月滚动总和

Question

我正在尝试从以下包含交易的 postgresql 表计算银行账户的每月余额:

# \d transactions
                 View "public.transactions"
 Column |       Type       | Collation | Nullable | Default 
--------+------------------+-----------+----------+---------
 year   | double precision |           |          | 
 month  | double precision |           |          | 
 bank   | text             |           |          | 
 amount | numeric          |           |          | 

在“滚动总和”中，我的意思是总和应该包含从开始时间到给定月末的所有交易的总和，而不仅仅是给定月份的所有交易。

我提出了以下查询:

select 
    a.year, a.month, a.bank, 
    (select sum(b.amount) from transactions b 
     where b.year < a.year 
        or (b.year = a.year and b.month <= a.month)) 
from 
    transactions a 
order by 
    bank, year, month;

问题在于，每个银行每个月的行数与那里的交易数一样多。多了就多了，没有了就没有了。我想要一个查询，其中包含每家银行的一行和整个时间间隔的月份，包括第一笔和最后一笔交易。

怎么做？

可以在 https://rextester.com/WJP53830 找到示例数据集和查询。 ，由@a_horse_with_no_name 提供

Answer 1

您需要先生成一个月份列表，然后您可以将您的交易表外连接到该列表。

with all_years as (
  select y.year, m.month, b.bank
  from generate_series(2010, 2019) as y(year) --<< adjust here for your desired range of years
    cross join generate_series(1,12) as m(month)
    cross join (select distinct bank from transactions) as b(bank)
)
select ay.*, sum(amount) over (partition by ay.bank order by ay.year, ay.month)
from all_years ay
  left join transactions t on (ay.year, ay.month, ay.bank) = (t.year::int, t.month::int, t.bank)
order by bank, year, month;  

与所有银行的交叉联接是必要的，这样 all_years CTE 也将包含每个月行的银行。

在线示例:https://rextester.com/ZZBVM16426