AnalyticDB:Perform vector analysis

Prerequisites

Vector data is imported to a table. For more information, see Import vector data.

Perform vector analysis

In this example, vector analysis is performed to obtain the squared Euclidean distance and dot product distance (or cosine similarity).

Obtain the squared Euclidean distance

Perform vector analysis to obtain the squared Euclidean distance.

SELECT id, l2_squared_distance(feature, array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[]) AS distance 
  FROM vector_test.car_info 
  ORDER BY feature <-> array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[] 
  LIMIT 10;

Sample result:

  id  |      distance
------+--------------------
    2 |                  0
 1331 | 0.0677967891097069
 1543 |  0.079616591334343
 5606 | 0.0892329216003418
 6423 | 0.0894578248262405
 1667 | 0.0903968289494514
 8215 | 0.0936210229992867
 7801 | 0.0952572822570801
 2581 | 0.0965127795934677
 2645 | 0.0987173467874527
(10 rows)

Obtain the dot product distance (or cosine similarity)

Perform vector analysis to obtain the dot product distance (or cosine similarity when normalization is used).

SELECT id, dp_distance(feature, array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[]) AS similarity 
  FROM vector_test.car_info 
  ORDER BY feature <-> array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[] 
  LIMIT 10;

Sample result:

  id  |    similarity
------+-------------------
    2 |                 1
 1331 | 0.966101586818695
 1543 | 0.960191607475281
 5606 | 0.955383539199829
 6423 | 0.955271065235138
 1667 | 0.954801559448242
 8215 | 0.953189492225647
 7801 |  0.95237135887146
 2581 | 0.951743602752686
 2645 | 0.950641334056854
(10 rows)

Integrated query

Execute an SQL statement to query both structured and unstructured data. Sample statement:

SELECT id, dp_distance(feature, array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[]) AS similarity 
  FROM vector_test.car_info 
  WHERE market_time >= '2020-10-30 00:00:00' 
  AND market_time < '2021-01-01 00:00:00' 
  AND color in ('red', 'white', 'blue') 
  AND price < 100 
  ORDER BY feature <-> array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[] 
  LIMIT 10;

Sample result:

  id  |    similarity
------+-------------------
 7645 | 0.922723233699799
 8956 | 0.920517802238464
 8219 |  0.91210675239563
 8503 | 0.895939946174622
 5113 | 0.895431876182556
 7680 | 0.893448948860168
 8433 | 0.893425941467285
 3604 |  0.89293098449707
 3945 | 0.891274154186249
 7153 | 0.891128540039062
(10 rows)

References

Vector analysis