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

with t as (
select dept,count(distinct visit_id) as feb_2024_visits,
count(distinct patient_id) as feb_2024_unique_patients,
ifnull(round(sum(if(is_antibiotic = 1,1,0))/nullif(count(is_antibiotic),0)*100,2),0) as feb_2024_antibiotic_rate from visits
left join prescriptions using(visit_id)
where date_format(visit_date,'%Y-%m') = '2024-02'
group by dept),

t1 as (
select dept,round(count(if(间隔时间 <= 30,间隔时间,null))/count(visit_date)*100,2) as feb_2024_revisit_rate 
from 
(select dept,patient_id,visit_date,
datediff(visit_date,lag(visit_date)over(partition by dept,patient_id order by visit_date)) as 间隔时间
from visits) a
where date_format(visit_date,'%Y-%m') = '2024-02'
group by dept)

select dept,feb_2024_visits,feb_2024_unique_patients,feb_2024_revisit_rate,feb_2024_antibiotic_rate from t 
join t1 using(dept)
order by dept