SQL - how to sum groups of 15 rows and find the max sum

Question

The purpose of this question is to optimize some SQL by using set-based operations vs iterative (looping, like I'm doing below):

Some Explanation -

I have this cte that is inserted to a temp table #dataForPeak. Each row represents a minute, and a respective value retrieved.

For every row, my code uses a while loop to add 15 rows at a time (the current row + the next 14 rows). These sums are inserted into another temp table #PeakDemandIntervals, which is my workaround for then finding the max sum of these groups of 15.

I've bolded my end goal above. My code achieves this but in about 12 seconds for 26k rows. I'll be looking at much more data, so I know this is not enough for my use case.

My question is,

can anyone help me find a fast alternative to this loop?

It can include more tables, CTEs, nested queries, whatever. The while loop might not even be the issue, it's probably the inner code.

insert into #dataForPeak
    select timestamp, value
    from cte
    order by timestamp;

while @@ROWCOUNT<>0
begin
    declare @timestamp datetime = (select top 1 timestamp from #dataForPeak);
    insert into #PeakDemandIntervals
        select @timestamp, sum(interval.value) as peak
        from (select * from #dataForPeak base
              where base.timestamp >= @timestamp
              and base.timestamp < DATEADD(minute,14,@timestamp)
        ) interval;
    delete from #dataForPeak where timestamp = @timestamp;
end

select max(peak)
from #PeakDemandIntervals;

Edit

Here's an example of my goal, using groups of 3min instead of 15min. Given the data:

Time | Value
1:50 | 2
1:51 | 4
1:52 | 6
1:53 | 8
1:54 | 6
1:55 | 4
1:56 | 2

the max sum (peak) I'm looking for is 20, because the group

1:52 | 6
1:53 | 8
1:54 | 6

has the highest sum.

Let me know if I need to clarify more than that.

Looks like you're trying to group your results into 15 minute intervals. You should always use a set based approach when possible, and this looks like one of these times. Look at the answer in the link: https://stackoverflow.com/questions/13648693/group-table-into-15-minute-intervals — NotAnAuthor, Mar 28 '18 at 19:24

Edmond Quinton · Accepted Answer · 2018-03-29T14:13:26.777

4

Based on the example given it seems like you are trying to get the maximum value of a rolling sum. You can calculate the 15-minute rolling sum very easily as follow:

SELECT   [Time]
        ,[Value] 
        ,SUM([Value]) OVER (ORDER BY [Time] ASC ROWS 14 PRECEDING) [RollingSum]
FROM    #dataForPeak

Note the key here is the ROWS 14 PRECEDING statement. It effectively state that SQL Server should sum the preceding 14 records with the current record which will give you your 15 minute interval. Now you can simply max the result of the rolling sum. The full query will look as follow:

;WITH CTE_RollingSum
AS
(
    SELECT   [Time]
            ,[Value] 
            ,SUM([Value]) OVER (ORDER BY [Time] ASC ROWS 14 PRECEDING) [RollingSum]
    FROM    #dataForPeak
)
SELECT  MAX([RollingSum]) AS Peak
FROM    CTE_RollingSum

edited Mar 29 '18 at 14:13

answered Mar 29 '18 at 02:47

Edmond Quinton

1,709
9
10

Thanks! I had heard of rolling sums, but was unaware of it's implementation via `ROWS`. What I needed was the `CURRENT ROW` / `FOLLOWING` keywords, but your answer definitely led me there quickly. For anyone looking at this later, here's a guide I found that explains this method: [Sliding aggregation](http://stevestedman.com/2012/03/rows-preceding-and-following-in-tsql-2012/) ...And in case that link breaks... [official docs](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql#d-specifying-the-rows-clause) – Camilo Mar 29 '18 at 14:15
@Camilo glad to help – Edmond Quinton Mar 29 '18 at 14:16

SQL - how to sum groups of 15 rows and find the max sum

Edit

1 Answers1