Changes between Initial Version and Version 1 of AdvancedReport14

Timestamp:

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

Author:

201178

Comment:

--

AdvancedReport14

@@ +1 @@
+= Прогноза за идни приходи врз основа на тренд и сезонност =
+
+== Опис ==
+
+Извештајот ја пресметува прогнозата за приход во следните 3 месеци користејќи: (1) линеарен тренд од постоечките месечни податоци, (2) сезонски индекс по месец, (3) комбинирана прогноза. Користи прозорски функции, регресиони функции (REGR_SLOPE, REGR_INTERCEPT) и generate_series за генерирање на идни месеци. Доколку нема доволно месеци за сезонски индекс, се користи неутрална вредност 1.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION project.get_revenue_forecast()
+RETURNS TABLE (
+    forecast_month TEXT,
+    base_trend NUMERIC,
+    seasonal_index NUMERIC,
+    forecast_revenue NUMERIC,
+    confidence TEXT
+)
+LANGUAGE sql
+AS $$
+    WITH monthly AS (
+        SELECT 
+            DATE_TRUNC('month', p.payment_date) AS m,
+            EXTRACT(MONTH FROM p.payment_date)::INT AS mo,
+            SUM(p.amount)::numeric AS rev,
+            ROW_NUMBER() OVER (ORDER BY DATE_TRUNC('month', p.payment_date)) AS month_idx
+        FROM project.payment p
+        JOIN project.orders o ON o.order_id = p.order_id
+        WHERE o.status = 'ПЛАТЕНА'
+        GROUP BY 1, 2
+    ),
+    trend AS (
+        SELECT 
+            COALESCE(REGR_SLOPE(rev, month_idx), 0)::numeric AS slope,
+            COALESCE(REGR_INTERCEPT(rev, month_idx), AVG(rev))::numeric AS intercept,
+            COALESCE(AVG(rev), 0)::numeric AS avg_rev,
+            COALESCE(STDDEV(rev), 0)::numeric AS stddev_rev,
+            MAX(month_idx) AS last_idx
+        FROM monthly
+    ),
+    seasonal AS (
+        SELECT 
+            mo,
+            AVG(rev)::numeric AS month_avg,
+            (SELECT AVG(rev)::numeric FROM monthly) AS overall_avg
+        FROM monthly
+        GROUP BY mo
+    ),
+    forecast AS (
+        SELECT 
+            gs.n AS month_offset,
+            TO_CHAR(NOW() + (gs.n || ' months')::INTERVAL, 'YYYY-MM') AS fm,
+            EXTRACT(MONTH FROM NOW() + (gs.n || ' months')::INTERVAL)::INT AS fmo
+        FROM generate_series(1, 3) gs(n)
+    )
+    SELECT 
+        f.fm,
+        ROUND((t.intercept + t.slope * (t.last_idx + f.month_offset))::numeric, 2),
+        ROUND(COALESCE(s.month_avg / NULLIF(s.overall_avg, 0), 1)::numeric, 3),
+        ROUND(GREATEST(
+            (t.intercept + t.slope * (t.last_idx + f.month_offset)) 
+            * COALESCE(s.month_avg / NULLIF(s.overall_avg, 0), 1),
+            0
+        )::numeric, 2),
+        CASE 
+            WHEN t.avg_rev = 0 THEN 'НИСКА'
+            WHEN t.stddev_rev / t.avg_rev < 0.2 THEN 'ВИСОКА'
+            WHEN t.stddev_rev / t.avg_rev < 0.4 THEN 'СРЕДНА'
+            ELSE 'НИСКА'
+        END::TEXT
+    FROM forecast f
+    CROSS JOIN trend t
+    LEFT JOIN seasonal s ON s.mo = f.fmo
+    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 ← γ m, mo; SUM(amount) → rev, ROW_NUMBER() → month_idx (F1)
+
+A1 ← α REGR_SLOPE(rev, month_idx) → slope,
+        REGR_INTERCEPT(rev, month_idx) → intercept,
+        AVG(rev) → avg_rev,
+        STDDEV(rev) → stddev_rev,
+        MAX(month_idx) → last_idx (G1)
+
+G2 ← γ mo; AVG(rev) → month_avg (G1)
+A2 ← α AVG(rev) → overall_avg (G1)
+S ← G2 ⨝ (G2.month_avg / A2.overall_avg) → seasonal_index
+
+GS ← generate_series(1, 3) → month_offset
+J2 ← GS ⟕ S ON S.mo = MONTH(NOW() + month_offset months)
+
+R ← π forecast_month, base_trend, seasonal_index, forecast_revenue, confidence
+      ( A1 × J2 )
+
+R_final ← τ forecast_month ASC (R)
+}}}