Sum of Current Previous X Nth Rows-CodePudding

I am looking to find the sum of the current the last X Nth Rows. I am able to do this with the following query, however it is not very scalable.

SELECT [id], [amount]   LAG([amount],6) OVER(ORDER BY [id])   LAG([amount],12) OVER(ORDER BY [id])   LAG([amount],18) OVER(ORDER BY [id])

If this example, I'm finding the current value of "amount", plus the last 3 "amounts" split 6 apart:

X = 3
N = 6

I will be using these within dynamic queries and would prefer not to build such a complex query each time. There could be many "lags" in some of the queries. Is there another way to write this query that would be more scalable?

SOURCE DATA

ID	Amount
1	107.35
2	105.41
3	104.63
4	106.7
5	108.7
6	110.21
7	108.8
8	108.91
9	108.5
10	106.66
11	105.2
12	106.5
13	108.27
14	109.72
15	111.53
16	112.8
17	109.03
18	115.31
19	115.56
20	116.85
21	116.08
22	117.61
23	118.31
24	119.25
25	118.45
26	118.43
27	120.16
28	122.5
29	125.57
30	125.65

EXPECTED RESULTS

ID	SUM OF LAST 4
1	NULL
2	NULL
3	NULL
4	NULL
5	NULL
6	NULL
7	NULL
8	NULL
9	NULL
10	NULL
11	NULL
12	NULL
13	NULL
14	NULL
15	NULL
16	NULL
17	NULL
18	NULL
19	439.98
20	440.89
21	440.74
22	443.77
23	441.24
24	451.27
25	451.08
26	453.91
27	456.27
28	459.57
29	458.11
30	466.71

CodePudding user response：

At a best guess, it seems like what you want is something like this:

DECLARE @X int = 3,
        @N int = 6;

SELECT YT.ID,
       YT.Amount,
       CASE WHEN ROW_NUMBER() OVER (PARTITION BY G.Grp ORDER BY ID) < @X 1 THEN NULL
            ELSE SUM(Amount) OVER (PARTITION BY G.Grp ORDER BY ID
                                   ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
       END
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(ID % @N))G(Grp)
ORDER BY YT.ID;

You'll note, however, that the 3 is hardcoded in one place, as you cannot use a variable for the ROWS BETWEEN clause. If you need to parametrise this, you'll need to use dynamic SQL:

DECLARE @X int = 3,
        @N int = 6;

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SET @SQL = CONCAT(N'SELECT YT.ID,', @CRLF,
                  N'       YT.Amount,', @CRLF,
                  N'       CASE WHEN ROW_NUMBER() OVER (PARTITION BY G.Grp ORDER BY ID) < @X 1 THEN NULL', @CRLF,
                  N'            ELSE SUM(Amount) OVER (PARTITION BY G.Grp ORDER BY ID', @CRLF,
                  N'                                   ROWS BETWEEN ',@X,N' PRECEDING AND CURRENT ROW)', @CRLF, --I don't like injecting raw values, but if @X is an int, it is "safe"
                  N'       END', @CRLF,
                  N'FROM dbo.YourTable YT', @CRLF,
                  N'     CROSS APPLY (VALUES(ID % @N))G(Grp)', @CRLF,
                  N'ORDER BY YT.ID;');
PRINT @SQL; --Your best debugging friend

EXEC sys.sp_executesql @SQL, N'@X int, @N int', @X, @N;

db<>fiddle