SQLite - 获得最接近的值

Question

我有 SQLite 数据库，其中有某些类型为“double”的列。我想获得此列中的值最接近指定值的行。

例如，在我的表中我有:

id: 1; value: 47
id: 2; value: 56
id: 3; value: 51

我想得到一个值最接近 50 的行。所以我想接收 id:3(值 = 51)。

我怎样才能实现这个目标？

谢谢。

Answer 1

使用 order-by，SQLite 将扫描整个表并将所有值加载到临时 b 树中以对它们进行排序，从而使任何索引都无用。这将非常慢并且在大表上使用大量内存:

explain query plan select * from 'table' order by abs(10 - value) limit 1;
0|0|0|SCAN TABLE table
0|0|0|USE TEMP B-TREE FOR ORDER BY

您可以像这样使用索引获取下一个较低或较高的值:

select min(value) from 'table' where x >= N;
select max(value) from 'table' where x <= N;

并且您可以使用 union 从单个查询中获取两者:

explain query plan 
        select min(value) from 'table' where value >= 10
  union select max(value) from 'table' where value <= 10;
1|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value>?)
2|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value<?)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)

即使在大表上，这也会非常快。您可以简单地加载这两个值并在您的代码中评估它们，或者使用更多的 sql 以各种方式选择一个值:

explain query plan select v from
   (      select min(value) as v from 'table' where value >= 10
    union select max(value) as v from 'table' where value <= 10)
  order by abs(10-v) limit 1;
2|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value>?)
3|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value<?)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY

或

explain query plan select 10+v from
   (      select min(value)-10 as v from 'table' where value >= 10
    union select max(value)-10 as v from 'table' where value <= 10)
  group by v having max(abs(v)) limit 1;
2|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value>?)
3|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value<?)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR GROUP BY

由于您对任意大于和小于目标的值感兴趣，因此您无法避免进行两次索引搜索。但是，如果您知道目标在一个小范围内，则可以使用“between”来只命中索引一次:

explain query plan select * from 'table' where value between 9 and 11 order by abs(10-value) limit 1;
0|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value>? AND value<?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

当它只评估 1-2 个值时，这将比上面的联合查询快 2 倍左右，但如果您开始不得不加载更多数据，它会很快变慢。