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

with
t1 as(
    select
        dept,
        coalesce(count(visit_id),0) as feb_2024_visits,
        coalesce(count(distinct patient_id),0) as feb_2024_unique_patients
    from visits where left(visit_date,7)='2024-02'
    group by dept),
t2 as(
    select
        b.dept,
        b.visit_id
    from visits a join visits b
    on a.dept=b.dept and a.patient_id=b.patient_id and datediff(b.visit_date,a.visit_date)<=30 and b.visit_date<>a.visit_date-----鸡贼的核心条件，不限定好两天不相等，系统就会把同一天的记录也算一次
    where left(b.visit_date,7)='2024-02'),
t3 as(
    select
        v.dept,
        coalesce(round(sum(if(t2.visit_id=v.visit_id,1,0))*100/count(v.visit_id),2),0) as feb_2024_revisit_rate
    from visits v 
    left join t1 on v.dept=t1.dept
    left join t2 on v.dept=t2.dept
    where left(v.visit_date,7)='2024-02'
    group by v.dept),
t4 as(
    select
        v.dept,
        coalesce(round(sum(p.is_antibiotic)*100/count(p.prescription_id),2),0) as feb_2024_antibiotic_rate
    from visits v left join prescriptions p on v.visit_id=p.visit_id
    where left(v.visit_date,7)='2024-02'
    group by v.dept)

select 
    t1.dept,
    t1.feb_2024_visits,
    t1.feb_2024_unique_patients,
    t3.feb_2024_revisit_rate,
    t4.feb_2024_antibiotic_rate
from t1 
left join t3 on t1.dept=t3.dept 
left join t4 on t1.dept=t4.dept
order by t1.dept