SQL-sales sum over past N week including week with 0 sales

user_13579129 0
Viewed 976

I want to display sum of 4 weeks sales every week(current week plus the previous 3 week) per item.

Raw data 

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

Expected Result(take item a as example) 

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

I tried to use

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

However, it skips the Week with no sale record and add up all the none-zero values. For example: For Week 7, sum over calculates result as 40 as it adds the sales from Week 7,4,2,1. Is there a way to achieve the expected results?

amazon-redshift window-functions sql
4 Answers

For every week, if your table is 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
user_29772560 
SELECT week,Item ,sum(sales) WHERE (curweek - week < 4) group by week,Item
user_135791300

As the question did not mention RDBMS, I tested below code in SQL Server. The below solution is for SQL Server

I am doing below activities:

  1. Generate all combination of week, Item
  2. For the combination, generate sum(sales) for preceding 4 weeks
  3. Remove the weeks, which are not present in the original list
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

Result Set

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

If you only need to see the sum for the existing weeks (so not filling the missing ones), this can be achieved in standard SQL by using a range, rather than rows:

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;

If you need to fill in the missing weeks as rows, the answer depends heavily on the DBMS being used. In Postgres you can do the following:

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

For other DBMS systems, the generate_series() call can be replaced with a fixed number of weeks:

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

Online example

user_3303150
Related