-- Tìm Standard Concept từ ICD-10 code
SELECT
c1.concept_code AS source_code,
c1.concept_name AS source_name,
c1.vocabulary_id AS source_vocab,
cr.relationship_id,
c2.concept_id AS standard_concept_id,
c2.concept_name AS standard_name,
c2.vocabulary_id AS standard_vocab,
c2.domain_id
FROM concept c1
JOIN concept_relationship cr
ON c1.concept_id = cr.concept_id_1
AND cr.relationship_id = 'Maps to'
AND cr.invalid_reason IS NULL
JOIN concept c2
ON cr.concept_id_2 = c2.concept_id
AND c2.standard_concept = 'S'
AND c2.invalid_reason IS NULL
WHERE c1.concept_code = 'E11'
AND c1.vocabulary_id = 'ICD10CM';
1.4。 “Is a” — SNOMED 層次結構
Diabetes mellitus (concept_id = 201820)
↑ Is a
├── Type 1 diabetes mellitus (201254)
│ ↑ Is a
│ ├── Type 1 DM without complication (435216)
│ └── Type 1 DM with ketoacidosis (443727)
│
├── Type 2 diabetes mellitus (201826)
│ ↑ Is a
│ ├── Type 2 DM without complication (443732)
│ └── Type 2 DM with peripheral angiopathy (318712)
│
└── Gestational diabetes (4058243)
-- Tìm concept cha trực tiếp
SELECT
c_parent.concept_id,
c_parent.concept_name
FROM concept_relationship cr
JOIN concept c_parent ON cr.concept_id_2 = c_parent.concept_id
WHERE cr.concept_id_1 = 201826 -- Type 2 DM
AND cr.relationship_id = 'Is a'
AND cr.invalid_reason IS NULL;
-- Tìm concept con trực tiếp
SELECT
c_child.concept_id,
c_child.concept_name
FROM concept_relationship cr
JOIN concept c_child ON cr.concept_id_1 = c_child.concept_id
WHERE cr.concept_id_2 = 201826 -- Type 2 DM
AND cr.relationship_id = 'Is a'
AND cr.invalid_reason IS NULL;
-- Tìm TẤT CẢ concept thuộc nhóm "Diabetes mellitus"
-- Bao gồm bản thân + mọi hậu duệ
SELECT
ca.descendant_concept_id,
c.concept_name,
ca.min_levels_of_separation AS levels
FROM concept_ancestor ca
JOIN concept c ON ca.descendant_concept_id = c.concept_id
WHERE ca.ancestor_concept_id = 201820 -- Diabetes mellitus
AND c.standard_concept = 'S'
ORDER BY ca.min_levels_of_separation, c.concept_name;
-- Kết quả: ~300+ concepts bao gồm mọi loại & biến chứng
3.4。分析應用
-- Đếm BN có BẤT KỲ loại tiểu đường nào
SELECT COUNT(DISTINCT co.person_id) AS dm_patients
FROM condition_occurrence co
WHERE co.condition_concept_id IN (
SELECT descendant_concept_id
FROM concept_ancestor
WHERE ancestor_concept_id = 201820 -- Diabetes mellitus
);
-- So sánh: KHÔNG dùng ancestor (chỉ bắt 1 loại)
SELECT COUNT(DISTINCT co.person_id) AS dm_type2_only
FROM condition_occurrence co
WHERE co.condition_concept_id = 201826; -- Chỉ Type 2 DM
-- → Bỏ sót Type 1, gestational, neonatal, with complications...!
4. SOURCE_TO_CONCEPT_MAP — 自訂映射
當詞彙沒有映射時使用（例如：越南醫院的內部代碼）。
專欄
類型
說明
source_code
VARCHAR(50)
原始碼
source_concept_id
整數
來源概念（若不可用則為 0）
source_vocabulary_id
VARCHAR(20)
自訂詞彙ID
source_code_description
VARCHAR(255)
描述
target_concept_id
整數
FK → 標準概念
target_vocabulary_id
VARCHAR(20)
字彙目的地
valid_start_date
日期
valid_end_date
日期
invalid_reason
VARCHAR(1)
VARCHAR(1)
-- Tạo mapping cho mã ICD-10-VN nội bộ
INSERT INTO source_to_concept_map (
source_code, source_concept_id,
source_vocabulary_id, source_code_description,
target_concept_id, target_vocabulary_id,
valid_start_date, valid_end_date
) VALUES
('E11', 0, 'VN_ICD10',
'Đái tháo đường type 2',
201826, 'SNOMED',
'2024-01-01', '2099-12-31'),
('METFORMIN500', 0, 'VN_DRUG',
'Metformin 500mg viên nén',
1503328, 'RxNorm',
'2024-01-01', '2099-12-31');
5. CONCEPT_SYNONYM — 同義詞名稱
專欄
類型
說明
concept_id
整數
FK → 概念
concept_synonym_name
VARCHAR(1000)
同義詞名稱
language_concept_id
整數
語言（4180186 = 英語）
-- Tìm concept qua tên đồng nghĩa
SELECT DISTINCT c.concept_id, c.concept_name
FROM concept_synonym cs
JOIN concept c ON cs.concept_id = c.concept_id
WHERE LOWER(cs.concept_synonym_name) LIKE '%heart attack%'
AND c.standard_concept = 'S';
-- Tìm được: Acute myocardial infarction