-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS mimiciv_derived.sepsis3;
CREATE TABLE mimiciv_derived.sepsis3 AS
WITH sofa AS (
SELECT
stay_id,
starttime,
endtime,
respiration_24hours AS respiration,
coagulation_24hours AS coagulation,
liver_24hours AS liver,
cardiovascular_24hours AS cardiovascular,
cns_24hours AS cns,
renal_24hours AS renal,
sofa_24hours AS sofa_score
FROM mimiciv_derived.sofa
WHERE sofa_24hours >= 2
),
s1 AS (
SELECT
soi.subject_id,
soi.stay_id,
soi.ab_id,
soi.antibiotic,
soi.antibiotic_time,
soi.culture_time,
soi.suspected_infection,
soi.suspected_infection_time,
soi.specimen,
soi.positive_culture,
s.starttime,
s.endtime,
s.respiration,
s.coagulation,
s.liver,
s.cardiovascular,
s.cns,
s.renal,
s.sofa_score,
(s.sofa_score >= 2 AND soi.suspected_infection = true) AS sepsis3,
ROW_NUMBER() OVER (
PARTITION BY soi.stay_id
ORDER BY soi.suspected_infection_time NULLS FIRST,
soi.antibiotic_time NULLS FIRST,
soi.culture_time NULLS FIRST,
s.endtime NULLS FIRST
) AS rn_sus
FROM mimiciv_derived.suspicion_of_infection AS soi
INNER JOIN sofa AS s
ON soi.stay_id = s.stay_id
AND s.endtime >= soi.suspected_infection_time - INTERVAL '48 HOUR'
AND s.endtime <= soi.suspected_infection_time + INTERVAL '24 HOUR'
WHERE soi.stay_id IS NOT NULL
),
-- 添加患者基本信息、住院信息和实验室检查
patient_data AS (
SELECT
s1.subject_id,
s1.stay_id,
s1.antibiotic_time,
s1.culture_time,
s1.suspected_infection_time,
s1.endtime AS sofa_time,
s1.sofa_score,
s1.respiration,
s1.coagulation,
s1.liver,
s1.cardiovascular,
s1.cns,
s1.renal,
s1.sepsis3,
p.gender,
p.anchor_age,
-- 计算住院时长
EXTRACT(EPOCH FROM (ie.outtime - ie.intime))/3600 AS hospital_stay_hours,
-- 计算发病至入组时间
EXTRACT(EPOCH FROM (s1.suspected_infection_time - ie.intime))/3600 AS onset_to_admission_hours,
-- 获取最近的白蛋白值
FIRST_VALUE(le.valuenum) OVER (
PARTITION BY le.subject_id
ORDER BY ABS(EXTRACT(EPOCH FROM (le.charttime - s1.suspected_infection_time)))
) AS albumin_value
FROM s1
INNER JOIN mimiciv_hosp.patients p
ON s1.subject_id = p.subject_id
INNER JOIN mimiciv_icu.icustays ie
ON s1.stay_id = ie.stay_id
LEFT JOIN mimiciv_hosp.labevents le
ON s1.subject_id = le.subject_id
AND le.itemid = 50862 -- 白蛋白的项目ID
AND le.charttime BETWEEN ie.intime - INTERVAL '24 HOUR' AND ie.intime + INTERVAL '24 HOUR'
WHERE s1.rn_sus = 1
AND s1.sepsis3 = true -- 修复这里，使用布尔值true而不是整数1
),
-- 排除标准
exclusion_criteria AS (
SELECT
pd.subject_id,
pd.stay_id,
-- 排除标准判断
MAX(CASE
WHEN dx.icd_code LIKE 'O%' OR dx.icd_code IN ('V22.0', 'V23.0', 'V24.0')
THEN 1 ELSE 0
END) AS pregnancy_exclude,
MAX(CASE
WHEN dx.icd_code IN ('K70.2', 'K70.3', 'K70.4', 'K71.7', 'K72.1', 'K72.9', 'K76.7')
THEN 1 ELSE 0
END) AS liver_disease_exclude,
MAX(CASE
WHEN dx.icd_code = 'N18.5' OR dx.icd_code IN ('Z49.0', 'Z49.1', 'Z99.2')
THEN 1 ELSE 0
END) AS renal_disease_exclude,
MAX(CASE
WHEN dx.icd_code LIKE 'F%' OR dx.icd_code IN ('291%', '292%', '293%', '294%', '295%', '296%', '297%', '298%')
THEN 1 ELSE 0
END) AS mental_health_exclude
FROM patient_data pd
LEFT JOIN mimiciv_hosp.diagnoses_icd dx
ON pd.subject_id = dx.subject_id
GROUP BY pd.subject_id, pd.stay_id
)
SELECT
pd.*,
CASE
WHEN ec.pregnancy_exclude = 1 OR
ec.liver_disease_exclude = 1 OR
ec.renal_disease_exclude = 1 OR
ec.mental_health_exclude = 1
THEN true ELSE false
END AS excluded,
-- 低蛋白血症分组
CASE
WHEN pd.albumin_value < 3.5 THEN '低蛋白血症组'
ELSE '非低蛋白血症组'
END AS albumin_group
FROM patient_data pd
LEFT JOIN exclusion_criteria ec
ON pd.subject_id = ec.subject_id AND pd.stay_id = ec.stay_id
WHERE
-- 纳入标准
pd.anchor_age >= 18
AND pd.hospital_stay_hours >= 24
AND pd.onset_to_admission_hours <= 24
-- 确保不符合任何排除标准
AND (ec.pregnancy_exclude = 0 AND
ec.liver_disease_exclude = 0 AND
ec.renal_disease_exclude = 0 AND
ec.mental_health_exclude = 0);不能用
本文探讨了IHTMLDocument2接口中get_Anchors方法存在的问题，并建议通过get_all方法结合检查tagName为a的方式作为更可靠的选择。
扫一扫
1022
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
