如何在SQL Server中最好地表示有理数？

Question

我正在处理本地提供的有理数数据。我在C#中有一个流畅的通用类，它可以美观地表示这些数据并允许转换为许多其他形式。不幸的是，当我想要将其存储在SQL中时，我有几个解决方案，但没有一个令人满意。

这里是一个例子。我有原始值2/3，我的new Rational<int>(2, 3)在C#中很容易处理。我考虑存储这个值的选项如下：

1. 只作为十进制/浮点数，即值=0.66666667，具有各种精度和准确性。 优点：这使我能够查询数据，例如查找值 < 1。 缺点：它会失去精度，并且在我回到UI中显示这个简单值时非常丑陋。

2. 作为两个精确的整数字段存储，例如分子=2，分母=3，具有各种精度和准确性。 优点：这使我能够精确地表示原始值，并在以后以最简单的形式显示它。 缺点：我现在有两个字段来表示这个值，查询变得复杂/不太有效率，因为每个查询都必须执行算术运算，例如查找分子/分母 < 1。

3. 序列化为字符串数据，即"2/3"。我将能够知道最大字符串长度并具有可以容纳此字符串的varchar。 优点：我回到了一个字段，但是有一个精确的表示。 缺点：查询基本上已经破坏了，需要支付序列化成本。

4. #1和#2的组合。 优点：轻松/高效地查询值范围，和在UI中拥有精确值。 缺点：需要三个字段来保存一个数据片段，必须保持多个表示同步，这会破坏D.R.Y.原则。

5. #1和#3的组合。 优点：轻松/高效地查询值范围，和在UI中拥有精确值。 缺点：回到了两个字段来保存一个数据片段，必须保持多个表示同步，这会破坏D.R.Y.原则，并且必须支付额外的序列化成本。

有没有其他比这些更好的开箱即用的解决方案？我是否考虑了其他事情？在SQL中是否有相对简单的方法可以做到这一点，而我只是不知道？

- mckamey

---

我可以问一下为什么这是一个通用类吗？我无法理解你为什么会使用除<int>之外的任何东西。 - Paul Creasey

当您想要将其与<long>或<short>等一起使用时，:)具有讽刺意味的是，我实际上正在将其与多种不同类型一起使用，因为原始数据仅限于某些精度。 - mckamey

@Paul：我更倾向于问“为什么不呢？”像Haskell或者LINQ这样的语言已经让我相信，强类型泛型应该成为我们思考问题的默认方式。 - Cogwheel

Answer 1

如果您正在使用SQL Server 2005或2008，则可以选择定义自己的CLR数据类型：

从SQL Server 2005开始，您可以使用用户定义类型（UDT）来扩展服务器的标量类型系统，使得CLR对象可以存储在SQL Server数据库中。UDT可以包含多个元素，并且可以具有行为，这使它们与传统的别名数据类型有所区别，后者由单个SQL Server系统数据类型组成。

由于整个系统都可以访问UDT，因此将其用于复杂数据类型可能会对性能产生负面影响。通常最好使用传统的行和表来建模复杂数据。 SQL Server中的UDT非常适合以下内容：

日期、时间、货币和扩展数字类型 地理空间应用程序

编码或加密数据

如果您可以接受限制，我无法想象有比映射已在自定义类中捕获的数据更好的方法了。

- Michael Petrotta

---

有趣的想法，但在我看来，在 SQL 中使用 CLR 似乎有点过度设计，因为我只有两个整数。不过这对于将来遇到的其他问题可能是一个有用的想法。 - mckamey

为什么这是过度设计？CLR类不必复杂。 - RickNZ

需要在SQL中使用CLR来处理一个非常简单的类对于我的需求来说有些过度了。 - mckamey

Answer 2

我会选择选项#4，但使用计算列来避免同步/DRY问题（也意味着您实际上只存储了2列，避免了“三个字段”问题）。

在SQL Server中，计算列的定义如下：

CREATE TABLE dbo.Whatever(
   Numerator INT NOT NULL,
   Denominator INT NOT NULL,
   Value AS (Numerator / Denominator) PERSISTED
)

请注意，您可能需要进行一些类型转换和验证，以确保分母不为零等。

此外，SQL 2005添加了一个持久化计算列，可以在查询时消除计算。

- Chris Shaffer

---

嗯...我喜欢这种纯粹的方式：没有序列化，没有SQL中的CLR。我可以获得精度、可查询性和DRY。存储大小是唯一真正的缺点，但也不是很大。 - mckamey

@Chris：你可以使用CASE语句来停止计算，如果分母为null的话 - 详见我的回答。 - OMG Ponies

如果我使用如下计算列，Numerator INT NOT NULL, Denominator INT NOT NULL, Value AS CASE WHEN Denominator <> 0 THEN (Numerator / Denominator) ELSE 0 END PERSISTED，那么它的数据类型是什么？它会变成十进制类型还是会尝试将其截断为INT类型？ - mckamey

如输入不带小数点的话，它可能是一个整数。你可以将其中一个数字转型为浮点型，或者乘以1.0来得到一个小数（例如，（分子*1.0）/ 分母），或使用CAST（Numerator AS DECIMAL（14,5））[精度由您决定]。 - Chris Shaffer

此外，如果您想要摆脱CASE，您可以在分母上放置一个CHECK约束，以确保其不为零；这取决于您的数据类型是否允许。 - Chris Shaffer

另一个可能的选项是：Numerator / NULLIF(CAST(Denominator AS DECIMAL(14,5), 0)，在除以零时会返回空值。 - jnm2

Answer 3

你需要多高的精度？

无论是使用C#还是其他语言，在某个精度位置上，都会将2/3舍入。如果对于你正在处理的内容来说，使用科学计数法的10进制值是可以接受的，那么请在数据库中相应地设置精度。

如果精度真的很重要，那么将分子和分母分开。这将确保您始终可以访问所需的任何精度，并且您可以使用计算列来表示该值以进行快速过滤：

numerator INT,
denominator INT,
result AS CASE WHEN denominator > 0 THEN numerator / denominator ELSE NULL END

- OMG Ponies

---

部分原因是我不应该为可以用一对非常小的整数表示的东西选择需要多少精度。感谢您进行除以零检查。 - mckamey

@McKAMEY：涉及到成本，具体取决于你想做什么。这些成本可能会迫使你重新评估你的需求，并寻求妥协。 - OMG Ponies

同意。在此应用程序的用户界面中显示“0.6666667”是不可接受的，这就是为什么我开始走有理数路线的原因。感谢您的帮助。 - mckamey

Answer 4

我曾经尝试过在 SQL Server 2008 中使用 几何数据类型 存储和操作有理数。基本上，我假设分子放置在一个虚构的几何点的 X 位置，分母放置在 Y 位置。

这对我的需求很好，但这可能对你来说是无用的。这取决于你的优先级是什么（性能、代码可读性等）。我个人发现，用于几何数据操作的 T-SQL 很难编写和阅读。

- CesarGon

---

有趣的想法。不过我猜我不能将其作为单个值进行查询。 - mckamey

说实话，我不确定。我还没有做到那一步。而且对我来说，T-SQL的几何查询语法并不是很直观。 - CesarGon

Answer 5

你需要多少精度？在我看来，double/float提供了相当不错的精度。我非常确定科学/天文数据需要比这更高的精度。我知道像matlab和mathematica这样的库很擅长处理这些问题。我发现你可以将mathematica与你的.net程序一起使用。这里是链接

编辑：添加更多链接和引用

"当Mathematica操作有理数时，无论需要多少位数字，它都会给出一个精确的结果" 来自 这里

另一篇好文章，但我想你可能需要自己实现它。

- ram

---

好文章；我喜欢这篇论文。它可能在将来的某些方面有所用处，但在不疯狂的情况下可能不能用于SQL。 - mckamey