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

#复诊人次
with t1 as(
    select count(distinct v1.visit_id) as revisit_nums,
        v1.dept
    from visits v1
    join visits v2 on v1.patient_id=v2.patient_id and v1.dept=v2.dept
    where year(v1.visit_date)=2024 and month(v1.visit_date)=2 and datediff(v1.visit_date,v2.visit_date) between 1 and 30
    group by v1.dept
)

select v.dept,
    count(distinct v.visit_id) as feb_2024_visits,
    count(distinct patient_id) as feb_2024_unique_patients,
    round(ifnull(100*revisit_nums/count(distinct v.visit_id),0),2) as feb_2024_revisit_rate,
    round(ifnull(100*sum(is_antibiotic)/count(prescription_id),0),2) as feb_2024_antibiotic_rate
from visits v 
left join prescriptions p on v.visit_id=p.visit_id
left join t1 on v.dept=t1.dept
where year(visit_date)=2024 and month(visit_date)=2 
group by v.dept,revisit_nums
order by v.dept