September 3, 2014 at 5:48 pm
Code is below, and DDL is in the text file (sorry, it's long).
I was sort of expecting the 10 minute and hourly averages to all be the same number for a given time period. For instance, the first 10 minutes would all have the same average, and then the second 10 would all be the same, and the hour window would follow suit. I probably accidentally did what most people set out trying to do selecting a rolling average. Heh.
Thanks
WITH Total
AS ( SELECT Completed ,
InProgress ,
Total ,
CollectionTime ,
ROW_NUMBER() OVER ( ORDER BY CollectionTime ) AS Rn
FROM master.dbo.IPRO_TIMING WITH ( NOLOCK )
)
SELECT t.Completed ,
t.InProgress ,
t.Total ,
t.CollectionTime ,
ca.CompletedLastMinute ,
AVG(ca.CompletedLastMinute) OVER ( ORDER BY CollectionTime ROWS 9 PRECEDING ) AS TenMinuteAverage ,
AVG(ca.CompletedLastMinute) OVER ( ORDER BY CollectionTime ROWS 59 PRECEDING ) AS HourAverage ,
SUM(ca.CompletedLastMinute) OVER ( ORDER BY CollectionTime ROWS UNBOUNDED PRECEDING ) AS [TotalCompleted],
(t.Total - t.Completed) AS [Remaining]
FROM Total t
CROSS APPLY ( SELECT ( t.InProgress - ta.InProgress )
FROM Total ta
WHERE t.Rn = ta.Rn - 1
) ca ( CompletedLastMinute )
--WHERE t.CollectionTime >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) - 30, 0)
GROUP BY t.Completed ,
t.InProgress ,
t.Total ,
t.CollectionTime ,
ca.CompletedLastMinute
September 3, 2014 at 7:07 pm
You were doing a rolling average on 10 minutes and an hour.
Not sure what you want as a result though. Are you able to expand on your requirement?
If you want averages for ten minute and hour blocks from the beginning of the data this may do it.
WITH Total AS (
SELECT Completed ,
InProgress ,
Total ,
CollectionTime ,
LAG(InProgress,1) OVER ( ORDER BY CollectionTime ) - InProgress CompletedLastMinute,
(ROW_NUMBER() OVER ( ORDER BY CollectionTime ) - 2) / 10 AS _10minuteBlock,
(ROW_NUMBER() OVER ( ORDER BY CollectionTime ) - 2) / 60 AS _1hourBlock
FROM dbo.IPRO_TIMING
)
SELECT t.Completed ,
t.InProgress ,
t.Total ,
t.CollectionTime ,
t.CompletedLastMinute ,
AVG(t.CompletedLastMinute) OVER ( PARTITION BY _10minuteBlock ) AS TenMinuteBlockAverage ,
AVG(t.CompletedLastMinute) OVER ( PARTITION BY _1hourBlock ) AS HourBlockAverage ,
SUM(t.CompletedLastMinute) OVER ( ORDER BY CollectionTime ROWS UNBOUNDED PRECEDING ) AS [TotalCompleted],
MIN(CollectionTime) OVER ( PARTITION BY _10minuteBlock ) TenMinuteBlockStartTime,
MIN(CollectionTime) OVER ( PARTITION BY _1hourBlock ) HourBlockStartTime,
(t.Total - t.Completed) AS [Remaining]
FROM Total t
WHERE CompletedLastMinute is not null;
September 3, 2014 at 8:27 pm
That actually looks how I expected it to look. I can't quite figure this out though. Why -2? Using LAG was cool - I always rely pretty heavily on CROSS APPLY - guess it's habit from seeing it on here so much.
LAG(InProgress,1) OVER ( ORDER BY CollectionTime ) - InProgress CompletedLastMinute,
(ROW_NUMBER() OVER ( ORDER BY CollectionTime ) - 2) / 10 AS _10minuteBlock,
(ROW_NUMBER() OVER ( ORDER BY CollectionTime ) - 2) / 60 AS _1hourBlock
September 3, 2014 at 8:38 pm
The -2 on the row number was to get it to ignore the first row of data in the grouping. So the first group of 10 would be rows 2 - 11, the second group 12 - 21 and so on.
Run the following by itself to show the grouping
SELECT Completed ,
InProgress ,
Total ,
CollectionTime ,
LAG(InProgress,1) OVER ( ORDER BY CollectionTime ) - InProgress CompletedLastMinute,
(ROW_NUMBER() OVER ( ORDER BY CollectionTime ) - 2) / 10 AS _10minuteBlock,
(ROW_NUMBER() OVER ( ORDER BY CollectionTime ) - 2) / 60 AS _1hourBlock
FROM dbo.IPRO_TIMING
September 3, 2014 at 8:52 pm
Ah. Because this being NULL in the first row would give you a nine row chunk.
WHERE CompletedLastMinute is not null;
Thanks, makes sense
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply