Changes between Version 1 and Version 2 of AdvancedReport6

Timestamp:

12/29/25 18:58:53 (4 days ago)

Author:

211101

Comment:

--

AdvancedReport6

@@ -1 @@
-===== Број на трансакции што ги надминуваат приходите на сметка
+==== Број на трансакции што ги надминуваат приходите на сметка
 {{{#!sql
-WITH CumulativeBalances AS (
-    SELECT 
-        t.transaction_id,
-        ta.account_name,
-        SUM(tb.earned_amount - tb.spent_amount) OVER (
-            PARTITION BY ta.transaction_account_id
-            ORDER BY t.date
-        ) AS calculated_balance,
-        tb.spent_amount
-    FROM 
-        transaction_account ta
-    JOIN 
-        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
-    JOIN 
-        transaction t ON tb.transaction_id = t.transaction_id
+CREATE OR REPLACE FUNCTION get_account_transaction_overdraft_counts()
+RETURNS TABLE (
+    account_name TEXT,
+    transactions_exceeding_balance INT
 )
-SELECT 
-    account_name, 
-    COUNT(transaction_id) AS transactions_exceeding_balance
-FROM 
-    CumulativeBalances
-WHERE 
-    spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс
-    AND spent_amount > 0
-GROUP BY 
-    account_name
-ORDER BY 
-    transactions_exceeding_balance DESC;
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    WITH cumulative_balances AS (
+        SELECT
+            ta.account_name,
+            t.transaction_id,
+            tb.spent_amount,
+            SUM(tb.earned_amount - tb.spent_amount)
+                OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date)
+                AS calculated_balance
+        FROM transaction_account ta
+        JOIN transaction_breakdown tb
+            ON ta.transaction_account_id = tb.transaction_account_id
+        JOIN transaction t
+            ON tb.transaction_id = t.transaction_id
+    )
+    SELECT
+        account_name,
+        COUNT(transaction_id) AS transactions_exceeding_balance
+    FROM cumulative_balances
+    WHERE
+        spent_amount > calculated_balance
+        AND spent_amount > 0
+    GROUP BY account_name
+    ORDER BY transactions_exceeding_balance DESC;
+END;
+$$;
 }}}
-Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
+
+==== Релациона алгебра
+- TA(transaction_account_id, account_name)
+- TB(transaction_id, transaction_account_id, spent_amount, earned_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
+
+Пресметка на кумулативен баланс по сметка:
+- CB ← γ,,account_name, transaction_id, spent_amount; Σ(earned_amount - spent_amount) OVER (PARTITION BY transaction_account_id ORDER BY date) → calculated_balance,,(J2)
+
+Филтрирање на трансакции што го надминуваат балансот:
+- F ← σ,,spent_amount > calculated_balance ∧ spent_amount > 0,,(CB)
+
+Броење на трансакции по сметка:
+- R ← γ,,account_name; COUNT(transaction_id) → transactions_exceeding_balance,,(F)
+
+Подредување по број на прекршувања:
+- R_final ← τ,,transactions_exceeding_balance DESC,,(R)
+
+== 
+
+> Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
 {{{#!sql
-WITH CumulativeTotalBalances AS (
-    SELECT 
-        t.transaction_id,
-        u.user_id,
-        SUM(tb.earned_amount - tb.spent_amount) OVER (
-            PARTITION BY u.user_id
-            ORDER BY t.date
-        ) AS calculated_total_balance,
-        tb.spent_amount
-    FROM 
-        transaction_account ta
-    JOIN 
-        user u ON ta.user_id = u.user_id
-    JOIN 
-        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
-    JOIN 
-        transaction t ON tb.transaction_id = t.transaction_id
+CREATE OR REPLACE FUNCTION get_total_transaction_overdraft_count()
+RETURNS TABLE (
+    total_transactions_exceeding_balance INT
 )
-SELECT 
-    COUNT(transaction_id) AS total_transactions_exceeding_balance
-FROM 
-    CumulativeTotalBalances
-WHERE 
-    spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс
-    AND spent_amount > 0;
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    WITH cumulative_total_balances AS (
+        SELECT
+            t.transaction_id,
+            tb.spent_amount,
+            SUM(tb.earned_amount - tb.spent_amount)
+                OVER (PARTITION BY u.user_id ORDER BY t.date)
+                AS calculated_total_balance
+        FROM transaction_account ta
+        JOIN "user" u
+            ON ta.user_id = u.user_id
+        JOIN transaction_breakdown tb
+            ON ta.transaction_account_id = tb.transaction_account_id
+        JOIN transaction t
+            ON tb.transaction_id = t.transaction_id
+    )
+    SELECT
+        COUNT(transaction_id) AS total_transactions_exceeding_balance
+    FROM cumulative_total_balances
+    WHERE
+        spent_amount > calculated_total_balance
+        AND spent_amount > 0;
+END;
+$$;
 }}}
+
+==== Релациона алгебра
+- U(user_id)
+- TA(transaction_account_id, user_id)
+- TB(transaction_id, transaction_account_id, spent_amount, earned_amount)
+- T(transaction_id, date)
+
+JOIN на сите табели:
+- J1 ← TA ⨝,,TA.user_id = U.user_id,, U
+- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
+- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T
+
+Пресметка на кумулативен вкупен баланс по корисник:
+- CT ← γ,,transaction_id, spent_amount; Σ(earned_amount - spent_amount) OVER (PARTITION BY user_id ORDER BY date) → calculated_total_balance,,(J3)
+
+Филтрирање на трансакции што го надминуваат вкупниот баланс:
+- F ← σ,,spent_amount > calculated_total_balance ∧ spent_amount > 0,,(CT)
+
+Броење на сите прекршувачки трансакции:
+- R_final ← γ,,; COUNT(transaction_id) → total_transactions_exceeding_balance,,(F)