SQL Server 2008 - 平均时序计算

Question

我有一个查询

select
  *,
  right(
    convert(varchar, cast(JOB_DONE_time-JOB_send_time as datetime), 121),
    12
  ) [TimeTaken]
from dbo.insert_data
order by job_id desc  

这个查询的结果如下：

JOB_ID     JOB_DONE_TIME            JOB_SEND_TIME            TimeTaken
---------  -----------------------  -----------------------  ------------
001-99900  2011-12-28 13:36:57.860  2011-12-28 13:36:57.593  00:00:00.267
001-99800  2011-12-28 13:36:57.843  2011-12-28 13:36:57.593  00:00:00.250
001-99700  2011-12-28 13:36:57.687  2011-12-28 13:36:57.547  00:00:00.140
001-99600  2011-12-28 13:36:57.593  2011-12-28 13:36:57.547  00:00:00.047
001-99500  2011-12-28 13:36:57.563  2011-12-28 13:36:57.437  00:00:00.127
001-99400  2011-12-28 13:36:57.547  2011-12-28 13:36:57.437  00:00:00.110
001-99300  2011-12-28 13:36:57.483  2011-12-28 13:36:57.377  00:00:00.107
001-99200  2011-12-28 13:36:57.437  2011-12-28 13:36:57.377  00:00:00.060
001-99100  2011-12-28 13:36:57.407  2011-12-28 13:36:57.187  00:00:00.220
001-99000  2011-12-28 13:36:57.360  2011-12-28 13:36:57.187  00:00:00.173
001-9900   2011-12-28 13:32:22.657  2011-12-28 13:32:22.500  00:00:00.127

通过这个查询，我已经得到了TimeTaken列。现在我想计算timetaken列的加和和平均值。我尝试使用SUM()函数，但是它给我返回了一个错误：

Msg 8117, Level 16, State 1, Line 1 Operand data type varchar is invalid for sum operator.

我该如何计算？？？

- Rikky

---

你遇到了哪个错误？（请提供完整和准确的错误信息！） - marc_s

消息 8117，级别 16，状态 1，行 1 varchar 操作数类型对于 sum 运算符无效。消息 8117，级别 16，状态 1，行 1 varchar 操作数类型对于 avg 运算符无效。 - Rikky

尝试在http://sqlfiddle.com上发布您的查询和一些示例数据。AVG仅接受数字，您的AVG参数是varchar类型吗？“Msg 8117，Level 16，State 1，Line 1操作数数据类型varchar对于sum运算符无效。Msg 8117，Level 16，State 1，Line 1操作数数据类型** varchar **对于avg运算符无效。- Rikky” - Michael Buen

嗯，信息非常明确：你不能对varchar列求和！如果你想要求和，这个列的数据类型必须是数字类型，像是INT或其他数字类型。 - marc_s

@Marc_s-除了JOB_ID列以外，其它列都需要采用日期时间格式。 - Rikky

Answer 1

正如之前所说，问题在于您试图将AVG()或SUM()应用于字符串列，而实际上您只能在数字列上使用这些函数。

作为一个非常SQL Server特定的解决方案，您可以暂时将JOB_DONE_time - JOB_send_time的结果转换为float，计算AVG()和/或SUM()，然后将结果转换回datetime，再转换为varchar，最后提取最后12个字符，就像您现在在减法的非聚合结果上所做的那样。

以下是我所说的示例：

SELECT
  JOB_ID,
  JOB_DONE_TIME,
  JOB_SEND_TIME,
  TimeTaken      = RIGHT(CONVERT(varchar(30), CAST(    TimeTakenFP          AS datetime), 121), 12),
  AvgTimeTaken   = RIGHT(CONVERT(varchar(30), CAST(AVG(TimeTakenFP) OVER () AS datetime), 121), 12),
  TotalTimeTaken = RIGHT(CONVERT(varchar(30), CAST(SUM(TimeTakenFP) OVER () AS datetime), 121), 12)
FROM (
  SELECT
    *,
    TimeTakenFP = CAST(JOB_DONE_TIME - JOB_SEND_TIME AS float)
  FROM insert_data
) s
;

对于您的样本数据，上述查询返回以下结果集：

工作编号 完成时间 发送时间 耗时 平均耗时 总耗时 --------- ----------------------- ----------------------- ------------ ------------ -------------- 001-99900 2011-12-28 13:36:57.860 2011-12-28 13:36:57.593 00:00:00.267 00:00:00.150 00:00:01.657 001-99800 2011-12-28 13:36:57.843 2011-12-28 13:36:57.593 00:00:00.250 00:00:00.150 00:00:01.657 001-99700 2011-12-28 13:36:57.687 2011-12-28 13:36:57.547 00:00:00.140 00:00:00.150 00:00:01.657 001-99600 2011-12-28 13:36:57.593 2011-12-28 13:36:57.547 00:00:00.047 00:00:00.150 00:00:01.657 001-99500 2011-12-28 13:36:57.563 2011-12-28 13:36:57.437 00:00:00.127 00:00:00.150 00:00:01.657 001-99400 2011-12-28 13:36:57.547 2011-12-28 13:36:57.437 00:00:00.110 00:00:00.150 00:00:01.657 001-99300 2011-12-28 13:36:57.483 2011-12-28 13:36:57.377 00:00:00.107 00:00:00.150 00:00:01.657 001-99200 2011-12-28 13:36:57.437 2011-12-28 13:36:57.377 00:00:00.060 00:00:00.150 00:00:01.657 001-99100 2011-12-28 13:36:57.407 2011-12-28 13:36:57.187 00:00:00.220 00:00:00.150 00:00:01.657 001-99000 2011-12-28 13:36:57.360 2011-12-28 13:36:57.187 00:00:00.170 00:00:00.150 00:00:01.657 001-9900 2011-12-28 13:32:22.657 2011-12-28 13:32:22.500 00:00:00.157 00:00:00.150 00:00:01.657

此查询使用 windowed 聚合函数计算聚合结果，以便您可以看到聚合和非聚合结果。我只使用了窗口函数，因为我想向您展示如何将您的时间显示方法应用于非聚合和聚合结果，以便您可以看到表达式的哪个部分会根据所需转换为时间表示的结果类型而更改。实际上，如果这是您的要求，您可以使用“正常”的聚合结果（和分组，如果必要）。

您可以在 SQL Fiddle 上玩一个“实时”演示查询。

- Andriy M

Answer 2

在SQL中使用avg函数，它将为您提供列的平均值

SELECT AVG(column name) FROM tabelname;

为求和操作使用以下代码

 SELECT sum(column name)  FROM tabelname;

- user1102001

---

出现以下错误Msg 8117，级别16，状态1，行1 varchar操作数数据类型无效，不适用于sum运算符。Msg 8117，级别16，状态1，行1 varchar操作数数据类型无效，不适用于avg运算符。 - Rikky

Answer 3

您可以确定完成时间和发送时间列的日期差（以毫秒为单位），然后对这些结果进行平均或求和。

select sum(datediff(ms, JOB_send_time, JOB_DONE_time)) as SUM_Timing
,      avg(datediff(ms, JOB_send_time, JOB_DONE_time)) as AVG_Timing
from   dbo.insert_data

- rene

Answer 4

我认为（抱歉，现在无法尝试）你需要使用DATEDIFF函数。

在你的情况下：

select avg(DATEDIFF(Millisecond, JOB_DONE_time, JOB_send_time)) from dbo.insert_data

- CapelliC