如果所有日期都有值，且没有间隔，则可以将窗口函数与 rows 框架：

select
    date,
    count(*) cnt
    sum(count(*)) over(order by date rows between 7 preceding and current row) cnt_d7
from mytable
group by date
order by date

您可以尝试以下方法：

select
    date_,
    count(*) as count_,
    (select count(*) 
        from table as b
        where b.date_ <= a.date_ and b.date_ > a.date - interval '7 days'
    ) as count7days_
from table as a
group by date_

如果存在间隙，则可以执行更复杂的解决方案，在其中添加和减去值：

with t as (
      select date_, count(*) as count_
      from table 
      group by date_
      union all
      select date_ + interval '8 day', -count(*) as count_
      from table 
      group by date_
     )
select date_, 
       sum(sum(count_)) over (order by date_ rows between unbounded preceding and current row) - sum(count_)
from t;

这个 - sum(count_) 是因为你似乎不想在当天的累计金额。
你也可以使用讨厌的自连接方法。7天内都可以：

with t as (
      select date_, count(*) as count_
      from table 
      group by date_
     )
select t.date_, t.count_, sum(tprev.count_)
from t left join
     t tprev
     on tprev.date_ >= t.date_ - interval '7 day' and
        tprev.date_ < t.date_
group by t.date_, t.count_;

随着“7”越来越大，性能会越来越差。

尝试使用新列的子查询：

select

table.date_ as groupdate,
count(table.date_) as date_count,
(select count(table.date_)
   from table
   where table.date_ <= groupdate and table.date_ >= groupdate - interval '7 day'
) as total7

from table

group by groupdate
order by groupdate