SQL return past x weeks of data for each week-CodePudding

I have a weekly revenue data for each product; for every week I need a sum of the past 4 weeks of revenue exclusive of that week in an extra column. Please see the example of data in the table below.

Struggling with coming up with self join that works for more than 1 week of data.

I would appreciate your help!

CodePudding user response：

You can have a subquery as a column expression; this should do it.

select Week, Product_id, Revenue,
   (select sum(Revenue) 
    from MyTbl tblB
    where tblB.product_id=tblA.product_id
       and tblB.Week between tblA.Week-35 and tblA.Week-7
    ) as RevenuePrev4Weeks
from MyTbl tblA

The constant 35 is to find the week 5 weeks prior to current row's week.

CodePudding user response：

You might want to use an analytic function instead of self join. Consider below query.

SELECT *, IFNULL(SUM(revenue) OVER w, 0) AS last_4_weeks_revenue
  FROM sample_table
WINDOW w AS (PARTITION BY product_id ORDER BY EXTRACT(WEEK from Week) RANGE BETWEEN 4 PRECEDING AND 1 PRECEDING)

Note: because above query sorts a partition using a week number (EXTRACT(WEEK from Week)), it's assuming that Week date is in same year.