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

WITH
    T1 as (
        select
            dept,
            count(distinct visit_id) as feb_2024_visits,
            count(distinct patient_id) as feb_2024_unique_patients,
            sum(
                case
                    when is_antibiotic = 1 then 1
                end
            ) as an,
            count(distinct prescription_id) as al
        from
            patients
            left join visits using (patient_id)
            left join prescriptions using (visit_id)
        where
            visit_date between '2024-02-01' and '2024-02-28'
        group by
            dept
    ),
    T2 as (
        select
            dept,
            sum(r) as re
        from
            (
                select
                    dept,
                    patient_id,
                    case
                        when datediff(
                            visit_date,
                            lag(visit_date, 1) over (partition by dept,patient_id order by visit_date )
                        ) <= 30  and visit_date >= '2024-02-01'then 1
                    end as r
                from visits
            ) as a
        group by
            dept
    )
select dept,
feb_2024_visits,
feb_2024_unique_patients,
ifnull(round(re/feb_2024_visits*100,2),0) as feb_2024_revisit_rate,
ifnull(round(an/al*100,2),0) as feb_2024_antibiotic_rate
from T1 left join T2 using(dept)
order by dept asc;