Changes between Initial Version and Version 1 of AdvancedReport1

Timestamp:

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

Author:

201178

Comment:

--

AdvancedReport1

@@ +1 @@
+= Најпрофитабилни производи во последните 12 месеци =
+
+== Опис ==
+
+Извештајот ги прикажува сите производи кои се продадени во последните 12 месеци, заедно со вкупната количина продадена, вкупниот приход и просечната цена по која се продавале. Резултатот е подреден според вкупниот приход опаѓачки.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION get_top_products_last_12_months()
+RETURNS TABLE (
+    product_id INT,
+    product_name TEXT,
+    category_name TEXT,
+    total_quantity BIGINT,
+    total_revenue NUMERIC,
+    avg_price NUMERIC,
+    order_count BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        p.product_id,
+        p.name::TEXT AS product_name,
+        c.name::TEXT AS category_name,
+        SUM(oi.quantity) AS total_quantity,
+        SUM(oi.quantity * oi.unit_price) AS total_revenue,
+        ROUND(AVG(oi.unit_price), 2) AS avg_price,
+        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 p.product_id, p.name, c.name
+    ORDER BY total_revenue DESC;
+END;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+P(product_id, name, 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 ← γ product_id, name, category_name;
+     SUM(quantity) → total_quantity,
+     SUM(quantity * unit_price) → total_revenue,
+     AVG(unit_price) → avg_price,
+     COUNT(DISTINCT order_id) → order_count (F1)
+
+R ← π product_id, product_name, category_name, total_quantity, total_revenue, avg_price, order_count (G)
+
+R_final ← τ total_revenue DESC (R)
+}}}