Changes between Initial Version and Version 1 of AdvancedReport4

Timestamp:

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

Author:

201178

Comment:

--

AdvancedReport4

@@ +1 @@
+= Најпрометни часови во денот =
+
+== Опис ==
+
+Извештајот го прикажува бројот на нарачки и вкупниот приход групирани по час од денот. Овозможува идентификување на најпрометните периоди.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION get_busiest_hours()
+RETURNS TABLE (
+    hour_of_day NUMERIC,
+    order_count BIGINT,
+    total_items BIGINT,
+    total_revenue NUMERIC,
+    avg_order_value NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        EXTRACT(HOUR FROM o.created_at) AS hour_of_day,
+        COUNT(DISTINCT o.order_id) AS order_count,
+        SUM(oi.quantity) AS total_items,
+        SUM(oi.quantity * oi.unit_price) AS total_revenue,
+        ROUND(AVG(oi.quantity * oi.unit_price), 2) AS avg_order_value
+    FROM project.orders o
+    JOIN project.order_item oi ON oi.order_id = o.order_id
+    WHERE o.status = 'ПЛАТЕНА'
+    GROUP BY EXTRACT(HOUR FROM o.created_at)
+    ORDER BY total_revenue DESC;
+END;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+O(order_id, created_at, status)
+OI(order_id, quantity, unit_price)
+
+J1 ← O ⨝ O.order_id = OI.order_id OI
+
+F1 ← σ status='ПЛАТЕНА' (J1)
+
+G ← γ hour_of_day;
+     COUNT(DISTINCT order_id) → order_count,
+     SUM(quantity) → total_items,
+     SUM(quantity * unit_price) → total_revenue,
+     AVG(quantity * unit_price) → avg_order_value (F1)
+
+R ← π hour_of_day, order_count, total_items, total_revenue, avg_order_value (G)
+
+R_final ← τ total_revenue DESC (R)
+}}}