Как посчитать cosine similarity в SQL

Зачем cosine

Cosine similarity — угол между двумя векторами. Не зависит от их длины, только от направления. Поэтому подходит когда:

• Два юзера купили разное количество товаров, но «похожие по структуре».
• Документы разной длины — но темы похожие.
• Item-item рекомендации в коллабфильтрации.

В отличие от Jaccard, cosine учитывает частоту: «10 раз купил» весит больше, чем «1 раз».

Формула

cos(A, B) = (A · B) / (||A|| * ||B||)
        = Σ a_i*b_i / sqrt(Σ a_i²) / sqrt(Σ b_i²)

Для неотрицательных векторов диапазон [0, 1]. Для значений со знаком [-1, 1].

Cosine двух пользователей

Два юзера, каждый имеет вектор покупок (product_id → quantity). Cosine между ними:

WITH user_a AS (
    SELECT product_id, SUM(quantity) AS qty
    FROM purchases WHERE user_id = 42
    GROUP BY product_id
),
user_b AS (
    SELECT product_id, SUM(quantity) AS qty
    FROM purchases WHERE user_id = 99
    GROUP BY product_id
),
dot AS (
    SELECT SUM(a.qty * b.qty) AS dot_product
    FROM user_a a
    JOIN user_b b USING (product_id)
),
norm_a AS (
    SELECT SQRT(SUM(qty * qty)) AS n FROM user_a
),
norm_b AS (
    SELECT SQRT(SUM(qty * qty)) AS n FROM user_b
)
SELECT
    COALESCE(dot.dot_product, 0)::NUMERIC
    / NULLIF(na.n * nb.n, 0) AS cosine_similarity
FROM dot, norm_a na, norm_b nb;

Item-item recommendations

Для каждой пары товаров — насколько похожи по покупательскому профилю:

WITH item_user AS (
    SELECT product_id, user_id, SUM(quantity) AS qty
    FROM purchases
    WHERE purchase_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY product_id, user_id
),
norms AS (
    SELECT product_id, SQRT(SUM(qty * qty)) AS norm
    FROM item_user
    GROUP BY product_id
),
dots AS (
    SELECT
        a.product_id AS item_a,
        b.product_id AS item_b,
        SUM(a.qty * b.qty) AS dot_product
    FROM item_user a
    JOIN item_user b USING (user_id)
    WHERE a.product_id < b.product_id
    GROUP BY a.product_id, b.product_id
)
SELECT
    d.item_a,
    d.item_b,
    d.dot_product::NUMERIC / NULLIF(na.norm * nb.norm, 0) AS cosine
FROM dots d
JOIN norms na ON na.product_id = d.item_a
JOIN norms nb ON nb.product_id = d.item_b
WHERE d.dot_product > 0
ORDER BY cosine DESC
LIMIT 100;

a.product_id < b.product_id исключает self-pairs и дубли (A-B и B-A).

User-item similarity

В коллабфильтрации: для user 42 найти товары, похожие по покупателям на те, что он уже купил.

WITH user_items AS (
    SELECT DISTINCT product_id FROM purchases WHERE user_id = 42
),
item_norms AS (
    SELECT product_id, SQRT(SUM(quantity * quantity)) AS norm
    FROM purchases
    GROUP BY product_id
),
similarity_scores AS (
    SELECT
        b.product_id AS candidate,
        SUM(a.quantity * b.quantity)
        / NULLIF(MAX(na.norm) * MAX(nb.norm), 0) AS cosine
    FROM purchases a
    JOIN purchases b USING (user_id)
    JOIN item_norms na ON na.product_id = a.product_id
    JOIN item_norms nb ON nb.product_id = b.product_id
    WHERE EXISTS (SELECT 1 FROM user_items u WHERE u.product_id = a.product_id)
      AND NOT EXISTS (SELECT 1 FROM user_items u WHERE u.product_id = b.product_id)
    GROUP BY b.product_id
)
SELECT candidate, cosine
FROM similarity_scores
WHERE cosine > 0.1
ORDER BY cosine DESC
LIMIT 20;

Частые ошибки

Ошибка 1. Норма по неполным векторам. В SQL JOIN ... USING (product_id) оставляет только общие товары. Норма должна считаться по ВСЕМ товарам юзера, иначе значения завышены. Считайте norm_a отдельным CTE по всему user_a.

Ошибка 2. Деление на ноль для новых юзеров. Юзер без покупок → норма 0 → cosine = NULL. Фильтруйте WHERE norm > 0 или обрабатывайте NULL отдельно.

Ошибка 3. Не нормировать частоту. Если у одного юзера буквально 10000 покупок одного товара, dot product перекосится. Иногда лучше TF-IDF или log(1+qty).

Ошибка 4. Cosine для отрицательных значений. Для рейтингов 1–5 cosine не учитывает «не понравилось». Сначала центрируйте rating вокруг среднего юзера (adjusted cosine).

Ошибка 5. Self-similarity без фильтра. A с самим собой даст cosine = 1, забьёт топ. a.product_id < b.product_id или <>.

Связанные темы

• Как посчитать Jaccard similarity в SQL
• Как посчитать lookalike audience в SQL
• Как посчитать NDCG в SQL
• Cosine vs Euclidean на собесе DS

FAQ

Cosine vs Euclidean?

Cosine — направление. Euclidean — абсолютная разница. Для разреженных векторов (sparse) cosine стабильнее.

Cosine vs Jaccard?

Jaccard — наличие. Cosine — частота. Для бинарных данных совпадают по интуиции, но cosine более «гладкий».

Cosine для текстов?

TF-IDF векторизация → cosine. Стандарт в search и near-duplicate detection.

Производительность на миллионах товаров?

В чистом SQL — O(N²). Для prod используют LSH / FAISS / Postgres pgvector с HNSW индексом.

Range отрицательный?

Для tf-idf или count-векторов — [0, 1]. Для рейтингов со знаком (после центрирования) — [-1, 1].