Сумарни податоци за тагови

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

Вкупен број на трансакции

CREATE OR REPLACE FUNCTION get_monthly_transaction_count_by_tag()
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    col_list TEXT;
    dynamic_query TEXT;
BEGIN
    SELECT STRING_AGG(
        DISTINCT format(
            'SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I',
            tag_name, tag_name
        ),
        ', '
    )
    INTO col_list
    FROM tag;

    dynamic_query := format(
        'SELECT
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
         FROM transaction t
         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
         JOIN tag tg ON tat.tag_id = tg.tag_id
         GROUP BY transaction_month
         ORDER BY transaction_month;',
        col_list
    );

    RETURN QUERY EXECUTE dynamic_query;
END;
$$;

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

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

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

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

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

• G ← γ_{month = FORMAT(date, 'YYYY-MM'), tag_name; COUNT(transaction_id) → transaction_count}(J2)

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

CREATE OR REPLACE FUNCTION get_monthly_income_by_tag()
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    col_list TEXT;
    dynamic_query TEXT;
BEGIN
    SELECT STRING_AGG(
        DISTINCT format(
            'SUM(CASE WHEN tg.tag_name = %L THEN tb.earned_amount ELSE 0 END) AS %I',
            tag_name, tag_name
        ),
        ', '
    )
    INTO col_list
    FROM tag;

    dynamic_query := format(
        'SELECT
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
         FROM transaction t
         JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
         JOIN tag tg ON tat.tag_id = tg.tag_id
         GROUP BY transaction_month
         ORDER BY transaction_month;',
        col_list
    );

    RETURN QUERY EXECUTE dynamic_query;
END;
$$;

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

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

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

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

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

• G ← γ_{month = FORMAT(date, 'YYYY-MM'), tag_name; Σ(earned_amount) → total_income}(J3)

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

CREATE OR REPLACE FUNCTION get_monthly_expense_by_tag()
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    col_list TEXT;
    dynamic_query TEXT;
BEGIN
    SELECT STRING_AGG(
        DISTINCT format(
            'SUM(CASE WHEN tg.tag_name = %L THEN tb.spent_amount ELSE 0 END) AS %I',
            tag_name, tag_name
        ),
        ', '
    )
    INTO col_list
    FROM tag;

    dynamic_query := format(
        'SELECT
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
         FROM transaction t
         JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
         JOIN tag tg ON tat.tag_id = tg.tag_id
         GROUP BY transaction_month
         ORDER BY transaction_month;',
        col_list
    );

    RETURN QUERY EXECUTE dynamic_query;
END;
$$;

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

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

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

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

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

• G ← γ_{month = FORMAT(date, 'YYYY-MM'), tag_name; Σ(spent_amount) → total_expense}(J3)