Changes between Initial Version and Version 1 of AdvancedReport10

Timestamp:

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

Author:

201178

Comment:

--

AdvancedReport10

@@ +1 @@
+= Келнери со надпросечна ефикасност и нивниот најпрофитабилен производ =
+
+== Опис ==
+
+Извештајот ги прикажува келнерите чие просечно време до плаќање е помало од просекот на сите келнери, заедно со производот што им донел најголем приход. Користи повеќе нивоа на вгнездување: (1) агрегација по келнер, (2) споредба со глобален просек, (3) корелационен потпрашалник за најпрофитабилен производ.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION project.get_efficient_waiters_top_product()
+RETURNS TABLE (
+    user_id INT,
+    waiter_name TEXT,
+    avg_minutes NUMERIC,
+    global_avg_minutes NUMERIC,
+    total_orders BIGINT,
+    top_product TEXT,
+    top_product_revenue NUMERIC
+)
+LANGUAGE sql
+AS $$
+    WITH waiter_stats AS (
+        SELECT 
+            u.user_id AS uid,
+            (u.first_name || ' ' || u.last_name)::TEXT AS wname,
+            AVG(EXTRACT(EPOCH FROM (p.payment_date - o.created_at)) / 60)::numeric AS avg_min,
+            COUNT(DISTINCT o.order_id) AS total_orders
+        FROM project.orders o
+        JOIN project.payment p ON p.order_id = o.order_id
+        JOIN project.app_user u ON u.user_id = o.user_id
+        WHERE o.status = 'ПЛАТЕНА'
+        GROUP BY u.user_id, u.first_name, u.last_name
+    ),
+    global_avg AS (
+        SELECT AVG(avg_min)::numeric AS g_avg FROM waiter_stats
+    )
+    SELECT 
+        ws.uid,
+        ws.wname,
+        ROUND(ws.avg_min, 2),
+        ROUND(ga.g_avg, 2),
+        ws.total_orders,
+        (
+            SELECT p.name::TEXT
+            FROM project.order_item oi2
+            JOIN project.orders o2 ON o2.order_id = oi2.order_id
+            JOIN project.product p ON p.product_id = oi2.product_id
+            WHERE o2.user_id = ws.uid
+              AND o2.status = 'ПЛАТЕНА'
+            GROUP BY p.product_id, p.name
+            ORDER BY SUM(oi2.quantity * oi2.unit_price) DESC
+            LIMIT 1
+        ),
+        (
+            SELECT SUM(oi3.quantity * oi3.unit_price)::numeric
+            FROM project.order_item oi3
+            JOIN project.orders o3 ON o3.order_id = oi3.order_id
+            JOIN project.product p3 ON p3.product_id = oi3.product_id
+            WHERE o3.user_id = ws.uid
+              AND o3.status = 'ПЛАТЕНА'
+              AND p3.name = (
+                  SELECT p4.name
+                  FROM project.order_item oi4
+                  JOIN project.orders o4 ON o4.order_id = oi4.order_id
+                  JOIN project.product p4 ON p4.product_id = oi4.product_id
+                  WHERE o4.user_id = ws.uid AND o4.status = 'ПЛАТЕНА'
+                  GROUP BY p4.product_id, p4.name
+                  ORDER BY SUM(oi4.quantity * oi4.unit_price) DESC
+                  LIMIT 1
+              )
+        )
+    FROM waiter_stats ws, global_avg ga
+    WHERE ws.avg_min < ga.g_avg
+    ORDER BY 3 ASC;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+U(user_id, first_name, last_name)
+O(order_id, user_id, created_at, status)
+P(payment_id, order_id, payment_date)
+OI(order_id, product_id, quantity, unit_price)
+PR(product_id, name)
+
+J1 ← O ⨝ O.order_id = P.order_id P
+J2 ← J1 ⨝ O.user_id = U.user_id U
+F1 ← σ status='ПЛАТЕНА' (J2)
+G1 ← γ user_id, waiter_name; AVG((payment_date - created_at)/60) → avg_min,
+                              COUNT(DISTINCT order_id) → total_orders (F1)
+
+G2 ← γ ; AVG(avg_min) → g_avg (G1)
+
+F2 ← σ avg_min < g_avg (G1 × G2)
+
+J3 ← OI ⨝ O ⨝ PR
+F3 ← σ O.user_id = F2.user_id ∧ status='ПЛАТЕНА' (J3)
+G3 ← γ product_id, name; SUM(quantity * unit_price) → prod_rev (F3)
+TOP ← τ prod_rev DESC (G3) [LIMIT 1]
+
+R ← π user_id, waiter_name, avg_minutes, global_avg_minutes,
+      total_orders, top_product, top_product_revenue (F2 ⨝ TOP)
+
+R_final ← τ avg_minutes ASC (R)
+}}}