PostgreSQL遍历行并使用自定义距离函数找到最接近的匹配项 [英] PostgreSQL to iterate through rows and find the closest match using custom distance-function

问题描述

我是PostgreSQL的新手，我的问题类似于此处的问题：链接

I am new to PostgreSQL and my question is similar to the one here: link

例如，我有下表：

| id |       vector         |
|  1 |  { 1.43, 3.54, 5.55} |
|  2 |  { 4.46, 5.56, 4.55} |
|  3 |  { 7.48, 2.54, 4.55} |
|  4 |  { 2.44, 2.34, 4.55} |

示例查询类似于

SELECT * FROM my_table WHERE vector CLOSEST('{1.23, 4.43, 4.63}') 

应该以排序的方式返回结果行，其中我使用自定义的Distance-function确定最接近的向量，例如

Should return resulting rows in a sorted fashion, where I determine the "closest" vector using a custom Distance-function e.g. calc_l2norm( double precision[], double precision[] ) which returns the Euclidean Distance.

推荐答案

通常来说，您可以解决这种类型的问题。calc_l2norm（double precision []，double precision []）返回欧几里得距离。通过使用用Java或Scala编写的存储函数来解决问题（有些人可能更喜欢PL / SQL，C或C ++）。

Generally speaking you can solve this type of problems by using a stored function, written in Java or Scala (some might prefer PL/SQL, C or C++).

PostgreSql支持（基于Java的）存储函数，因此，让SQL查询获取数据，并将其传递给存储的函数。存储的函数返回距离，因此您可以对其进行过滤/排序。

PostgreSql supports (Java based) stored functions, so let the SQL query fetch the data, and pass it to a stored function. The stored function returns the distance, so you can filter/sort etc. on it.

基于这样的表

create table point(vector float8[]);
insert into point values('{0.0, 0.0, 0.0}');
insert into point values('{0.5, 0.5, 0.5}');

具有如下Java函数：

with a Java function like this:

public class PlJava {
    public final static double distance2(double[] v1, double[] v2) {
        return Math.sqrt(Math.pow(v2[0] - v1[0], 2)
          + Math.pow(v2[1] - v1[1], 2) + Math.pow(v2[2] - v1[2], 2));
    }
}

以及SQL中的函数声明：

and the function declaration in SQL:

CREATE FUNCTION pljava.distance2(float8[], float8[])
  RETURNS float8
  AS 'PlJava.distance2'
  IMMUTABLE
  LANGUAGE java;

您的查询可能看起来像这样：

your query could look like this:

select
    point.*, 
    pljava.distance2(vector, '{1.0, 1.0, 1.0}') as dist
  from
    point 
  order by
    dist;    

这将导致

    vector     |       dist  
---------------+-------------------  
 {0.5,0.5,0.5} | 0.866025403784439  
 {0,0,0}       |  1.73205080756888  

更新

存储的函数也可以用C和C ++编写。 C ++需要更多的精力，因为PostgreSql的接口使用的是C调用约定。请参见使用C ++进行扩展

Stored functions can be written in C and C++ as well. C++ requires more effort, because the interface to PostgreSql is using the C calling convention. See Using C++ for Extensibility

这篇关于PostgreSQL遍历行并使用自定义距离函数找到最接近的匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！