如何从多行中计算格式化为VARCHAR的日期的平均值

Question

我遇到了一个问题，我正在针对数据库运行脚本，以获取需要转换为DateTimes的多个VARCHAR之间的平均差，然后获取所有结果之间的平均值。

我的代码是：

SELECT YEAR(b.DateAcknow),AVG(datediff(dd,convert(datetime,b.DateAssign),
convert(datetime,b.DateResolv))) as DayAverage, 
AVG(datediff(hh,convert(datetime,b.TimeAcknow),  
convert(datetime,b.TimeResolv))) as HourAverage
FROM      table AS b              
WHERE     (x = y) 
AND YEAR(DateResolv) >= 2006
AND YEAR(DateResolv) < 2016
AND  b.resolution <>''
GROUP BY YEAR(b.DateAcknow)
ORDER BY YEAR(b.DateAcknow)`

我得到的结果似乎并没有意义，更不用说它包含了1900，这超出了我的where子句的参数范围

这是：

NULL        42          NULL
1900        0           12
2006        7           -5
2007        6           1
2008        7           1
2009        4           1
2010        2           0
2011        2           0
2012        2           0
2013        2           0
2014        2           0
2015        2           0

我把VARCHAR转换错了吗？

我怀疑2010-2015年数千个条目的平均值是否都是一样的2天零小时，所以要么我做错了什么，要么数据不好。

Answer 1

您按DateResolv进行筛选，按DateAcknow进行分组。

根据相同的字段和NULL进行筛选和分组，并且范围之外的值应该消失。

Answer 2

您可能需要去掉聚合部分，然后运行：

SELECT YEAR(b.DateAcknow)
, convert(datetime,b.DateAssign) AS DateAssignDateTime
, convert(datetime,b.DateResolv) AS DateResolveDateTime
, datediff(dd,convert(datetime,b.DateAssign), convert(datetime,b.DateResolv)) AS AssignResolveDayDiff
, convert(datetime,b.TimeAcknow) AS TimeAcknowDateTime
, convert(datetime,b.TimeResolv) AS TimeResolveDateTime
, datediff(hh,convert(datetime,b.TimeAcknow), convert(datetime,b.TimeResolv)) AS AcknowResolveHourDiff
FROM      table AS b              
WHERE     (x = y) 
AND YEAR(DateAcknow) >= 2006
AND YEAR(DateAcknow) < 2016
AND  b.resolution <>''
ORDER BY YEAR(b.DateAcknow)

首先要确保您的所有转换都是有意义的。然后你会更好地了解你的平均值是多少。

之后，如果全部检查出来，那么您的查询应该可以正常工作（不过，请检查mxix是否从更改

...
AND YEAR(DateResolv) >= 2006
AND YEAR(DateResolv) < 2016
...

至

...
AND YEAR(b.DateAcknow) >= 2006
AND YEAR(b.DateAcknow) < 2016
...

对你来说很有意义。

如果你想提高输出的精度，那么试着这样转换你的datediff：旧：AVG(datediff(dd,convert(datetime,b.DateAssign), convert(datetime,b.DateResolv)))

新增：AVG(Convert(Decimal(10, 5), datediff(dd,convert(datetime,b.DateAssign), convert(datetime,b.DateResolv))))

您的旧查询是平均天数，四舍五入到最接近的整数值，得到类似"2"的值。这个新的调整将给你像"1.51235"天这样的答案。

由于有10万个差异记录（正负），如果它们遵循正态或均匀分布，那么平均值很有可能接近零。也可以尝试：AVG(Convert(Decimal(10, 5), ABS(datediff(dd,convert(datetime,b.DateAssign), convert(datetime,b.DateResolv)))))

如果你想要绝对的差异。如果你的旧数据的值为"5，-3，4，-1，3"，那么旧方法会产生2的平均值，但如果你有"ABS"函数处理它们，它会将值更改为"5、3、4、1、3"，并将产生的平均值向++方向移动（如果你也进行了十进制转换，它会更改为"3"或"3.2"）。

Answer 3

我的意图是显示每年的平均反应时间以天和小时为单位obizues

假设：

1. DateAcknow是一个带有空时间戳的varchar日期（例如，"2011/01/15"）
2. TimeAcknow是DateAcknow对应的varchar时间（例如，"15:35"）
3. DateResolve是一个带有空时间戳的varchar日期（例如，"2011/01/16"），该时间戳始终大于或等于DateAcknow
4. TimeResolve是DateResolve对应的varchar时间（例如，"13:47"）
5. 您想要平均总时差（使用上面的示例，此记录的时差为22）

如果您需要有关varchar日期格式和转换函数的帮助，请参阅：http://msdn.microsoft.com/en-us/library/ms187928.aspx

以下方法应能实现您的意图：

SELECT YEAR(b.DateAcknow)
, AVG(DateDiff(Day, Convert(datetime, b.DateAcknow) + convert(datetime, b.TimeAcknow), Convert(datetime, b.DateResolv) + Convert(datetime, b.TimeResolve)))  AS AvgDaysDifference
, AVG(DateDiff(Hour, Convert(datetime, b.DateAcknow) + convert(datetime, b.TimeAcknow), Convert(datetime, b.DateResolv) + Convert(datetime, b.TimeResolve)))  AS AvgHoursDifference
FROM      table AS b              
WHERE (x = y) AND YEAR(DateAcknow) >= 2006 AND YEAR(DateAcknow) < 2016
   AND  b.resolution <>''
GROUP BY YEAR(b.DateAcknow)

如果对您的数据和意图的假设是正确的，就应该这样做。不清楚的时候很难帮忙。