sql - 如何获得前几周的总金额

Question

例如，我有这张表：

日期	数量
2021-02-16T21:06:38	10
2021-02-16T21:07:01	5
2021-02-17T01:10:12	-1
2021-02-19T12:00:00	3
2021-02-24T12:00:00	20
2021-02-25T12:00:00	-1

我想要每周所有前几周的总数。所以这种情况下的结果是：

日期	数量
2021-02-15	0
2021-02-22	17
2021-03-01	36

注意：日期现在是每周（星期一）的开始。任何帮助将不胜感激。

标签： sqlpostgresqlwindow-functions

Answer 1

尝试这个：

select week_date, sum(amount) over (order by week_date ) 
from (
SELECT date(date_) + cast(abs(extract(dow FROM date_) -7 ) + 1 as int) "week_date",
sum(amount) "amount"
from example group by 1) t 

演示

上面的查询将只涵盖有交易记录的那一周。如果您想涵盖所有缺失的一周，请尝试以下查询：

with cte as (
SELECT date(date_) + cast(abs(extract(dow FROM date_) -7 ) + 1 as int) "week_date",
sum(amount) "amount"
from example group by 1
)
select 
t1."Date",coalesce(sum(cte.amount) over (order by t1."Date"),0)
from cte right join 
(select generate_series(min(week_date)- interval '1 week', max(week_date),interval '1 week') "Date" from cte) t1 on cte.week_date=t1."Date"

演示