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

with a1 as (
    select 
    visit_id,
    dept,
    patient_id,
    datediff(visit_date,(lag(visit_date) over(partition by dept,patient_id order by visit_date))) as u
    # 如果用LEAD，在后面链接表的时候，上次就诊记录在2024-02之前的记录会被where date_format(visit_date,'%Y-%m')='2024-02' 给过滤掉
    from visits
)

select
v.dept,
count(distinct v.visit_id) as feb_2024_visits,
count(distinct v.patient_id) as feb_2024_unique_patients,
round(100*count(distinct u)/count(distinct v.visit_id),2) as feb_2024_revisit_rate,
#用distinct u 而不用sum(case when u <=30 then 1 else 0 end)，因为如果一条visit_id有两条处方记录，可能会使该visit_id产生两个<=30的记录从而多算分子。但其实distinct u 也不保险，如果有两个visit_id的u正好一样，会被压缩成一条记录，从而少算分子
ifnull(round(100*sum(is_antibiotic)/count(prescription_id),2) ,0.00) as feb_2024_antibiotic_rate
from visits v 
left join prescriptions using(visit_id)
join patients using(patient_id)
left join a1 on v.visit_id=a1.visit_id and u <=30
where date_format(visit_date,'%Y-%m')='2024-02' 
group by v.dept
order by v.dept