postgres 有 CLOSEST 运算符吗？

Question

我正在寻找这样的东西，给定一个这样的表：

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

查询 SELECT * FROM my_table WHEREnumberCLOSEST(1) 将返回第 3 行。我只关心数字。现在我有一个程序，它只循环每一行并进行比较，但我认为信息应该可以从 b 树索引中获得，所以这可能作为内置程序，但我找不到任何文档表明确实如此。

Answer 1

我可能在语法上有点偏离，但是这个参数化查询（所有 ? 都取原始问题的“1”）应该运行得很快，基本上是 2 个 B 树查找 [假设数字已索引]。

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）的查询计划如下所示：

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)

Answer 2

您可以尝试这样的操作：

select *
from my_table
where abs(1 - number) = (select min(abs(1 - number)) from t)

这与手动循环表没有太大不同，但至少它可以让数据库在“数据库空间”内进行循环，而不必在函数和数据库内部之间来回跳转。此外，将所有内容推送到单个查询中可以让查询引擎知道您要做什么，然后它可以尝试以合理的方式执行此操作。

Answer 3

第二个答案是正确的，但我在“UNION ALL”上遇到错误：

DBD::Pg::st执行失败：错误：“UNION”处或附近的语法错误

我用这段代码修复了它：

SELECT * FROM
  (
    (SELECT * FROM table WHERE num >= ? ORDER BY num LIMIT 1)
        UNION ALL
    (SELECT * FROM table WHERE num < ?  ORDER BY num DESC LIMIT 1)
  ) as foo
ORDER BY abs(?-num) LIMIT 1;

技巧是从内表中删除 AS 并仅在 UNION 上使用它。

Answer 4

如果您希望找到组内最接近的值，此代码会很有帮助。在这里，我根据列 val 与目标值 0.5column_you_wish_to_group_by 拆分表 tb >。

SELECT *
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY t.column_you_wish_to_group_by ORDER BY abs(t.val - 0.5) ASC) AS r,
    t.*
  FROM
    tb t) x 
WHERE x.r = 1;