题解 | 医院门诊复诊率与抗生素用药占比统计

with
    temp0 as (
        select
            dept,
            count(*) as feb_2024_visits,
            count(distinct patient_id) as feb_2024_unique_patients
        from
            visits
        where
            date_format(visit_date, "%Y-%m") = "2024-02"
        group by
            dept
    ),
    temp1 as (
        select
            dept,
            ifnull(
                round(
                    count(if(is_antibiotic = 1, 1, null)) * 100 / count(*),
                    2
                ),
                0.00
            ) as feb_2024_antibiotic_rate
        from
            visits v
            inner join prescriptions p on v.visit_id = p.visit_id
        where
            date_format(visit_date, "%Y-%m") = "2024-02"
        group by
            dept
    ),
    temp2 as (
        select
            dept,
            patient_id,
            visit_date,
            lag(visit_date, 1) over (
                partition by
                    dept,
                    patient_id
                order by
                    visit_date asc
            ) as last_visit_date
        from
            visits
    ),
    temp3 as (
        select
            dept,
            count(*) as fz
        from
            temp2
        where
            timestampdiff(day, last_visit_date, visit_date) <= 30
            and date_format(visit_date, "%Y-%m") = "2024-02"
        group by
            dept
    )
select distinct
    v.dept,
    feb_2024_visits,
    feb_2024_unique_patients,
    ifnull(round(fz * 100 / feb_2024_visits, 2), 0.00) as feb_2024_revisit_rate,
    ifnull(feb_2024_antibiotic_rate, 0.00) as feb_2024_antibiotic_rate
from
    visits v
    left join temp0 t0 on v.dept = t0.dept
    left join temp1 t1 on v.dept = t1.dept
    left join temp2 t2 on v.dept = t2.dept
    left join temp3 t3 on v.dept = t3.dept
order by
    v.dept asc;