xDev Asia

概念 (マップ先、ある、RxNorm には成分がある...) と祖先 - 子孫階層ツリー間の関係。 ETL マッピングと階層分析にとって最も重要なテーブル。

はじめに

CONCEPT が「辞書」である場合、CONCEPT_RELATIONSHIP は単語を結び付ける「地図」、CONCEPT_ANCESTOR は祖先と子孫の関係を示す「家系図」です。これら 2 つのテーブルは非常に重要です。CONCEPT_RELATIONSHIP は ETL (ICD-10 → SNOMED のマッピング) に使用され、CONCEPT_ANCESTOR は分析 (1 型、2 型、妊娠などを含むすべての「糖尿病」コードを検索) に使用されます。

1. コンセプト_関係

＃＃＃１．１．テーブル構造

コラム	タイプ	説明
`concept_id_1`	整数	FK → CONCEPT (出典)
`concept_id_2`	整数	FK → CONCEPT (目的地)
`relationship_id`	VARCHAR(20)	FK → 関係
`valid_start_date`	日付	開始日
`valid_end_date`	日付	有効期限
`invalid_reason`	VARCHAR(1)	NULL/U/D

＃＃＃１．２．関係が最も重要です

関係ID	意味	使用例
マップ先	ソース → 標準	ETL マッピング (コア!)
マッピング元	標準 → ソース	対照的に、
はです	息子→父	SNOMED 階層
包含	父→息子	逆に、は
RxNorm には成分があります	薬剤 → 成分	有効成分を探す
商品名あり	ジェネリック → ブランド	薬物マッピング

＃＃＃１．３．「マップ先」 — ETL にとって最も重要

  ICD10CM: E11 "Type 2 diabetes mellitus"
  concept_id = 45591837
  standard_concept = NULL (Non-standard)
       │
       │ relationship_id = 'Maps to'
       ↓
  SNOMED: "Type 2 diabetes mellitus"
  concept_id = 201826
  standard_concept = 'S' (Standard)

-- 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';

＃＃＃１．４．「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;

2. 関係テーブル

コラム	タイプ	説明
`relationship_id`	VARCHAR(20)	PK
`relationship_name`	VARCHAR(255)	関係名
`is_hierarchical`	VARCHAR(1)	1 = 階層
`defines_ancestry`	VARCHAR(1)	1 = 祖先を作成
`reverse_relationship_id`	VARCHAR(20)	逆関係
`relationship_concept_id`	整数	FK → コンセプト

3. CONCEPT_ANCESTOR — 完全な階層ツリー

＃＃＃３．１．テーブル構造

コラム	タイプ	説明
`ancestor_concept_id`	整数	FK → CONCEPT（先祖）
`descendant_concept_id`	整数	FK → CONCEPT (子孫)
`min_levels_of_separation`	整数	最小距離
`max_levels_of_separation`	整数	最大距離

＃＃＃３．２． CONCEPT_RELATIONSHIP と CONCEPT_ANCESTOR の比較

	コンセプト_リレーションシップ	CONCEPT_ANCESTOR
内容	直接連絡	すべての先祖 - 子孫
例	タイプ 2 DM → です → DM	タイプ 2 DM → すべての祖先
レベル	1 レベルのみ	n レベルを含む
用途	ETLマッピング	階層分析
自分自身を含む	いいえ	✅ (最小レベル = 0)

＃＃＃３．３． CONCEPT_ANCESTOR が必要な理由は何ですか?

CONCEPT_RELATIONSHIP には「1 レベル」の関係のみがあります。 すべてのタイプの糖尿病 (1 型、2 型、妊娠期、新生児など) を見つけたい場合は、ツリーを何度も参照する必要があります。 CONCEPT_ANCESTOR は事前に計算されます (事前に計算された推移閉包)。

-- 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

＃＃＃３．４．分析用途

-- Đế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)

-- 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

6. ETL マッピングプロセスを完了する

  Bước 1: Lấy mã nguồn
  HIS: ma_benh = 'E11.65'
       │
  Bước 2: Tìm Source Concept
       │ SELECT * FROM concept
       │ WHERE concept_code = 'E11.65'
       │   AND vocabulary_id = 'ICD10CM'
       ↓
  Source: concept_id = 45591837
       │
  Bước 3: Tìm Maps to
       │ SELECT * FROM concept_relationship
       │ WHERE concept_id_1 = 45591837
       │   AND relationship_id = 'Maps to'
       ↓
  Standard: concept_id = 201826
            domain_id = 'Condition'
       │
  Bước 4: Domain routing
       │ domain_id = 'Condition'
       ↓
  Lưu vào: CONDITION_OCCURRENCE
            condition_concept_id = 201826
            condition_source_concept_id = 45591837
            condition_source_value = 'E11.65'

概要

CONCEPT_RELATIONSHIP: 2 つの概念間の直接の関係
「マップ先」 = ETL にとって最も重要な関係 (ソース → 標準)
「Is a」 = SNOMED 階層 (子→親)
CONCEPT_ANCESTOR: すべての祖先/子孫を事前に計算しました
SOURCE_TO_CONCEPT_MAP: 内部コードのカスタムマッピング
階層構造を分析する場合は、常に CONCEPT_ANCESTOR を使用します。

次の記事: DRUG_STRENGTH と残りの語彙テーブル。

レッスン 15: CONCEPT_RELATIONSHIP と CONCEPT_ANCESTOR

はじめに

1. コンセプト_関係

2. 関係テーブル

3. CONCEPT_ANCESTOR — 完全な階層ツリー

4. SOURCE_TO_CONCEPT_MAP — カスタムマッピング

5. CONCEPT_SYNONYM — シノニム名

6. ETL マッピングプロセスを完了する

概要

参考文献

レッスン 15: CONCEPT_RELATIONSHIP と CONCEPT_ANCESTOR

はじめに

1. コンセプト_関係

2. 関係テーブル

3. CONCEPT_ANCESTOR — 完全な階層ツリー

4. SOURCE_TO_CONCEPT_MAP — カスタム マッピング

5. CONCEPT_SYNONYM — シノニム名

6. ETL マッピング プロセスを完了する

概要

参考文献

4. SOURCE_TO_CONCEPT_MAP — カスタムマッピング

6. ETL マッピングプロセスを完了する