Changes between Version 1 and Version 2 of AdvancedReport23

Timestamp:

12/29/25 20:12:35 (5 days ago)

Author:

211101

Comment:

--

AdvancedReport23

@@ -1 @@
-===== Годишни трендови на трансакции за корисник
+==== Годишни трендови на трансакции за корисник
 Трошења по квартали за тековната година за одреден корисник
 {{{#!sql
-SELECT 
-    ta.account_name, 
-    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
-    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
-    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
-    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
-FROM 
-    transaction_breakdown tb
-JOIN transaction t ON tb.transaction_id = t.transaction_id
-JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
-WHERE 
-    ta.user_id = 101 -- ID на конкретниот корисник
-    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
-GROUP BY 
-    ta.account_name
-ORDER BY 
-    ta.account_name;
+CREATE OR REPLACE FUNCTION get_quarterly_spending_trends_user(
+    p_user_id INT
+)
+RETURNS TABLE (
+    account_name TEXT,
+    q1_spent NUMERIC,
+    q2_spent NUMERIC,
+    q3_spent NUMERIC,
+    q4_spent NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        ta.account_name, 
+        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
+        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
+        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
+        SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
+    FROM transaction_breakdown tb
+    JOIN transaction t 
+        ON tb.transaction_id = t.transaction_id
+    JOIN transaction_account ta 
+        ON tb.transaction_account_id = ta.transaction_account_id
+    WHERE ta.user_id = p_user_id
+      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
+    GROUP BY ta.account_name
+    ORDER BY ta.account_name;
+END;
+$$;
 }}}
+
+==== Релациона алгебра
+- TA(transaction_account_id, user_id, account_name)
+- TB(transaction_id, transaction_account_id, spent_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
+
+Филтрирање по корисник и тековна година:
+- F ← σ,,user_id = p_user_id ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J2)
+
+Агрегација по квартали:
+- G ← γ,,account_name; SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 1 THEN spent_amount ELSE 0 END) → q1_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 2 THEN spent_amount ELSE 0 END) → q2_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 3 THEN spent_amount ELSE 0 END) → q3_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 4 THEN spent_amount ELSE 0 END) → q4_spent,,(F)
+
+Подредување по име на сметка:
+- R_final ← τ,,account_name,,(G)