sql - 如何在不更改总和的情况下舍入单个 SQL 请求中的列？

Question

我有一个这样定义的表:

create table #tbFoo
(bar float)

我正在寻找一种方法来舍入列条中包含的每个值而不更改总和(已知是整数，或者由于 float 精度而非常接近整数)。

将每个值四舍五入到最接近的整数是行不通的(例如:1,5;1,5 将四舍五入为 1;1 或 2;2)

使用多个请求(例如，存储原始总和、四舍五入、计算新总和，并根据需要更新尽可能多的行以返回原始总和)很容易做到这一点，但这不是一个非常优雅的解决方案.

有没有办法使用单个 SQL 请求来做到这一点？

---

我使用的是 SQL Server 2008，因此欢迎使用利用该特定供应商的解决方案。

---

编辑:我正在寻找一个最小化旧值和新值之间差异的请求。换句话说，如果向下舍入了更大的值，则永远不应向上舍入该值，反之亦然

Answer 1

更新:

在我的博客文章中查看此解决方案的详细解释:

• Rounding numbers preserving their sum

---

您需要为每个值保留累积偏移量:

1.2   (1 + 0.0)  ~ 1    1   1.2   +0.2
1.2   (1 + 0.2)  ~ 1    2   2.4   +0.4
1.2   (1 + 0.4)  ~ 1    3   3.6   +0.6
1.2   (1 + 0.6)  ~ 2    5   4.8   -0.2
1.2   (1 - 0.2)  ~ 1    6   6.0   0.0

这在 MySQL 中很容易完成，但在 SQL Server 中，您将不得不编写游标或使用累积子选择(效率较低)。

更新:

下面的查询选择值之和与向下舍入到最接近的较小整数的值之和之间的差值。

这给了我们应该四舍五入的值的数量 (N)。

然后我们按小数部分对值进行排序(最接近上限的部分排在最前面)并将第一个 N 向上舍入，将其他向下舍入。

SELECT  value,
        FLOOR(value) + CASE WHEN ROW_NUMBER() OVER (ORDER BY value - FLOOR(value) DESC) <= cs THEN 1 ELSE 0 END AS nvalue
FROM    (
        SELECT  cs, value
        FROM    (
                SELECT  SUM(value) - SUM(FLOOR(value)) AS cs
                FROM    @mytable
                ) c
        CROSS JOIN
                @mytable
        ) q

这是测试数据的脚本:

SET NOCOUNT ON
GO
SELECT  RAND(0.20090917)
DECLARE @mytable TABLE (value FLOAT NOT NULL)
DECLARE @cnt INT;
SET @cnt = 0;
WHILE @cnt < 100
BEGIN
        INSERT
        INTO    @mytable
        VALUES  (FLOOR(RAND() * 100) / 10)
        SET @cnt = @cnt + 1
END

INSERT
INTO    @mytable
SELECT  600 - SUM(value)
FROM    @mytable