Changes between Version 2 and Version 3 of AdvancedReports


Ignore:
Timestamp:
12/20/24 23:49:07 (12 months ago)
Author:
211101
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v2 v3  
    11= Напредни извештаи од базата (SQL и складирани процедури)
    22
    3 ===== Надминување на баланс на една сметка со трансакција
     3===== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки
     4{{{#!sql
     5WITH CumulativeBalances AS (
     6    SELECT
     7        t.transaction_id,
     8        t.transaction_name,
     9        t.date AS transaction_date,
     10        ta.account_name,
     11        u.user_id,
     12        u.user_name,
     13        tb.spent_amount AS transaction_amount,
     14        SUM(tb.earned_amount - tb.spent_amount) OVER (
     15            PARTITION BY ta.transaction_account_id
     16            ORDER BY t.date
     17        ) AS calculated_balance
     18    FROM
     19        transaction_account ta
     20    JOIN
     21        user u ON ta.user_id = u.user_id
     22    JOIN
     23        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
     24    JOIN
     25        transaction t ON tb.transaction_id = t.transaction_id
     26    WHERE
     27        ta.account_name = 'Specific Account Name' -- Може да се замени со специфично име на акаунт
     28)
     29SELECT
     30    user_id,
     31    user_name,
     32    account_name,
     33    transaction_id,
     34    transaction_name,
     35    transaction_amount,
     36    transaction_date,
     37    calculated_balance
     38FROM
     39    CumulativeBalances
     40WHERE
     41    transaction_amount > calculated_balance -- Трансакцијата го надминува пресметаниот баланс
     42    AND transaction_amount > 0
     43ORDER BY
     44    user_id, account_name, transaction_date DESC;
     45}}}
     46
     47===== Надминување на баланс на една сметка со трансакција во сегашно време
    448{{{#!sql
    549SELECT
    … …  
    1862JOIN transaction t ON tb.transaction_id = t.transaction_id
    1963WHERE
    20     tb.spent_amount > ta.balance -- Трансакцијата го надминува балансот на СМЕТКАТА
     64    tb.spent_amount > ta.balance -- Трансакцијата го надминува моменталниот баланс на СМЕТКАТА
    2165    AND tb.spent_amount > 0
    2266ORDER BY
    … …  
    2468}}}
    2569
    26 ===== Надминување на вкупниот баланс на сите сметки на корисникот
    27 Овде ќе го собереме балансот на сите сметки на корисникот и ќе провериме дали сумата на трансакцијата ја надминува таа вкупна вредност
     70===== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки
     71{{{#!sql
     72WITH CumulativeTotalBalances AS (
     73    SELECT
     74        t.transaction_id,
     75        t.transaction_name,
     76        t.date AS transaction_date,
     77        u.user_id,
     78        u.user_name,
     79        tb.spent_amount AS transaction_amount,
     80        SUM(tb.earned_amount - tb.spent_amount) OVER (
     81            PARTITION BY u.user_id
     82            ORDER BY t.date
     83        ) AS calculated_total_balance
     84    FROM
     85        transaction_account ta
     86    JOIN
     87        user u ON ta.user_id = u.user_id
     88    JOIN
     89        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
     90    JOIN
     91        transaction t ON tb.transaction_id = t.transaction_id
     92)
     93SELECT
     94    user_id,
     95    user_name,
     96    transaction_id,
     97    transaction_name,
     98    transaction_amount,
     99    transaction_date,
     100    calculated_total_balance
     101FROM
     102    CumulativeTotalBalances
     103WHERE
     104    transaction_amount > calculated_total_balance -- Трансакцијата го надминува пресметаниот вкупен баланс од сите сметки
     105    AND transaction_amount > 0
     106ORDER BY
     107    user_id, transaction_date DESC;
     108}}}
     109
     110===== Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време
     111Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува таа вкупна вредност
    28112{{{#!sql
    29113WITH total_user_balance AS (
    … …  
    53137    u.user_id, u.user_name, tub.total_balance
    54138HAVING
    55     SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ баланс
     139    SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ моментален баланс
    56140ORDER BY
    57141    u.user_id;
    … …  
    80164===== Број на трансакции што ги надминуваат приходите на сметка
    81165{{{#!sql
    82 SELECT
    83     ta.account_name,
    84     COUNT(tb.transaction_id) AS transactions_exceeding_balance
    85 FROM
    86     transaction_account ta
    87 JOIN
    88     transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    89 WHERE
    90     tb.spent_amount > ta.balance -- Кога потрошеното е поголемо од состојбата на сметката
    91 GROUP BY
    92     ta.account_name
     166WITH CumulativeBalances AS (
     167    SELECT
     168        t.transaction_id,
     169        ta.account_name,
     170        SUM(tb.earned_amount - tb.spent_amount) OVER (
     171            PARTITION BY ta.transaction_account_id
     172            ORDER BY t.date
     173        ) AS calculated_balance,
     174        tb.spent_amount
     175    FROM
     176        transaction_account ta
     177    JOIN
     178        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
     179    JOIN
     180        transaction t ON tb.transaction_id = t.transaction_id
     181)
     182SELECT
     183    account_name,
     184    COUNT(transaction_id) AS transactions_exceeding_balance
     185FROM
     186    CumulativeBalances
     187WHERE
     188    spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс
     189    AND spent_amount > 0
     190GROUP BY
     191    account_name
    93192ORDER BY
    94193    transactions_exceeding_balance DESC;
    … …  
    96195Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
    97196{{{#!sql
    98 SELECT
    99     COUNT(tb.transaction_id) AS total_transactions_exceeding_balance
    100 FROM
    101     transaction_account ta
    102 JOIN
    103     transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    104 WHERE
    105     tb.spent_amount > ta.balance;
     197WITH CumulativeTotalBalances AS (
     198    SELECT
     199        t.transaction_id,
     200        u.user_id,
     201        SUM(tb.earned_amount - tb.spent_amount) OVER (
     202            PARTITION BY u.user_id
     203            ORDER BY t.date
     204        ) AS calculated_total_balance,
     205        tb.spent_amount
     206    FROM
     207        transaction_account ta
     208    JOIN
     209        user u ON ta.user_id = u.user_id
     210    JOIN
     211        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
     212    JOIN
     213        transaction t ON tb.transaction_id = t.transaction_id
     214)
     215SELECT
     216    COUNT(transaction_id) AS total_transactions_exceeding_balance
     217FROM
     218    CumulativeTotalBalances
     219WHERE
     220    spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс
     221    AND spent_amount > 0;
    106222}}}
    107223
    … …  
    110226 - Вкупен број на трансакции
    111227{{{#!sql
    112 SELECT
    113     TO_CHAR(t.date, 'YYYY-MM') AS transaction_month,
    114     tg.tag_name,
    115     COUNT(t.transaction_id) AS total_transactions
    116 FROM
    117     transaction t
    118 JOIN
    119     tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    120 JOIN
    121     tag tg ON tat.tag_id = tg.tag_id
    122 GROUP BY
    123     transaction_month, tg.tag_name
    124 ORDER BY
    125     transaction_month, tg.tag_name;
     228DO $$
     229DECLARE
     230    col_list TEXT; -- Листа на тагови за изведување на пивот
     231    dynamic_query TEXT; -- Динамичко SQL прашање
     232BEGIN
     233    -- Чекор 1: Динамички се генерира листа на колони
     234    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', tag_name, tag_name), ', ')
     235    INTO col_list
     236    FROM tag;
     237
     238    -- Чекор 2: Се гради динамичкото прашање
     239    dynamic_query := format(
     240        'SELECT
     241            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
     242            %s
     243        FROM
     244            transaction t
     245        JOIN
     246            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     247        JOIN
     248            tag tg ON tat.tag_id = tg.tag_id
     249        GROUP BY
     250            transaction_month
     251        ORDER BY
     252            transaction_month;',
     253        col_list
     254    );
     255
     256    -- Чекор 3: Извршување на динамичкото прашање
     257    EXECUTE dynamic_query;
     258END $$;
    126259}}}
    127260 - Вкупно примени средства
    128261{{{#!sql
    129 SELECT
    130     TO_CHAR(t.date, 'YYYY-MM') AS transaction_month,
    131     tg.tag_name,
    132     SUM(tb.earned_amount) AS total_earned
    133 FROM
    134     transaction t
    135 JOIN
    136     transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    137 JOIN
    138     tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    139 JOIN
    140     tag tg ON tat.tag_id = tg.tag_id
    141 GROUP BY
    142     transaction_month, tg.tag_name
    143 ORDER BY
    144     transaction_month, tg.tag_name;
     262DO $$
     263DECLARE
     264    col_list TEXT; -- Листа на тагови за изведување на пивот
     265    dynamic_query TEXT; -- Динамичко SQL прашање
     266BEGIN
     267    -- Чекор 1: Динамички се генерира листа на колони
     268    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), ', ')
     269    INTO col_list
     270    FROM tag;
     271
     272    -- Чекор 2: Се гради динамичкото прашање
     273    dynamic_query := format(
     274        'SELECT
     275            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
     276            %s
     277        FROM
     278            transaction t
     279        JOIN
     280            transaction_breakdown tb ON t.transaction_id = tb.transaction_id
     281        JOIN
     282            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     283        JOIN
     284            tag tg ON tat.tag_id = tg.tag_id
     285        GROUP BY
     286            transaction_month
     287        ORDER BY
     288            transaction_month;',
     289        col_list
     290    );
     291
     292    -- Чекор 3: Извршување на динамичкото прашање
     293    EXECUTE dynamic_query;
     294END $$;
    145295}}}
    146296 - Вкупно потрошени средства
    147297{{{#!sql
    148 SELECT
    149     TO_CHAR(t.date, 'YYYY-MM') AS transaction_month,
    150     tg.tag_name,
    151     SUM(tb.spent_amount) AS total_spent
    152 FROM
    153     transaction t
    154 JOIN
    155     transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    156 JOIN
    157     tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    158 JOIN
    159     tag tg ON tat.tag_id = tg.tag_id
    160 GROUP BY
    161     transaction_month, tg.tag_name
    162 ORDER BY
    163     transaction_month, tg.tag_name;
     298DO $$
     299DECLARE
     300    col_list TEXT; -- Листа на тагови за изведување на пивот
     301    dynamic_query TEXT; -- Динамичко SQL прашање
     302BEGIN
     303    -- Чекор 1: Динамички се генерира листа на колони
     304    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), ', ')
     305    INTO col_list
     306    FROM tag;
     307
     308    -- Чекор 2: Се гради динамичкото прашање
     309    dynamic_query := format(
     310        'SELECT
     311            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
     312            %s
     313        FROM
     314            transaction t
     315        JOIN
     316            transaction_breakdown tb ON t.transaction_id = tb.transaction_id
     317        JOIN
     318            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     319        JOIN
     320            tag tg ON tat.tag_id = tg.tag_id
     321        GROUP BY
     322            transaction_month
     323        ORDER BY
     324            transaction_month;',
     325        col_list
     326    );
     327
     328    -- Чекор 3: Извршување на динамичкото прашање
     329    EXECUTE dynamic_query;
     330END $$;
    164331}}}
    165332