Changes between Version 3 and Version 4 of UseCaseScenarios/scenario3

Timestamp:

09/28/25 20:38:14 (35 hours ago)

Author:

221007

Comment:

--

UseCaseScenarios/scenario3

@@ -154 +154 @@
                   FROM criminal) AS DOUBLE PRECISION) AS resolution_ratio
 }}}
+
+3. Во делот со статистиките се бираат персони кои имаат слични дијагнози, со цел да се има некаква особа за консултирање доколку е потребно или за споредба. Истото тоа се прави со следното квери:
+
+{{{
+with selected_person_diagnosis as(
+                select distinct d.diagnosis_id as diagnosis_id, d.short_description as label
+                from person p
+                         join report r on r.person_id = p.person_id
+                         join medicalreport_diagnosis mrd on mrd.report_id = r.report_id
+                         join diagnosis d on mrd.diagnosis_id = d.diagnosis_id
+                where p.person_id = :person_id
+            )
+            select cast(p2.person_id as bigint),
+                 p2.name || ' ' || p2.surname as full_name,
+                 cast(count(distinct spd.diagnosis_id) as bigint) as matching_diagnoses_count,
+                 string_agg(distinct spd.label, ', ') as matching_labels
+            from selected_person_diagnosis spd
+                     join medicalreport_diagnosis mrd2 on mrd2.diagnosis_id = spd.diagnosis_id
+                     join report r2 on r2.report_id = mrd2.report_id
+                     join person p2 on p2.person_id = r2.person_id
+            where p2.person_id != :person_id
+            group by p2.person_id, p2.name, p2.surname
+            having count(distinct spd.diagnosis_id) >=1
+            order by matching_diagnoses_count desc;
+}}}
+
+4. Исто така постои и квери кое се користи за правење на споредба и подредување за топ 3 институции за школување:
+
+{{{
+                WITH top_3_institutions AS (
+                  SELECT ar.institution_id, COUNT(*) AS total_reports
+                  FROM report r
+                  JOIN academicreport ar ON ar.report_id = r.report_id
+                  WHERE r.created_at >= date_trunc('year', now()) - interval '1 year'
+                  GROUP BY ar.institution_id
+                  ORDER BY COUNT(*) DESC
+                  LIMIT 3
+                )
+                SELECT i.name, a.total_reports
+                FROM top_3_institutions a
+                JOIN institution i ON i.institution_id = a.institution_id
+                ORDER BY a.total_reports DESC;
+}}}