Changes between Initial Version and Version 1 of AdvancedReport13

Timestamp:

09/25/26 16:23:39 (5 days ago)

Author:

201178

Comment:

--

AdvancedReport13

@@ +1 @@
+= Откривање на аномалии: нарачки со невообичаено високи вредности =
+
+== Опис ==
+
+Извештајот ги прикажува нарачките чија вредност е повеќе од 2 стандардни отстапувања над просекот за тој келнер. Користи статистички функции (STDDEV) и корелационен потпрашалник за споредба со глобалниот просек.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION project.get_anomalous_orders()
+RETURNS TABLE (
+    order_id INT,
+    waiter_name TEXT,
+    order_total NUMERIC,
+    waiter_avg NUMERIC,
+    waiter_stddev NUMERIC,
+    z_score NUMERIC,
+    deviation_level TEXT
+)
+LANGUAGE sql
+AS $$
+    WITH order_totals AS (
+        SELECT 
+            o.order_id AS oid,
+            o.user_id AS uid,
+            SUM(oi.quantity * oi.unit_price)::numeric AS total
+        FROM project.orders o
+        JOIN project.order_item oi ON oi.order_id = o.order_id
+        WHERE o.status = 'ПЛАТЕНА'
+        GROUP BY o.order_id, o.user_id
+    ),
+    waiter_stats AS (
+        SELECT 
+            ot.uid,
+            AVG(ot.total)::numeric AS avg_total,
+            STDDEV(ot.total)::numeric AS stddev_total,
+            COUNT(*) AS num_orders
+        FROM order_totals ot
+        GROUP BY ot.uid
+    )
+    SELECT 
+        ot.oid,
+        (u.first_name || ' ' || u.last_name)::TEXT,
+        ot.total,
+        ROUND(ws.avg_total, 2),
+        ROUND(ws.stddev_total, 2),
+        ROUND(((ot.total - ws.avg_total) / NULLIF(ws.stddev_total, 0))::numeric, 2),
+        CASE 
+            WHEN (ot.total - ws.avg_total) / NULLIF(ws.stddev_total, 0) > 3 
+                 THEN 'ЕКСТРЕМНО ВИСОКА'
+            WHEN (ot.total - ws.avg_total) / NULLIF(ws.stddev_total, 0) > 2 
+                 THEN 'ВИСОКА'
+            ELSE 'НОРМАЛНА'
+        END::TEXT
+    FROM order_totals ot
+    JOIN waiter_stats ws ON ws.uid = ot.uid
+    JOIN project.app_user u ON u.user_id = ot.uid
+    WHERE ws.num_orders >= 1
+      AND (ot.total - ws.avg_total) / NULLIF(ws.stddev_total, 0) > 1
+    ORDER BY 6 DESC;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+O(order_id, user_id, status)
+OI(order_id, quantity, unit_price)
+U(user_id, first_name, last_name)
+
+J1 ← O ⨝ O.order_id = OI.order_id OI
+F1 ← σ status='ПЛАТЕНА' (J1)
+G1 ← γ order_id, user_id; SUM(quantity * unit_price) → order_total (F1)
+
+G2 ← γ user_id; AVG(order_total) → avg_total,
+                  STDDEV(order_total) → stddev_total,
+                  COUNT(*) → num_orders (G1)
+
+J2 ← G1 ⨝ G1.user_id = G2.user_id G2
+J3 ← J2 ⨝ U
+F2 ← σ num_orders ≥ 1 ∧ (order_total - avg_total)/stddev_total > 1 (J3)
+
+R ← π order_id, waiter_name, order_total, waiter_avg,
+      waiter_stddev, z_score, deviation_level (F2)
+
+R_final ← τ z_score DESC (R)
+}}}