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

-- 理解题目：
-- 1.每个科室，说明没有记录的科室也要列出来
-- 2.时间限定为2024年02月-where visit_date >= '2024-02-01' and visit_date <= '2024-02-29'(该限定方法性能最优)
-- 3.就诊人次-count(visit_id),就诊去重人数count(distinct patient_id)
-- 4.复诊率（难点，注意上一次就诊可能不在2月份，所以注意过滤条件），可以用lag(visit_date) over (partition by patient_id,dept order by visit_date)求出上一次就诊时间，求出上一次就诊时间后，再对最近一次就诊时间过滤出2月份的visit_date，一定要注意顺序！！！再用datediff() <= 30
-- 5.抗生素用药占比=sum(is_antibiotic)/count(is_antibiotic)
-- 6.细节：处理分母为0的情况，可以用nullif()、coalesce()或者case when处理
with t1 as (
select visit_id 
        ,patient_id 
        ,dept   
        ,visit_date 
        ,lag(visit_date) over (partition by patient_id,dept order by visit_date) as pre_visit_date
from visits
),
t2 as (
select *
from t1
where visit_date >= '2024-02-01' and visit_date <= '2024-02-29'
),
t3 as (
select dept
        ,count(visit_id) as feb_2024_visits
        ,count(distinct patient_id) as feb_2024_unique_patients
        ,round(100*count(
            case when datediff(visit_date,pre_visit_date) <= 30 
                then visit_id end)/count(visit_id)
            ,2) as feb_2024_revisit_rate
from t2
group by dept
),
t4 as (
select dept
        ,case when count(is_antibiotic) = 0 then 0.00
              else round(100*sum(is_antibiotic)/count(is_antibiotic)
                            ,2) end as feb_2024_antibiotic_rate
from t2 left join prescriptions p on t2.visit_id = p.visit_id
group by dept
)
select t3.dept
        ,t3.feb_2024_visits
        ,t3.feb_2024_unique_patients
        ,t3.feb_2024_revisit_rate
        ,t4.feb_2024_antibiotic_rate
from t3 left join t4 using(dept)
order by dept;