Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)

Сите извештаи подолу се напишани и тестирани врз шемата project на доделената проектна база, врз податоците од data_load.sql​.

Секој извештај е даден во три форми:

• SQL - барањето како што се извршува врз базата
• Релациона алгебра - истото барање изразено со операторите на релациона алгебра
• Складирана процедура - барањето спакувано како рутина во базата, за да апликацијата го повикува со едно име наместо да го носи барањето во кодот

Сите рутини се во скриптата reports.sql​, која се стартува по schema_creation.sql и data_load.sql. Скриптата е повторлива - секоја рутина се креира со OR REPLACE.

Заедничка забелешка за сите извештаи: оценката е од набројувачки тип (grade_type), па за пресметка на просек мора двојно да се претвори - ps.grade::TEXT::INT.

1. Проодност по предмет и по семестар со отстапување од просекот на предметот

Опис на извештајот

За секој предмет и секој семестар во кој тој бил слушан се прикажува колку студенти го запишале, колку добиле потпис, колку го положиле, процентот на проодност и просечната оценка. Последната колона покажува колку просекот во тој семестар отстапува од вкупниот просек на предметот, што покажува дали предметот во определен семестар бил полесен или потежок од вообичаеното.

SQL

SET search_path TO project;

WITH enrolled_per_semester AS (
    SELECT ss.subjects_id                      AS subject_id,
           es.semester_id                      AS semester_id,
           COUNT(ss.id)                        AS enrolled,
           COUNT(ps.id)                        AS passed,
           ROUND(AVG(ps.grade::TEXT::INT), 2)  AS average_grade
    FROM semesters_subjects ss
    JOIN enrolled_semesters es   ON es.id = ss.enrolled_semesters_id
    LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
    GROUP BY ss.subjects_id, es.semester_id
),
signed_per_semester AS (
    SELECT ss.subjects_id  AS subject_id,
           es.semester_id  AS semester_id,
           COUNT(ss.id)    AS signed_count
    FROM semesters_subjects ss
    JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
    WHERE ss.signature = TRUE
    GROUP BY ss.subjects_id, es.semester_id
),
subject_average AS (
    SELECT ss.subjects_id                      AS subject_id,
           ROUND(AVG(ps.grade::TEXT::INT), 2)  AS subject_average
    FROM semesters_subjects ss
    JOIN passed_subjects ps ON ps.enrolled_id = ss.id
    GROUP BY ss.subjects_id
)
SELECT s.code                                                      AS subject_code,
       s.name                                                      AS subject,
       a.year,
       a.type                                                      AS semester_type,
       eps.enrolled,
       COALESCE(sps.signed_count, 0)                               AS signed_count,
       eps.passed,
       ROUND(100.0 * eps.passed / eps.enrolled, 1)                 AS pass_rate,
       COALESCE(CAST(eps.average_grade AS VARCHAR), 'нема оценки')  AS average_grade,
       COALESCE(CAST(eps.average_grade - sa.subject_average AS VARCHAR), 'n/a') AS deviation
FROM enrolled_per_semester eps
JOIN subjects s         ON s.id = eps.subject_id
JOIN active_semesters a ON a.id = eps.semester_id
LEFT JOIN signed_per_semester sps ON sps.subject_id  = eps.subject_id
                                 AND sps.semester_id = eps.semester_id
LEFT JOIN subject_average sa      ON sa.subject_id   = eps.subject_id
ORDER BY a.year, a.type, pass_rate DESC, s.code;

Релациона алгебра

EnrolledPerSemester <-
γ subject_id := ss.subjects_id,
  semester_id := es.semester_id;
  enrolled := COUNT(ss.id),
  passed := COUNT(ps.id),
  average_grade := AVG(ps.grade)
(
  (
    semesters_subjects ss
    ⨝ (ss.enrolled_semesters_id = es.id) enrolled_semesters es
  )
  ⟕ (ss.id = ps.enrolled_id) passed_subjects ps
)

SignedPerSemester <-
γ subject_id := ss.subjects_id,
  semester_id := es.semester_id;
  signed_count := COUNT(ss.id)
(
  σ ss.signature = TRUE
  (
    semesters_subjects ss
    ⨝ (ss.enrolled_semesters_id = es.id) enrolled_semesters es
  )
)

SubjectAverage <-
γ subject_id := ss.subjects_id;
  subject_average := AVG(ps.grade)
(
  semesters_subjects ss ⨝ (ss.id = ps.enrolled_id) passed_subjects ps
)

Result <-
τ a.year, a.type, pass_rate DESC, s.code
(
  π s.code,
    s.name,
    a.year,
    a.type,
    eps.enrolled,
    sps.signed_count,
    eps.passed,
    pass_rate := (eps.passed * 100) / eps.enrolled,
    eps.average_grade,
    deviation := eps.average_grade − sa.subject_average
  (
    (
      (
        (
          EnrolledPerSemester eps ⨝ (eps.subject_id = s.id) subjects s
        )
        ⨝ (eps.semester_id = a.id) active_semesters a
      )
      ⟕ (eps.subject_id = sps.subject_id ∧
         eps.semester_id = sps.semester_id) SignedPerSemester sps
    )
    ⟕ (eps.subject_id = sa.subject_id) SubjectAverage sa
  )
)

Складирана процедура

CREATE OR REPLACE FUNCTION rep_subject_pass_rate()
    RETURNS TABLE (
        subject_code   VARCHAR,
        subject        VARCHAR,
        year           INTEGER,
        semester_type  semester_type,
        enrolled       BIGINT,
        signed_count   BIGINT,
        passed         BIGINT,
        pass_rate      NUMERIC,
        average_grade  VARCHAR,
        deviation      VARCHAR
    )
    LANGUAGE sql
    STABLE
    SET search_path = project
AS $$
    -- барањето прикажано погоре
$$;

SELECT * FROM rep_subject_pass_rate();

Функцијата е означена како STABLE бидејќи само чита, и има сопствена патека SET search_path = project, па повикувачот не мора однапред да ја постави.

Резултат врз тест податоците

 subject_code |           subject           | year | semester_type | enrolled | signed_count | passed | pass_rate | average_grade | deviation
--------------+-----------------------------+------+---------------+----------+--------------+--------+-----------+---------------+-----------
 F18L1S001    | Structured Programming      | 2024 | winter        |        3 |            3 |      3 |     100.0 | 8.67          | 0.00
 F18L2S011    | Operating Systems           | 2024 | winter        |        1 |            1 |      1 |     100.0 | 8.00          | 0.00
 F18L2S010    | Databases                   | 2024 | winter        |        1 |            0 |      0 |       0.0 | нема оценки   | n/a
 F18L1S002    | Object Oriented Programming | 2025 | summer        |        1 |            0 |      0 |       0.0 | нема оценки   | n/a

2. Досие на студент - студии, финансии и документи во еден ред

Опис на извештајот

За секој студент се собираат податоци кои инаку се расфрлани низ пет различни табели: колку семестри запишал и колку завршил, колку предмети положил, колку му остануваат неположени, колку кредити собрал, каков просек има, колку вкупно платил и колку документи подигнал. Секој од овие броеви се пресметува во посебен подизраз, а потоа сите се спојуваат со надворешно спојување, за студент кој нема ниту еден запис во некоја од табелите сепак да се појави во извештајот.

SQL

SET search_path TO project;

WITH passed_stats AS (
    SELECT es.user_id,
           COUNT(ps.id)                        AS passed_subjects,
           SUM(s.awarded_credits)              AS credits,
           ROUND(AVG(ps.grade::TEXT::INT), 2)  AS average_grade
    FROM enrolled_semesters es
    JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id
    JOIN passed_subjects ps    ON ps.enrolled_id = ss.id
    JOIN subjects s            ON s.id = ss.subjects_id
    GROUP BY es.user_id
),
semester_stats AS (
    SELECT es.user_id,
           COUNT(es.id)        AS enrolled_semesters,
           COUNT(es.completed) AS completed_semesters
    FROM enrolled_semesters es
    GROUP BY es.user_id
),
pending_stats AS (
    SELECT es.user_id,
           COUNT(ss.id) AS pending_subjects
    FROM enrolled_semesters es
    JOIN semesters_subjects ss   ON ss.enrolled_semesters_id = es.id
    LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
    WHERE ps.id IS NULL
    GROUP BY es.user_id
),
payment_stats AS (
    SELECT es.user_id,
           SUM(p.amount) AS total_paid
    FROM payment p
    JOIN enrolled_semesters es ON es.id = p.enrollment_id
    GROUP BY es.user_id
),
document_stats AS (
    SELECT ud.user_id,
           COUNT(ud.document_id) AS num_documents,
           SUM(d.cost)           AS documents_cost
    FROM user_documents ud
    JOIN documents d ON d.id = ud.document_id
    GROUP BY ud.user_id
)
SELECT u."index"                                                  AS student_index,
       u.name || ' ' || u.surname                                 AS student,
       COALESCE(sem.enrolled_semesters, 0)                        AS enrolled_semesters,
       COALESCE(sem.completed_semesters, 0)                       AS completed_semesters,
       COALESCE(ps.passed_subjects, 0)                            AS passed_subjects,
       COALESCE(pen.pending_subjects, 0)                          AS pending_subjects,
       COALESCE(ps.credits, 0)                                    AS credits,
       COALESCE(CAST(ps.average_grade AS VARCHAR), 'нема оценки')  AS average_grade,
       COALESCE(pay.total_paid, 0)                                AS total_paid,
       COALESCE(doc.num_documents, 0)                             AS num_documents,
       COALESCE(doc.documents_cost, 0)                            AS documents_cost
FROM users u
LEFT JOIN passed_stats ps    ON ps.user_id  = u.id
LEFT JOIN semester_stats sem ON sem.user_id = u.id
LEFT JOIN pending_stats pen  ON pen.user_id = u.id
LEFT JOIN payment_stats pay  ON pay.user_id = u.id
LEFT JOIN document_stats doc ON doc.user_id = u.id
WHERE u.role = 'student'
ORDER BY COALESCE(ps.credits, 0) DESC, COALESCE(ps.average_grade, 0) DESC, u."index";

Подредувањето намерно оди по нумеричките колони од подизразите, а не по излезните колони. Излезната колона average_grade е претворена во текст заради вредноста 'нема оценки', па подредувањето по неа би било азбучно и оценката 9.00 би излегла пред 10.00.

Плаќањата се врзуваат за студентот преку enrolled_semesters, а не преку payment.user_id, согласно нормализираниот модел од Фаза 5 во кој таа колона е отстранета.

Релациона алгебра

PassedStats <-
γ user_id := es.user_id;
  passed_subjects := COUNT(ps.id),
  credits := SUM(s.awarded_credits),
  average_grade := AVG(ps.grade)
(
  (
    (enrolled_semesters es ⨝ (es.id = ss.enrolled_semesters_id) semesters_subjects ss)
    ⨝ (ss.id = ps.enrolled_id) passed_subjects ps
  )
  ⨝ (ss.subjects_id = s.id) subjects s
)

SemesterStats <-
γ user_id := es.user_id;
  enrolled_semesters := COUNT(es.id),
  completed_semesters := COUNT(es.completed)
(
  enrolled_semesters es
)

PendingStats <-
γ user_id := es.user_id;
  pending_subjects := COUNT(ss.id)
(
  σ ps.id IS NULL
  (
    (enrolled_semesters es ⨝ (es.id = ss.enrolled_semesters_id) semesters_subjects ss)
    ⟕ (ss.id = ps.enrolled_id) passed_subjects ps
  )
)

PaymentStats <-
γ user_id := es.user_id;
  total_paid := SUM(p.amount)
(
  payment p ⨝ (p.enrollment_id = es.id) enrolled_semesters es
)

DocumentStats <-
γ user_id := ud.user_id;
  num_documents := COUNT(ud.document_id),
  documents_cost := SUM(d.cost)
(
  user_documents ud ⨝ (ud.document_id = d.id) documents d
)

Result <-
τ credits DESC, average_grade DESC, u.index
(
  π u.index,
    student := u.name || ' ' || u.surname,
    sem.enrolled_semesters,
    sem.completed_semesters,
    ps.passed_subjects,
    pen.pending_subjects,
    ps.credits,
    ps.average_grade,
    pay.total_paid,
    doc.num_documents,
    doc.documents_cost
  (
    σ u.role = 'student'
    (
      (
        (
          (
            (
              users u ⟕ (u.id = ps.user_id) PassedStats ps
            )
            ⟕ (u.id = sem.user_id) SemesterStats sem
          )
          ⟕ (u.id = pen.user_id) PendingStats pen
        )
        ⟕ (u.id = pay.user_id) PaymentStats pay
      )
      ⟕ (u.id = doc.user_id) DocumentStats doc
    )
  )
)

Складирана процедура

Извештајот е даден и како функција со параметар, за апликацијата со истата рутина да добие и едно досие и списокот на сите студенти:

CREATE OR REPLACE FUNCTION rep_student_dossier(p_user_id INTEGER DEFAULT NULL)
    RETURNS TABLE (
        student_index        VARCHAR,
        student              TEXT,
        enrolled_semesters   BIGINT,
        completed_semesters  BIGINT,
        passed_subjects      BIGINT,
        pending_subjects     BIGINT,
        credits              BIGINT,
        average_grade        VARCHAR,
        total_paid           BIGINT,
        num_documents        BIGINT,
        documents_cost       BIGINT
    )
    LANGUAGE sql
    STABLE
    SET search_path = project
AS $$
    -- барањето прикажано погоре, со услов:
    -- AND (p_user_id IS NULL OR u.id = p_user_id)
$$;

SELECT * FROM rep_student_dossier();   -- сите студенти
SELECT * FROM rep_student_dossier(1);  -- едно досие

Истиот извештај е спакуван и како вистинска процедура која отвора курсор, за повикувач кој ги чита редовите еден по еден наместо одеднаш:

CREATE OR REPLACE PROCEDURE rep_student_dossier_cursor(
        IN    p_user_id INTEGER,
        INOUT p_cursor  REFCURSOR DEFAULT 'dossier')
    LANGUAGE plpgsql
    SET search_path = project
AS $$
BEGIN
    OPEN p_cursor FOR SELECT * FROM rep_student_dossier(p_user_id);
END;
$$;

Повик:

BEGIN;
CALL rep_student_dossier_cursor(1);
FETCH ALL FROM dossier;
COMMIT;

Курсорот постои само во рамки на трансакцијата во која е отворен, затоа повикот е опкружен со BEGIN и COMMIT.

Резултат врз тест податоците

 student_index |      student       | enrolled_semesters | completed_semesters | passed_subjects | pending_subjects | credits | average_grade | total_paid | num_documents | documents_cost
---------------+--------------------+--------------------+---------------------+-----------------+------------------+---------+---------------+------------+---------------+----------------
 233149        | Stefan Saveski     |                  2 |                   1 |               2 |                1 |      12 | 9.00          |        400 |             2 |            150
 233188        | Boris Gjorgjievski |                  1 |                   1 |               1 |                0 |       6 | 9.00          |        400 |             1 |             50
 233200        | Ana Petrova        |                  1 |                   0 |               1 |                1 |       6 | 7.00          |        200 |             1 |            100

3. Најуспешен студент по студиска програма

Опис на извештајот

За секоја студиска програма се бара студентот со најмногу собрани кредити, а при ист број кредити - оној со повисок просек. Ако и просекот е ист, победува студентот со помал идентификатор, за извештајот да враќа точно еден ред по програма и да дава ист резултат при секое стартување.

Барањето е решено без прозорски функции, со NOT EXISTS: се задржува само оној ред за кој не постои подобар ред во истата програма. Истата техника е употребена и во извештаите 4 и 5.

SQL

SET search_path TO project;

WITH standing AS (
    SELECT es.user_id,
           es.major_id,
           COUNT(ps.id)                                    AS passed_subjects,
           COALESCE(SUM(s.awarded_credits), 0)             AS credits,
           COALESCE(ROUND(AVG(ps.grade::TEXT::INT), 2), 0) AS average_grade
    FROM enrolled_semesters es
    LEFT JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id
    LEFT JOIN passed_subjects ps    ON ps.enrolled_id = ss.id
    LEFT JOIN subjects s            ON s.id = ss.subjects_id AND ps.id IS NOT NULL
    GROUP BY es.user_id, es.major_id
)
SELECT m.name                       AS major,
       u."index"                    AS student_index,
       u.name || ' ' || u.surname   AS student,
       st.passed_subjects,
       st.credits,
       st.average_grade
FROM standing st
JOIN users u ON u.id = st.user_id
JOIN major m ON m.id = st.major_id
WHERE NOT EXISTS (
    SELECT 1
    FROM standing st1
    WHERE st1.major_id = st.major_id
      AND (st.credits < st1.credits
           OR (st.credits = st1.credits AND st.average_grade < st1.average_grade)
           OR (st.credits = st1.credits AND st.average_grade = st1.average_grade
               AND st.user_id > st1.user_id))
)
ORDER BY m.name;

Празните вредности се претворени во нула уште во подизразот standing. Ако тоа не се направи, споредбите со NULL даваат NULL наместо точно или неточно, па студент без ниту една оценка не би можел да биде ниту задржан ниту отфрлен.

Релациона алгебра

Standing <-
γ user_id := es.user_id,
  major_id := es.major_id;
  passed_subjects := COUNT(ps.id),
  credits := SUM(s.awarded_credits),
  average_grade := AVG(ps.grade)
(
  (
    (enrolled_semesters es ⟕ (es.id = ss.enrolled_semesters_id) semesters_subjects ss)
    ⟕ (ss.id = ps.enrolled_id) passed_subjects ps
  )
  ⟕ (ss.subjects_id = s.id ∧ ps.id IS NOT NULL) subjects s
)

Dominated <-
π attributes(st)
(
  σ st.major_id = st1.major_id ∧
    (
      st.credits < st1.credits
      ∨ (st.credits = st1.credits ∧ st.average_grade < st1.average_grade)
      ∨ (st.credits = st1.credits ∧ st.average_grade = st1.average_grade ∧
         st.user_id > st1.user_id)
    )
  (
    ρ st(Standing) × ρ st1(Standing)
  )
)

TopStudents <- Standing − Dominated

Result <-
τ m.name
(
  π m.name,
    u.index,
    student := u.name || ' ' || u.surname,
    st.passed_subjects,
    st.credits,
    st.average_grade
  (
    (
      TopStudents st ⨝ (st.user_id = u.id) users u
    )
    ⨝ (st.major_id = m.id) major m
  )
)

Условот NOT EXISTS во релациона алгебра се изразува со разлика: од сите редови се одземаат оние за кои постои подобар ред во истата група.

Складирана процедура

CREATE OR REPLACE FUNCTION rep_top_student_per_major()
    RETURNS TABLE (
        major            VARCHAR,
        student_index    VARCHAR,
        student          TEXT,
        passed_subjects  BIGINT,
        credits          BIGINT,
        average_grade    NUMERIC
    )
    LANGUAGE sql
    STABLE
    SET search_path = project
AS $$
    -- барањето прикажано погоре
$$;

SELECT * FROM rep_top_student_per_major();

Резултат врз тест податоците

                    major                     | student_index |    student     | passed_subjects | credits | average_grade
----------------------------------------------+---------------+----------------+-----------------+---------+---------------
 Computer Science and Engineering             | 233200        | Ana Petrova    |               1 |       6 |          7.00
 Software Engineering and Information Systems | 233149        | Stefan Saveski |               2 |      12 |          9.00

4. Најоптоварен професор по активен семестар

Опис на извештајот

За секој активен семестар се бара професорот кај кого се запишани најмногу студенти, а при ист број - оној кој оценил повеќе. Извештајот тргнува од табелата active_semesters, па во него се појавуваат и семестрите во кои сè уште никој не се запишал; за нив се прикажува 'n/a' и нули. Тоа е список кој администраторот го користи за да види каде распоредот сè уште не е пополнет.

SQL

SET search_path TO project;

WITH professor_load AS (
    SELECT es.semester_id,
           ss.professor_id,
           COUNT(ss.id)                   AS enrolled_students,
           COUNT(ps.id)                   AS graded_students,
           COUNT(DISTINCT ss.subjects_id) AS subjects_taught
    FROM semesters_subjects ss
    JOIN enrolled_semesters es   ON es.id = ss.enrolled_semesters_id
    LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
    GROUP BY es.semester_id, ss.professor_id
),
busiest AS (
    SELECT pl.*
    FROM professor_load pl
    WHERE NOT EXISTS (
        SELECT 1
        FROM professor_load pl1
        WHERE pl1.semester_id = pl.semester_id
          AND (pl.enrolled_students < pl1.enrolled_students
               OR (pl.enrolled_students = pl1.enrolled_students
                   AND pl.graded_students < pl1.graded_students)
               OR (pl.enrolled_students = pl1.enrolled_students
                   AND pl.graded_students = pl1.graded_students
                   AND pl.professor_id > pl1.professor_id))
    )
)
SELECT a.year,
       a.type                                      AS semester_type,
       COALESCE(u.name || ' ' || u.surname, 'n/a') AS professor,
       COALESCE(b.subjects_taught, 0)              AS subjects_taught,
       COALESCE(b.enrolled_students, 0)            AS enrolled_students,
       COALESCE(b.graded_students, 0)              AS graded_students
FROM active_semesters a
LEFT JOIN busiest b ON b.semester_id = a.id
LEFT JOIN users u   ON u.id = b.professor_id
ORDER BY a.year, CASE a.type WHEN 'summer' THEN 1 ELSE 2 END;

Подредувањето не оди по името на семестарот, туку по година и по редоследот во академската година - летниот семестар доаѓа пред зимскиот од истата година.

Релациона алгебра

ProfessorLoad <-
γ semester_id := es.semester_id,
  professor_id := ss.professor_id;
  enrolled_students := COUNT(ss.id),
  graded_students := COUNT(ps.id),
  subjects_taught := COUNT(DISTINCT ss.subjects_id)
(
  (
    semesters_subjects ss
    ⨝ (ss.enrolled_semesters_id = es.id) enrolled_semesters es
  )
  ⟕ (ss.id = ps.enrolled_id) passed_subjects ps
)

Dominated <-
π attributes(pl)
(
  σ pl.semester_id = pl1.semester_id ∧
    (
      pl.enrolled_students < pl1.enrolled_students
      ∨ (pl.enrolled_students = pl1.enrolled_students ∧
         pl.graded_students < pl1.graded_students)
      ∨ (pl.enrolled_students = pl1.enrolled_students ∧
         pl.graded_students = pl1.graded_students ∧
         pl.professor_id > pl1.professor_id)
    )
  (
    ρ pl(ProfessorLoad) × ρ pl1(ProfessorLoad)
  )
)

Busiest <- ProfessorLoad − Dominated

Result <-
τ a.year, (a.type = 'summer' ? 1 : 2)
(
  π a.year,
    a.type,
    professor := u.name || ' ' || u.surname,
    b.subjects_taught,
    b.enrolled_students,
    b.graded_students
  (
    (
      active_semesters a ⟕ (a.id = b.semester_id) Busiest b
    )
    ⟕ (b.professor_id = u.id) users u
  )
)

Складирана процедура

CREATE OR REPLACE FUNCTION rep_busiest_professor()
    RETURNS TABLE (
        year               INTEGER,
        semester_type      semester_type,
        professor          TEXT,
        subjects_taught    BIGINT,
        enrolled_students  BIGINT,
        graded_students    BIGINT
    )
    LANGUAGE sql
    STABLE
    SET search_path = project
AS $$
    -- барањето прикажано погоре
$$;

SELECT * FROM rep_busiest_professor();

Резултат врз тест податоците

 year | semester_type |    professor     | subjects_taught | enrolled_students | graded_students
------+---------------+------------------+-----------------+-------------------+-----------------
 2024 | winter        | Vangel Ajanovski |               1 |                 3 |               3
 2025 | summer        | Vangel Ajanovski |               1 |                 1 |               0
 2025 | winter        | n/a              |               0 |                 0 |               0
 2026 | summer        | n/a              |               0 |                 0 |               0
 2026 | winter        | n/a              |               0 |                 0 |               0

5. Процентуална промена на активноста меѓу два последователни семестри

Опис на извештајот

За секој активен семестар се прикажува колку запишувања, колку запишани предмети и колку положени предмети имало, и колку тоа отстапува од претходниот семестар, изразено во проценти. Ова е извештајот со кој се следи дали бројот на запишувања расте или опаѓа.

Семестрите немаат колона со редослед - тие се пар од година и тип. Затоа прво се пресметува хронолошки клуч, а потоа за секој семестар се бара претходниот како оној пред него за кој не постои семестар помеѓу нив.

SQL

SET search_path TO project;

WITH ordered_semesters AS (
    SELECT a.id,
           a.year,
           a.type,
           a.year * 10 + CASE a.type WHEN 'summer' THEN 1 ELSE 2 END AS chrono
    FROM active_semesters a
),
semester_stats AS (
    SELECT o.id                  AS semester_id,
           o.year,
           o.type,
           o.chrono,
           COUNT(DISTINCT es.id) AS enrolments,
           COUNT(ss.id)          AS subject_enrolments,
           COUNT(ps.id)          AS passed
    FROM ordered_semesters o
    LEFT JOIN enrolled_semesters es ON es.semester_id = o.id
    LEFT JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id
    LEFT JOIN passed_subjects ps    ON ps.enrolled_id = ss.id
    GROUP BY o.id, o.year, o.type, o.chrono
),
consecutive AS (
    SELECT cur.year, cur.type, cur.chrono,
           cur.enrolments, cur.subject_enrolments, cur.passed,
           prev.year               AS prev_year,
           prev.type               AS prev_type,
           prev.subject_enrolments AS prev_subject_enrolments
    FROM semester_stats cur
    LEFT JOIN semester_stats prev
           ON prev.chrono < cur.chrono
          AND NOT EXISTS (SELECT 1
                          FROM semester_stats mid
                          WHERE mid.chrono < cur.chrono
                            AND mid.chrono > prev.chrono)
)
SELECT c.year || '-' || c.type                                       AS semester,
       COALESCE(c.prev_year || '-' || c.prev_type, 'нема претходен')  AS previous_semester,
       c.enrolments,
       c.subject_enrolments,
       c.passed,
       COALESCE(CAST(c.prev_subject_enrolments AS VARCHAR), 'n/a')   AS prev_subject_enrolments,
       COALESCE(CAST(ROUND(((c.subject_enrolments - c.prev_subject_enrolments) * 100.0)
                           / NULLIF(c.prev_subject_enrolments, 0)) AS VARCHAR) || '%',
                'n/a')                                              AS pct_change
FROM consecutive c
ORDER BY c.chrono;

Процентот се спојува со знакот '%' преку операторот ||, а не преку CONCAT. CONCAT ги игнорира празните вредности и за семестар без претходник би вратил само '%', па COALESCE никогаш не би се активирал. Операторот || враќа NULL ако некој од операндите е NULL, што е токму она што му треба на COALESCE за да испише 'n/a'.

Делењето е заштитено со NULLIF, за семестар во кој претходно немало ниту еден запишан предмет да не предизвика делење со нула.

Релациона алгебра

OrderedSemesters <-
π a.id,
  a.year,
  a.type,
  chrono := a.year * 10 + (a.type = 'summer' ? 1 : 2)
(
  active_semesters a
)

SemesterStats <-
γ semester_id := o.id,
  year := o.year,
  type := o.type,
  chrono := o.chrono;
  enrolments := COUNT(DISTINCT es.id),
  subject_enrolments := COUNT(ss.id),
  passed := COUNT(ps.id)
(
  (
    (
      OrderedSemesters o ⟕ (o.id = es.semester_id) enrolled_semesters es
    )
    ⟕ (es.id = ss.enrolled_semesters_id) semesters_subjects ss
  )
  ⟕ (ss.id = ps.enrolled_id) passed_subjects ps
)

Earlier <-
σ prev.chrono < cur.chrono
(
  ρ cur(SemesterStats) × ρ prev(SemesterStats)
)

NotImmediate <-
π attributes(Earlier)
(
  σ mid.chrono < cur.chrono ∧ mid.chrono > prev.chrono
  (
    Earlier × ρ mid(SemesterStats)
  )
)

Immediate <- Earlier − NotImmediate

Consecutive <-
SemesterStats cur ⟕ (cur.chrono = imm.cur_chrono) Immediate imm

Result <-
τ c.chrono
(
  π semester := c.year || '-' || c.type,
    previous_semester := c.prev_year || '-' || c.prev_type,
    c.enrolments,
    c.subject_enrolments,
    c.passed,
    c.prev_subject_enrolments,
    pct_change := ((c.subject_enrolments − c.prev_subject_enrolments) * 100)
                  / c.prev_subject_enrolments
  (
    Consecutive c
  )
)

Релацијата Immediate ги содржи само паровите (семестар, претходен семестар) меѓу кои нема трет семестар. Тоа е истата разлика како во извештаите 3 и 4: од сите порани парови се одземаат оние за кои постои семестар помеѓу.

Складирана процедура

CREATE OR REPLACE FUNCTION rep_semester_growth()
    RETURNS TABLE (
        semester                 TEXT,
        previous_semester        TEXT,
        enrolments               BIGINT,
        subject_enrolments       BIGINT,
        passed                   BIGINT,
        prev_subject_enrolments  VARCHAR,
        pct_change               VARCHAR
    )
    LANGUAGE sql
    STABLE
    SET search_path = project
AS $$
    -- барањето прикажано погоре
$$;

SELECT * FROM rep_semester_growth();

Резултат врз тест податоците

  semester   | previous_semester | enrolments | subject_enrolments | passed | prev_subject_enrolments | pct_change
-------------+-------------------+------------+--------------------+--------+-------------------------+------------
 2024-winter | нема претходен    |          3 |                  5 |      4 | n/a                     | n/a
 2025-summer | 2024-winter       |          1 |                  1 |      0 | 5                       | -80%
 2025-winter | 2025-summer       |          0 |                  0 |      0 | 1                       | -100%
 2026-summer | 2025-winter       |          0 |                  0 |      0 | 0                       | n/a
 2026-winter | 2026-summer       |          0 |                  0 |      0 | 0                       | n/a

Заеднички забелешки за имплементацијата

• Еден ред по група без прозорски функции. Извештаите 3, 4 и 5 бараат „најдобриот во групата“ односно „претходниот по ред“. Наместо ROW_NUMBER, употребен е NOT EXISTS, кој во релациона алгебра директно се пресликува во разлика на две релации. Условот за израмнување е секогаш строг и завршува со споредба на идентификатор, па извештајот враќа точно еден ред по група и е повторлив.
• Надворешни спојувања наместо внатрешни. Предмет кој никој не го положил, семестар во кој никој не се запишал и студент кој нема платено сепак се појавуваат во извештаите. Внатрешно спојување би ги сокрило токму редовите кои се најинтересни за администраторот.
• Претворање на оценката. grade е од набројувачки тип, па AVG(grade) не е дозволено. Секаде е употребено ps.grade::TEXT::INT.
• Празни вредности. Сите бројачи излегуваат преку COALESCE како нула, а просеците како текстот 'нема оценки'. Делењата се заштитени со NULLIF.
• Патека на шемата. Секоја рутина носи SET search_path = project, па работи исто без оглед на тоа што повикувачот поставил.

Историјат

Верзија 1 - Прва верзија: пет извештаи, секој со SQL, релациона алгебра и складирана рутина. Сите барања се тестирани врз шемата project со податоците од data_load.sql.

Статус

Во тек