Changes between Version 1 and Version 2 of AdvancedReport17

Timestamp:

12/29/25 20:03:58 (4 days ago)

Author:

211101

Comment:

--

AdvancedReport17

@@ -1 @@
-===== Вкупно трошење според тагови
+==== Вкупно трошење според тагови
 Вкупно трошење групирано според тагови за тековниот месец
 {{{#!sql
-SELECT 
-    tg.tag_name,
-    SUM(tb.spent_amount) AS total_spent
-FROM 
-    tag tg
-JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
-JOIN transaction t ON tat.transaction_id = t.transaction_id
-JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
-WHERE 
-    EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
-    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
-GROUP BY 
-    tg.tag_name
-ORDER BY 
-    total_spent DESC;
+CREATE OR REPLACE FUNCTION get_total_spent_by_tag_current_month()
+RETURNS TABLE (
+    tag_name TEXT,
+    total_spent NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        tg.tag_name,
+        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
+    FROM tag tg
+    JOIN tag_assigned_to_transaction tat 
+        ON tg.tag_id = tat.tag_id
+    JOIN transaction t 
+        ON tat.transaction_id = t.transaction_id
+    JOIN transaction_breakdown tb 
+        ON t.transaction_id = tb.transaction_id
+    WHERE EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
+      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
+    GROUP BY tg.tag_name
+    ORDER BY total_spent DESC;
+END;
+$$;
 }}}
+
+==== Релациона алгебра
+- TG(tag_id, tag_name)
+- TAT(tag_id, transaction_id)
+- T(transaction_id, date)
+- TB(transaction_id, spent_amount)
+
+JOIN на сите табели:
+- J1 ← TG ⨝,,TG.tag_id = TAT.tag_id,, TAT
+- J2 ← J1 ⨝,,TAT.transaction_id = T.transaction_id,, T
+- J3 ← J2 ⨝,,T.transaction_id = TB.transaction_id,, TB
+
+Филтрирање по тековен месец и година:
+- F ← σ,,EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J3)
+
+Групирање и агрегација по таг:
+- G ← γ,,tag_name; SUM(spent_amount) → total_spent,,(F)
+
+Подредување по вкупно потрошено:
+- R_final ← τ,,total_spent DESC,,(G)