Changes between Initial Version and Version 1 of AdvancedReport3

Timestamp:

09/24/26 02:07:34 (7 days ago)

Author:

201178

Comment:

--

AdvancedReport3

@@ +1 @@
+= Просечно време помеѓу нарачка и плаќање =
+
+== Опис ==
+
+Извештајот го пресметува просечното време (во минути) помеѓу креирањето на нарачката и нејзиното плаќање, групирано по келнер. Овозможува идентификување на најефикасните келнери.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION get_avg_time_to_payment()
+RETURNS TABLE (
+    user_id INT,
+    waiter_name TEXT,
+    total_orders BIGINT,
+    avg_minutes_to_payment NUMERIC,
+    min_minutes NUMERIC,
+    max_minutes NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        u.user_id,
+        (u.first_name || ' ' || u.last_name)::TEXT AS waiter_name,
+        COUNT(o.order_id) AS total_orders,
+        ROUND(AVG(EXTRACT(EPOCH FROM (p.payment_date - o.created_at)) / 60), 2) AS avg_minutes_to_payment,
+        MIN(EXTRACT(EPOCH FROM (p.payment_date - o.created_at)) / 60) AS min_minutes,
+        MAX(EXTRACT(EPOCH FROM (p.payment_date - o.created_at)) / 60) AS max_minutes
+    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
+    HAVING COUNT(o.order_id) >= 5
+    ORDER BY avg_minutes_to_payment ASC;
+END;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+U(user_id, first_name, last_name)
+O(order_id, user_id, created_at, status)
+P(payment_id, order_id, payment_date)
+
+J1 ← O ⨝ O.order_id = P.order_id P
+J2 ← J1 ⨝ O.user_id = U.user_id U
+
+F1 ← σ status='ПЛАТЕНА' (J2)
+
+G ← γ user_id, waiter_name;
+     COUNT(order_id) → total_orders,
+     AVG((payment_date - created_at)/60) → avg_minutes_to_payment,
+     MIN((payment_date - created_at)/60) → min_minutes,
+     MAX((payment_date - created_at)/60) → max_minutes (F1)
+
+F2 ← σ total_orders ≥ 5 (G)
+
+R ← π user_id, waiter_name, total_orders, avg_minutes_to_payment, min_minutes, max_minutes (F2)
+
+R_final ← τ avg_minutes_to_payment ASC (R)
+}}}