Вкупно потрошено во тековниот месец за корисник

Вкупна сума на трошоци во тековниот месец на еден корисник

CREATE OR REPLACE FUNCTION get_total_spent_current_month_user(
    p_user_id INT
)
RETURNS TABLE (
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM transaction_breakdown tb
    JOIN transaction t
        ON tb.transaction_id = t.transaction_id
    JOIN transaction_account ta
        ON tb.transaction_account_id = ta.transaction_account_id
    WHERE ta.user_id = p_user_id
      AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
END;
$$;

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

• TA(transaction_account_id, user_id)
• TB(transaction_id, transaction_account_id, spent_amount)
• T(transaction_id, date)

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

• J1 ← TA ⨝_{TA.transaction_account_id = TB.transaction_account_id} TB
• J2 ← J1 ⨝_{TB.transaction_id = T.transaction_id} T

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

• F ← σ_{user_id = p_user_id ∧ EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE)} (J2)

Агрегација на вкупно потрошено:

• R_final ← γ_{; SUM(spent_amount) → total_spent}(F)