Changes between Version 1 and Version 2 of AdvancedReport16

Timestamp:

12/29/25 19:57:04 (4 days ago)

Author:

211101

Comment:

--

AdvancedReport16

@@ -1 @@
-===== Трендови на трошење според тагови за корисник
+==== Трендови на трошење според тагови за корисник
 Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник
 {{{#!sql
-SELECT 
-    tg.tag_name, 
-    DATE_TRUNC('month', t.date) AS month,
-    SUM(tb.spent_amount) AS total_spent
-FROM 
-    tag_assigned_to_transaction tat
-JOIN transaction t ON tat.transaction_id = t.transaction_id
-JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
-JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
-JOIN tag tg ON tat.tag_id = tg.tag_id
-WHERE 
-    ta.user_id = 101 -- ID на конкретниот корисник
-    AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
-GROUP BY 
-    tg.tag_name, month
-ORDER BY 
-    tg.tag_name, month;
+CREATE OR REPLACE FUNCTION get_spending_trends_last_6_months_user(
+    p_user_id INT
+)
+RETURNS TABLE (
+    tag_name TEXT,
+    month DATE,
+    total_spent NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        tg.tag_name, 
+        DATE_TRUNC('month', t.date) AS month,
+        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
+    FROM tag_assigned_to_transaction tat
+    JOIN transaction t 
+        ON tat.transaction_id = t.transaction_id
+    JOIN transaction_breakdown tb 
+        ON t.transaction_id = tb.transaction_id
+    JOIN transaction_account ta 
+        ON tb.transaction_account_id = ta.transaction_account_id
+    JOIN tag tg 
+        ON tat.tag_id = tg.tag_id
+    WHERE ta.user_id = p_user_id
+      AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
+    GROUP BY tg.tag_name, month
+    ORDER BY tg.tag_name, month;
+END;
+$$;
 }}}
+
+==== Релациона алгебра
+- TA(transaction_account_id, user_id)
+- T(transaction_id, date)
+- TB(transaction_id, transaction_account_id, spent_amount)
+- TAT(tag_id, transaction_id)
+- TG(tag_id, tag_name)
+
+JOIN на сите табели:
+- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
+- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T
+- J3 ← J2 ⨝,,T.transaction_id = TAT.transaction_id,, TAT
+- J4 ← J3 ⨝,,TAT.tag_id = TG.tag_id,, TG
+
+Филтрирање по корисник и последни 6 месеци:
+- F ← σ,,user_id = p_user_id ∧ date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS'),,(J4)
+
+Групирање по таг и месец:
+- G ← γ,,tag_name, month = DATE_TRUNC('month', date); Σ(spent_amount) → total_spent,,(F)
+
+Подредување по таг и месец:
+- R_final ← τ,,tag_name, month,,(G)