当可能存在重复的浮点值时，对单个（统一随机）浮点值的SQL查询

Question

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

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

我想从这个表中统一查询范围为[0, 1]的随机行，为此我建议生成一个统一的随机浮点，并将其替换到上面的查询中

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

当我使用随机选择的浮点数查询数据库时，我将以一致的概率得到0.5（部分由0.5和下一个最小浮点数之间的任何间隙偏置），但是，让我们忽略这个细节）但是，当我随机选择0.5值时，我想随机选择任何具有0.5精确值的行

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

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;