SQL中用户定义的顺序非常困难的问题

某些应用程序（例如待办事项列表）需要维护用户定义的项目顺序。挑战在于顺序是任意的，并且在用户重新排列项目时可以更改：

本文研究了在SQL中建模情况的最佳方法。我们将研究几种方法，并对每种方法评估三个属性：

时空效率。在磁盘上存储行顺序应紧凑，重新排序项目应使用最少的CPU和I / O资源。

坚固性。对可以重新订购商品的次数没有实际限制。

优雅。该解决方案不需要复杂的PL / pgSQL函数或解析字符串中的数字列表。

最自然的第一次尝试是添加一个自动递增的整数列以跟踪每个项目的位置：

插入todo（任务）值（使用sql进行实验），（撰写文章），（放松），（重复＆＃ 39;）;从pos asc的todos订单中选择*； / *┌────────────────│任务│pos│├─────────── ────────┼──────┤│尝试sql│1││写文章│2││放松│3││重复│4│└────────── ──────────┴──────┘* /

更困难的是在列表中插入项目或对现有项目进行重新排序。假设我们要在第2项和第3项之间插入一个新的“编辑文章”任务，这需要将第3项和第一个位置向前移动，并将该项目插入位置3。但是，即使第一步也遇到了问题：

-将项目3和向前移动一个位置以更新待办事项集pos = pos + 1，其中pos＆gt; = 3; / *错误：23505：重复的键值违反了唯一约束＆＃34; todos_pos_key＆＃34;详细信息：密钥（pos）=（4）已存在。 * /

唯一性约束使更新对每个表行的处理时间敏感。在我们的案例中，它尝试将项目3移动到位置4，而没有先将4移动到5。

创建表待办事项（任务文本，pos序列，唯一（pos）可推迟的初始推迟-^^^添加此）; -现在我们可以移动列表并开始插入项目；更新待办事项集pos = pos + 1，其中pos＆gt; = 3;插入待办事项（位置，任务）值（3，编辑文章）。 -不要忘了增加序列选择nextval（＆＃39; todos_pos_seq＆＃39;）;承诺;

强大的？是。它支持在其他项目之间可靠地插入/移动项目。此外，即使在“读取已提交”隔离级别，并发插入项目也不会导致不一致。 （至少在我的测试中。）

优雅？否。该技术需要一系列脆弱的步骤，包括推迟约束和调整顺序。

与其使用顺序整数，不如在它们之间留出空间？就像跳过BASIC编程语言中的行号一样，我们也可以跳过表中的位置值在它们之间留出空间。就像是：

要在其他两个项目之间插入项目，只需使用周围项目平均的位置即可。但是，通过在每个项目之间选择2 ^ 16个空格，我们可以在第一个和下一个项目之间支持不超过16个连续插入。达到此限制后，我们将恢复到以前将项目向前移动的方法。

优雅？不，由于混合逻辑，它甚至比以前的方法还要糟糕。

如果我们使用浮点或数值而不是int或bigint存储每一行​​的位置怎么办？这将允许在其他元素之间挤压新元素，而不是向前移动项目以腾出空间。这是个主意：

创建序列todos_seq;创建表待办事项（任务文本，pos浮动不为null默认nextval（＆＃39; todos_seq＆＃39;），唯一（pos））;插入todo（任务）值（使用sql进行实验），（撰写文章），（放松），（重复＆＃ 39;）;

似乎是一个完美的解决方案！但是，浮点数的精度有限。例如，如果我们重复插入1000和1001之间，每次都切成两半，那么到第38次迭代时，它将截断为正好1000：

i────┬──────────────┐│i│val│├────┼──────────── ────┤│25│1000.0000000298││26│1000.0000000149││27│1000.00000000745││28│1000.00000000373││29│1000.00000000186││30│1000.00000000093││31│1000.00000000047││32│1000.00000000023││33│1000.00000000012│ │34│1000.00000000006││35│1000.00000000003││36│1000.00000000001││37│1000.00000000001││38│1000││39│1000││40│1000│└────┴──────── ────────┘

有效率吗？是。一个简单的64位列和一些算法使得将新元素快速插入列表中。

好的，所以浮点类型最终会超出精度范围–在SQL中使用数字（又称十进制）类型会怎样？它具有可变大小，可以根据需要增长以存储精确数字。它有多大？让我们问一下数据库：

从generate_series（1，21）中选择（1 / power（2，i））：：numeric作为val，pg_column_size（1 / power（2，i）：：numeric）作为sz从i_generate_series（1，21）中作为i; / *┌──────────────────┐│val│sz│├──────── ──────────────────┼│0.5│8││0.25│8││0.125│8││0.0625│8││0.03125│10││0.015625│10 ││0.0078125│10││0.00390625│10││0.001953125│12││0.0009765625│12││0.00048828125│12││0.000244140625│12││0.0001220703125│14││0.00006103515625│12││0.000525517 ││0.00000762939453125│14││0.000003814697265625│14││0.0000019073486328125│14││0.00000095367431640625│14││0.000000476837158203125│16│└────────── ──┴────┘* /

它实际上很快达到了12个字节，而实际上最好在0到1之间切入。随着整数部分的增长，数字需要更多的字节。但是，除了磁盘使用以外，这是迄今为止最好的方法。

有效率吗？是的，主要是。如果列表重新排列的次数不多，则数字类型超过128位的情况并不常见，并且该列上的大多数操作都是索引比较，而不是算术索引。

强大的？是。在很长一段时间内，随着列表的重新排列，数值可能会继续占用更多空间，但是出于所有实际目的，您可以永久重新排列列表。

由于我们使用平均值选择中点的方式，浮点型和数值型最终都受到限制。这些中点很快消耗了精度。浮点数根本无法处理，并且数字变得肿。但是，我们可以使用一个数学技巧来获取浮点数大小的数字类型的稳健性。

在撰写本文的过程中，我为PostgreSQL创建了一个新的基本类型，称为Rational。可在此处作为数据库扩展名使用。它执行精确的小数运算，我设计为始终每小数精确使用64位，这与PostgreSQL的float类型大小相同。 （我从postgres Wiki获得了灵感。）

分数比在学校展示的分数有趣得多。非负分数实际上形成了一个二叉树，每个分数（最低限度）出现在一个唯一节点上。

这棵“斯特罗科树”以1860年代的独立发现者命名：数学家和钟表匠。如果您想进一步了解该理论，请观看此youtube视频。

这棵树可以这样帮助我们：如果您想在特定范围内找到一个分数（a＆lt; x＆lt; b），则遍历Stern-Brocot树-在二元搜索中随便构造它。一旦您击中边界内的节点，请停止。听起来可能很困难，但是pg_rational具有内置的逻辑。

-＆＃34;理性＆＃34;类型来自扩展名-https://github.com/begriffs/pg_rational创建扩展名pg_rational;创建序列todos_seq为整数；创建表待办事项（任务文本，pos有理不为null默认nextval（＆＃39; todos_seq＆＃39;）：：整数，唯一（pos））;插入todo（任务）值（使用sql进行实验），（撰写文章），（放松），（重复＆＃ 39;）;从pos asc的todos订单中选择*； / *┌────────────────│任务│pos│├─────────── ────────┼──────┤│尝试sql│1/1││写文章│2/1│││放松│3/1││重复│4/1│└─── ────────────────┴─────┘* /

就像使用浮点数或整数一样简单！插入新值也很容易：

在todo（pos，task）值中插入（rational_intermediate（2，3），＆＃39; edit article＆＃39;）;从pos asc的todos订单中选择*； / *┌────────────────│任务│pos│├─────────── ────────┼──────┤│用sql进行实验│1/1││写文章│2/1││编辑文章│5/2││放松│3/1││重复│ 4/1│└──────────────┴* /

这些分数的术语以最低的术语表示，并在每次插入时缓慢增长。例如，您可以从树形图中更早地看到，在1和0之间插入0将生成1 / 2、1 / 3、1 / 4…，这可能会花费很长时间，因为pg_rational中的分子和分母每个都获得32位存储空间。

强大的？是。通过重复的列表重新排序，实际上将永远耗尽空间。

存储列表顺序的最后一种方法是使用有理类型进行计算，但将结果存储为浮点型。库中的理性可以来回转换：

-将float转换为有理数选择0.263157894737：：float：：rational; -=＆gt; 5/19-将有理数转换为浮点数select＆＃39; -1 / 2＆＃39 ;:：rational：：float; -=＆gt; -0.5

创建序列todos_seq;创建表待办事项（任务文本，pos浮动不为null默认nextval（＆＃39; todos_seq＆＃39;），唯一（pos））;插入todo（任务）值（使用sql进行实验），（撰写文章），（放松），（重复＆＃ 39;）;

但是现在使用有理数进行插入，将其保存到列中时将被强制浮动。

在todo（pos，task）值中插入（rational_intermediate（2，3），＆＃39; edit article＆＃39;）;从pos asc的todos订单中选择*； / *┌────────────────│任务│pos│├─────────── ────────┼──────┤│尝试sql│1││写文章│2││编辑文章│2.5││放松│3││重复│4│└────── ──────────────┴──────┘* /

由Rational_intermediate创建的浮动对象似乎并没有遇到像求平均值那样的精度问题。例如，采用从零到一的重复中间形式，将产生以下模式：

从generate_series（1，15）AS i中选择（i，i + 1）：：ratt：：rational AS fract，i：：float /（i + 1）：：float AS float / *┌──────┬──────────┐│fract│float│├───────┼───── ──────────────┤│1/2│0.5││2/3│0.666666666666667││3/4│0.75││4/5│0.8││5/6│0.833333333333333│ │6/7│0.857142857142857│││7/8│0.875││8/9│0.888888888888889│││9/10│0.9│││10/11│0.909090909090909││11/12│0.916666666666667││12/13│0.923076923076923││ 13/14│0.928571428571429││14/15│0.933333333333333│││15/16│0.9375│└───────┴──────────── /

-多久才能浮起才能分辨出连续的分数？从generate_series（1，100000000）中选择min（i）作为i其中（i：：float /（i + 1.0））=（（i：：float + 1.0）/（i + 2.0））; -=＆gt; 94911150

其他分数可能会对浮点表示法造成更大的困难，但是从我有限的实验来看，选择中点的这种方法似乎比取平均值更好。

最终，与仅使用pg_rational相比，这种方法有其优点和缺点。磁盘上的空间是相同的。比较浮点数的速度比比较分数的速度快（单个CPU指令与整数项的额外交叉乘法）。但是要找到中间体，首先需要使用迭代算法将浮点数转换为分数。与简单地存储分数相比，这增加了开销。

以下是一些额外的注释，可帮助您充分利用扩展程序。

将第一项插入为1/1而不是0/1。查找中间值的算法永远不会低于零，因此从1开始将允许您在第一个项目之前插入将来的项目。您甚至可以向position列添加一个约束，以强制所有值严格大于零。

将btree索引添加到position列。有理类型支持索引。您只需在列中添加唯一性约束即可自动获得索引。

这是在给定项目之后立即插入新项目的最简单方法，它比任何现有的后续项目都要早切割： -在项目2 INSERT INTO待办事项（位置，任务）之后立即添加项目。SELECT Rational_intermediate（2，min（pos）），＆＃39; edit article＆＃39; 从todos WHERE pos＆gt; 2; 即使项目是最大项目，此方法也有效，因为Rational_intermediate将第二个参数中的NULL视为无穷大。 您甚至可能不想使用序列来跟踪插入的最大项目。 您可以通过执行以下操作在列表末尾插入： 即使在空表上也可以使用。 我之前使用了一个序列，只是为了减少插入一批项目的代码。 尽管将其发送到客户端应用程序将阻止客户端知道真实位置，以要求数据库重新排列它们，所以重新标记可能应该在显示时发生。