はじめに
OMOP CDM (Common Data Model) は、OHDSI エコシステム全体の中核基盤です。 ATLAS、WebAPI、ACHILLES、HADES などのすべてのツールは、このモデルに従って正規化されたデータを操作します。
この記事では、OMOP CDM v5.4 の構造、設計原則、および主要な領域について詳しく説明します。
1. OMOP CDM 設計原則
1.1 基本理念
1. Patient-Centric (Lấy bệnh nhân làm trung tâm)
→ Mọi dữ liệu gắn với PERSON
2. Event-Based (Dựa trên sự kiện)
→ Mỗi record = 1 sự kiện y tế (chẩn đoán, kê thuốc, xét nghiệm...)
3. Concept-Oriented (Dựa trên khái niệm)
→ Mỗi sự kiện gắn với Standard Concept từ Vocabulary
4. Source-Preserving (Giữ nguyên dữ liệu gốc)
→ Luôn có cột source_value, source_concept_id bên cạnh standard
5. Platform-Agnostic (Không phụ thuộc nền tảng)
→ Chạy trên PostgreSQL, SQL Server, Oracle, Spark, Databricks...
1.2 人物訪問イベントモデル
PERSON (bệnh nhân)
│
│ 1:N
▼
VISIT_OCCURRENCE (lượt khám)
│
│ 1:N
┌─────────┼─────────────────────────────┐
▼ ▼ ▼ ▼ ▼
CONDITION DRUG PROCEDURE MEASURE OBSERVATION
(chẩn đoán) (thuốc) (thủ thuật) (XN) (quan sát)
実際の例:
-- Bệnh nhân đến khám ngày 2024-03-01
-- Được chẩn đoán Tăng huyết áp (I10), kê Amlodipine 5mg,
-- Xét nghiệm Glucose máu: 126 mg/dL
PERSON: person_id = 12345
VISIT: visit_id = 67890, visit_date = 2024-03-01
CONDITION: condition_concept_id = 320128 (Essential HTN)
DRUG_EXPOSURE: drug_concept_id = 1332419 (Amlodipine 5mg)
MEASUREMENT: measurement_concept_id = 3004410 (Glucose)
value_as_number = 126, unit_concept_id = 8840 (mg/dL)
2. OMOP CDM v5.4 のテーブル グループ
2.1 概要
OMOP CDM v5.4
├── Standardized Vocabularies (15 tables)
│ ├── CONCEPT
│ ├── VOCABULARY
│ ├── DOMAIN
│ ├── CONCEPT_CLASS
│ ├── CONCEPT_RELATIONSHIP
│ ├── RELATIONSHIP
│ ├── CONCEPT_SYNONYM
│ ├── CONCEPT_ANCESTOR
│ ├── SOURCE_TO_CONCEPT_MAP
│ ├── DRUG_STRENGTH
│ └── ...
│
├── Standardized Clinical Data (12 tables)
│ ├── PERSON
│ ├── OBSERVATION_PERIOD
│ ├── VISIT_OCCURRENCE
│ ├── VISIT_DETAIL
│ ├── CONDITION_OCCURRENCE
│ ├── DRUG_EXPOSURE
│ ├── PROCEDURE_OCCURRENCE
│ ├── DEVICE_EXPOSURE
│ ├── MEASUREMENT
│ ├── OBSERVATION
│ ├── NOTE
│ └── NOTE_NLP
│
├── Standardized Health System (2 tables)
│ ├── LOCATION
│ └── CARE_SITE
│
├── Standardized Health Economics (2 tables)
│ ├── PAYER_PLAN_PERIOD
│ └── COST
│
├── Standardized Derived Elements (4 tables)
│ ├── DRUG_ERA
│ ├── DOSE_ERA
│ ├── CONDITION_ERA
│ └── EPISODE + EPISODE_EVENT
│
├── Results Schema
│ ├── COHORT
│ └── COHORT_DEFINITION
│
└── Metadata
├── CDM_SOURCE
└── METADATA
3. 重要な臨床データの表
3.1人
CREATE TABLE person (
person_id BIGINT NOT NULL, -- PK, auto-generated
gender_concept_id INT NOT NULL, -- 8507=Male, 8532=Female
year_of_birth INT NOT NULL,
month_of_birth INT NULL,
day_of_birth INT NULL,
birth_datetime TIMESTAMP NULL,
race_concept_id INT NOT NULL,
ethnicity_concept_id INT NOT NULL,
location_id BIGINT NULL,
care_site_id BIGINT NULL,
person_source_value VARCHAR(50) NULL, -- Mã BN gốc
gender_source_value VARCHAR(50) NULL,
gender_source_concept_id INT NULL
);
重要な注意事項:
person_id元の患者コードではありません (プライバシー)- 元の患者コードは次の場所に保存されます。
person_source_value - 性別、人種、民族はすべて標準コンセプト ID を使用します
3.2 VISIT_OCCURRENCE
CREATE TABLE visit_occurrence (
visit_occurrence_id BIGINT NOT NULL, -- PK
person_id BIGINT NOT NULL, -- FK → person
visit_concept_id INT NOT NULL, -- Loại visit
visit_start_date DATE NOT NULL,
visit_start_datetime TIMESTAMP NULL,
visit_end_date DATE NOT NULL,
visit_end_datetime TIMESTAMP NULL,
visit_type_concept_id INT NOT NULL, -- Nguồn dữ liệu
care_site_id BIGINT NULL,
visit_source_value VARCHAR(50) NULL,
visit_source_concept_id INT NULL
);
-- visit_concept_id phổ biến:
-- 9201 = Inpatient Visit (nội trú)
-- 9202 = Outpatient Visit (ngoại trú)
-- 9203 = Emergency Room Visit (cấp cứu)
-- 262 = Emergency Room and Inpatient Visit
3.3 条件発生
CREATE TABLE condition_occurrence (
condition_occurrence_id BIGINT NOT NULL,
person_id BIGINT NOT NULL,
condition_concept_id INT NOT NULL, -- Standard Concept
condition_start_date DATE NOT NULL,
condition_start_datetime TIMESTAMP NULL,
condition_end_date DATE NULL,
condition_end_datetime TIMESTAMP NULL,
condition_type_concept_id INT NOT NULL,
condition_status_concept_id INT NULL,
stop_reason VARCHAR(20) NULL,
visit_occurrence_id BIGINT NULL, -- FK → visit
condition_source_value VARCHAR(50) NULL, -- Mã ICD gốc: "I10"
condition_source_concept_id INT NULL -- ICD concept: 45566052
);
二重概念パターン (非常に重要):
condition_concept_id = 320128 ← SNOMED "Essential HTN" (Standard)
condition_source_concept_id = 45566052 ← ICD-10CM "I10" (Source)
condition_source_value = "I10" ← Giá trị text gốc
3.4 薬物暴露
CREATE TABLE drug_exposure (
drug_exposure_id BIGINT NOT NULL,
person_id BIGINT NOT NULL,
drug_concept_id INT NOT NULL, -- RxNorm concept
drug_exposure_start_date DATE NOT NULL,
drug_exposure_start_datetime TIMESTAMP NULL,
drug_exposure_end_date DATE NOT NULL,
drug_type_concept_id INT NOT NULL,
stop_reason VARCHAR(20) NULL,
refills INT NULL,
quantity NUMERIC NULL,
days_supply INT NULL,
sig TEXT NULL, -- Hướng dẫn sử dụng
route_concept_id INT NULL, -- Đường dùng: oral, IV
lot_number VARCHAR(50) NULL,
visit_occurrence_id BIGINT NULL,
drug_source_value VARCHAR(50) NULL, -- Tên thuốc gốc
drug_source_concept_id INT NULL,
route_source_value VARCHAR(50) NULL,
dose_unit_source_value VARCHAR(50) NULL
);
3.5 測定
CREATE TABLE measurement (
measurement_id BIGINT NOT NULL,
person_id BIGINT NOT NULL,
measurement_concept_id INT NOT NULL, -- LOINC concept
measurement_date DATE NOT NULL,
measurement_datetime TIMESTAMP NULL,
measurement_type_concept_id INT NOT NULL,
operator_concept_id INT NULL, -- =, <, >, <=, >=
value_as_number NUMERIC NULL, -- Giá trị số
value_as_concept_id INT NULL, -- Giá trị mã (Pos/Neg)
unit_concept_id INT NULL, -- Đơn vị đo (UCUM)
range_low NUMERIC NULL, -- Khoảng tham chiếu
range_high NUMERIC NULL,
visit_occurrence_id BIGINT NULL,
measurement_source_value VARCHAR(50) NULL,
measurement_source_concept_id INT NULL,
unit_source_value VARCHAR(50) NULL,
value_source_value VARCHAR(50) NULL
);
4. 標準化された語彙
4.1 コンセプトテーブル — すべての中心
CREATE TABLE concept (
concept_id INT NOT NULL, -- Unique ID
concept_name VARCHAR(255) NOT NULL, -- Tên concept
domain_id VARCHAR(20) NOT NULL, -- Condition, Drug, Measurement...
vocabulary_id VARCHAR(20) NOT NULL, -- SNOMED, RxNorm, LOINC...
concept_class_id VARCHAR(20) NOT NULL, -- Clinical Finding, Ingredient...
standard_concept VARCHAR(1) NULL, -- 'S'=Standard, 'C'=Class, NULL=Non-standard
concept_code VARCHAR(50) NOT NULL, -- Mã gốc: "38341003"
valid_start_date DATE NOT NULL,
valid_end_date DATE NOT NULL, -- 2099-12-31 = vẫn active
invalid_reason VARCHAR(1) NULL -- NULL=Valid, 'D'=Deleted, 'U'=Upgraded
);
4.2 主要な語彙領域
Domain Vocabulary Ví dụ
─────────────────────────────────────────────────────────
Condition SNOMED CT Tăng huyết áp, Đái tháo đường
Drug RxNorm Amlodipine 5mg, Metformin 500mg
Measurement LOINC Glucose máu, HbA1c, Creatinine
Procedure SNOMED CT/CPT4 Phẫu thuật, nội soi, siêu âm
Observation SNOMED CT Tiền sử gia đình, thói quen
Device SNOMED CT Stent, pacemaker
Spec Anatomic Site SNOMED CT Tim, gan, thận
Unit UCUM mg/dL, mmol/L, kg
Gender Gender Male, Female
Race Race Asian, White, Black
4.3 標準とソースの概念
Source Data: "I10" (ICD-10-CM)
│
│ "Maps to" relationship
▼
Standard Concept: "Essential hypertension" (SNOMED CT, concept_id=320128)
Quy tắc:
- Phân tích DÙng standard_concept = 'S' (Standard)
- Source concept giữ lại để truy xuất ngược
- Một source concept có thể map sang nhiều standard concepts
5. 派生要素 — 事前計算されたテーブル
5.1 ERA テーブル
CONDITION_ERA: Gộp các condition liên tiếp thành 1 "era"
Timeline:
├── Condition A (01/01 - 15/01)
├── Gap 10 ngày
├── Condition A (25/01 - 10/02)
└── CONDITION_ERA: 01/01 - 10/02 (gap_days ≤ 30 → merge)
DRUG_ERA: Tương tự, gộp drug exposures liên tiếp
├── Drug X 30 ngày supply (01/01)
├── Drug X 30 ngày supply (01/02)
└── DRUG_ERA: 01/01 - 02/03 (liên tục dùng thuốc)
5.2 意味
- CONDITION_ERA: 患者はどのくらいの期間、X という病気を患っていますか? (持続時間)
- DRUG_ERA: 患者は薬 Y をどれくらい継続的に使用していますか? （遵守）
- ETLまたはACHILLESによって自動計算されます
6. ERD — テーブル間の関係
┌──────────────┐
│ LOCATION │
└──────┬───────┘
│
┌──────────────┐ ┌──────────────┐ ┌──┴───────────┐
│ CDM_SOURCE │ │ CARE_SITE │◄───│ PERSON │
└──────────────┘ └──────────────┘ └──────┬───────┘
│
┌──────────┴──────────┐
│ OBSERVATION_PERIOD │
└──────────┬──────────┘
│
┌──────────┴──────────┐
│ VISIT_OCCURRENCE │
└──────────┬──────────┘
│
┌──────────┬──────────┬────────────────┼────────────┐
▼ ▼ ▼ ▼ ▼
┌──────────┐ ┌────────┐ ┌───────────┐ ┌───────────┐ ┌─────────┐
│CONDITION │ │ DRUG │ │PROCEDURE │ │MEASUREMENT│ │OBSERV │
│OCCURRENCE│ │EXPOSURE│ │OCCURRENCE │ │ │ │ATION │
└──────────┘ └────────┘ └───────────┘ └───────────┘ └─────────┘
│ │
▼ ▼
┌──────────┐ ┌────────┐
│CONDITION │ │DRUG_ERA│
│ERA │ │DOSE_ERA│
└──────────┘ └────────┘
7. クエリの例
7.1 性別ごとに患者を数える
SELECT
c.concept_name AS gender,
COUNT(*) AS patient_count
FROM person p
JOIN concept c ON p.gender_concept_id = c.concept_id
GROUP BY c.concept_name;
7.2 一般的な診断トップ 10
SELECT
c.concept_name AS condition_name,
COUNT(DISTINCT co.person_id) AS patient_count
FROM condition_occurrence co
JOIN concept c ON co.condition_concept_id = c.concept_id
WHERE c.standard_concept = 'S'
GROUP BY c.concept_name
ORDER BY patient_count DESC
LIMIT 10;
7.3 アムロジピンを服用している高血圧患者
SELECT COUNT(DISTINCT p.person_id)
FROM person p
JOIN condition_occurrence co ON p.person_id = co.person_id
JOIN drug_exposure de ON p.person_id = de.person_id
WHERE co.condition_concept_id = 320128 -- Essential HTN
AND de.drug_concept_id = 1332419 -- Amlodipine 5mg
AND de.drug_exposure_start_date >= co.condition_start_date;
概要
| コンセプト | 説明 |
|---|---|
| OMOP CDM v5.4 | 最大 37 個のテーブルを含む汎用データ モデル |
| 人物訪問イベント | 訪問経由の個人に関連するすべてのイベント |
| デュアルコンセプト | 標準コンセプト + ソースコンセプトを並行 |
| スタンダードコンセプト | 分析で使用される標準概念 (SNOMED、RxNorm、LOINC) |
| ソースコンセプト | ソースデータからのオリジナルコンセプト (ICD-10、内部コード) |
| ERA テーブル | 派生したテーブルは、連続するイベントを期間ごとにグループ化します。 |
次の記事: Athena — 標準化された語彙の検索と管理