图片来源：https://openhis.org.cn/open/medical

以 OpenHIS 医院系统（由天天开源提供）为例，其包含门诊、住院、医技、后勤、手术、病案、物资耗材等多个子系统，数据来源复杂，结构多样。若缺乏统一的数据治理与分析平台，医院将无法实现精细化运营、科学决策和绩效考核。

一、背景与挑战

1.1 OpenHIS 系统现状（基于官网图示）

从《OpenHIS 医院系统》架构图可见，系统分为四大业务板块：

• 门诊业务：挂号、收费、医生工作站
• 住院业务：入院登记、电子病历、护理记录
• 医技与康复：检验检查、康复评估
• 后勤管理：药房、库存、财务、医保

同时还有多个独立系统模块，如：

• 收费结算系统（含医保对账）
• 病案管理系统
• 手术管理系统
• 物资耗材系统
• 电子病历系统

这些系统虽通过接口互通，但：

• 每个系统使用独立数据库
• 字段命名不一致（如“病人ID”可能叫 patient_id
   或 p_id
  ）
• 时间戳格式不同（部分为 datetime
  ，部分为 varchar
  ）
• 缺少主键关联逻辑（如处方与药品未建立外键）
• 多次重复录入（同一患者多次就诊生成多个档案）

👉 结果：管理者无法查看“某科室月度收入”、“某医生手术量”、“药品成本利润”等关键指标。

二、目标：构建医院级数据仓库

我们设计如下四层架构：

我们将围绕 “门诊患者诊疗全流程” 和 “药品进销存分析” 两个核心场景展开建模。

三、ODS 层：原始数据接入

ODS 层直接映射源系统表结构，仅做全量或增量同步，保留原始信息。

3.1 门诊挂号表（ods_outpatient_registration）

CREATE TABLE ods_outpatient_registration (
    id BIGINT COMMENT'主键ID',
    patient_id VARCHAR(50) COMMENT'患者ID',
    patient_name VARCHAR(50) COMMENT'患者姓名',
    sex CHAR(1) COMMENT'性别 M/F',
    age INT COMMENT'年龄',
    phone VARCHAR(20) COMMENT'联系电话',
    visit_date DATECOMMENT'就诊日期',
    dept_id VARCHAR(20) COMMENT'科室ID',
    dept_name VARCHAR(100) COMMENT'科室名称',
    doctor_id VARCHAR(20) COMMENT'医生ID',
    doctor_name VARCHAR(100) COMMENT'医生姓名',
    register_time TIMESTAMP COMMENT'挂号时间',
    status VARCHAR(10) COMMENT'状态：待就诊/已就诊/取消',
    create_time TIMESTAMP COMMENT'创建时间',
    update_time TIMESTAMP COMMENT'更新时间'
) PARTITIONED BY (dt STRING);

“

✅ 来源：OpenHIS 门诊挂号管理模块
⚠️ 注意：patient_id
 可能为空或重复，需后续清洗

3.2 门诊收费明细表（ods_outpatient_charge）

CREATE TABLE ods_outpatient_charge (
    charge_id BIGINT COMMENT'收费单号',
    patient_id VARCHAR(50) COMMENT'患者ID',
    visit_no VARCHAR(50) COMMENT'就诊号',
    item_code VARCHAR(50) COMMENT'项目编码',
    item_name VARCHAR(100) COMMENT'项目名称',
    quantity DECIMAL(10,2) COMMENT'数量',
    unit_price DECIMAL(10,2) COMMENT'单价',
    total_amount DECIMAL(10,2) COMMENT'金额',
    charge_type VARCHAR(20) COMMENT'收费类型：药品/检查/治疗',
    pay_mode VARCHAR(20) COMMENT'支付方式：现金/医保/自费',
    charge_time TIMESTAMP COMMENT'收费时间',
    operator_id VARCHAR(20) COMMENT'操作员ID',
    remark VARCHAR(200) COMMENT'备注',
    create_time TIMESTAMP COMMENT'创建时间'
) PARTITIONED BY (dt STRING);

“

✅ 来源：收费结算系统 → 门诊收费模块
⚠️ 问题：item_code
 无字典映射，需对接字典表

3.3 药品销售明细表（ods_pharmacy_sale）

CREATE TABLE ods_pharmacy_sale (
    sale_id BIGINT COMMENT'销售ID',
    drug_id VARCHAR(50) COMMENT'药品ID',
    drug_name VARCHAR(100) COMMENT'药品名称',
    spec VARCHAR(50) COMMENT'规格',
    unit VARCHAR(10) COMMENT'单位：盒/瓶/支',
    qty INTCOMMENT'数量',
    price_per_unit DECIMAL(10,2) COMMENT'单价',
    total_price DECIMAL(10,2) COMMENT'总价',
    patient_id VARCHAR(50) COMMENT'患者ID',
    prescription_id VARCHAR(50) COMMENT'处方ID',
    sale_time TIMESTAMP COMMENT'销售时间',
    pharmacy_type VARCHAR(10) COMMENT'药房类型：门诊/住院',
    operator_id VARCHAR(20) COMMENT'操作员ID',
    batch_no VARCHAR(50) COMMENT'批号',
    expiry_date DATE COMMENT'有效期',
    is_prescription BOOLEAN COMMENT'是否处方药'
) PARTITIONED BY (dt STRING);

“

✅ 来源：药房药库系统 → 门诊药房模块
⚠️ 问题：drug_id
 未标准化，存在同名异码现象

3.4 住院费用明细表（ods_inpatient_fee）

CREATE TABLE ods_inpatient_fee (
    fee_id BIGINT COMMENT'费用ID',
    patient_id VARCHAR(50) COMMENT'患者ID',
    inpatient_no VARCHAR(50) COMMENT'住院号',
    item_code VARCHAR(50) COMMENT'项目编码',
    item_name VARCHAR(100) COMMENT'项目名称',
    category VARCHAR(20) COMMENT'类别：床位/药品/手术',
    amount DECIMAL(10,2) COMMENT'金额',
    charge_date DATE COMMENT'计费日期',
    department_id VARCHAR(20) COMMENT'科室ID',
    doctor_id VARCHAR(20) COMMENT'责任医生ID',
    payment_status VARCHAR(10) COMMENT'支付状态：已付/未付',
    create_time TIMESTAMP COMMENT'创建时间'
) PARTITIONED BY (dt STRING);

“

✅ 来源：住院收费系统
⚠️ 注意：inpatient_no
 是唯一标识，用于关联住院主表

四、DWD 层：明细数据清洗与标准化

DWD 层对 ODS 数据进行清洗、去重、补全、统一命名，并建立主键关系。

4.1 清洗逻辑说明

4.2 患者基础信息表（dwd_patient_dim）

CREATE TABLE dwd_patient_dim AS
SELECT
    COALESCE(p.patient_id, CONCAT('P_', md5(CONCAT(p.patient_name, p.phone))) ) AS patient_id,
    p.patient_name AS patient_name,
    p.sex AS sex,
    p.age AS age,
    p.phone AS phone,
    p.birth_date AS birth_date,
    p.gender_code AS gender_code,
    p.address AS address,
    p.card_no AS card_no,
    p.create_time AS create_time,
    p.update_time AS update_time,
    CASE WHEN p.patient_id IS NULL THEN' NEW' ELSE 'EXISTING' END AS source_flag,
    'dwd_patient_dim'AS table_name,
    CURRENT_DATE AS dt
FROM ods_outpatient_registration p
WHERE p.patient_id IS NOT NULL OR (p.patient_name IS NOT NULL AND p.phone IS NOT NULL)
UNION ALL
SELECT
    COALESCE(p.patient_id, CONCAT('P_', md5(CONCAT(p.patient_name, p.phone))) ) AS patient_id,
    p.patient_name AS patient_name,
    p.sex AS sex,
    p.age AS age,
    p.phone AS phone,
    p.birth_date AS birth_date,
    p.gender_code AS gender_code,
    p.address AS address,
    p.card_no AS card_no,
    p.create_time AS create_time,
    p.update_time AS update_time,
    CASE WHEN p.patient_id IS NULL THEN 'NEW' ELSE 'EXISTING' END AS source_flag,
    'dwd_patient_dim' AS table_name,
    CURRENT_DATE AS dt
FROM ods_inpatient_fee p
WHERE p.patient_id IS NOT NULL  OR (p.patient_name IS NOT NULL AND p.phone IS NOT NULL)
;

“

✅ 目标：合并门诊与住院患者，形成统一患者视图
📌 字段说明：

• patient_id
  : 统一生成唯一ID（空则MD5拼接）
• source_flag
  : 标记来源系统
• table_name
  : 便于溯源

4.3 门诊诊疗事实表（dwd_outpatient_visit_fact）

CREATE TABLE dwd_outpatient_visit_fact AS
SELECT
    r.id AS visit_id,
    r.patient_id AS patient_id,
    r.patient_name AS patient_name,
    r.sex AS sex,
    r.age AS age,
    r.visit_date AS visit_date,
    r.dept_id AS dept_id,
    r.dept_name AS dept_name,
    r.doctor_id AS doctor_id,
    r.doctor_name AS doctor_name,
    r.register_time AS register_time,
    r.status ASstatus,
    c.total_amount AS charge_amount,
    c.charge_type AS charge_type,
    c.pay_mode AS pay_mode,
    c.operator_id AS operator_id,
    -- 补充：关联药品与检查项目
    COALESCE(
        (SELECT COUNT(*) FROM ods_outpatient_charge cc WHERE cc.patient_id = r.patient_id AND cc.visit_no = r.visit_no AND cc.charge_type = '药品'),
        0
    ) AS medicine_count,
    COALESCE(
        (SELECT COUNT(*) FROM ods_outpatient_charge cc WHERE cc.patient_id = r.patient_id AND cc.visit_no = r.visit_no AND cc.charge_type = '检查'),
        0
    ) AS exam_count,
    CURRENT_DATE AS dt
FROM ods_outpatient_registration r
LEFT JOIN (
    SELECT
        patient_id, 
        visit_no, 
        SUM(total_amount) AS total_amount,
        MAX(charge_type) AS charge_type,
        MAX(pay_mode) AS pay_mode,
        MAX(operator_id) AS operator_id
    FROM ods_outpatient_charge 
    GROUP BY patient_id, visit_no
) c ON r.patient_id = c.patient_id AND r.visit_no = c.visit_no
WHERE r.status IN ('已就诊', '已完成')
ORDER BY r.visit_date DESC;

“

✅ 功能：构建一次完整的门诊就诊事件
📌 关键点：

• 使用 visit_no
   关联收费
• 统计药品/检查次数
• 已就诊状态过滤

4.4 药品销售明细表（dwd_drug_sale_fact）

CREATE TABLE dwd_drug_sale_fact AS
SELECT
    s.sale_id,
    s.drug_id,
    s.drug_name,
    s.spec,
    s.unit,
    s.qty,
    s.price_per_unit,
    s.total_price,
    s.patient_id,
    s.prescription_id,
    s.sale_time,
    s.pharmacy_type,
    s.operator_id,
    s.batch_no,
    s.expiry_date,
    s.is_prescription,
    -- 标准化药品编码
    CASE
        WHEN s.drug_id LIKE 'Y%' THEN s.drug_id
        WHEN s.drug_id LIKE 'X%' THEN CONCAT('Y', RIGHT(s.drug_id, 6))
        ELSE 'UNKNOWN'
    END AS standard_drug_id,
    -- 获取药品分类（假设存在字典表）
    COALESCE(
        (SELECT category FROM dim_drug_dict WHERE drug_id = s.drug_id),
        'OTHER'
    ) AS drug_category,
    CURRENT_DATE AS dt
FROM ods_pharmacy_sale s
WHERE s.sale_time >= '2023-01-01'-- 时间范围限制
AND s.qty > 0;

“

✅ 目标：清洗药品销售数据，标准化编码，补充分类信息
📌 字段说明：

• standard_drug_id
  : 统一编码规则
• drug_category
  : 从字典表获取类别（如抗生素、降压药）

五、DWS 层：聚合宽表设计

DWS 层构建面向主题的宽表，支持快速查询与多维分析。

5.1 科室日维度汇总表（dws_dept_daily_summary）

CREATE TABLE dws_dept_daily_summary AS
SELECT
    DATE(register_time) AS stat_date,
    dept_id,
    dept_name,
    COUNT(DISTINCT patient_id) AS patient_count,
    SUM(charge_amount) AS total_income,
    AVG(age) AS avg_age,
    SUM(CASE WHEN charge_type = '药品'THEN 1 ELSE 0 END) AS medicine_count,
    SUM(CASE WHEN charge_type = '检查'THEN 1 ELSE 0 END) AS exam_count,
    COUNT(DISTINCT doctor_id) AS doctor_count,
    MAX(CASE WHEN status = '已就诊'THEN 1 ELSE 0 END) AS completed_rate,
    CURRENT_DATE AS dt
FROM dwd_outpatient_visit_fact
GROUP BY stat_date, dept_id, dept_name
ORDER BY stat_date DESC;

“

✅ 用途：院长查询每日科室运营情况
📌 场景：按天看哪个科室最忙？收入最高？

5.2 医生绩效宽表（dws_doctor_performance）

CREATE TABLE dws_doctor_performance AS
SELECT
    doctor_id,
    doctor_name,
    dept_id,
    dept_name,
    COUNT(DISTINCT patient_id) AS patient_count,
    SUM(charge_amount) AS income,
    AVG(charge_amount) AS avg_income_per_patient,
    COUNT(CASE WHEN charge_type = '手术'THEN 1 ELSE 0 END) AS surgery_count,
    COUNT(CASE WHEN charge_type = '检查'THEN 1 ELSE 0 END) AS exam_count,
    SUM(CASE WHEN pay_mode = '医保'THEN charge_amount ELSE 0 END) AS insurance_income,
    SUM(CASE WHEN pay_mode = '自费'THEN charge_amount ELSE 0 END) AS self_pay_income,
    CURRENT_DATE AS dt
FROM dwd_outpatient_visit_fact
GROUP BY doctor_id, doctor_name, dept_id, dept_name
ORDER BY income DESC;

“

✅ 用途：医生绩效考核、奖金分配
📌 场景：谁是“顶流医生”？谁贡献最大？

六、ADS 层：应用层报表与大屏

ADS 层面向最终用户，输出可视化所需数据。

6.1 医院总收入日报（ads_hospital_daily_revenue）

CREATE TABLE ads_hospital_daily_revenue AS
SELECT
    stat_date,
    SUM(total_income) AS total_revenue,
    SUM(insurance_income) AS insurance_revenue,
    SUM(self_pay_income) AS self_pay_revenue,
    COUNT(DISTINCT patient_id) AS total_patients,
    AVG(avg_income_per_patient) AS avg_patient_revenue,
    MAX(doctor_count) AS max_doctors_on_duty,
    CURRENT_DATE AS dt
FROM dws_dept_daily_summary
GROUP BY stat_date
ORDER BY stat_date DESC;

“

✅ 输出：数据大屏展示“今日总收入”、“医保占比”等

6.2 药品畅销排行榜（ads_top_drugs_ranking）

CREATE TABLE ads_top_drugs_ranking AS
SELECT
    drug_id,
    drug_name,
    drug_category,
    SUM(qty) AS total_qty_sold,
    SUM(total_price) AS total_revenue,
    AVG(price_per_unit) AS avg_price,
    COUNT(DISTINCT patient_id) AS unique_patients,
    RANK() OVER (ORDER BY total_revenue DESC) AS rank_num,
    CURRENT_DATE AS dt
FROM dwd_drug_sale_fact
GROUP BY drug_id, drug_name, drug_category
HAVING total_qty_sold > 100
ORDER BY total_revenue DESC
LIMIT10;

“

✅ 用途：采购决策、促销活动
📌 场景：哪些药卖得最好？是否需要备货？

七、实际案例：某三甲医院 DRG 分析项目

场景描述：

某三甲医院引入 DRG 支付改革，需分析各病种成本与收益。

数据链路：

1. 从 住院业务系统 抽取 inpatient_diagnosis
   （诊断）、inpatient_cost
   （费用）
2. 在 DWD 层关联 ICD-10 编码表，生成 dwd_case_drug_cost
   
3. 在 DWS 层按 DRG 分组（基于主要诊断），计算平均成本
4. 在 ADS 层输出“高成本低收益病种”清单

-- 示例：DRG 成本分析表
CREATE TABLE ads_drg_cost_analysis AS
SELECT
    drg_code,
    disease_name,
    COUNT(*) AS case_count,
    AVG(total_cost) AS avg_cost,
    AVG(revenue) AS avg_revenue,
    AVG(revenue - total_cost) AS profit_margin,
    CASE
        WHEN AVG(revenue - total_cost) < 0 THEN '亏损'
        WHEN AVG(revenue - total_cost) < 5000 THEN '微利'
        ELSE '盈利'
    END AS profitability_level,
    CURRENT_DATE AS dt
FROM (
    SELECT
        ic.drg_code,
        ic.disease_name,
        SUM(c.amount) AS total_cost,
        SUM(c.revenue) AS revenue
    FROM dwd_inpatient_cost c
    JOIN dim_drg_mapping ic ON c.item_code = ic.item_code
    GROUP BY ic.drg_code, ic.disease_name
) t
GROUP BY drg_code, disease_name
ORDER BY avg_cost DESC;

“

✅ 实际价值：帮助医院调整病种结构，避免“亏钱治病”

八、技术选型建议

“

✅ 推荐使用 Apache Doris 作为 ADS 层引擎，支持实时分析、高并发、OLAP 查询优化。

九、总结：告别数据孤岛的关键步骤

十、结语

从 OpenHIS 到 BI 的演进，不仅是技术升级，更是医院管理模式的变革。通过构建统一数据仓库，我们实现了：

• 打破数据孤岛：门诊、住院、药房、医保数据融合
• 提升决策效率：实时掌握收入、成本、绩效
• 支撑政策落地：DRG、医保控费、分级诊疗
• 赋能智慧医疗：为 AI 辅助诊断、慢病管理提供数据底座

“

💡 记住：没有完美的数据，只有持续迭代的模型。真正的 BI，始于数据治理，终于业务价值。

📌 附录：术语对照表

本公众号相关内容推荐

• （SCD）缓慢变化维度全解析：Type 1/2/3/4/6如何选？
• 从0到1的数据仓库建设日记：记录踩过的每一个坑和填坑方案
• 你的数据仓库真的合格吗？京东数据负责人：衡量好坏的6个维度，千万别搞错！

• 从ODS到ADS：一条SQL的数据奇幻漂流与层层加工之旅
• 别只回答“做什么”！新业务入仓，说清DWD/DWS的“建仓依据”才是加分项
• 主题域 vs 数据域：数仓设计不是重复造轮子，90%的人都理解错了！
• 数据中台建设的首要难题：如何用主题域划分破解“数据孤岛”？
• 数据仓库分层设计：ODS/DWD/DWS/ADS到底该怎么划边界？
• 为什么你的DWD层总是混乱？维度建模三件套拯救你！
• DWS层实战：宽表建模的10个经典场景！
• 宽表设计避坑指南：哪些字段该加？哪些不该加？
• ADS层设计指南：面向业务的指标聚合艺术

优惠券先到先得👇