Анализа на кошничка: производи што најчесто се продаваат заедно

Опис

Извештајот ги прикажува паровите производи што најчесто се појавуваат во иста нарачка, со број на заеднички нарачки и коефициент на асоцијација. Користи само-спојување со потпрашалник за филтрирање на нарачки со повеќе од еден производ.

SQL решение

CREATE OR REPLACE FUNCTION project.get_product_pairs()
RETURNS TABLE (
    product_a TEXT,
    product_b TEXT,
    category_a TEXT,
    category_b TEXT,
    co_occurrences BIGINT,
    support_a NUMERIC,
    confidence NUMERIC
)
LANGUAGE sql
AS $$
    WITH multi_item_orders AS (
        SELECT order_id
        FROM project.order_item
        GROUP BY order_id
        HAVING COUNT(DISTINCT product_id) > 1
    ),
    pairs AS (
        SELECT 
            oi1.product_id AS p1,
            oi2.product_id AS p2,
            COUNT(DISTINCT oi1.order_id) AS co_occ
        FROM project.order_item oi1
        JOIN project.order_item oi2 
            ON oi1.order_id = oi2.order_id 
           AND oi1.product_id < oi2.product_id
        WHERE oi1.order_id IN (SELECT order_id FROM multi_item_orders)
        GROUP BY oi1.product_id, oi2.product_id
    ),
    product_totals AS (
        SELECT product_id, COUNT(DISTINCT order_id) AS total_orders
        FROM project.order_item
        GROUP BY product_id
    )
    SELECT 
        pa.name::TEXT,
        pb.name::TEXT,
        ca.name::TEXT,
        cb.name::TEXT,
        pr.co_occ,
        ROUND((100.0 * pr.co_occ / NULLIF(pt_a.total_orders, 0))::numeric, 2),
        ROUND((100.0 * pr.co_occ / NULLIF(pt_a.total_orders, 0))::numeric, 2)
    FROM pairs pr
    JOIN project.product pa ON pa.product_id = pr.p1
    JOIN project.product pb ON pb.product_id = pr.p2
    JOIN project.category ca ON ca.category_id = pa.category_id
    JOIN project.category cb ON cb.category_id = pb.category_id
    JOIN product_totals pt_a ON pt_a.product_id = pr.p1
    WHERE pr.co_occ >= 1
    ORDER BY 5 DESC, 7 DESC
    LIMIT 50;
$$;

Релациона алгебра

OI(order_id, product_id, quantity)
PR(product_id, name, category_id)
C(category_id, name)

G1 ← γ order_id; COUNT(DISTINCT product_id) → cnt (OI)
F1 ← σ cnt > 1 (G1)

J1 ← OI ⨝ OI.order_id = OI2.order_id ∧ OI.product_id < OI2.product_id OI2
F2 ← σ order_id ∈ π order_id (F1) (J1)
G2 ← γ p1, p2; COUNT(DISTINCT order_id) → co_occ (F2)

G3 ← γ product_id; COUNT(DISTINCT order_id) → total_orders (OI)

J2 ← G2 ⨝ G2.p1 = G3.product_id G3
J3 ← J2 ⨝ PR ⨝ C ⨝ PR ⨝ C
F3 ← σ co_occ ≥ 1 (J3)

R ← π product_a, product_b, category_a, category_b, co_occurrences,
      support_a, confidence (F3)

R_final ← τ co_occurrences DESC, confidence DESC (R)