I have some performance test results on the database, and what I want to do is to group every 1000 records (previously sorted in ascending order by date) and then aggregate results with AVG.
I’m actually looking for a standard SQL solution, however any T-SQL specific results are also appreciated.
The query looks like this:
x
SELECT TestId,Throughput FROM dbo.Results ORDER BY id
Advertisement
Answer
WITH T AS (
SELECT RANK() OVER (ORDER BY ID) Rank,
P.Field1, P.Field2, P.Value1,
FROM P
)
SELECT (Rank - 1) / 1000 GroupID, AVG( )
FROM T
GROUP BY ((Rank - 1) / 1000)
;
Something like that should get you started. If you can provide your actual schema I can update as appropriate.