python - 可能存在重复浮点数时对单个（均匀随机）浮点值的 SQL 查询

Question

给定一个带有索引的浮点字段的表（并允许重复），我假设我可以使用以下方法根据下一个最近的浮点值查询一行：

SELECT * FROM my_table WHERE my_float_column > 0.1234 ORDER BY my_float_column LIMIT 1

我想从该表中均匀地查询一个随机行 range [0, 1]，为此我建议生成一个均匀随机的浮点数并将其替换为上面的查询。

从概念上讲，这似乎应该有效（如果我没有想到什么，请纠正我），除了存在多个相同值的边缘情况。0.5就是这样一种情况，在数据库中有大量相同的0.5浮点值。

当我使用随机选择的浮点数查询数据库时，我将以0.5均匀的概率得到（由于与下一个最小浮点数之间的任何差距而有一些偏差0.5，但让我们忽略该细节）但是当我随机选择0.5值时，我想随机选择任何一个具有 的确切值的行0.5。

以随机统一的方式查询是否有更好的解决方案？或者是否有解决方案可以确保我LIMIT 1在这些条件下获得随机行？

标签： pythonsqlsqlite

Answer 1

首先，如果你想要平等，你需要平等：

SELECT *
FROM my_table
WHERE my_float_column >= 0.1234
LIMIT 1

0.5否则，如果你通过了，你永远无法得到0.5。另请注意，浮点表示可能难以比较。0.5 具有精确表示，但大多数其他值是近似值，这可能会导致相等比较出现问题。. . 但我会忽略这一点。

浮点值也不能很好地表示均匀分布。0 到 0.00001 之间的浮点数（如数据类型中所示）比 0.99999 到 1.00000 之间的浮点数要多得多。在这样的范围内，定点数字可能是更好的表示。

综上所述，您的问题真正是关于相等值的随机性。首先，您的方法有缺陷，因为您只是返回任何大于您想要的值。这更正确：

SELECT *
FROM my_table
WHERE my_float_column >= 0.1234
ORDER BY my_float_column DESC
LIMIT 1;

并且，要获得随机性，请rand()用作第二个密钥：

ORDER BY my_float_column, rand()

为了性能，我建议在my_float_column. 这rand()是一个性能杀手，因为它阻止使用索引，但您可以修改查询：

SELECT t.*
FROM my_table t
WHERE t.my_float_column >= 0.1234 AND
      t.my_float_column <= (SELECT COALESCE(MIN(t2.my_float_column), t.my_float_column))
                            FROM my_table t2
                            WHERE t2.my_float_column > 0.1234
                           )
ORDER BY my_float_column DESC, rand()
LIMIT 1;