-- BN tử vong do nhồi máu cơ tim cấp
INSERT INTO death (
person_id, death_date,
death_type_concept_id,
cause_concept_id,
cause_source_value,
cause_source_concept_id
) VALUES (
100001, '2024-06-20',
32885, -- Death certificate
4329847, -- SNOMED: AMI
'I21.9', -- ICD-10
45572161 -- ICD10CM concept
);
1.5。 SQL分析
-- Top 10 nguyên nhân tử vong
SELECT
c.concept_name AS cause_of_death,
COUNT(*) AS death_count,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM death d
JOIN concept c ON d.cause_concept_id = c.concept_id
WHERE d.cause_concept_id != 0
GROUP BY c.concept_name
ORDER BY death_count DESC
LIMIT 10;
-- Tỉ lệ tử vong sau nhập viện ICU
SELECT
ROUND(
COUNT(DISTINCT d.person_id) * 100.0 /
NULLIF(COUNT(DISTINCT v.person_id), 0), 1
) AS mortality_rate_pct
FROM visit_occurrence v
LEFT JOIN death d ON v.person_id = d.person_id
AND d.death_date BETWEEN v.visit_start_date
AND v.visit_start_date + INTERVAL '30 days'
WHERE v.visit_concept_id = 32037; -- ICU visit
-- Chẩn đoán ung thư → Episode chẩn đoán
INSERT INTO episode_event VALUES (
200001, -- Episode: lung cancer
70010, -- condition_occurrence_id
1147127 -- condition_occurrence table
);
-- Hóa trị Cisplatin → Episode Cycle 1
INSERT INTO episode_event VALUES (
200003, -- Episode: Cycle 1
80010, -- drug_exposure_id (Cisplatin)
1147094 -- drug_exposure table
);
-- XN máu trước hóa trị → Episode Cycle 1
INSERT INTO episode_event VALUES (
200003,
110020, -- measurement_id (CBC)
1147138 -- measurement table
);
4. EPISODE在研究中的应用
-- Tìm BN ung thư phổi có >= 4 cycle hóa trị
SELECT
e_disease.person_id,
c_disease.concept_name AS cancer_type,
COUNT(e_cycle.episode_id) AS total_cycles
FROM episode e_disease
JOIN concept c_disease
ON e_disease.episode_object_concept_id = c_disease.concept_id
JOIN episode e_regimen
ON e_disease.episode_id = e_regimen.episode_parent_id
AND e_regimen.episode_concept_id = 32531 -- Treatment regimen
JOIN episode e_cycle
ON e_regimen.episode_id = e_cycle.episode_parent_id
AND e_cycle.episode_concept_id = 32532 -- Treatment cycle
WHERE e_disease.episode_concept_id = 32528 -- First occurrence
AND c_disease.concept_id = 4311499 -- Lung cancer
GROUP BY e_disease.person_id, c_disease.concept_name
HAVING COUNT(e_cycle.episode_id) >= 4;
-- Timeline điều trị 1 BN
SELECT
e.episode_number,
ec.concept_name AS episode_type,
e.episode_start_date,
e.episode_end_date,
oc.concept_name AS episode_object
FROM episode e
JOIN concept ec ON e.episode_concept_id = ec.concept_id
JOIN concept oc ON e.episode_object_concept_id = oc.concept_id
WHERE e.person_id = 100001
ORDER BY e.episode_start_date, e.episode_number;