Changes between Version 1 and Version 2 of AdvancedReport19

Timestamp:

12/29/25 20:07:00 (4 days ago)

Author:

211101

Comment:

--

AdvancedReport19

@@ -1 @@
-===== Најчести времиња за трансакции
+==== Најчести времиња за трансакции
 Во кој час од денот корисниците најчесто вршат трансакции
 {{{#!sql
-SELECT 
-    EXTRACT(HOUR FROM t.date) AS transaction_hour, 
-    COUNT(*) AS transaction_count
-FROM 
-    transaction t
-GROUP BY 
-    transaction_hour
-ORDER BY 
-    transaction_count DESC;
+CREATE OR REPLACE FUNCTION get_most_frequent_transaction_hours()
+RETURNS TABLE (
+    transaction_hour INT,
+    transaction_count BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        EXTRACT(HOUR FROM t.date)::INT AS transaction_hour, 
+        COUNT(*) AS transaction_count
+    FROM transaction t
+    GROUP BY transaction_hour
+    ORDER BY transaction_count DESC;
+END;
+$$;
 }}}
+
+==== Релациона алгебра
+- T(transaction_id, date)
+
+Групирање по час на трансакција:
+- G ← γ,,transaction_hour = EXTRACT(HOUR FROM date); COUNT(transaction_id) → transaction_count,,(T)
+
+Подредување по број на трансакции:
+- R_final ← τ,,transaction_count DESC,,(G)