Início / dba / Perguntas / 209574
Accepted
Elaskanator
Asked: 2018-06-14 08:41:59 +0800 CST

SQL Server - Desvio padrão em uma consulta em janela

  • 772

O SQL Server fornece a STDEVfunção , mas não pode ser usada em conjunto com funções de janela .

Por exemplo

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

Como posso calcular percentis e desvio padrão ao mesmo tempo?

sql-server

1 respostas

  1. Best Answer

    Encontrei uma solução aqui . Essencialmente, você deve calculá-lo usando estatísticas :

    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

    PS Sinta-se à vontade para usar esta declaração ao testar o código de desempenho. É por isso que escrevi (em particular para esta pergunta ) .

    • 1

relate perguntas