2019-08-11：各类起始抗生素药物占比

-- 起始抗菌药物选择占比
select indicator_name as "指标名称",
       valid_count as "分子",
       all_count as "分母",
       case 
            when all_count = 0 then concat("——", " ( ", valid_count, "/",all_count, " )")
            else concat(ROUND(valid_count / all_count * 100, 2) , "% ( ", valid_count, "/",all_count, " )")
        end "统计结果值"
from 
(
    -- 分子项目，由于该项目也需要一个动态交互式查询功能，所以where条件相对复杂且不可省略
    select "起始抗菌药物中青霉素占比" as indicator_name,
           count(distinct cap_statistics_drug.uuid) as valid_count
    from cap_statistics_drug, (select csa.uuid, csa.common_drug, csa.drug_category, csa.order_create_date, 
                          min(order_create_date) as min_order_create_date
                                                    from cap_statistics_drug as csa
                                                    where drug_category!=0
                                                    group by csa.uuid
                                                    ) as part1 
    where part1.min_order_create_date=cap_statistics_drug.order_create_date and cap_statistics_drug.drug_category=1
    and cap_statistics_drug.uuid in (
                                select uuid from yd_patient_11_basic_info 
                                where disease_class_name="小儿肺炎"
                                    -- and { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
                                    -- AND { inpatient_department = [ inpatient_department ]} 
                                    -- AND { disease_name = [disease_class_name ]}  
                                    -- AND { diagnosis_category = [ diagnose_class_name ]}
    )
    
    union
    -- 分子项目，由于该项目也需要一个动态交互式查询功能，所以where条件相对复杂且不可省略
    select "起始抗菌药物中头孢类占比" as indicator_name,
           count(distinct cap_statistics_drug.uuid) as valid_count
    from cap_statistics_drug, (select csa.uuid, csa.common_drug, csa.drug_category, csa.order_create_date, 
                          min(order_create_date) as min_order_create_date
                                                    from cap_statistics_drug as csa
                                                    where drug_category!=0
                                                    group by csa.uuid
                                                    ) as part1 
    where part1.min_order_create_date=cap_statistics_drug.order_create_date and cap_statistics_drug.drug_category=2
    and cap_statistics_drug.uuid in (
                                select uuid from yd_patient_11_basic_info 
                                where disease_class_name="小儿肺炎"
                                    -- and { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
                                    -- AND { inpatient_department = [ inpatient_department ]} 
                                    -- AND { disease_name = [disease_class_name ]}  
                                    -- AND { diagnosis_category = [ diagnose_class_name ]}
    )

    union
    -- 分子项目，由于该项目也需要一个动态交互式查询功能，所以where条件相对复杂且不可省略
    select "起始抗菌药物中大环内酯类占比" as indicator_name,
           count(distinct cap_statistics_drug.uuid) as valid_count
    from cap_statistics_drug, (select csa.uuid, csa.common_drug, csa.drug_category, csa.order_create_date, 
                          min(order_create_date) as min_order_create_date
                                                    from cap_statistics_drug as csa
                                                    where drug_category!=0
                                                    group by csa.uuid
                                                    ) as part1 
    where part1.min_order_create_date=cap_statistics_drug.order_create_date and cap_statistics_drug.drug_category=3
    and cap_statistics_drug.uuid in (
                                select uuid from yd_patient_11_basic_info 
                                where disease_class_name="小儿肺炎"
                                    -- and { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
                                    -- AND { inpatient_department = [ inpatient_department ]} 
                                    -- AND { disease_name = [disease_class_name ]}  
                                    -- AND { diagnosis_category = [ diagnose_class_name ]}
    )

    union
    -- 分子项目，由于该项目也需要一个动态交互式查询功能，所以where条件相对复杂且不可省略
    select "起始抗菌药物中其他类占比" as indicator_name,
           count(distinct cap_statistics_drug.uuid) as valid_count
    from cap_statistics_drug, (select csa.uuid, csa.common_drug, csa.drug_category, csa.order_create_date, 
                          min(order_create_date) as min_order_create_date
                                                    from cap_statistics_drug as csa
                                                    where drug_category!=0
                                                    group by csa.uuid
                                                    ) as part1 
    where part1.min_order_create_date=cap_statistics_drug.order_create_date and cap_statistics_drug.drug_category=4
    and cap_statistics_drug.uuid in (
                                select uuid from yd_patient_11_basic_info 
                                where disease_class_name="小儿肺炎"
                                    -- and { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
                                    -- AND { inpatient_department = [ inpatient_department ]} 
                                    -- AND { disease_name = [disease_class_name ]}  
                                    -- AND { diagnosis_category = [ diagnose_class_name ]}
    )

) as part1
join 
(
        -- 分母项目，由于该项目也需要一个动态交互式查询功能，所以where条件相对复杂且不可省略
        select 
                count( DISTINCT uuid) as  all_count
            FROM
                indicator_statistics.yd_patient_11_basic_info
            WHERE 
                disease_class_name="小儿肺炎"
--              AND { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
--              AND { inpatient_department = [ inpatient_department ]}  
--              AND { disease_name = [disease_class_name ]}  
--              AND { diagnosis_category = [ diagnose_class_name ]}
) as all_AMI_count