SQLite - получение ближайшего значения

У меня есть база данных SQLite, и у меня есть в ней определенный столбец типа "double". Я хочу получить строку, которая имеет в этом столбце значение, ближайшее к указанному.

Например, в моей таблице есть:

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

И я хочу получить строку, которая имеет значение, самое близкое к 50. Поэтому я хочу получить id: 3 (value = 51).

Как я могу достичь этой цели?

Спасибо.

Ответы

Ответ 1

Это должно работать:

SELECT * FROM table
ORDER BY ABS(? - value)
LIMIT 1

Где ? представляет значение, которое вы хотите сравнить.

Ответ 2

Используя заказ, 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

Поскольку вы заинтересованы в значениях как произвольно больших, так и меньших, чем цель, вы не можете избежать выполнения двух запросов индекса. Если вы знаете, что цель находится в небольшом диапазоне, вы можете использовать "между", чтобы только один раз нажать индекс:

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

Это будет примерно в 2 раза быстрее, чем запрос объединения выше, когда он оценивает только 1-2 значения, но если вы начнете загружать больше данных, он быстро станет медленнее.