-- Phẫu thuật cắt ruột thừa nội soi (bên phải)
INSERT INTO procedure_occurrence (
procedure_occurrence_id, person_id, procedure_concept_id,
procedure_date, procedure_end_date,
procedure_type_concept_id,
modifier_concept_id, quantity,
provider_id, visit_occurrence_id,
procedure_source_value
) VALUES (
90001, 100001, 44783086, -- SNOMED: Lapar appendectomy
'2024-06-15', '2024-06-15',
32817, -- EHR
4149625, 1, -- Right side, 1 time
5001, 50001,
'0DTJ4ZZ' -- ICD-10-PCS
);
5. ETL VN 數據
5.1。熱門資料來源
來源
描述
原始詞彙
醫院技術服務一覽
BV技術服務
內部代碼
ICD-9-CM 程序
舊手術代碼
ICD9 程序
社會保險一覽
社會保險服務代碼
內部保險代碼
5.2。 SQL ETL
SELECT
ROW_NUMBER() OVER() AS procedure_occurrence_id,
pm.person_id,
COALESCE(cr.concept_id_2, 0) AS procedure_concept_id,
tt.ngay_thuchien AS procedure_date,
tt.ngay_ketthuc AS procedure_end_date,
32817 AS procedure_type_concept_id,
0 AS modifier_concept_id,
tt.so_lan AS quantity,
tt.ma_dvkt AS procedure_source_value,
COALESCE(c_source.concept_id, 0) AS procedure_source_concept_id
FROM thuthuat_his tt
JOIN person_mapping pm ON tt.ma_bn = pm.source_id
LEFT JOIN source_to_concept_map stcm
ON tt.ma_dvkt = stcm.source_code
AND stcm.source_vocabulary_id = 'VN_PROCEDURE'
LEFT JOIN concept c_std
ON stcm.target_concept_id = c_std.concept_id
AND c_std.standard_concept = 'S'
AND c_std.domain_id = 'Procedure' -- ← Chỉ Procedure domain!
LEFT JOIN concept c_source
ON tt.ma_dvkt = c_source.concept_code
LEFT JOIN concept_relationship cr
ON c_source.concept_id = cr.concept_id_1
AND cr.relationship_id = 'Maps to';
5.3。處理域路由
-- DVKT "Xét nghiệm HbA1c" → mã CPT 83036
-- Standard concept domain = Measurement → KHÔNG lưu vào PROCEDURE
-- Bước 1: Tìm Standard Concept
SELECT c.*
FROM concept c
JOIN concept_relationship cr ON c.concept_id = cr.concept_id_2
AND cr.relationship_id = 'Maps to'
JOIN concept c_src ON cr.concept_id_1 = c_src.concept_id
WHERE c_src.concept_code = '83036'
AND c_src.vocabulary_id = 'CPT4';
-- domain_id = 'Measurement' → route sang MEASUREMENT table
-- Bước 2: Lưu vào đúng bảng
-- Nếu domain = 'Procedure' → procedure_occurrence
-- Nếu domain = 'Measurement' → measurement
-- Nếu domain = 'Observation' → observation
-- Nếu domain = 'Drug' → drug_exposure
6.SQL分析
-- Top 10 thủ thuật phổ biến
SELECT
c.concept_name AS procedure_name,
COUNT(*) AS procedure_count,
COUNT(DISTINCT po.person_id) AS patient_count
FROM procedure_occurrence po
JOIN concept c ON po.procedure_concept_id = c.concept_id
WHERE po.procedure_concept_id != 0
GROUP BY c.concept_name
ORDER BY procedure_count DESC
LIMIT 10;
-- Thống kê phẫu thuật theo tháng
SELECT
DATE_TRUNC('month', po.procedure_date) AS month,
c.concept_name AS procedure_name,
COUNT(*) AS total
FROM procedure_occurrence po
JOIN concept c ON po.procedure_concept_id = c.concept_id
WHERE po.procedure_concept_id = 44783086 -- Appendectomy
GROUP BY month, c.concept_name
ORDER BY month;
-- BN có cả chẩn đoán + phẫu thuật liên quan
SELECT
po.person_id,
co_cond.concept_name AS diagnosis,
co_proc.concept_name AS procedure_name,
co.condition_start_date,
po.procedure_date
FROM procedure_occurrence po
JOIN condition_occurrence co ON po.person_id = co.person_id
AND po.visit_occurrence_id = co.visit_occurrence_id
JOIN concept co_cond ON co.condition_concept_id = co_cond.concept_id
JOIN concept co_proc ON po.procedure_concept_id = co_proc.concept_id
WHERE co.condition_concept_id = 441604 -- Appendicitis
AND po.procedure_concept_id = 44783086 -- Appendectomy
LIMIT 20;