Changes between Version 1 and Version 2 of AdvancedReport9

Timestamp:

12/29/25 19:31:23 (5 days ago)

Author:

211101

Comment:

--

AdvancedReport9

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