我正在创建一些测试数据,这需要我计算一个百分比。
在我的谓词中,我排除了任何可能导致零错误划分的记录,当我在该数据集上运行SQL查询时,一切都很好。
生成的记录总数(所有组合):92,345,408
记录总数不包括零零实例的鸿沟:92,141,104
当我添加"有资格使用用例1"条件时,查询仍然没有错误执行。但是,当我还将"用例2"添加到谓词时,我会遇到零错误的鸿沟。我不明白这是怎么发生的,因为我排除了这种情况:
WHERE CAST(m1.MoneyValue1 AS FLOAT) - CAST(m2.MoneyValue2 AS FLOAT) != 0
以下是我的代码创建3个不同的美元价值列(十进制(18,2)),然后我使用十字架应用以获取所有可能的组合。
DECLARE @Money1 TABLE
(
ID INT IDENTITY (1,1) NOT NULL,
MoneyValue1 DECIMAL (18,2) NOT NULL
)
DECLARE @Money2 TABLE
(
ID INT IDENTITY (1,1) NOT NULL,
MoneyValue2 DECIMAL (18,2) NOT NULL
)
DECLARE @Money3 TABLE
(
ID INT IDENTITY (1,1) NOT NULL,
MoneyValue3 DECIMAL (18,2) NOT NULL
)
DECLARE @stop DECIMAL(18,2) = 2000.00 -- capping the maximum test value at $2000.00
DECLARE @interval FLOAT = 4.43 -- adding a random dollar amount to create variability and several test values
DECLARE @MoneyValue DECIMAL (18,2) = 0 -- for my test, I don't care about negative dollar amounts
WHILE @MoneyValue < @stop
BEGIN
INSERT INTO @Money1
(
MoneyValue1
)
SELECT CAST(@MoneyValue AS DECIMAL(18,2))
SET @MoneyValue = CAST(@MoneyValue AS FLOAT) + CAST(@interval AS FLOAT)
END
INSERT INTO @Money2 -- use the same values generated by the statement above for my second Money column
(
MoneyValue2
)
SELECT
CAST(MoneyValue1 AS DECIMAL(18,2))
FROM @Money1
INSERT INTO @Money3 -- use the same values generated by the statement above for my second Money column
(
MoneyValue3
)
SELECT
CAST(MoneyValue1 AS DECIMAL(18,2))
FROM @Money1
接下来,我想创建10个随机数据样本;计算列是要显示用例1的值(请参见谓词示例中的谓词,在下面引起错误)。
SELECT TOP 10
m1.MoneyValue1 AS TotalPmt,
m2.MoneyValue2 AS TotalPmtChange,
m3.MoneyValue3 AS PmtChangeAmount
,CAST(m2.MoneyValue2 AS FLOAT) / (CAST(m1.MoneyValue1 - m2.MoneyValue2 AS FLOAT)) AS Calc
FROM @Money1 AS m1
CROSS APPLY @Money2 AS m2
CROSS APPLY @Money3 AS m3
WHERE CAST(m1.MoneyValue1 AS FLOAT) - CAST(m2.MoneyValue2 AS FLOAT) != 0 -- exclude the possibility of a divide by zero error
ORDER BY NEWID()
如果我将谓词更改为现在也只包括用例1,则查询执行没有错误。
WHERE CAST(m1.MoneyValue1 AS FLOAT) - CAST(m2.MoneyValue2 AS FLOAT) != 0 -- exclude the possibility of a divide by zero error
AND CAST(m2.MoneyValue2 AS FLOAT) / (CAST(m1.MoneyValue1 - m2.MoneyValue2 AS FLOAT)) > .1 -- qualify for Use Case 1
ORDER BY NEWID()
但是,如果我将谓词更改为也包括用例1和用例2条件,我现在将获得零错误的划分!
WHERE CAST(m1.MoneyValue1 AS FLOAT) - CAST(m2.MoneyValue2 AS FLOAT) != 0 -- exclude the possibility of a divide by zero error
AND CAST(m2.MoneyValue2 AS FLOAT) / (CAST(m1.MoneyValue1 - m2.MoneyValue2 AS FLOAT)) > .1 -- qualify for Use Case 1
AND CAST(m3.MoneyValue3 AS FLOAT) / (CAST(m1.MoneyValue1 - m2.MoneyValue2 AS FLOAT)) > .1 -- qualify for Use Case 2
来自SSM的消息:
(452 row(s) affected)
Msg 8134, Level 16, State 1, Line 58
Divide by zero error encountered.
虽然我不一定要指出故障的机制,但我可以说,一旦将数据移至物理表中,就会停止发生零错误。
一个支持使用表变量作为原因的帖子:我什么时候应该在SQL Server中使用表变量与临时表?
也许无法在表变量上创建和运行统计信息,这会导致引擎与零记录相撞。另一种可能性是SQL Server无法正确查看表变量的基数,即从表变量估算一个记录以输出。
从上面位置的链接中,我发现一件事很有趣,它在此链接中具有引用:sql Server中的temp表和表变量之间有什么区别?
无列统计
具有更准确的桌子基数并不意味着估计 但是,行计数将更准确(除非进行操作 在表中的所有行上)。SQL Server无法维护列 表变量的统计数据,因此会依靠猜测 根据比较谓词(例如,表的10%将 返回以抗非唯一列的= AN => 30% 比较)。相反的列统计是为#TEMP维护的 桌子。
不管原因是什么,我发现解决方案回到了我原来问题的原因(当我的谓词明确排除了零除以零的可能性时遇到零错误的鸿沟)作为使用副产品的副产品表变量包含数百万个记录。