SELECT week,Item ,sum(sales) WHERE (curweek - week < 4) group by week,Item

每周，如果你的table是table

SELECT sum(B.sales), A.week as curweek As totalsales
FROM table A, table B
WHERE curweek - B.week <4 
AND curweek - B.week >=0
GROUP BY curweek

由于问题没有提到rdbms，我在sql server中测试了下面的代码。下面的解决方案是针对sql server的
我正在做以下活动：
生成周、项的所有组合
对于组合，生成前4周的总和（销售额）
删除原始列表中不存在的周

DECLARE @table TABLE (Item CHAR(1), Week TINYINT, sales INT)

INSERT INTO @table
VALUES ('a', 1, 10), ('a', 2, 10), ('a', 4, 10), ('a', 7, 10), ('a', 8, 10), ('a', 10, 10), ('b', 1, 10), ('b', 2, 10), ('b', 4, 10), ('b', 7, 10), ('b', 8, 10), ('b', 10, 10);;

WITH CTE_ItemWeek
AS (
    SELECT Item, Week
    FROM (
        VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
        ) AS t(week)
    CROSS JOIN (
        SELECT DISTINCT Item
        FROM @table
        ) A
    )
SELECT *
FROM (
    SELECT t1.Item, CASE 
            WHEN t2.Week IS NULL
                THEN LAG(t2.week) OVER (
                        PARTITION BY t1.Item ORDER BY t2.week
                        )
            ELSE t2.week
            END AS week, SUM(t2.sales) OVER (
            PARTITION BY t1.Item ORDER BY t1.week ROWS BETWEEN 3 preceding
                    AND CURRENT ROW
            ) AS total_sales
    FROM CTE_ItemWeek AS t1
    LEFT OUTER JOIN @table AS t2 ON t1.Item = t2.Item
        AND t1.week = t2.week
    ) AS t
WHERE week IS NOT NULL

结果集

+------+------+-------------+
| Item | week | total_sales |
+------+------+-------------+
| a    |    1 |          10 |
| a    |    2 |          20 |
| a    |    4 |          30 |
| a    |    7 |          20 |
| a    |    8 |          20 |
| a    |   10 |          30 |
| b    |    1 |          10 |
| b    |    2 |          20 |
| b    |    4 |          30 |
| b    |    7 |          20 |
| b    |    8 |          20 |
| b    |   10 |          30 |
+------+------+-------------+

如果您只需要查看现有周数的总和（因此不必填充缺少的周数），可以在标准sql中使用范围而不是行来实现这一点：

select item, 
       week,
       sum (sales) over (partition by item order by week range between 3 preceding and current row)
from the_table
order by item, week;

如果您需要以行的形式填写缺少的周，那么答案在很大程度上取决于所使用的dbms。在postgres中，您可以执行以下操作：

with items as (
  select distinct item
  from data
), all_weeks as (
  select i.item, g.week
  from items i
    cross join generate_series(1,10) as g(week)
)    
select t.item,
       t.week,
       sum (d.sales) over (partition by t.item order by t.week range between 3 preceding and current row)
from all_weeks t
  left join data d on d.item = t.item and d.week = t.week

对于其他dbms系统 generate_series() 呼叫可以替换为固定的周数：

select i.item, g.week
from items i
  cross join ( values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) as g(week)

在线示例