Calculate Sum From Moving 4 Rows in SQL

问题:

I've have the following data.

   WM_Week  POS_Store_Count POS_Qty POS_Sales   POS_Cost
   ------   --------------- ------  --------    --------
   201541   3965            77722   153904.67   102593.04
   201542   3952            77866   154219.66   102783.12
   201543   3951            70690   139967.06   94724.60
   201544   3958            70773   140131.41   95543.55
   201545   3958            76623   151739.31   103441.05
   201546   3956            73236   145016.54   98868.60
   201547   3939            64317   127368.62   86827.95
   201548   3927            60762   120309.32   82028.70

I need to write a SQL query to get the last four weeks of data, and their last four weeks summed for each of the following columns: POS_Store_Count,POS_Qty,POS_Sales, and POS_Cost.

For example, if I wanted 201548's data it would contain 201548, 201547, 201546, and 201545's.

The sum of 201547 would contain 201547, 201546, 201545, and 201544.

The query should return 4 rows when ran successfully.

How would I formulate a recursive query to do this? Is there something easier than recursive to do this?

Edit: The version is Azure Sql DW with version number 12.0.2000. Edit2: The four rows that should be returned would have the sum of the columns from itself and it's three earlier weeks.

For example, if I wanted the figures for 201548 it would return the following:

WM_Week POS_Store_Count POS_Qty  POS_Sales   POS_Cost
------  --------------- -------  --------    --------
201548  15780           274938   544433.79   371166.3

Which is the sum of the four (non-identity) columns from 201548, 201547, 201546, and 201545.

回答1:

Pretty sure this will get you what you want.. Im using cross apply after ordering the data to apply the SUMS

Create  Table #WeeklyData (WM_Week Int, POS_Store_Count Int, POS_Qty Int, POS_Sales Money, POS_Cost Money)

Insert #WeeklyData Values
(201541,3965,77722,153904.67,102593.04),
(201542,3952,77866,154219.66,102783.12),
(201543,3951,70690,139967.06,94724.6),
(201544,3958,70773,140131.41,95543.55),
(201545,3958,76623,151739.31,103441.05),
(201546,3956,73236,145016.54,98868.6),
(201547,3939,64317,127368.62,86827.95),
(201548,3927,60762,120309.32,82028.7)

DECLARE @StartWeek INT = 201548;
WITH cte AS
(
    SELECT *, 
            ROW_NUMBER() OVER (ORDER BY [WM_Week] DESC) rn
    FROM #WeeklyData 
    WHERE WM_Week BETWEEN @StartWeek - 9 AND @StartWeek  
)
SELECT *
FROM cte c1
CROSS APPLY (SELECT SUM(POS_Store_Count) POS_Store_Count_SUM, 
                    SUM(POS_Qty) POS_Qty_SUM, 
                    SUM(POS_Sales) POS_Sales_SUM,
                    SUM(POS_Cost) POS_Cost_SUM
             FROM   cte c2 
             WHERE  c2.rn BETWEEN c1.rn AND (c1.rn + 3)
) ca     
WHERE c1.rn <= 4 

回答2:

You can use SUM() in combination with the OVER Clause

Something like:

SELECT WM_Week.
, SUM(POS_Store_Count) OVER (ORDER BY WM_Week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table

回答3:

You should be able to use a SQL window function for this.

Add a column to your query like the following:

SUM(POS_Sales) OVER(
   ORDER BY WM_Week 
   ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
) AS POS_Sales_4_Weeks

回答4:

If I understand correctly, you don't want to return 4 rows, but rather 4 summed columns for each group? If so, here's one option:

select max(WM_Week) as WM_Week,
       sum(POS_Store_Count),
       sum(POS_Qty),
       sum(POS_Sales),
       sum(POS_Cost)
from (select top 4 *
      from yourtable
      where wm_week <= 201548
      order by wm_week desc) t

This uses a subquery with top to get the 4 rows you want to aggregate based on the where criteria and order by clause.

---

Here is a condensed fiddle demonstrating the example (sorry fiddle isn't supporting sql server right now, so the syntax is slightly off):