Changes between Initial Version and Version 1 of AdvancedReport12

Timestamp:

09/25/26 16:23:20 (5 days ago)

Author:

201178

Comment:

--

AdvancedReport12

@@ +1 @@
+= Сезонска анализа: споредба на исти месец во различни години =
+
+== Опис ==
+
+Извештајот го споредува приходот во секој месец од тековната година со истиот месец од претходната година, со процент на промена. Користи CTE со две нивоа и прозорска функција RANK за рангирање на месеците.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION project.get_yoy_monthly_comparison()
+RETURNS TABLE (
+    month_num INT,
+    month_name TEXT,
+    current_year_revenue NUMERIC,
+    previous_year_revenue NUMERIC,
+    change_pct NUMERIC,
+    rank_in_year INT
+)
+LANGUAGE sql
+AS $$
+    WITH monthly_rev AS (
+        SELECT 
+            EXTRACT(YEAR FROM p.payment_date)::INT AS yr,
+            EXTRACT(MONTH FROM p.payment_date)::INT AS mo,
+            SUM(p.amount)::numeric AS revenue
+        FROM project.payment p
+        JOIN project.orders o ON o.order_id = p.order_id
+        WHERE o.status = 'ПЛАТЕНА'
+        GROUP BY 1, 2
+    ),
+    yoy AS (
+        SELECT 
+            curr.yr, curr.mo, curr.revenue AS curr_rev,
+            prev.revenue AS prev_rev,
+            ROUND((100.0 * (curr.revenue - prev.revenue) 
+                  / NULLIF(prev.revenue, 0))::numeric, 2) AS change_pct
+        FROM monthly_rev curr
+        LEFT JOIN monthly_rev prev 
+            ON prev.yr = curr.yr - 1 AND prev.mo = curr.mo
+        WHERE curr.yr = EXTRACT(YEAR FROM NOW())::INT
+    ),
+    ranked AS (
+        SELECT yr, mo, curr_rev, prev_rev, change_pct,
+               RANK() OVER (ORDER BY curr_rev DESC) AS rnk
+        FROM yoy
+    )
+    SELECT 
+        mo, 
+        TO_CHAR(TO_DATE(mo::TEXT, 'MM'), 'Month')::TEXT,
+        curr_rev, 
+        COALESCE(prev_rev, 0), 
+        COALESCE(change_pct, 0),
+        rnk::INT
+    FROM ranked
+    WHERE curr_rev > (
+        SELECT AVG(revenue) FROM monthly_rev
+        WHERE yr = EXTRACT(YEAR FROM NOW())::INT
+    )
+    ORDER BY 1;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+P(payment_id, order_id, amount, payment_date)
+O(order_id, status)
+
+J1 ← P ⨝ P.order_id = O.order_id O
+F1 ← σ status='ПЛАТЕНА' (J1)
+
+G1 ← γ yr, mo; SUM(amount) → revenue (F1)
+
+J2 ← G1 ⨝ G1.yr = G1b.yr + 1 ∧ G1.mo = G1b.mo G1b
+F2 ← σ yr = YEAR(NOW()) (J2)
+
+W ← γ yr, mo; RANK() OVER (ORDER BY curr_rev DESC) → rnk (F2)
+F3 ← σ curr_rev > AVG(revenue) (W)
+
+R ← π month_num, month_name, current_year_revenue,
+      previous_year_revenue, change_pct, rank_in_year (F3)
+
+R_final ← τ month_num ASC (R)
+}}}