Changes between Version 1 and Version 2 of AdvancedReport7


Ignore:
Timestamp:
12/29/25 19:13:33 (5 days ago)
Author:
211101
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReport7

    v1 v2  
    1 ===== Сумарни податоци за тагови
     1==== Сумарни податоци за тагови
    22Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:
    3  - Вкупен број на трансакции
     3===== Вкупен број на трансакции
    44{{{#!sql
    5 DO $$
     5CREATE OR REPLACE FUNCTION get_monthly_transaction_count_by_tag()
     6RETURNS SETOF RECORD
     7LANGUAGE plpgsql
     8AS $$
    69DECLARE
    7     col_list TEXT; -- Листа на тагови за изведување на пивот
    8     dynamic_query TEXT; -- Динамичко SQL прашање
     10    col_list TEXT;
     11    dynamic_query TEXT;
    912BEGIN
    10     -- Чекор 1: Динамички се генерира листа на колони
    11     SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', tag_name, tag_name), ', ')
     13    SELECT STRING_AGG(
     14        DISTINCT format(
     15            'SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I',
     16            tag_name, tag_name
     17        ),
     18        ', '
     19    )
    1220    INTO col_list
    1321    FROM tag;
    1422
    15     -- Чекор 2: Се гради динамичкото прашање
    1623    dynamic_query := format(
    17         'SELECT 
     24        'SELECT
    1825            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    1926            %s
    20         FROM
    21             transaction t
    22         JOIN
    23             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    24         JOIN
    25             tag tg ON tat.tag_id = tg.tag_id
    26         GROUP BY
    27             transaction_month
    28         ORDER BY
    29             transaction_month;',
     27         FROM transaction t
     28         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     29         JOIN tag tg ON tat.tag_id = tg.tag_id
     30         GROUP BY transaction_month
     31         ORDER BY transaction_month;',
    3032        col_list
    3133    );
    3234
    33     -- Чекор 3: Извршување на динамичкото прашање
    34     EXECUTE dynamic_query;
    35 END $$;
     35    RETURN QUERY EXECUTE dynamic_query;
     36END;
     37$$;
    3638}}}
    37  - Вкупно примени средства
     39
     40===== Релациона алгебра
     41- T(transaction_id, date)
     42- TAT(transaction_id, tag_id)
     43- TG(tag_id, tag_name)
     44
     45JOIN на сите табели:
     46- J1 ← T ⨝,,T.transaction_id = TAT.transaction_id,, TAT
     47- J2 ← J1 ⨝,,TAT.tag_id = TG.tag_id,, TG
     48
     49Групирање по месец и таг:
     50- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; COUNT(transaction_id) → transaction_count,,(J2)
     51
     52==
     53
     54===== Вкупно примени средства
    3855{{{#!sql
    39 DO $$
     56CREATE OR REPLACE FUNCTION get_monthly_income_by_tag()
     57RETURNS SETOF RECORD
     58LANGUAGE plpgsql
     59AS $$
    4060DECLARE
    41     col_list TEXT; -- Листа на тагови за изведување на пивот
    42     dynamic_query TEXT; -- Динамичко SQL прашање
     61    col_list TEXT;
     62    dynamic_query TEXT;
    4363BEGIN
    44     -- Чекор 1: Динамички се генерира листа на колони
    45     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), ', ')
     64    SELECT STRING_AGG(
     65        DISTINCT format(
     66            'SUM(CASE WHEN tg.tag_name = %L THEN tb.earned_amount ELSE 0 END) AS %I',
     67            tag_name, tag_name
     68        ),
     69        ', '
     70    )
    4671    INTO col_list
    4772    FROM tag;
    4873
    49     -- Чекор 2: Се гради динамичкото прашање
    5074    dynamic_query := format(
    51         'SELECT 
     75        'SELECT
    5276            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    5377            %s
    54         FROM
    55             transaction t
    56         JOIN
    57             transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    58         JOIN
    59             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    60         JOIN
    61             tag tg ON tat.tag_id = tg.tag_id
    62         GROUP BY
    63             transaction_month
    64         ORDER BY
    65             transaction_month;',
     78         FROM transaction t
     79         JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
     80         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     81         JOIN tag tg ON tat.tag_id = tg.tag_id
     82         GROUP BY transaction_month
     83         ORDER BY transaction_month;',
    6684        col_list
    6785    );
    6886
    69     -- Чекор 3: Извршување на динамичкото прашање
    70     EXECUTE dynamic_query;
    71 END $$;
     87    RETURN QUERY EXECUTE dynamic_query;
     88END;
     89$$;
    7290}}}
    73  - Вкупно потрошени средства
     91
     92==== Релациона алгебра
     93- T(transaction_id, date)
     94- TB(transaction_id, earned_amount)
     95- TAT(transaction_id, tag_id)
     96- TG(tag_id, tag_name)
     97
     98JOIN на сите табели:
     99- J1 ← T ⨝,,T.transaction_id = TB.transaction_id,, TB
     100- J2 ← J1 ⨝,,T.transaction_id = TAT.transaction_id,, TAT
     101- J3 ← J2 ⨝,,TAT.tag_id = TG.tag_id,, TG
     102
     103Групирање по месец и таг:
     104- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; Σ(earned_amount) → total_income,,(J3)
     105
     106==
     107
     108===== Вкупно потрошени средства
    74109{{{#!sql
    75 DO $$
     110CREATE OR REPLACE FUNCTION get_monthly_expense_by_tag()
     111RETURNS SETOF RECORD
     112LANGUAGE plpgsql
     113AS $$
    76114DECLARE
    77     col_list TEXT; -- Листа на тагови за изведување на пивот
    78     dynamic_query TEXT; -- Динамичко SQL прашање
     115    col_list TEXT;
     116    dynamic_query TEXT;
    79117BEGIN
    80     -- Чекор 1: Динамички се генерира листа на колони
    81     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), ', ')
     118    SELECT STRING_AGG(
     119        DISTINCT format(
     120            'SUM(CASE WHEN tg.tag_name = %L THEN tb.spent_amount ELSE 0 END) AS %I',
     121            tag_name, tag_name
     122        ),
     123        ', '
     124    )
    82125    INTO col_list
    83126    FROM tag;
    84127
    85     -- Чекор 2: Се гради динамичкото прашање
    86128    dynamic_query := format(
    87         'SELECT 
     129        'SELECT
    88130            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    89131            %s
    90         FROM
    91             transaction t
    92         JOIN
    93             transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    94         JOIN
    95             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    96         JOIN
    97             tag tg ON tat.tag_id = tg.tag_id
    98         GROUP BY
    99             transaction_month
    100         ORDER BY
    101             transaction_month;',
     132         FROM transaction t
     133         JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
     134         JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     135         JOIN tag tg ON tat.tag_id = tg.tag_id
     136         GROUP BY transaction_month
     137         ORDER BY transaction_month;',
    102138        col_list
    103139    );
    104140
    105     -- Чекор 3: Извршување на динамичкото прашање
    106     EXECUTE dynamic_query;
    107 END $$;
     141    RETURN QUERY EXECUTE dynamic_query;
     142END;
     143$$;
    108144}}}
     145
     146==== Релациона алгебра
     147- T(transaction_id, date)
     148- TB(transaction_id, spent_amount)
     149- TAT(transaction_id, tag_id)
     150- TG(tag_id, tag_name)
     151
     152JOIN на сите табели:
     153- J1 ← T ⨝,,T.transaction_id = TB.transaction_id,, TB
     154- J2 ← J1 ⨝,,T.transaction_id = TAT.transaction_id,, TAT
     155- J3 ← J2 ⨝,,TAT.tag_id = TG.tag_id,, TG
     156
     157Групирање по месец и таг:
     158- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; Σ(spent_amount) → total_expense,,(J3)