Changes between Version 1 and Version 2 of AdvancedReports


Ignore:
Timestamp:
12/16/24 23:54:33 (4 weeks ago)
Author:
211101
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v1 v2  
    11= Напредни извештаи од базата (SQL и складирани процедури)
    22
    3 ===== Просечно трошење во последните 3 дена
     3===== Надминување на баланс на една сметка со трансакција
     4{{{#!sql
     5SELECT
     6    u.user_id,
     7    u.user_name,
     8    ta.account_name,
     9    ta.balance AS current_balance,
     10    t.transaction_id,
     11    t.transaction_name,
     12    tb.spent_amount AS transaction_amount,
     13    t.date AS transaction_date
     14FROM
     15    transaction_account ta
     16JOIN user u ON ta.user_id = u.user_id
     17JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
     18JOIN transaction t ON tb.transaction_id = t.transaction_id
     19WHERE
     20    tb.spent_amount > ta.balance -- Трансакцијата го надминува балансот на СМЕТКАТА
     21    AND tb.spent_amount > 0
     22ORDER BY
     23    u.user_id, ta.account_name, t.date DESC;
     24}}}
     25
     26===== Надминување на вкупниот баланс на сите сметки на корисникот
     27Овде ќе го собереме балансот на сите сметки на корисникот и ќе провериме дали сумата на трансакцијата ја надминува таа вкупна вредност
     28{{{#!sql
     29WITH total_user_balance AS (
     30    SELECT
     31        u.user_id,
     32        SUM(ta.balance) AS total_balance
     33    FROM
     34        transaction_account ta
     35    JOIN user u ON ta.user_id = u.user_id
     36    GROUP BY
     37        u.user_id
     38)
     39SELECT
     40    u.user_id,
     41    u.user_name,
     42    SUM(tb.spent_amount) AS total_transaction_amount,
     43    tub.total_balance AS user_total_balance
     44FROM
     45    transaction_account ta
     46JOIN user u ON ta.user_id = u.user_id
     47JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
     48JOIN transaction t ON tb.transaction_id = t.transaction_id
     49JOIN total_user_balance tub ON u.user_id = tub.user_id
     50WHERE
     51    t.date <= CURRENT_DATE -- Само активни трансакции до денешен датум
     52GROUP BY
     53    u.user_id, u.user_name, tub.total_balance
     54HAVING
     55    SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ баланс
     56ORDER BY
     57    u.user_id;
     58}}}
     59
     60===== Сумарни приходи и расходи по месеци
     61Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата
     62{{{#!sql
     63SELECT
     64    TO_CHAR(t.date, 'YYYY-MM') AS month, -- Форматирање на датумот во формат "Година-Месец"
     65    SUM(CASE WHEN tb.earned_amount > 0 THEN tb.earned_amount ELSE 0 END) AS total_income, -- Вкупен приход
     66    SUM(CASE WHEN tb.spent_amount > 0 THEN tb.spent_amount ELSE 0 END) AS total_expense, -- Вкупен расход
     67    SUM(CASE WHEN tb.earned_amount > 0 THEN tb.earned_amount ELSE 0 END) -
     68    SUM(CASE WHEN tb.spent_amount > 0 THEN tb.spent_amount ELSE 0 END) AS net_balance -- Нето состојба
     69FROM
     70    transaction_breakdown tb
     71JOIN
     72    transaction t ON tb.transaction_id = t.transaction_id
     73GROUP BY
     74    TO_CHAR(t.date, 'YYYY-MM') -- Групирање по месец
     75ORDER BY
     76    month;
     77}}}
     78Проблематичен месец е месец каде што {{{net_balance}}} е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците
     79
     80===== Број на трансакции што ги надминуваат приходите на сметка
     81{{{#!sql
     82SELECT
     83    ta.account_name,
     84    COUNT(tb.transaction_id) AS transactions_exceeding_balance
     85FROM
     86    transaction_account ta
     87JOIN
     88    transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
     89WHERE
     90    tb.spent_amount > ta.balance -- Кога потрошеното е поголемо од состојбата на сметката
     91GROUP BY
     92    ta.account_name
     93ORDER BY
     94    transactions_exceeding_balance DESC;
     95}}}
     96Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
     97{{{#!sql
     98SELECT
     99    COUNT(tb.transaction_id) AS total_transactions_exceeding_balance
     100FROM
     101    transaction_account ta
     102JOIN
     103    transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
     104WHERE
     105    tb.spent_amount > ta.balance;
     106}}}
     107
     108===== Сумарни податоци за тагови
     109Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:
     110 - Вкупен број на трансакции
     111{{{#!sql
     112SELECT
     113    TO_CHAR(t.date, 'YYYY-MM') AS transaction_month,
     114    tg.tag_name,
     115    COUNT(t.transaction_id) AS total_transactions
     116FROM
     117    transaction t
     118JOIN
     119    tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     120JOIN
     121    tag tg ON tat.tag_id = tg.tag_id
     122GROUP BY
     123    transaction_month, tg.tag_name
     124ORDER BY
     125    transaction_month, tg.tag_name;
     126}}}
     127 - Вкупно примени средства
     128{{{#!sql
     129SELECT
     130    TO_CHAR(t.date, 'YYYY-MM') AS transaction_month,
     131    tg.tag_name,
     132    SUM(tb.earned_amount) AS total_earned
     133FROM
     134    transaction t
     135JOIN
     136    transaction_breakdown tb ON t.transaction_id = tb.transaction_id
     137JOIN
     138    tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     139JOIN
     140    tag tg ON tat.tag_id = tg.tag_id
     141GROUP BY
     142    transaction_month, tg.tag_name
     143ORDER BY
     144    transaction_month, tg.tag_name;
     145}}}
     146 - Вкупно потрошени средства
     147{{{#!sql
     148SELECT
     149    TO_CHAR(t.date, 'YYYY-MM') AS transaction_month,
     150    tg.tag_name,
     151    SUM(tb.spent_amount) AS total_spent
     152FROM
     153    transaction t
     154JOIN
     155    transaction_breakdown tb ON t.transaction_id = tb.transaction_id
     156JOIN
     157    tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     158JOIN
     159    tag tg ON tat.tag_id = tg.tag_id
     160GROUP BY
     161    transaction_month, tg.tag_name
     162ORDER BY
     163    transaction_month, tg.tag_name;
     164}}}
     165
     166===== Просечно трошење во последните 3 дена за цел систем
    4167Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:
    5168{{{#!sql
    … …  
    22185}}}
    23186
    24 ===== Вкупно потрошено во тековниот месец
     187===== Просечно трошење во последните 3 дена за корисник
     188Просечно дневно трошење за последните 3 дена за одреден корисник:
     189{{{#!sql
     190SELECT
     191    AVG(daily_spending) AS average_spending_last_3_days
     192FROM (
     193    SELECT
     194        t.date::date AS transaction_date,
     195        SUM(tb.spent_amount) AS daily_spending
     196    FROM
     197        transaction t
     198    JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
     199    JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
     200    WHERE
     201        ta.user_id = 101 -- ID на конкретниот корисник
     202        AND t.date >= CURRENT_DATE - INTERVAL '2 DAY'
     203        AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
     204    GROUP BY
     205        t.date::date
     206) daily_totals;
     207}}}
     208
     209===== Вкупно потрошено во тековниот месец за цел систем
    25210Вкупна сума на трошоци во тековниот месец
    26211{{{#!sql
    … …  
    35220}}}
    36221
    37 ===== Просечно дневно трошење за тековниот месец
    38 {{{#!sql
    39 SELECT
    40     SUM(tb.spent_amount) / EXTRACT(DAY FROM CURRENT_DATE) AS average_daily_spending
    41 FROM
    42     transaction_breakdown tb
    43 JOIN transaction t ON tb.transaction_id = t.transaction_id
    44 WHERE
    45     EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    46     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    47     AND t.date <= CURRENT_DATE;
    48 }}}
    49 
    50 ===== Дневен буџет до крајот на месецот
    51 Дневен буџет за преостанатите денови од месецот врз основа на вкупниот баланс на сите сметки
     222===== Вкупно потрошено во тековниот месец за корисник
     223Вкупна сума на трошоци во тековниот месец на еден корисник
     224{{{#!sql
     225SELECT
     226    SUM(tb.spent_amount) AS total_spent
     227FROM
     228    transaction_breakdown tb
     229JOIN transaction t ON tb.transaction_id = t.transaction_id
     230JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
     231WHERE
     232    ta.user_id = 101 -- ID на конкретниот корисник
     233    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
     234    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
     235}}}
     236
     237===== Дневен буџет до крајот на месецот за корисник
     238Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот
    52239{{{#!sql
    53240SELECT
    54241    SUM(ta.balance) / (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY') - DATE_PART('day', CURRENT_DATE)) AS daily_budget
    55242FROM
    56     transaction_account ta;
    57 }}}
    58 
    59 ===== Долг на кредитна картичка од минатиот месец
     243    transaction_account ta
     244WHERE
     245    ta.user_id = 101; -- ID на конкретниот корисник
     246}}}
     247
     248===== Долг на кредитна картичка од минатиот месец за цел систем
    60249Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец
    61250{{{#!sql
    … …  
    72261}}}
    73262
    74 ===== Трендови на трошење според тагови
     263===== Долг на кредитна картичка од минатиот месец за корисник
     264Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник
     265{{{#!sql
     266SELECT
     267    SUM(tb.spent_amount) AS credit_card_debt
     268FROM
     269    transaction_breakdown tb
     270JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
     271JOIN transaction t ON tb.transaction_id = t.transaction_id
     272WHERE
     273    ta.user_id = 101 -- ID на конкретниот корисник
     274    AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
     275    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
     276    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
     277}}}
     278
     279===== Трендови на трошење според тагови за цел систем
    75280Трендови на трошење за секој таг во последните шест месеци
    76281{{{#!sql
    … …  
    81286FROM
    82287    tag tg
    83 JOIN transaction t ON tg.tag_id = t.tag_id
     288JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
     289JOIN transaction t ON tat.transaction_id = t.transaction_id
    84290JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    85291WHERE
    86292    t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
     293GROUP BY
     294    tg.tag_name, month
     295ORDER BY
     296    tg.tag_name, month;
     297}}}
     298
     299===== Трендови на трошење според тагови за корисник
     300Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник
     301{{{#!sql
     302SELECT
     303    tg.tag_name,
     304    DATE_TRUNC('month', t.date) AS month,
     305    SUM(tb.spent_amount) AS total_spent
     306FROM
     307    tag_assigned_to_transaction tat
     308JOIN transaction t ON tat.transaction_id = t.transaction_id
     309JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
     310JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
     311JOIN tag tg ON tat.tag_id = tg.tag_id
     312WHERE
     313    ta.user_id = 101 -- ID на конкретниот корисник
     314    AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
    87315GROUP BY
    88316    tg.tag_name, month
    … …  
    99327FROM
    100328    tag tg
    101 JOIN transaction t ON tg.tag_id = t.tag_id
     329JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
     330JOIN transaction t ON tat.transaction_id = t.transaction_id
    102331JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    103332WHERE
    … …  
    142371}}}
    143372
    144 ===== Трансакции според тагови со највисоко трошење
    145 {{{#!sql
    146 SELECT
    147     tg.tag_name,
    148     SUM(tb.spent_amount) AS total_spent
    149 FROM
    150     transaction_breakdown tb
    151 JOIN transaction t ON tb.transaction_id = t.transaction_id
    152 JOIN tag tg ON t.tag_id = tg.tag_id
     373===== Трансакции според тагови со највисоко трошење за цел систем
     374{{{#!sql
     375SELECT
     376    tg.tag_name,
     377    SUM(tb.spent_amount) AS total_spent
     378FROM
     379    transaction_breakdown tb
     380JOIN transaction t ON tb.transaction_id = t.transaction_id
     381JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     382JOIN tag tg ON tat.tag_id = tg.tag_id
    153383GROUP BY
    154384    tg.tag_name
    … …  
    157387}}}
    158388
    159 ===== Годишни трендови на трансакции
     389===== Трансакции според тагови со највисоко трошење за корисник
     390Сумирање на трошењата според тагови за одреден корисник
     391{{{#!sql
     392SELECT
     393    tg.tag_name,
     394    SUM(tb.spent_amount) AS total_spent
     395FROM
     396    transaction_breakdown tb
     397JOIN transaction t ON tb.transaction_id = t.transaction_id
     398JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
     399JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
     400JOIN tag tg ON tat.tag_id = tg.tag_id
     401WHERE
     402    ta.user_id = 101 -- ID на конкретниот корисник
     403GROUP BY
     404    tg.tag_name
     405ORDER BY
     406    total_spent DESC;
     407}}}
     408
     409===== Годишни трендови на трансакции за цел систем
    160410Трошења според сметки за секој квартал од тековната година
    161411{{{#!sql
    162412SELECT
    163413    ta.account_name,
    164     CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN SUM(tb.spent_amount) ELSE 0 END AS q1_spent,
    165     CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN SUM(tb.spent_amount) ELSE 0 END AS q2_spent,
    166     CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN SUM(tb.spent_amount) ELSE 0 END AS q3_spent,
    167     CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN SUM(tb.spent_amount) ELSE 0 END AS q4_spent
    168 FROM
    169     transaction_breakdown tb
    170 JOIN transaction t ON tb.transaction_id = t.transaction_id
    171 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    172 WHERE
    173     EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM NOW())
    174 GROUP BY
    175     ta.account_name, EXTRACT(QUARTER FROM t.date);
     414    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
     415    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
     416    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
     417    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
     418FROM
     419    transaction_breakdown tb
     420JOIN transaction t ON tb.transaction_id = t.transaction_id
     421JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
     422WHERE
     423    EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
     424GROUP BY
     425    ta.account_name
     426ORDER BY
     427    ta.account_name;
     428}}}
     429
     430===== Годишни трендови на трансакции за корисник
     431Трошења по квартали за тековната година за одреден корисник
     432{{{#!sql
     433SELECT
     434    ta.account_name,
     435    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
     436    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
     437    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
     438    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
     439FROM
     440    transaction_breakdown tb
     441JOIN transaction t ON tb.transaction_id = t.transaction_id
     442JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
     443WHERE
     444    ta.user_id = 101 -- ID на конкретниот корисник
     445    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
     446GROUP BY
     447    ta.account_name
     448ORDER BY
     449    ta.account_name;
    176450}}}
    177451
    … …  
    183457FROM
    184458    tag tg
    185 LEFT JOIN transaction t ON tg.tag_id = t.tag_id
     459LEFT JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
     460LEFT JOIN transaction t ON tat.transaction_id = t.transaction_id
    186461WHERE
    187462    t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';