INSERT INTO observation (
observation_id, person_id, observation_concept_id,
observation_date, observation_type_concept_id,
value_as_concept_id,
observation_source_value
) VALUES (
130001, 100001,
4275495, -- Tobacco smoking behavior
'2024-06-15', 32817,
4298794, -- Current every day smoker
'SMOKING_STATUS'
);
3.2。藥物過敏
-- Dị ứng Penicillin
INSERT INTO observation (
observation_id, person_id, observation_concept_id,
observation_date, observation_type_concept_id,
value_as_concept_id,
qualifier_concept_id,
observation_source_value
) VALUES (
130002, 100001,
439224, -- Allergy to substance
'2024-06-15', 32817,
1713332, -- Penicillin (RxNorm ingredient)
4129512, -- Severe (qualifier)
'ALLERGY_PENICILLIN'
);
3.3。家族史
-- Mẹ bị ung thư vú
INSERT INTO observation (
observation_id, person_id, observation_concept_id,
observation_date, observation_type_concept_id,
value_as_concept_id,
qualifier_concept_id,
observation_source_value
) VALUES (
130003, 100001,
4167217, -- Family history of clinical finding
'2024-06-15', 32817,
4112853, -- Malignant neoplasm of breast
4166847, -- Mother (qualifier)
'FHX_BREAST_CANCER_MOTHER'
);
4.observation_event_id — CDM 5.4
與measurement_event_id類似，允許將觀察結果連結到其他事件。
-- Ghi nhận: "Lý do nhập viện: Đau ngực"
-- Liên kết với visit_occurrence_id = 50001
INSERT INTO observation (
observation_id, person_id, observation_concept_id,
observation_date, observation_type_concept_id,
value_as_concept_id,
observation_event_id,
obs_event_field_concept_id
) VALUES (
130004, 100001,
4148832, -- Chief complaint
'2024-06-15', 32817,
77670, -- Chest pain
50001, -- visit_occurrence_id
1147082 -- Field = visit_occurrence.visit_occurrence_id
);
5. qualifier_concept_id — 新增上下文
概念 ID
預選賽
用於
4129512
4129512嚴重
嚴重程度
4148136
4148136溫和
輕度水平
4129511
4129511中
中等程度
4166847
4166847媽媽
家庭關係
4166848
4166848父親
家庭關係
4192403
4192403兄弟姊妹
兄弟/姊妹
4167233
4167233一級親屬
一級親屬
6.VN數據ETL
-- Tiền sử từ HIS
SELECT
ROW_NUMBER() OVER() AS observation_id,
pm.person_id,
COALESCE(stcm.target_concept_id, 0) AS observation_concept_id,
ts.ngay_ghinhan AS observation_date,
32817 AS observation_type_concept_id,
-- Map giá trị
CASE ts.loai_tiensu
WHEN 'HUT_THUOC' THEN
CASE ts.gia_tri
WHEN 'CO' THEN 4298794 -- Current smoker
WHEN 'DA_BO' THEN 4144272 -- Former smoker
WHEN 'KHONG' THEN 4144273 -- Never smoker
END
WHEN 'DI_UNG' THEN
COALESCE(stcm_drug.target_concept_id, 0)
END AS value_as_concept_id,
ts.mo_ta AS value_as_string,
ts.ma_tiensu AS observation_source_value
FROM tiensu_his ts
JOIN person_mapping pm ON ts.ma_bn = pm.source_id
LEFT JOIN source_to_concept_map stcm
ON ts.loai_tiensu = stcm.source_code
AND stcm.source_vocabulary_id = 'VN_OBS_TYPE'
LEFT JOIN source_to_concept_map stcm_drug
ON ts.gia_tri = stcm_drug.source_code
AND stcm_drug.source_vocabulary_id = 'VN_DRUG';
7. SQL分析
-- Tỉ lệ hút thuốc theo giới tính
SELECT
g.concept_name AS gender,
s.concept_name AS smoking_status,
COUNT(DISTINCT o.person_id) AS patients
FROM observation o
JOIN person p ON o.person_id = p.person_id
JOIN concept g ON p.gender_concept_id = g.concept_id
JOIN concept s ON o.value_as_concept_id = s.concept_id
WHERE o.observation_concept_id = 4275495 -- Tobacco smoking
GROUP BY g.concept_name, s.concept_name
ORDER BY g.concept_name, patients DESC;
-- Top dị ứng thuốc
SELECT
c.concept_name AS allergen,
COUNT(DISTINCT o.person_id) AS patients
FROM observation o
JOIN concept c ON o.value_as_concept_id = c.concept_id
WHERE o.observation_concept_id = 439224 -- Allergy
GROUP BY c.concept_name
ORDER BY patients DESC
LIMIT 10;