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

with a as (
    select *,lag(visit_date) over(partition by dept,patient_id order by visit_date asc) l_date
    from visits),
b as(
    select dept,ifnull(round(count(if(is_antibiotic=1 and date_format(visit_date,'%Y%m')=202402,1,null))/count(1)*100,2),0.00) c
    from prescriptions pr
    inner join visits v 
    on pr.visit_id = v.visit_id
    group by dept
)

select a.dept,
    count(if(date_format(visit_date,'%Y%m')=202402,a.visit_id,null)) feb_2024_visits,
    count( distinct if(date_format(visit_date,'%Y%m')=202402,a.patient_id,null)) feb_2024_unique_patients,
    round(count(if(datediff(visit_date,l_date)<=30 and date_format(visit_date,'%Y%m')=202402,a.visit_id,null))/count(if(date_format(visit_date,'%Y%m')=202402,a.visit_id,null))*100,2) feb_2024_revisit_rate,
    ifnull(max(c),0.00) feb_2024_antibiotic_rate
from a 
inner join patients p 
on a.patient_id = p.patient_id 
left join b 
on b.dept=a.dept
group by a.dept
order by a.dept;