Сезонска анализа: споредба на исти месец во различни години

Опис

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