Вкупна годишна анализа од пациенти - расположение, успешност на консултации и терапија

SELECT
    EXTRACT(YEAR FROM d.date) AS Year,
    COUNT(DISTINCT p.id_user) AS Total_Patients,
    COUNT(DISTINCT c.id_consultation) AS Total_Consultations,
    COUNT(DISTINCT th.id_therapy) AS Total_Therapies,
    ROUND(AVG(d.daily_rating), 2) AS Avg_Mood_All_Patients,
    MIN(d.daily_rating) AS Lowest_Mood_Recorded
FROM project.patient p
JOIN project.user u 
    ON u.id_user = p.id_user
LEFT JOIN project.consultation c 
    ON c.id_patient = p.id_user
LEFT JOIN project.therapy th 
    ON th.id_consultation = c.id_consultation
JOIN project.diary d                     
    ON d.id_patient = p.id_user
GROUP BY EXTRACT(YEAR FROM d.date)
ORDER BY Year;

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

• P(id_user)
• U(id_user, ...)
• C(id_consultation, id_patient)
• TH(id_therapy, id_consultation)
• D(id_patient, date, daily_rating)

JOIN на сите табели:

J1 ← P ⨝{P.id_user = U.id_user} U

Ј2 ← Ј1 ⟕{P.id_user = C.id_patient} C

Ј3 ← Ј2 ⟕{C.id_consultation = TH.id_consultation} TH

Ј4 ← Ј3 ⨝{P.id_user = D.id_patient} D

Проекција со трансформација на датум

D' ← π{id_user, id_consultation, id_therapy, daily_rating, YEAR(date) -> Year}(J4)

Групирање и пресметка на агрегати

G ← Year σ COUNT(DISTINCT id_user) -> Total_Patients, COUNT(DISTINCT id_consultation) -> Total_Consultations, COUNT(DISTINCT id_therapy) -> Total_Therapies, AVG(daily_rating) -> Avg_Mood_All_Patients, MIN(daily_rating) -> Lowest_Mood_Recorded }(D')

Хронолошко подредување според година

R{final} ← τ{Year}(G)

Годишен извештај за честота на користење на консултации и процент на платени консултации заедно со нивната просечна цена

SELECT
    EXTRACT(YEAR FROM c.date) AS Year,
    t.id_user,
    u.name || ' ' || u.surname AS Therapist_Name,
    COUNT(c.id_consultation) AS Total_Consultations,
    COUNT(CASE WHEN date_of_payment IS NOT NULL THEN 1 END) AS Completed_Consultations,
    ROUND(
            COUNT(CASE WHEN date_of_payment IS NOT NULL THEN 1 END)::DECIMAL
                / NULLIF(COUNT(c.id_consultation), 0) * 100,
            2
    ) AS Completion_Rate_Percentage,
    ROUND(AVG(c.price), 2) AS Avg_Consultation_Price
FROM project.therapist t
         JOIN project.user u
              ON u.id_user = t.id_user
         JOIN project.consultation c
              ON c.id_therapist = t.id_user
GROUP BY
    EXTRACT(YEAR FROM c.date),
    t.id_user,
    u.name,
    u.surname
ORDER BY
    Year,
    Completion_Rate_Percentage DESC;

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

• T(id_user)
• U(id_user, name, surname)
• C(id_consultation, id_therapist, date, price, date_of_payment)

JOIN на сите табели:

J1 ← T ⨝{T.id_user = U.id_user} U

Ј2 ← Ј1 ⨝{T.id_user = C.id_therapist} C

Екстракција на име и година

F1 ← π{id_user, id_consultation, price, date_of_payment, YEAR(date) -> Year, CONCAT(name, ' ', surname) -> Therapist_Name} (J2)

Групирање и пресметка на агрегати

G ← {Year, id_user, Therapist_Name} σ COUNT(id_consultation) -> Total_Consultations, COUNT_IF(date_of_payment != NULL) -> Completed_Consultations, AVG(price) -> Avg_Consultation_Price (F1)

Пресметка на процент

P ← π{Year, id_user, Therapist_Name, Total_Consultations, Completed_Consultations, Avg_Consultation_Price, (Completed_Consultations/ Total_Consultations) * 100 -> Completion_Rate_Percentage} (G)

Подредување

R{final} ← τ{Year, Completion_Rate_Percentage DESC} (P)

Годишен извештај за честота на постирање блогови и број на коментари по блог кај луѓе кој имаат отидено на терапевт барем 10 пати, според тоа кој терапевт го посетуваат

WITH LoyalPatients AS (
    SELECT
        c.id_patient,
        c.id_therapist,
        u_t.name    AS therapist_name,
        u_t.surname AS therapist_surname
    FROM project.consultation c
        JOIN project.therapist t
            ON t.id_user = c.id_therapist
        JOIN project."user" u_t
            ON u_t.id_user = t.id_user
    GROUP BY
        c.id_patient,
        c.id_therapist,
        u_t.name,
        u_t.surname
    HAVING COUNT(c.id_consultation) >= 10
),

BlogStats AS (
    SELECT
        b.id_patient,
        EXTRACT(YEAR FROM b.date_of_post) AS post_year,
        COUNT(DISTINCT b.id_blog)         AS blogs_count,
        COUNT(c.id_comment)               AS comments_count
    FROM project.blog b
        LEFT JOIN project.comment c
            ON c.id_blog = b.id_blog
    GROUP BY
        b.id_patient,
        EXTRACT(YEAR FROM b.date_of_post)
 )

SELECT
    bs.post_year                                                AS "Година",
    lp.therapist_name || ' ' || lp.therapist_surname            AS "Терапевт",
    SUM(bs.blogs_count)                                         AS "Вкупно објавени блогови",
    SUM(bs.comments_count)                                      AS "Вкупно примени коментари",
    ROUND(AVG(bs.blogs_count), 2)                               AS "Просек блогови по пациент",
    ROUND(
        SUM(bs.comments_count)::DECIMAL
            / NULLIF(SUM(bs.blogs_count), 0),
        2
    )                                                           AS "Просек коментари по блог"
FROM LoyalPatients lp
    JOIN BlogStats bs
        ON bs.id_patient = lp.id_patient
GROUP BY
    bs.post_year,
    lp.id_therapist,
    lp.therapist_name,
    lp.therapist_surname
ORDER BY
    bs.post_year DESC,
    "Вкупно објавени блогови" DESC;

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

• C(id_consultation, id_patient, id_therapist)
• T(id_user)
• U(id_user, name, surname)
• B(id_blog, id_patient, date_of_post)
• COM(id_comment, id_blog)

Дефинирање на LoyalPatients

J{lp} ← C ⨝{C.id_therapist = T.id_user} T ⨝{T.id_user = U.id_user} U

LP ← σ{count_c >= 10} ({id_patient, id_therapist, name, surname} σ{COUNT(id_consultation) -> count_c} (J{lp}))

Дефинирање на BlogStats

J{bs} ← B ⟕{B.id_blog = COM.id_blog} COM

F{bs} ← π{id_patient, id_blog, id_comment, YEAR(date_of_post) -> post_year} (J{bs})

BS ← {id_patient, post_year} σCOUNT(DISTINCT {id_blog} -> blogs_count, COUNT(id_comment) -> comments_count} (F{bs})

Поврзување и пресметка на агрегати

J{final} ← LP ⨝{LP.id_patient = BS.id_patient} BS

F{final} ← π{post_year, CONCAT(name, ' ', surname) -> Therapist, blogs_count, comments_count} (J{final})

G ← {post_year, Therapist} σ SUM(blogs_count) -> Total_Blogs, SUM(comments_count) -> Total_Comments, AVG(blogs_count) -> Avg_Blogs, SUM(comments_count)/SUM(blogs_count) -> Avg_Comments_Per_Blog (F{final})

Подредување

R{final} ← τ{post_year DESC, Total_Blogs DESC} (G)

Вкупна годишна анализа на блогови по месец во однос на внесување дневници во просек по месец

WITH Blog_Monthly AS (
    SELECT
        EXTRACT(YEAR FROM b.date_of_post)  AS year,
        EXTRACT(MONTH FROM b.date_of_post) AS month,
        COUNT(b.id_blog)                   AS total_blogs,
        COUNT(DISTINCT b.id_patient)       AS active_bloggers
    FROM project.blog b
    GROUP BY 1, 2
),

Diary_Monthly AS (
    SELECT
        EXTRACT(YEAR FROM d.date)  AS year,
        EXTRACT(MONTH FROM d.date) AS month,
        COUNT(d.id_diary)          AS total_diaries,
        COUNT(DISTINCT d.id_patient) AS active_diarists,
        AVG(d.daily_rating)        AS avg_monthly_mood
    FROM project.diary d
    GROUP BY 1, 2
),

Monthly_Stats AS (
    SELECT
        COALESCE(bm.year, dm.year)   AS year,
        COALESCE(bm.month, dm.month) AS month,
        COALESCE(bm.total_blogs, 0)  AS total_blogs,
        COALESCE(bm.active_bloggers, 0) AS active_bloggers,
        COALESCE(dm.total_diaries, 0)   AS total_diaries,
        COALESCE(dm.active_diarists, 0) AS active_diarists,
        dm.avg_monthly_mood
    FROM Blog_Monthly bm
    FULL OUTER JOIN Diary_Monthly dm
    ON bm.year = dm.year
    AND bm.month = dm.month
)

SELECT
    year  AS "Година",
    month AS "Месец",

    total_blogs   AS "Вкупно Блогови",
    total_diaries AS "Вкупно Дневници",

    ROUND(
        total_blogs::NUMERIC / NULLIF(active_bloggers, 0),
        2
    ) AS "Блогови по корисник",

    ROUND(
        total_diaries::NUMERIC / NULLIF(active_diarists, 0),
        2
    ) AS "Дневници по корисник",

    ROUND(avg_monthly_mood, 2) AS "Просечно Расположение (1-10)",

    CASE
        WHEN total_diaries > total_blogs * 2 THEN 'Highly Private (Introspective)'
        WHEN total_blogs > total_diaries THEN 'Highly Social (Outspoken)'
        ELSE 'Balanced'
        END AS "Тип на Однесување",

    ROUND(
        total_blogs::NUMERIC / NULLIF(total_blogs + total_diaries, 0) * 100,
        2
    ) || '%' AS "Јавност (%)"

FROM Monthly_Stats
ORDER BY year DESC, month DESC;

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

• B(id_blog, id_patient, date_of_post)
• D(id_diary, id_patient, date, daily_rating)

Дефинирање на Blog_Monthly

F{bm} ← π{id_blog, id_patient, YEAR(date_of_post) -> year, MONTH(date_of_post) -> month} (B)

BM ← {year, month} σCOUNT(id_blog) -> total_blogs, COUNT(DISTINCT {id_patient}) -> active_bloggers} (F{bm})

Дефинирање на Diary_Monthly

F{dm} ← π{id_diary, id_patient, daily_rating, YEAR(date) -> year, MONTH(date) -> month} (D)

DM ← {year, month} σ{COUNT(id_diary) -> total_diaries, COUNT(DISTINCT id\_patient) -> active_diarists, AVG(daily_rating) -> avg_monthly_mood} (F{dm})

Поврзување на Monthly_Stats

MS ← BM ⟗{BM.year = DM.year ∧ BM.month = DM.month} DM

MS{final} ← π{COALESCE(BM.year, DM.year) -> year, COALESCE(BM.month, DM.month) -> month, ..., avg_monthly_mood} (MS)

Selection и Projection

P ← π{year, month, total_blogs, total_diaries, avg_monthly_mood, (total_blogs / active_bloggers) -> BlogsPerUser, (total_diaries / active_diarists) -> DiariesPerUser} (MS{final})

P{ext} ← π{*, CASE_WHEN}(...) -> BehaviourType, (total_blogs / (total_blogs + total_diaries)) * 100 -> PublicityRate} (P)

Подредување

R{final} ← τ{year DESC, month DESC} (P{ext})