Changes between Version 1 and Version 2 of AdvancedReport24

Timestamp:

12/29/25 20:13:42 (5 days ago)

Author:

211101

Comment:

--

AdvancedReport24

@@ -1 @@
-===== Неактивни тагови
+==== Неактивни тагови
 Идентификување тагови кои не биле користени во изминатиот месец
 {{{#!sql
-SELECT 
-    tg.tag_name
-FROM 
-    tag tg
-LEFT JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
-LEFT JOIN transaction t ON tat.transaction_id = t.transaction_id
-WHERE 
-    t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';
+CREATE OR REPLACE FUNCTION get_inactive_tags_last_month()
+RETURNS TABLE (
+    tag_name TEXT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        tg.tag_name
+    FROM tag tg
+    LEFT JOIN tag_assigned_to_transaction tat 
+        ON tg.tag_id = tat.tag_id
+    LEFT JOIN transaction t 
+        ON tat.transaction_id = t.transaction_id
+    WHERE t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';
+END;
+$$;
 }}}
+
+==== Релациона алгебра
+- TG(tag_id, tag_name)
+- TAT(tag_id, transaction_id)
+- T(transaction_id, date)
+
+LEFT JOIN на табелите:
+- J1 ← TG ⟕,,TG.tag_id = TAT.tag_id,, TAT
+- J2 ← J1 ⟕,,TAT.transaction_id = T.transaction_id,, T
+
+Филтрирање на неактивни тагови:
+- R_final ← σ,,date IS NULL ∨ date < NOW() - INTERVAL '1 MONTH',,(J2)