Трендови на трошење според тагови за цел систем

Трендови на трошење за секој таг во последните шест месеци

CREATE OR REPLACE FUNCTION get_spending_trends_last_6_months()
RETURNS TABLE (
    tag_name TEXT,
    month DATE,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tg.tag_name, 
        DATE_TRUNC('month', t.date) AS month,
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM tag tg
    JOIN tag_assigned_to_transaction tat 
        ON tg.tag_id = tat.tag_id
    JOIN transaction t 
        ON tat.transaction_id = t.transaction_id
    JOIN transaction_breakdown tb 
        ON t.transaction_id = tb.transaction_id
    WHERE t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
    GROUP BY tg.tag_name, month
    ORDER BY tg.tag_name, month;
END;
$$;

Релациона алгебра

• TG(tag_id, tag_name)
• TAT(tag_id, transaction_id)
• T(transaction_id, date)
• TB(transaction_id, spent_amount)

JOIN на сите табели:

• J1 ← TG ⨝_{TG.tag_id = TAT.tag_id} TAT
• J2 ← J1 ⨝_{TAT.transaction_id = T.transaction_id} T
• J3 ← J2 ⨝_{T.transaction_id = TB.transaction_id} TB

Филтрирање на трансакции од последните 6 месеци:

• F ← σ_{date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')}(J3)

Групирање по таг и месец:

• G ← γ_{tag_name, month = DATE_TRUNC('month', date); Σ(spent_amount) → total_spent}(F)

Подредување по таг и месец:

• R_final ← τ_{tag_name, month}(G)