sql - 操作数数据类型 varchar 对 avg 运算符无效

Question

我不断收到上述错误消息

“操作数数据类型 varchar 对 avg 运算符无效”

任何人都可以为我修好它吗？请

WITH Average -- Calculating Mean
AS (
    SELECT avg(convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) / 3600) + ':' + convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 3600 / 60) + ':' + convert(VARCHAR(5), (DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 60))) AS Average
    FROM [CLERKS]
    WHERE [ARRIVAL_DATE_TIME] >= DATEADD(dd, - 30, getdate() - 1)
    )
    ,data
AS (
    SELECT cast(ARRIVAL_DATE_TIME AS DATE) AS Attendance_Date
        ,avg(convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) / 3600) + ':' + convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 3600 / 60) + ':' + convert(VARCHAR(5), (DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 60))) AS Arr_Com
    FROM [Clerks]
    WHERE [ARRIVAL_DATE_TIME] >= DATEADD(dd, - 30, getdate() - 1)
    GROUP BY cast(ARRIVAL_DATE_TIME AS DATE)
    )
SELECT a.Attendance_Date
    ,a.Arr_Com
    ,c.Average
    ,abs(a.Arr_Com - b.Arr_Com) AS MR
FROM data a
LEFT JOIN data b ON cast(a.Attendance_Date AS DATETIME) = cast(b.Attendance_Date AS DATETIME) + 1
CROSS JOIN Average c
ORDER BY a.Attendance_Date
GO

提前致谢

标签： sqlsql-server

Answer 1

实际上，问题在于实现不正确。您不想平均时间戳（5h:3m:20s），而是平均持续时间。

因此，您需要以最小分母计算持续时间，在您的情况下为秒，通过使用 AVG() 函数计算以秒为单位的平均值，然后将该结果格式化为看起来像 hh:mm:ss。

您的代码应如下所示：

;WITH Average -- Calculating Mean
AS (
    SELECT AVG(DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME])) as Average
    FROM [CLERKS]
    WHERE [ARRIVAL_DATE_TIME] >= DATEADD(dd, - 30, getdate() - 1)
    )
    ,data
AS (
    SELECT cast(ARRIVAL_DATE_TIME AS DATE) AS Attendance_Date
        , AVG(DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME])) as Arr_Com
    FROM [Clerks]
    WHERE [ARRIVAL_DATE_TIME] >= DATEADD(dd, - 30, getdate() - 1)
    GROUP BY cast(ARRIVAL_DATE_TIME AS DATE)
    )
SELECT
    Attendance_Date
    , Arr_Com
    , Average
    , avg(convert(VARCHAR(5), MR / 3600) + ':' + convert(VARCHAR(5), MR % 3600 / 60) + ':' + convert(VARCHAR(5), MR % 60))) AS MR
FROM (
    SELECT a.Attendance_Date
        ,a.Arr_Com
        ,c.Average
        , abs(a.Arr_Com - b.Arr_Com) AS MR
    FROM data a
    LEFT JOIN data b ON cast(a.Attendance_Date AS DATETIME) = cast(b.Attendance_Date AS DATETIME) + 1
    CROSS JOIN Average c
    ) tmp
ORDER BY Attendance_Date