Comment obtenir la distance cosinus entre deux vecteurs dans postgres ?

Question

Je me demande s'il existe un moyen d'obtenir la distance en cosinus de deux vecteurs dans postgres. Pour stocker les vecteurs, j'utilise le type de données CUBE.

Voici la définition de ma table :

test=# \d vectors                                                                                                                                
                            Table "public.vectors"
 Column |  Type   | Collation | Nullable |               Default               
--------+---------+-----------+----------+-------------------------------------
 id     | integer |           | not null | nextval('vectors_id_seq'::regclass)
 vector | cube    |           |          | 

De plus, des exemples de données sont donnés ci-dessous :

test=# select * from vectors order by id desc limit 2;
   id    |                  vector                  
---------+------------------------------------------
 2000000 | (109, 568, 787, 938, 948, 126, 271, 499)
 1999999 | (139, 365, 222, 653, 313, 103, 215, 796)

En fait, je peux écrire ma propre fonction PLPGSql pour cela, mais je voulais éviter cela car cela pourrait ne pas être efficace.

Answer 1

A propos de votre table

Tout d'abord, je pense que vous devriez changer votre type de données en tableau simple.

CREATE TABLE public.vector ( 
  id serial NOT NULL,
  vctor double precision [3] --for three dimensional vectors; of course you can change the dimension or leave it unbounded if you need it.
 );

INSERT INTO public.vector (vctor) VALUES (ARRAY[2,3,4]);
INSERT INTO public.vector (vctor) VALUES (ARRAY[3,4,5]);

Alors

SELECT * FROM public.vector;

Les données suivantes seront obtenues

   id |   vctor
------|---------
    1 | {2,3,4}
    2 | {3,4,5}

Ce n'est peut-être pas la réponse que vous attendiez, mais considérez ceci

Comme vous le savez peut-être déjà, calculer le cosinus entre les vecteurs implique de calculer les magnitudes. Je ne pense pas que le problème soit l'algorithme mais l'implémentation ; il nécessite de calculer des carrés et des racines carrées, ce qui est coûteux pour un SGBDR.

En ce qui concerne l'efficacité, le processus du serveur ne prend pas en charge l'appel des fonctions mathématiques. Dans PostgreSQL, les fonctions mathématiques ( regardez ici ) s'exécutent à partir de la bibliothèque C et sont donc assez efficaces. Cependant, au final, l'hôte doit affecter certaines ressources pour effectuer ces calculs.

Je réfléchirais en effet soigneusement avant de mettre en œuvre ces opérations plutôt coûteuses à l'intérieur du serveur. Mais il n'y a pas de bonne réponse ; cela dépend de l'utilisation que vous faites de la base de données. Par exemple, s'il s'agit d'une base de données de production avec des milliers d'utilisateurs simultanés, je déplacerais ce type de calcul ailleurs (une couche intermédiaire ou une application utilisateur). Mais s'il y a peu d'utilisateurs et que votre base de données est destinée à une petite opération de recherche, il est possible de l'implémenter en tant que procédure stockée ou processus s'exécutant dans votre serveur, mais gardez à l'esprit que cela affectera l'évolutivité ou la portabilité. Bien sûr, il y a d'autres considérations à prendre en compte, comme le nombre de lignes à traiter, ou si vous avez l'intention ou non de déclencher des triggers, etc.

Envisager d'autres solutions

Créer une application client

Vous pouvez réaliser un programme rapide et correct en VB ou dans le langage de votre choix. Laissez l'application client effectuer les calculs lourds et utilisez la base de données pour ce qu'elle fait de mieux, à savoir stocker et récupérer des données.

Stocker les données différemment

Pour cet exemple particulier, vous pourriez stocker les vecteurs unitaires plus la magnitude. De cette façon, trouver le cosinus entre deux vecteurs quelconques se réduit simplement au produit scalaire des vecteurs unitaires (uniquement multiplication et division, sans carré ni racine carrée).

CREATE TABLE public.vector ( 
     id serial NOT NULL,
     uvctor double precision [3], --for three dimensional vectors; of course you can change the dimension or make it decimal if you need it
     magnitude double precision
 ); 

INSERT INTO public.vector (vctor) VALUES (ARRAY[0.3714, 0.5571, 0.7428], 5.385); -- {Ux, Uy, Uz}, ||V|| where V = [2, 3, 4];
INSERT INTO public.vector (vctor) VALUES (ARRAY[0.4243, 0.5657, 0.7071], 7.071); -- {Ux, Uy, Uz}, ||V|| where V = [3, 4, 5];

SELECT a.vctor as a, b.vctor as b, 1-(a.uvctor[1] * b.uvctor[1] + a.uvctor[2] * b.uvctor[2] + a.uvctor[3] * b.uvctor[3]) as cosine_distance FROM public.vector a
JOIN public.vector b ON a.id != b.id;

Résultant en

                          a  |                           b  | cosine_distance
-----------------------------|------------------------------|------------------
{0.3714,0.5571,0.7428,5.385} | {0.4243,0.5657,0.7071,7.071} |      0.00202963
{0.4243,0.5657,0.7071,7.071} | {0.3714,0.5571,0.7428,5.385} |      0.00202963

Même si vous devez calculer la magnitude du vecteur à l'intérieur du serveur, vous le ferez une fois par vecteur et non pas chaque fois que vous aurez besoin d'obtenir la distance entre deux d'entre eux. Cela devient plus important au fur et à mesure que le nombre de lignes augmente. Pour 1000 vecteurs par exemple, vous devrez calculer la magnitude 999000 fois si vous voulez obtenir la différence en cosinus entre deux vecteurs quelconques en utilisant les composants originaux du vecteur.

Toute combinaison des éléments ci-dessus

Conclusion

Lorsque nous recherchons l'efficacité, la plupart du temps, il n'y a pas de réponse canonique. Il s'agit plutôt de compromis que nous devons considérer et évaluer. Cela dépend toujours de l'objectif final que nous devons atteindre. Les bases de données sont excellentes pour stocker et récupérer des données ; elles peuvent certainement faire d'autres choses, mais cela a un coût supplémentaire. Si nous pouvons vivre avec ce surcoût, c'est parfait ; sinon, nous devons envisager d'autres solutions.

Answer 2

Vous pouvez prendre référence à mon code.

--for calculation of norm vector --
CREATE or REPLACE FUNCTION public.vector_norm(IN vector double precision[])
    RETURNS double precision AS 
$BODY$

BEGIN

    RETURN(SELECT SQRT(SUM(pow)) FROM (SELECT POWER(e,2) as pow from unnest(vector) as e) as norm);
END;
$BODY$ LANGUAGE 'plpgsql'; 
ALTER FUNCTION public.vector_norm(double precision[]) OWNER TO postgres;

COMMENT ON FUNCTION public.vector_norm(double precision[]) IS 'This function is used to find a norm of vectors.';

--call function--
select public.vector_norm('{ 0.039968978613615,0.357211461290717,0.753132887650281,0.760665621142834,0.20826127845794}')

--for caculation of dot_product--
CREATE OR REPLACE FUNCTION public.dot_product(IN vector1 double precision[], IN vector2 double precision[])
    RETURNS double precision    
AS $BODY$
BEGIN
    RETURN(SELECT sum(mul) FROM (SELECT v1e*v2e as mul FROM unnest(vector1, vector2) AS t(v1e,v2e)) AS denominator);
END;
$BODY$ LANGUAGE 'plpgsql';

ALTER FUNCTION public.dot_product(double precision[], double precision[]) OWNER TO postgres;

COMMENT ON FUNCTION public.dot_product(double precision[], double precision[])
    IS 'This function is used to find a cosine similarity between two multi-dimensional vectors.';

--call fuction--
SELECT public.dot_product(ARRAY[ 0.039968978613615,0.357211461290717,0.753132887650281,0.760665621142834,0.20826127845794],ARRAY[ 0.039968978613615,0.357211461290717,0.753132887650281,0.760665621142834,0.20826127845794])

--for calculatuion of cosine similarity--
CREATE OR REPLACE FUNCTION public.cosine_similarity(IN vector1 double precision[], IN vector2 double precision[])
    RETURNS double precision
    LANGUAGE 'plpgsql'

AS $BODY$
BEGIN
    RETURN(select ((select public.dot_product(ARRAY[ 0.63434,0.23487,0.324323], ARRAY[ 0.63434,0.23487,0.324323]) as dot_pod)/((select public.vector_norm(ARRAY[ 0.63434,0.23487,0.324323]) as norm1) * (select public.vector_norm(ARRAY[ 0.63434,0.23487,0.324323]) as norm2))) AS similarity_value) 
END;
$BODY$;

ALTER FUNCTION public.cosine_similarity(double precision[], double precision[])
    OWNER TO postgres;

COMMENT ON FUNCTION public.cosine_similarity(double precision[], double precision[])
    IS 'this function is used to find a cosine similarity between two vector';