有postgres CLOSEST运算子吗？

本文介绍了有postgres CLOSEST运算子吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

鉴于表格，我正在寻找一些东西

I'm looking for something that, given a table like:

| id | number | | 1 | .7 | | 2 | 1.25 | | 3 | 1.01 | | 4 | 3.0 |

查询 SELECT * FROM my_table WHERE number CLOSEST（1）将返回第3行。我只关心数字。现在，我有一个过程可以遍历每一行并进行比较，但是我认为该信息应该可以从b树索引中获取，因此这可能是内置的，但我找不到

the query SELECT * FROM my_table WHEREnumberCLOSEST(1) would return row 3. I only care about numbers. Right now I've got a procedure that just loops over every row and does a comparison, but I figure the information should be available from a b-tree index, so this might be possible as a builtin, but I can't find any documentation suggesting that it does.

推荐答案

我在语法上可能有些偏离，但是此参数化查询（所有？原始问题的 1）应该运行得很快，基本上是2次B-Tree查找（假设索引编号）。

I may be a little off on the syntax, but this parameterized query (all the ? take the '1' of the original question) should run fast, basically 2 B-Tree lookups [assuming number is indexed].

SELECT * FROM ( (SELECT id, number FROM t WHERE number >= ? ORDER BY number LIMIT 1) AS above UNION ALL (SELECT id, number FROM t WHERE number < ? ORDER BY number DESC LIMIT 1) as below ) ORDER BY abs(?-number) LIMIT 1;

具有〜5e5行的表（索引在 number ）看起来像这样：

The query plan for this with a table of ~5e5 rows (with an index on number) looks like this:

psql => explain select * from ( (SELECT id, number FROM t WHERE number >= 1 order by number limit 1) union all (select id, number from t where number < 1 order by number desc limit 1) ) as make_postgresql_happy order by abs (1 - number) limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Limit (cost=0.24..0.24 rows=1 width=12) -> Sort (cost=0.24..0.24 rows=2 width=12) Sort Key: (abs((1::double precision - public.t.number))) -> Result (cost=0.00..0.23 rows=2 width=12) -> Append (cost=0.00..0.22 rows=2 width=12) -> Limit (cost=0.00..0.06 rows=1 width=12) -> Index Scan using idx_t on t (cost=0.00..15046.74 rows=255683 width=12) Index Cond: (number >= 1::double precision) -> Limit (cost=0.00..0.14 rows=1 width=12) -> Index Scan Backward using idx_t on t (cost=0.00..9053.67 rows=66136 width=12) Index Cond: (number < 1::double precision) (11 rows)