我有以下查询:
-- CTE to remove outliers, e.g. remove the fastest and slowest results ;WITH MinMaxCTE AS ( SELECT ServerName, CONVERT(VARCHAR(10), UpdatedOn, 101) AS [Date], Version, MIN(JaguarStartupTime) AS MinStartTime, MAX(JaguarStartupTime) AS MaxStartTime FROM dbo.MachineConfiguration (NOLOCK) WHERE DomainLogin NOT IN (SELECT DomainLogin FROM dbo.SupportGroup) GROUP BY ServerName, CONVERT(VARCHAR(10), UpdatedOn, 101), Version ) SELECT AVG(mc.JaguarStartupTime) AS AverageTime , COUNT(*) AS NumEntries , mc.Version FROM #Eligible mc (NOLOCK) JOIN MinMaxCTE cte ON mc.ServerName = cte.ServerName AND CONVERT(VARCHAR(10), mc.UpdatedOn, 101) = cte.[Date] AND mc.Version = cte.Version AND mc.JaguarStartupTime <> cte.MinStartTime AND mc.JaguarStartupTime <> cte.MaxStartTime GROUP BY mc.Version ORDER BY Version DESC, AVG(mc.JaguarStartupTime) ASC
create table #Eligible ( Version nvarchar(50), JaguarStartupTime int, ServerName nvarchar(50), UpdatedOn datetime )
无论我注释掉什么条件或聚合,总是会出现以下错误:Arithmetic overflow error converting expression to data type int。
Arithmetic overflow error converting expression to data type int
我可以从这里去哪里?如何进一步调试?
编辑:样本数据
Version JaguarStartupTime ServerName UpdatedOn 6.4.6.082 16040 NewOrleansLA 2012-08-08 12:34:12.330 6.5.1.012 40390 BatonRougeLA 2012-08-08 18:33:17.440 6.5.1.012 48379 HonoluluHI 2012-08-09 04:42:50.453
您是否尝试过将jaguarstartup时间强制转换为avg聚合中的bigint,如下所示…
AVG(CAST(mc.JaguarStartupTime AS BIGINT))
这应该解决算术溢出。
要计算平均平均值,服务器需要首先能够对所有int求和,因此要求平均值的数据类型必须能够存储这些值的总和,即使返回的答案在int的范围内