SQL Sum window function-CodePudding

I have a table where I need one more column with sum values for 4 previous rows.

The table is something like this:

So for example in that new column for row number 5, value should be 29 because 4 rows before that have 6 8 8 7 = 29.

I know I should probably use window function, which I tried but without success to make it sum 4 previous values from order_rate column. (with partition by, with order by, with between, etc..) I'm probably missing something small as usual :'(

Thanks in advance!

CodePudding user response：

You don't say which database are you using but this solution should work on most of them. You can set the window frame to indicate the specific subrange of rows you want to aggregate. For example:

select t.*,
  sum(order_rate) over(
    order by quarter_time
    rows between 4 preceding and 1 preceding -- window frame
  ) as new_sum
from t

CodePudding user response：

I think you can achieve what you want by using something like

SUM(order_rate) OVER (ORDER BY quarter_time ROWS 4 PRECEDING) - order_rate

The window function will sum up the preceding 4 rows order_rates as well as the current row so subtracting the current rows order_rate value at the end gets the sum of the previous 4 values only.