在MSSQL中使用超分区时不一致的总和

On a static table, I'm trying to run the following query in SSMS (to get the latest transaction for each user and sum the dollar value):

在静态表上，我尝试在SSMS中运行以下查询(获取每个用户的最新事务并合计美元值):

SELECT
    SUM(nMoney) As TotalMoney
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY nGroup, nUser ORDER BY dTransaction DESC) AS SEQNUM
    , nMoney
    FROM [MyDB].[dbo].[MyTable]
) MySubquery
WHERE MySubquery.SEQNUM=1

This is a table with 2,701,510 rows and the nMoney column is of type Decimal(12,2). When I run it multiple times, I get a varying result:

这是一个包含2,701,510行的表，nMoney列类型为Decimal(12,2)。当我运行它多次时，我得到一个不同的结果:

2317367341.75
2317370443.45
2317449819.62
2317360649.43
2317449819.62

What could be causing the inconsistent result?

是什么导致了不一致的结果?

1 个解决方案

#1

For floating point arithmetic the order that numbers are added in can affect the result.

对于浮点算法，数字添加的顺序会影响结果。

But in this case you are using Decimal(12,2) which is precise.

但是在这种情况下，你使用的是精确的小数(12,2)

The issue is that with duplicate values for nGroup, nUser, dTransaction the ROW_NUMBER is not deterministic so different runs can return different results.

问题是，对于nGroup、nUser和dTransaction的重复值，ROW_NUMBER不是确定的，因此不同的运行可以返回不同的结果。

To get deterministic behaviour you can add guaranteed unique column(s) into the end of the ORDER BY to act as a tie breaker.

要获得确定性行为，您可以在订单的末尾添加有保证的惟一列(s)，作为一个tie断路器。

#1