如何在SQL Server透视表中计算每小时平均失败交易数？

我看你已经走对了方向，用PIVOT来把小时数据转成列格式，主要需要修正两个点：一是PIVOT里的列名错误，二是把小时平均值整合到结果里。这里给你两种方案，看哪种更符合你的需求：

方案1：在结果末尾添加平均行（推荐） #

这个方案会在每日数据的最后，加上一行所有日期对应小时的平均失败数，更直观展示整体趋势：

WITH HourlyFailures AS (
    -- 第一步：统计每个日期每个小时的失败交易数
    SELECT 
        CONVERT(DATE, TimeStamp) AS [Date],
        DATEPART(hour, TimeStamp) AS [Hour],
        SUM(CASE WHEN Result = 'F' THEN 1 ELSE 0 END) AS FAIL
    FROM TableName
    WHERE TimeStamp BETWEEN '2018-05-12 00:00:00' AND '2018-05-24 23:00:00'
    GROUP BY CONVERT(DATE, TimeStamp), DATEPART(hour, TimeStamp)
),
HourlyAverages AS (
    -- 第二步：计算每个小时的平均失败数（跨所有日期）
    SELECT 
        [Hour],
        AVG(CAST(FAIL AS FLOAT)) AS AvgFail  -- 转成FLOAT避免整数除法截断结果
    FROM HourlyFailures
    GROUP BY [Hour]
)
-- 第三步：合并数据并PIVOT，同时处理空值为0
SELECT 
    ISNULL(CAST([Date] AS VARCHAR(10)), 'Average') AS [Date],
    COALESCE([8], 0) AS [8],
    COALESCE([9], 0) AS [9],
    COALESCE([10], 0) AS [10],
    COALESCE([11], 0) AS [11],
    COALESCE([12], 0) AS [12],
    COALESCE([13], 0) AS [13],
    COALESCE([14], 0) AS [14],
    COALESCE([15], 0) AS [15],
    COALESCE([16], 0) AS [16],
    COALESCE([17], 0) AS [17],
    COALESCE([18], 0) AS [18],
    COALESCE([19], 0) AS [19],
    COALESCE([20], 0) AS [20],
    COALESCE([21], 0) AS [21],
    COALESCE([22], 0) AS [22],
    COALESCE([23], 0) AS [23]
FROM (
    -- 把每日数据和平均数据合并
    SELECT [Date], [Hour], FAIL FROM HourlyFailures
    UNION ALL
    SELECT NULL AS [Date], [Hour], AvgFail FROM HourlyAverages
) AS CombinedData
PIVOT(
    SUM(FAIL)
    FOR [Hour] IN ([8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
) AS DatePivot
-- 排序让平均行在最后
ORDER BY 
    CASE WHEN [Date] = 'Average' THEN 1 ELSE 0 END,
    [Date];

方案2：每行显示对应小时的平均值 #

如果你想在每一行（每个日期的每个小时）旁边显示该小时的整体平均值，可以用窗口函数直接在统计阶段计算：

WITH HourlyFailures AS (
    SELECT 
        CONVERT(DATE, TimeStamp) AS [Date],
        DATEPART(hour, TimeStamp) AS [Hour],
        SUM(CASE WHEN Result = 'F' THEN 1 ELSE 0 END) AS FAIL,
        -- 用窗口函数计算当前小时的跨日期平均值
        AVG(SUM(CASE WHEN Result = 'F' THEN 1 ELSE 0 END)) OVER (PARTITION BY DATEPART(hour, TimeStamp)) AS AvgHourlyFail
    FROM TableName
    WHERE TimeStamp BETWEEN '2018-05-12 00:00:00' AND '2018-05-24 23:00:00'
    GROUP BY CONVERT(DATE, TimeStamp), DATEPART(hour, TimeStamp)
)
-- 这里PIVOT只处理失败数，平均值可以单独作为列展示
SELECT 
    [Date],
    COALESCE([8], 0) AS [8],
    COALESCE([9], 0) AS [9],
    COALESCE([10], 0) AS [10],
    COALESCE([11], 0) AS [11],
    COALESCE([12], 0) AS [12],
    COALESCE([13], 0) AS [13],
    COALESCE([14], 0) AS [14],
    COALESCE([15], 0) AS [15],
    COALESCE([16], 0) AS [16],
    COALESCE([17], 0) AS [17],
    COALESCE([18], 0) AS [18],
    COALESCE([19], 0) AS [19],
    COALESCE([20], 0) AS [20],
    COALESCE([21], 0) AS [21],
    COALESCE([22], 0) AS [22],
    COALESCE([23], 0) AS [23]
FROM HourlyFailures
PIVOT(
    SUM(FAIL)
    FOR [Hour] IN ([8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
) AS DatePivot;

关键修正点说明： #

1. 原查询的小错误：你写的SUM(FAILS)应该是SUM(FAIL)，因为子查询里的列名是FAIL，不是FAILS，这会导致PIVOT报错。
2. 空值处理：用COALESCE把没有交易的小时显示为0，避免结果里出现NULL。
3. 平均值计算：用AVG(CAST(FAIL AS FLOAT))确保平均值是小数，不会因为整数除法被截断（比如3天的失败数是2、3、4，平均是3；如果是2、3、3，平均是8/3≈2.67，转成FLOAT才能保留小数精度）。

内容的提问来源于stack exchange，提问作者Nalo Nenu