Changes between Version 1 and Version 2 of AdvancedReport10

Timestamp:

12/29/25 19:41:15 (5 days ago)

Author:

211101

Comment:

--

AdvancedReport10

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