SQL-过去N周的销售总额，包括销售额为0的周

Question

我想显示每项产品每周4周的销售总额(当前一周加上前3周)。

原始数据

+--------+--------+----------+
| Item   | Week   | sales    |
+--------+--------+----------+
|   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   |
+--------+--------+----------+

预期结果(以a项为例)

+------+------+------------------------------------------------------+
| Item | Week |                        sales                         |
+------+------+------------------------------------------------------+
| a    |    1 | 10                                                   |
| a    |    2 | 20                                                   |
| a    |    3 | 30                                                   |
| a    |    4 | 30                                                   |
| a    |    5 | 20(Note: sales of Week 5+Week 4+Week 3+Week 2)       |
| a    |    6 | 10                                                   |
| a    |    7 | 20[Note: 10(Week 7)+0(Week 6)+0(Week 5)+10 (Week 4)] |
| a    |    8 | 20                                                   |
| a    |    9 | 20                                                   |
| a    |   10 | 30(Note: sales of Week 10+Week 9+Week 8+Week 7)      |
+------+------+------------------------------------------------------+

我试着用

sum (sales) over (partition by item order by week row 3 preceding)

然而，它跳过没有销售记录的一周，并将所有的零零值相加。例如:对于第7周，总和计算结果为40，因为它从第7周，第4周，第2周，1。是否有方法达到预期的结果？

Answer 1

每周，如果你的桌子是桌子

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

Answer 2

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

Answer 3

由于没有提到关系数据库管理系统，所以我在Server中测试了下面的代码。下面的解决方案适用于Server

我正在做以下活动：

1. 生成周、项的所有组合
2. 对于组合，生成前4周的总金额(销售)。
3. 删除原始列表中不存在的周数。

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 |
+------+------+-------------+