Changes between Initial Version and Version 1 of AdvancedReport8

Timestamp:

09/24/26 02:08:57 (7 days ago)

Author:

201178

Comment:

--

AdvancedReport8

@@ +1 @@
+= Најпрофитабилни маси =
+
+== Опис ==
+
+Извештајот ги прикажува масите со најголем вкупен приход, број на нарачки, просечна вредност на нарачка и приход по седиште.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION get_top_tables()
+RETURNS TABLE (
+    table_id INT,
+    table_number TEXT,
+    capacity INT,
+    order_count BIGINT,
+    total_revenue NUMERIC,
+    avg_order_value NUMERIC,
+    revenue_per_seat NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        rt.table_id,
+        rt.table_number::TEXT,
+        rt.capacity,
+        COUNT(DISTINCT o.order_id) AS order_count,
+        SUM(p.amount) AS total_revenue,
+        ROUND(AVG(p.amount), 2) AS avg_order_value,
+        ROUND(SUM(p.amount) / rt.capacity, 2) AS revenue_per_seat
+    FROM project.restaurant_table rt
+    JOIN project.orders o ON o.table_id = rt.table_id
+    JOIN project.payment p ON p.order_id = o.order_id
+    WHERE o.status = 'ПЛАТЕНА'
+    GROUP BY rt.table_id, rt.table_number, rt.capacity
+    ORDER BY total_revenue DESC;
+END;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+RT(table_id, table_number, capacity)
+O(order_id, table_id, status)
+P(payment_id, order_id, amount)
+
+J1 ← RT ⨝ RT.table_id = O.table_id O
+J2 ← J1 ⨝ O.order_id = P.order_id P
+
+F1 ← σ status='ПЛАТЕНА' (J2)
+
+G ← γ table_id, table_number, capacity;
+     COUNT(DISTINCT order_id) → order_count,
+     SUM(amount) → total_revenue,
+     AVG(amount) → avg_order_value,
+     SUM(amount)/capacity → revenue_per_seat (F1)
+
+R ← π table_id, table_number, capacity, order_count, total_revenue, avg_order_value, revenue_per_seat (G)
+
+R_final ← τ total_revenue DESC (R)
+}}}