每年分组,年+9,得到总数

您可以通过自联接和聚合来完成:

select sum(m2.count) total_count,  
       m1.year || '-' || (m1.year + 9) year_range
from my_table m1 left join my_table m2
on m2.year between m1.year and m1.year + 9
group by m1.year

demo .
结果:

> total_count | year_range
> ----------: | :---------
>          11 | 1961-1970 
>           9 | 1963-1972 
>          22 | 1969-1978 
>          23 | 1975-1984 
>          21 | 1976-1985 
>          23 | 1978-1987 
>          31 | 1983-1992 
>          28 | 1984-1993 
>          27 | 1985-1994 
>          24 | 1986-1995 
>          19 | 1987-1996 
>          13 | 1989-1998 

可以使用窗口函数;

select t.*,
       sum(count) over (order by year range between current row and 9 following) as total_count
from t;

year_range ,但这很简单: year || '-' || year + 9

Here 是一把小提琴。