Прогноза за идни приходи врз основа на тренд и сезонност

Опис

Извештајот ја пресметува прогнозата за приход во следните 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)