Changes between Initial Version and Version 1 of AdvancedReport11

Timestamp:

09/25/26 16:22:57 (5 days ago)

Author:

201178

Comment:

--

AdvancedReport11

@@ +1 @@
+= Анализа на кошничка: производи што најчесто се продаваат заедно =
+
+== Опис ==
+
+Извештајот ги прикажува паровите производи што најчесто се појавуваат во иста нарачка, со број на заеднички нарачки и коефициент на асоцијација. Користи само-спојување со потпрашалник за филтрирање на нарачки со повеќе од еден производ.
+
+== 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)
+}}}