主页 / dba / 问题 / 209574
Accepted
Elaskanator
Asked: 2018-06-14 08:41:59 +0800 CST

SQL Server - 窗口查询中的标准偏差

  • 772

SQL Server 提供了该STDEV函数，但它不能与窗口函数一起使用。

例如

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

如何同时计算百分位数和标准差？

sql-server

1 个回答

  1. Best Answer

    我在这里找到了解决方案。本质上，您必须使用统计信息来计算它：

    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 在性能测试代码时随意使用此语句。这就是我写它的原因（特别是针对这个问题）。

    • 1

相关问题