У меня есть база данных SQLite, и у меня есть в ней определенный столбец типа «двойной». Я хочу получить строку, которая имеет в этом столбце значение, наиболее близкое к указанному.
Например, в моей таблице у меня есть:
id: 1; value: 47
id: 2; value: 56
id: 3; value: 51
И я хочу получить строку, значение которой ближе всего к 50. Поэтому я хочу получить id: 3 (значение = 51).
Как я могу достичь этой цели?
Спасибо.
Это должно работать:
SELECT * FROM table
ORDER BY ABS(? - value)
LIMIT 1
Где ? представляет собой значение, с которым вы хотите сравнить.
log N времени?
- person Yakov Galka; 02.07.2014
ABS(? - value).
- person Alnitak; 02.07.2014
Используя упорядочивание, 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
or
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 значения, но если вам придется загружать больше данных, он быстро станет медленнее.
