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

with feb_count_temp as (
select dept,count(distinct v.visit_id) as feb_2024_visits,
count(distinct patient_id ) as feb_2024_unique_patients,
coalesce(round(sum(is_antibiotic)/count(prescription_id)*100,2),0)
 as  feb_2024_antibiotic_rate
from  visits v
left join prescriptions p on p.visit_id=v.visit_id
where visit_date between '2024-02-01' and  '2024-02-29'
group by  dept
),
rev_temp as (
select dept,coalesce(round(sum(case when prev_date is not null and 
timestampdiff(day,prev_date,visit_date)<30 then 1 else 0 end )/ count(*)*100,2),0) as feb_2024_revisit_rate
from (select patient_id,visit_id,dept,
visit_date,lag(visit_date) over(partition by dept,patient_id order by visit_date)
as prev_date
 from visits ) t
where visit_date between '2024-02-01' and  '2024-02-29'
group by dept
)
select rt.dept,feb_2024_visits,feb_2024_unique_patients,feb_2024_revisit_rate,feb_2024_antibiotic_rate
from  feb_count_temp fct 
join rev_temp   rt on rt.dept=fct.dept;