SQL Server - Standard Deviation in a Windowed Query

Question

SQL Server provides the STDEV function, but it cannot be used in conjunction with window functions.

For example

SELECT
     TestName
    ,[BatchSize]
    ,[NumIterations] = COUNT(*)
    ,[Min] = MIN(RuntimeMs)
    ,[Average] = AVG(RuntimeMs)
    ,[Max] = MAX(RuntimeMs)
    ,[StdDev] = STDEV(RuntimeMs)
FROM #TimingResult
GROUP BY TestName, [BatchSize]

How can I compute percentiles and standard deviation at the same time?

Answer 1

I found a solution here. Essentially, you have to compute it using statistics:

SELECT DISTINCT
    TestName
    ,[BatchSize]
    ,[Iterations] = COUNT(*) OVER(PARTITION BY TestName, [BatchSize])
    ,[Min] = PERCENTILE_DISC(0) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[5th] = PERCENTILE_DISC(0.05) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[10th] = PERCENTILE_DISC(0.1) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[25th] = PERCENTILE_DISC(0.25) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[Avg] = PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[75th] = PERCENTILE_DISC(0.75) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[90th] = PERCENTILE_DISC(0.9) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[95th] = PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[Max] = PERCENTILE_DISC(1) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[StdDev] = SQRT((
        (COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize])
        * SUM(RuntimeMs*RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]))
        - POWER(SUM(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]), 2))
        / (COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize])
        * COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]) - 1))
FROM #TimingResult

P.S. Feel free to use this statement when performance testing code. That's why I wrote it _{(in particular for this question)}.