-- 起始抗菌药物选择占比
select indicator_name as "指标名称",
valid_count as "分子",
case
when indicator_name="首剂抗生素中位数" then ""
when indicator_name="住院总天数" then ""
when indicator_name="住院平均天数" then ""
when indicator_name="住院日中位数" then ""
when indicator_name="疗程总天数" then ""
when indicator_name="疗程平均天数" then ""
when indicator_name="疗程中位数" then ""
else all_count
end "分母",
case
when all_count = 0 then concat("——", " ( ", valid_count, "/",all_count, " )")
when indicator_name="首剂抗生素中位数" then ""
when indicator_name="住院总天数" then ""
when indicator_name="住院平均天数" then ""
when indicator_name="住院日中位数" then ""
when indicator_name="疗程总天数" then ""
when indicator_name="疗程平均天数" then ""
when indicator_name="疗程中位数" then ""
else concat(ROUND(valid_count / all_count * 100, 2) , "% ( ", valid_count, "/",all_count, " )")
end "统计结果值"
from
(
-- 分子项目，由于该项目也需要一个动态交互式查询功能，所以where条件相对复杂且不可省略
select "严重肺部病变患儿比例" as indicator_name,
count(distinct uuid) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where belonged_category="严重肺部病变"
-- 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 uuid) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where disease_class_name="小儿肺炎" and (belonged_category!="严重肺部病变" or belonged_category is null)
-- 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 uuid) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where belonged_category="重症肺炎"
-- 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 uuid) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where disease_class_name="小儿肺炎" and (belonged_category!="重症肺炎" or belonged_category is null)
-- 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 uuid) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where belonged_category="完成血痰培养"
-- 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 uuid) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where disease_class_name="小儿肺炎" and (belonged_category!="完成血痰培养" or belonged_category is null)
-- 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 uuid) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where belonged_category="完成实验室检查"
-- 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 uuid) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where disease_class_name="小儿肺炎" and (belonged_category!="完成实验室检查" or belonged_category is null)
-- 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
-- 入院4小时内接受首剂抗生素病人
select "入院4小时内接受首剂抗生素" as indicator_name,
count(distinct uuid) as valid_count
from cap_statistics_antibiotic
where (TIMESTAMPDIFF(HOUR, admission_date, antibiotic_execute_date) < 4
or TIMESTAMPDIFF(HOUR, admission_date, antibiotic_create_date) < 4)
-- 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
-- 入院4小时内未接受首剂抗生素病人
select "入院4小时内未接受首剂抗生素" as indicator_name,
count(distinct uuid) as valid_count
from cap_statistics_antibiotic
where (TIMESTAMPDIFF(HOUR, admission_date, antibiotic_execute_date) > 4
or TIMESTAMPDIFF(HOUR, admission_date, antibiotic_create_date) > 4)
-- 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
-- 入院6小时内接受首剂抗生素病人
select "入院6小时内接受首剂抗生素" as indicator_name,
count(distinct uuid) as valid_count
from cap_statistics_antibiotic
where (TIMESTAMPDIFF(HOUR, admission_date, antibiotic_execute_date) < 6
or TIMESTAMPDIFF(HOUR, admission_date, antibiotic_create_date) < 6)
-- 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
-- 入院6小时内未接受首剂抗生素病人
select "入院6小时内未接受首剂抗生素" as indicator_name,
count(distinct uuid) as valid_count
from cap_statistics_antibiotic
where (TIMESTAMPDIFF(HOUR, admission_date, antibiotic_execute_date) > 6
or TIMESTAMPDIFF(HOUR, admission_date, antibiotic_create_date) > 6)
-- 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
-- 入院8小时内接受首剂抗生素病人
select "入院8小时内接受首剂抗生素" as indicator_name,
count(distinct uuid) as valid_count
from cap_statistics_antibiotic
where (TIMESTAMPDIFF(HOUR, admission_date, antibiotic_execute_date) < 8
or TIMESTAMPDIFF(HOUR, admission_date, antibiotic_create_date) < 8)
-- 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
-- 入院8小时内未接受首剂抗生素病人
select "入院8小时内未接受首剂抗生素" as indicator_name,
count(distinct uuid) as valid_count
from cap_statistics_antibiotic
where (TIMESTAMPDIFF(HOUR, admission_date, antibiotic_execute_date) > 8
or TIMESTAMPDIFF(HOUR, admission_date, antibiotic_create_date) > 8)
-- 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
-- 首剂抗生素
select "首剂抗生素中位数" as indicator_name,
first_time as valid_count
from (
select @ID2:=@ID2+1 AS ID, first_time from
(
select detail.uuid, TIMESTAMPDIFF(HOUR, csa.admission_date, if(antibiotic_execute_date is not null, antibiotic_execute_date, antibiotic_create_date)) as first_time from cap_statistics_antibiotic as csa
join yd_patient_11_basic_info as detail
on detail.uuid=csa.uuid
-- where { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
-- AND { inpatient_department = [ inpatient_department ]}
-- AND { disease_name = [disease_class_name ]}
-- AND { diagnosis_category = [ diagnose_class_name ]}
) as a, (SELECT @ID2:=0) as b
order by first_time
) t where id = ((@ID2+1) div 2)
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=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 ]}
)
union
-- 住院总天数
select "住院总天数" as indicator_name,
sum(DATEDIFF(discharge_date, admission_date)) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where disease_class_name="小儿肺炎"
-- and { admission_date BETWEEN [ startTime1 ]} AND {[ endTime1 ]}
-- AND { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
-- AND { discharge_dept_name = [ inpatient_department ]}
-- AND { disease_name = [disease_class_name ]}
union
-- 住院平均数
select "住院平均天数" as indicator_name,
avg(DATEDIFF(discharge_date, admission_date)) as valid_count
from indicator_statistics.yd_patient_11_basic_info
where disease_class_name="小儿肺炎"
-- and { admission_date BETWEEN [ startTime1 ]} AND {[ endTime1 ]}
-- AND { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
-- AND { discharge_dept_name = [ inpatient_department ]}
-- AND { disease_name = [disease_class_name ]}
union
-- 住院日中位数
SELECT
"住院日中位数" as indicator_name,
hospitalized_duration as valid_count
FROM
(
SELECT
@ID3 := @ID3 + 1 AS ID,
hospitalized_duration
FROM
(
SELECT
DATEDIFF( discharge_date, admission_date ) AS hospitalized_duration
FROM
indicator_statistics.yd_patient_11_basic_info
WHERE
disease_class_name = "小儿肺炎"
AND DATEDIFF( discharge_date, admission_date ) IS NOT NULL
-- and diagnose_class_name like "%肺炎%" and patient_age<18
-- and { admission_date BETWEEN [ startTime1 ]} AND {[ endTime1 ]}
-- AND { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
-- AND { discharge_dept_name = [ inpatient_department ]}
-- AND { disease_name = [disease_class_name ]}
) AS part1,
( SELECT @ID3 := 0 ) AS part2
ORDER BY
hospitalized_duration
) AS temp
WHERE
ID = ROUND( @ID3 / 2, 0 )
union
-- 疗程总天数
SELECT
"疗程总天数" as indicator_name,
sum(TIMESTAMPDIFF( DAY, part2.order_create_date, part4.order_stop_date )) AS valid_count
FROM
(
-- 最早一个抗生素的开立时间
SELECT
part1.uuid,
part1.order_create_date,
part1.order_stop_date
FROM
( SELECT * FROM cap_statistics_drug WHERE drug_category != 0 ORDER BY order_create_date LIMIT 999999 ) AS part1
GROUP BY
uuid
) AS part2
JOIN (
-- 最晚一个抗生素的停止时间
SELECT
part3.uuid,
part3.order_stop_date
FROM
( SELECT * FROM cap_statistics_drug WHERE drug_category != 0 ORDER BY order_stop_date DESC LIMIT 999999 ) AS part3
WHERE
part3.order_stop_date IS NOT NULL
-- and { admission_date BETWEEN [ startTime1 ]} AND {[ endTime1 ]}
-- AND { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
-- AND { discharge_dept_name = [ inpatient_department ]}
-- AND { disease_name = [disease_class_name ]}
GROUP BY
uuid
) AS part4 ON part2.uuid = part4.uuid
union
-- 疗程平均天数
SELECT
"疗程平均天数" as indicator_name,
avg(TIMESTAMPDIFF( DAY, part2.order_create_date, part4.order_stop_date )) AS valid_count
FROM
(
-- 最早一个抗生素的开立时间
SELECT
part1.uuid,
part1.order_create_date,
part1.order_stop_date
FROM
( SELECT * FROM cap_statistics_drug WHERE drug_category != 0 ORDER BY order_create_date LIMIT 999999 ) AS part1
GROUP BY
uuid
) AS part2
JOIN (
-- 最晚一个抗生素的停止时间
SELECT
part3.uuid,
part3.order_stop_date
FROM
( SELECT * FROM cap_statistics_drug WHERE drug_category != 0 ORDER BY order_stop_date DESC LIMIT 999999 ) AS part3
WHERE
part3.order_stop_date IS NOT NULL
-- and { admission_date BETWEEN [ startTime1 ]} AND {[ endTime1 ]}
-- AND { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
-- AND { discharge_dept_name = [ inpatient_department ]}
-- AND { disease_name = [disease_class_name ]}
GROUP BY
uuid
) AS part4 ON part2.uuid = part4.uuid
union
-- 疗程中位数
SELECT
"疗程中位数" as indicator_name,
duration as valid_count
FROM
(
SELECT
@ID1 := @ID1 + 1 AS ID,
duration
FROM
(
SELECT
TIMESTAMPDIFF( DAY, part2.order_create_date, part4.order_stop_date ) AS duration
FROM
(
-- 最早一个抗生素的开立时间
SELECT
part1.uuid,
part1.order_create_date,
part1.order_stop_date
FROM
( SELECT * FROM cap_statistics_drug WHERE drug_category != 0 ORDER BY order_create_date LIMIT 999999 ) AS part1
GROUP BY
uuid
) AS part2
JOIN (
-- 最晚一个抗生素的停止时间
SELECT
part3.uuid,
part3.order_stop_date
FROM
( SELECT * FROM cap_statistics_drug WHERE drug_category != 0 ORDER BY order_stop_date DESC LIMIT 999999 ) AS part3
WHERE
part3.order_stop_date IS NOT NULL -- and { admission_date BETWEEN [ startTime1 ]} AND {[ endTime1 ]}
-- AND { discharge_date BETWEEN [ startTime2 ]} AND {[ endTime2 ]}
-- AND { discharge_dept_name = [ inpatient_department ]}
-- AND { disease_name = [disease_class_name ]}
GROUP BY
uuid
) AS part4 ON part2.uuid = part4.uuid
) AS tmp,
( SELECT @ID1 := 0 ) AS id_cnt
ORDER BY
duration
) AS result
WHERE
id = ( ( @ID1 + 1 ) DIV 2 )
) 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
网友评论