-- SQL ETL
SELECT
ROW_NUMBER() OVER() AS condition_occurrence_id,
pm.person_id,
COALESCE(cr.concept_id_2, 0) AS condition_concept_id,
cd.ngay_chandoan AS condition_start_date,
NULL AS condition_end_date,
32817 AS condition_type_concept_id,
CASE cd.loai_chandoan
WHEN 'CHINH' THEN 32902 -- Primary
WHEN 'PHU' THEN 32908 -- Secondary
ELSE 0
END AS condition_status_concept_id,
cd.ma_icd10 AS condition_source_value,
COALESCE(c_source.concept_id, 0) AS condition_source_concept_id
FROM chandoan_his cd
JOIN person_mapping pm ON cd.ma_bn = pm.source_id
LEFT JOIN concept c_source
ON cd.ma_icd10 = c_source.concept_code
AND c_source.vocabulary_id = 'ICD10CM'
LEFT JOIN concept_relationship cr
ON c_source.concept_id = cr.concept_id_1
AND cr.relationship_id = 'Maps to'
LEFT JOIN concept c_std
ON cr.concept_id_2 = c_std.concept_id
AND c_std.standard_concept = 'S';
4.2。越南特定數據處理
問題
解決方案
ICD-10-VN 與 ICD-10-CM 不同
透過 SOURCE_TO_CONCEPT_MAP
BV內部程式碼
Usagi 繪圖工具
缺少結束日期
條件結束日期 = NULL（有效）
1 ICD 地圖許多 SNOMED
選擇最適合的理念
5. 區分條件與觀察
標準
CONDITION_OCCURRENCE
條件發生觀察
內容
目前生病/正在治療
史前、生活方式、記錄
範例
“第2型糖尿病”
“糖尿病家族史”
域名
條件
觀察
標準詞彙
SNOMED CT
SNOMED CT
**什麼時候？ **
活動性疾病
記錄資訊
規則： 請務必檢查 Athena 上 Standard Concept 的 domain_id。如果域 =“觀察”，則保存到“觀察”，即使來源是 ICD-10。
6.流行SQL分析
-- Top 10 chẩn đoán phổ biến nhất
SELECT
c.concept_name AS condition_name,
COUNT(DISTINCT co.person_id) AS patient_count,
COUNT(*) AS record_count
FROM condition_occurrence co
JOIN concept c ON co.condition_concept_id = c.concept_id
WHERE co.condition_concept_id != 0
GROUP BY c.concept_name
ORDER BY patient_count DESC
LIMIT 10;
-- Tỉ lệ mắc bệnh theo giới tính
SELECT
g.concept_name AS gender,
c.concept_name AS condition_name,
COUNT(DISTINCT co.person_id) AS patients
FROM condition_occurrence co
JOIN person p ON co.person_id = p.person_id
JOIN concept g ON p.gender_concept_id = g.concept_id
JOIN concept c ON co.condition_concept_id = c.concept_id
WHERE co.condition_concept_id = 201826 -- Type 2 DM
GROUP BY g.concept_name, c.concept_name;
-- Comorbidity: BN tiểu đường có tăng huyết áp?
SELECT
COUNT(DISTINCT co_dm.person_id) AS dm_patients,
COUNT(DISTINCT co_ht.person_id) AS dm_with_hypertension,
ROUND(
COUNT(DISTINCT co_ht.person_id) * 100.0 /
NULLIF(COUNT(DISTINCT co_dm.person_id), 0), 1
) AS comorbidity_pct
FROM condition_occurrence co_dm
LEFT JOIN condition_occurrence co_ht
ON co_dm.person_id = co_ht.person_id
AND co_ht.condition_concept_id IN (
SELECT descendant_concept_id
FROM concept_ancestor
WHERE ancestor_concept_id = 320128 -- Essential hypertension
)
WHERE co_dm.condition_concept_id IN (
SELECT descendant_concept_id
FROM concept_ancestor
WHERE ancestor_concept_id = 201826 -- Type 2 DM
);
