Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

02/06/25 17:57:07 (2 weeks ago)

Author:

221028

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Напредни извештаи од базата (SQL и складирани процедури) =
 
-=== Извештај за студенти и бројот на експериментите кои ги направиле за една недела ===
+=== Извештај за студенти и бројот на експериментите кои ги извршиле ===
 {{{#!sql
 SELECT 
-    u.user_name,
-    u.user_surname,
-    COUNT(e.experiment_id) AS experiment_count
-FROM 
-    User u
-JOIN 
-    Experiment e ON u.user_id = e.user_id
+    s.student_id,
+    u.user_name || ' ' || u.user_surname AS full_name,
+    COUNT(up.experiment_id) AS total_experiments
+FROM Student s
+JOIN "User" u ON s.student_id = u.user_id
+JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
+JOIN Experiment e ON up.experiment_id = e.experiment_id
 WHERE 
-    u.professor_username = 'profesor_username'  -- Тука треба да се замени со корисничкото име на професорот
-    AND e.time_stamp >= NOW() - INTERVAL 1 WEEK  -- Филтер за експерименти студентите што ги направиле во последната недела
-GROUP BY 
-    u.user_name, u.user_surname
-ORDER BY 
-    experiment_count DESC;
+    s.teacher_id = :teacher_id  -- Замени со ID на професорот
+GROUP BY s.student_id, full_name
+ORDER BY total_experiments DESC;
 
 }}}
@@ -39 +36 @@
 {{{#!sql
 SELECT 
-    u.user_name,
-    u.user_surname,
-    GROUP_CONCAT(e.result SEPARATOR ', ') AS results
-FROM 
-    Users u
-JOIN 
-    Experiment e ON u.user_id = e.user_id
-WHERE 
-    u.professor_username = 'profesor_username'  -- Тука треба да се замени со корисничкото име 
-GROUP BY 
-    u.user_name, u.user_surname;
+    s.student_id,
+    u.user_name || ' ' || u.user_surname AS full_name,
+    e.experiment_id,
+    e.result,
+    up.participation_timestamp AS participation_time
+FROM Student s
+JOIN "User" u ON s.student_id = u.user_id
+JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
+JOIN Experiment e ON up.experiment_id = e.experiment_id
+ORDER BY u.user_name, up.participation_timestamp DESC;
+
 }}}
 
@@ -56 +53 @@
 {{{#!sql
 SELECT 
-    u.user_name,
-    u.user_surname
-FROM 
-    Users u
-JOIN 
-    Experiment e ON u.user_id = e.user_id
-JOIN 
-    Reaction r ON e.reaction_id = r.reaction_id
-WHERE 
-     u.professor_username = 'profesor_username'  -- Тука треба да се замени со корисничкото име 
-    AND r.product = 'H2O'  -- Тука треба да се замени со името на производот од реакцијата
-ORDER BY 
-    u.user_surname ASC;
+    s.student_id,
+    u.user_name || ' ' || u.user_surname AS full_name,
+    e.experiment_id,
+    e.result
+FROM Student s
+JOIN "User" u ON s.student_id = u.user_id
+JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
+JOIN Experiment e ON up.experiment_id = e.experiment_id
+ORDER BY u.user_name;
+
 }}}
 
@@ -75 +69 @@
 {{{#!sql
 SELECT 
-    AVG(ele_count) AS average_instruments_per_experiment
+    AVG(instrument_count) AS average_lab_equipment_per_experiment
 FROM (
     SELECT 
-        e.experiment_id,
-        COUNT(lee.equipment_id) AS ele_count
-    FROM 
-        Experiment e
-    JOIN 
-        ExperimentLabEquipment lee ON e.experiment_id = lee.experiment_id
-    GROUP BY 
-        e.experiment_id
-) AS subquery;
+        e.experiment_id, 
+        COUNT(ele.equipment_id) AS instrument_count
+    FROM Experiment e
+    LEFT JOIN ExperimentLabEquipment ele ON e.experiment_id = ele.experiment_id
+    GROUP BY e.experiment_id
+) subquery;
+
 }}}
 
+=== Извештај за најчесто користени хемиски елементи во екпериментите ===
+
+{{{#!sql
+SELECT 
+    el.element_name,
+    COUNT(r.reaction_id) AS total_uses
+FROM Elements el
+JOIN Reaction r ON el.element_id = r.element1_id OR el.element_id = r.element2_id
+GROUP BY el.element_name
+ORDER BY total_uses DESC;
+
+}}}
+
+=== Извештај за најчесто реализирани експерименти ===
+
+{{{#!sql
+SELECT 
+    e.experiment_id,
+    e.result,
+    COUNT(up.user_id) AS student_participation
+FROM Experiment e
+JOIN UserParticipatesInExperiment up ON e.experiment_id = up.experiment_id
+GROUP BY e.experiment_id, e.result
+ORDER BY student_participation DESC;
+
+}}}
+
+=== Извештај за студенти кои никогаш не учествувале во екперименти ===
+
+{{{#!sql
+SELECT 
+    s.student_id,
+    u.user_name || ' ' || u.user_surname AS full_name
+FROM Student s
+JOIN "User" u ON s.student_id = u.user_id
+LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
+WHERE s.teacher_id = :teacher_id  -- Замени со ID на професорот
+AND up.user_id IS NULL;
+
+}}}
+
+=== Извештај за студенти кои имаат учествувано во помалку од одреден број екперименти  ===
+
+{{{#!sql
+SELECT 
+    s.student_id,
+    u.user_name || ' ' || u.user_surname AS full_name,
+    COUNT(up.experiment_id) AS total_experiments
+FROM Student s
+JOIN "User" u ON s.student_id = u.user_id
+LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
+GROUP BY s.student_id, full_name
+HAVING COUNT(up.experiment_id) < 3 
+ORDER BY total_experiments ASC;
+
+}}}
+
+=== Извештај за број на студенти по професор и просечен број на екперименти по студент  ===
+
+{{{#!sql
+SELECT 
+    t.teacher_id,
+    u.user_name || ' ' || u.user_surname AS full_name,
+    COUNT(DISTINCT s.student_id) AS total_students,
+    COUNT(up.experiment_id) AS total_experiments,
+    ROUND(
+        COUNT(up.experiment_id) * 1.0 / NULLIF(COUNT(DISTINCT s.student_id), 0), 2
+    ) AS avg_experiments_per_student
+FROM Teacher t
+JOIN "User" u ON t.teacher_id = u.user_id
+LEFT JOIN Student s ON t.teacher_id = s.teacher_id
+LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
+GROUP BY t.teacher_id, full_name
+ORDER BY avg_experiments_per_student DESC;
+
+}}}
+
+=== Извештај за број на студентите бројот на прегледани хемиски елементи и лабараториска опрема ===
+
+{{{#!sql
+SELECT 
+    s.student_id,
+    u.user_name || ' ' || u.user_surname AS full_name,
+    COUNT(DISTINCT ue.element_id) AS total_elements_viewed,
+    COUNT(DISTINCT ul.equipment_id) AS total_lab_equipment_viewed
+FROM Student s
+JOIN "User" u ON s.student_id = u.user_id
+LEFT JOIN UserViewsElement ue ON s.student_id = ue.user_id
+LEFT JOIN UserViewsLabEquipment ul ON s.student_id = ul.user_id
+GROUP BY s.student_id, full_name
+ORDER BY total_elements_viewed DESC, total_lab_equipment_viewed DESC;
+
+}}}
+
+
+
+