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

with r1 as(
    select v2.dept, count(1) as again 
    from visits as v1 join visits as v2 on v1.patient_id = v2.patient_id and v1.dept = v2.dept and left(v2.visit_date,7) = '2024-02' 
    where DATEDIFF(v2.visit_date , v1.visit_date) <= 30 and DATEDIFF(v2.visit_date , v1.visit_date) > 0
    group by dept
)

select 
    v.dept,
    count(distinct v.visit_id) as feb_2024_visits,
    count(distinct v.patient_id) as feb_2024_unique_patients,
    round(ifnull(again,0)/count(distinct v.visit_id)*100,2) as feb_2024_revisit_rate,
    round(ifnull(sum(is_antibiotic)/count(prescription_id)*100,0),2) as feb_2024_antibiotic_rate
from visits as v left join patients as p on p.patient_id = v.patient_id
    left join prescriptions as pr on v.visit_id = pr.visit_id
    left join r1 on r1.dept = v.dept
where left(visit_date,7) = '2024-02'
group by dept,again
order by dept;