向量数据库常用SQL语句

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    embedding VECTOR(1536)  -- 假设使用1536维向量
);

INSERT INTO products (name, description, embedding) 
VALUES ('智能手机', '高端旗舰手机', '[0.1, 0.2, ..., 0.5]');

SELECT id, name, 1 - (embedding <=> '[0.3, 0.1, ..., 0.4]') AS similarity
FROM products
ORDER BY embedding <=> '[0.3, 0.1, ..., 0.4]'
LIMIT 10;

CREATE INDEX ON products USING ivfflat (embedding vector_l2_ops) 
WITH (lists = 100);  -- 针对IVFFlat索引

# 假设已获取用户浏览记录的向量表示
user_vector = [0.2, 0.15, ..., 0.3]

# 使用pgvector查询相似商品
query = """
    SELECT id, name, 1 - (embedding <=> %s) AS similarity
    FROM products
    WHERE category = 'electronics'
    ORDER BY embedding <=> %s
    LIMIT 5
"""
cursor.execute(query, (user_vector, user_vector))

from sentence_transformers import SentenceTransformer
model = SentenceTransformer('all-MiniLM-L6-v2')

# 将搜索查询转换为向量
query_text = "续航持久的蓝牙耳机"
query_vector = model.encode(query_text).tolist()

# 向量数据库查询
similar_products = collection.query(
    query_embeddings=[query_vector],
    n_results=3,
    include=["metadata", "distances"]
)

import faiss
import numpy as np

# 创建索引
dimension = 768
index = faiss.IndexFlatIP(dimension)

# 添加向量数据
vectors = np.random.rand(1000, dimension).astype('float32')
index.add(vectors)

# 相似度搜索
query_vector = np.random.rand(1, dimension).astype('float32')
D, I = index.search(query_vector, k=5)  # 返回前5个最相似结果

from pymilvus import connections, Collection

# 连接数据库
connections.connect("default", host="localhost", port="19530")

# 获取集合
collection = Collection("products")

# 向量搜索
search_params = {
    "metric_type": "L2",
    "params": {"nprobe": 10}
}
results = collection.search(
    data=[query_vector],
    anns_field="embedding",
    param=search_params,
    limit=5,
    output_fields=["name"]
)

-- 调整nlist参数平衡查询精度和速度
CREATE INDEX ON products USING ivfflat (embedding vector_l2_ops) WITH (lists = 500);

SELECT id, name FROM products
WHERE price < 1000 
AND embedding <=> '[0.1, ..., 0.2]' < 0.3
ORDER BY embedding <=> '[0.1, ..., 0.2]'
LIMIT 10;

# 使用Faiss的PQ压缩
quantizer = faiss.IndexFlatL2(dimension)
index = faiss.IndexIVFPQ(quantizer, dimension, 100, 16, 8)  # 100个簇，16个子向量，8bits