Changes between Initial Version and Version 1 of AdvancedReport25

Timestamp:

12/29/25 19:38:40 (5 days ago)

Author:

211101

Comment:

--

AdvancedReport25

@@ +1 @@
+==== Просечно трошење во последните N дена
+- по корисник (со параметар p_user_id)
+- за цел систем (со параметар p_user_id = NULL)
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_average_daily_spending_last_N_days(
+    p_days INT,
+    p_user_id INT DEFAULT NULL  -- ако е NULL, се зема цел систем
+)
+RETURNS TABLE (
+    average_spending NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        SUM(daily_spending) / p_days AS average_spending
+    FROM (
+        -- генерација на последни p_days
+        SELECT
+            d.day::date AS transaction_date,
+            COALESCE(SUM(tb.spent_amount), 0) AS daily_spending
+        FROM (
+            SELECT generate_series(CURRENT_DATE - (p_days - 1), CURRENT_DATE, '1 day') AS day
+        ) d
+        LEFT JOIN transaction t
+            ON t.date::date = d.day
+        LEFT JOIN transaction_breakdown tb
+            ON t.transaction_id = tb.transaction_id
+        LEFT JOIN transaction_account ta
+            ON tb.transaction_account_id = ta.transaction_account_id
+            AND (p_user_id IS NULL OR ta.user_id = p_user_id)
+        GROUP BY d.day
+    ) daily_totals;
+END;
+$$;
+}}}
+
+==== Релациона алгебра
+- TA(transaction_account_id, user_id)
+- T(transaction_id, date)
+- TB(transaction_id, spent_amount)
+- D(day) -- генерација на последните p_days
+
+Генерација на последни p_days:
+- D ← { CURRENT_DATE − (p_days − 1), ..., CURRENT_DATE }
+
+LEFT JOIN со трансакции:
+- J1 ← D ⟕,,TRUE,, T
+- J2 ← J1 ⟕,,T.transaction_id = TB.transaction_id,, TB
+- J3 ← J2 ⟕,,TB.transaction_account_id = TA.transaction_account_id ∧ (p_user_id IS NULL OR TA.user_id = p_user_id),, TA
+
+Групирање по ден и пресметка на дневно трошење:
+- G ← γ,,day; Σ(spent_amount) → daily_spending,,(J3)
+
+Просечно дневно трошење (фиксно делење со p_days):
+- R_final ← γ,,; (Σ(daily_spending) / p_days) → average_spending,,(G)