Hibrit kişiselleştirilmiş ürün önerisi

Tek SELECT icinde VECTOR_SEARCH ve GRAPH MATCH birlikte: anlamsal yakinlik ile birlikte alinma sayisi ayni skorda birlesiyor.

DECLARE @centroid VECTOR(1024) = CAST(@centroid_json AS VECTOR(1024));

-- ----------------------------------------------------------------------------
-- 2-5) VECTOR_SEARCH + filter + graph social score + hibrit skor
-- ----------------------------------------------------------------------------
WITH hits AS (
    SELECT * FROM VECTOR_SEARCH(
        TABLE      = vector.product_embedding,
        COLUMN     = embedding_bge_1024,
        SIMILAR_TO = @centroid,
        METRIC     = 'cosine',
        TOP_N      = 30)
),
unowned AS (
    SELECT h.product_id, h.distance
    FROM   hits h
    WHERE  NOT EXISTS (
        SELECT 1
        FROM   shop.[order]    o
        JOIN   shop.order_line ol ON ol.order_id = o.order_id
        WHERE  o.customer_id = @customer_id AND ol.product_id = h.product_id
    )
),
social AS (
    SELECT
        u.product_id, u.distance,
        (SELECT COUNT(DISTINCT pur.order_id)
         FROM   graph.product_node pn,
                graph.purchased    pur,
                graph.customer_node c
         WHERE  MATCH(c-(pur)->pn)
           AND  pn.product_id = u.product_id) AS social_count
    FROM   unowned u
)
SELECT TOP (@top_k)
    s.product_id,
    p.name,
    cat.name AS category,
    p.price,
    CAST(s.distance AS DECIMAL(6, 4))                                          AS vector_distance,
    s.social_count,
    CAST(s.distance + 1.0 / (1.0 + s.social_count) AS DECIMAL(6, 4))           AS hybrid_score
FROM   social s
JOIN   shop.product          p   ON p.product_id   = s.product_id
JOIN   shop.product_category cat ON cat.category_id = p.category_id
ORDER BY hybrid_score ASC;
GO

PRINT N'> Senaryo 5: vector + graph hibrit öneriler';
GO

Tam betik: sql/24-personalized.sql