Changes between Initial Version and Version 1 of AdvancedReport2

Timestamp:

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

Author:

201178

Comment:

--

AdvancedReport2

@@ +1 @@
+= Најпрофитабилни категории по месеци =
+
+== Опис ==
+
+Извештајот ги прикажува сите категории на производи, групирани по месец, со вкупниот приход и бројот на продадени артикли. Овозможува анализа на сезонските трендови по категорија.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION get_category_revenue_by_month()
+RETURNS TABLE (
+    category_id INT,
+    category_name TEXT,
+    month TEXT,
+    total_items_sold BIGINT,
+    total_revenue NUMERIC,
+    order_count BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        c.category_id,
+        c.name::TEXT AS category_name,
+        TO_CHAR(o.created_at, 'YYYY-MM')::TEXT AS month,
+        SUM(oi.quantity) AS total_items_sold,
+        SUM(oi.quantity * oi.unit_price) AS total_revenue,
+        COUNT(DISTINCT o.order_id) AS order_count
+    FROM project.order_item oi
+    JOIN project.orders o ON o.order_id = oi.order_id
+    JOIN project.product p ON p.product_id = oi.product_id
+    JOIN project.category c ON c.category_id = p.category_id
+    WHERE o.status = 'ПЛАТЕНА'
+      AND o.created_at >= NOW() - INTERVAL '12 months'
+    GROUP BY c.category_id, c.name, TO_CHAR(o.created_at, 'YYYY-MM')
+    ORDER BY month DESC, total_revenue DESC;
+END;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+P(product_id, category_id)
+C(category_id, name)
+O(order_id, status, created_at)
+OI(order_id, product_id, quantity, unit_price)
+
+J1 ← OI ⨝ OI.order_id = O.order_id O
+J2 ← J1 ⨝ OI.product_id = P.product_id P
+J3 ← J2 ⨝ P.category_id = C.category_id C
+
+F1 ← σ status='ПЛАТЕНА' ∧ created_at ≥ NOW() - INTERVAL '12 months' (J3)
+
+G ← γ category_id, category_name, month;
+     SUM(quantity) → total_items_sold,
+     SUM(quantity * unit_price) → total_revenue,
+     COUNT(DISTINCT order_id) → order_count (F1)
+
+R ← π category_id, category_name, month, total_items_sold, total_revenue, order_count (G)
+
+R_final ← τ month DESC, total_revenue DESC (R)
+}}}