doris-sync/sql/patient_exam/03_create_ads_tables.sql

-- ============================================================
-- ADS 层（应用数据层）建表语句
-- 说明：患者检查周期全量宽表，以末次取镜时间为中心点
-- ============================================================

-- 患者检查周期宽表事实表
CREATE TABLE IF NOT EXISTS fact_patient_exam_wide (
    -- 患者基本信息
    patient_id BIGINT COMMENT '患者ID',
    patient_id_str VARCHAR(50) COMMENT '病历号',
    patient_name VARCHAR(100) COMMENT '姓名',
    sex VARCHAR(10) COMMENT '性别',
    age INT COMMENT '年龄',
    birthday DATE COMMENT '生日',
    mobile VARCHAR(20) COMMENT '手机号',
    identity VARCHAR(50) COMMENT '身份证号',

    -- 商品和视光师信息
    fee_class VARCHAR(50) COMMENT '收费分类',
    customer_optometrist_name VARCHAR(100) COMMENT '专属视光师',
    customer_optometrist_org_name VARCHAR(200) COMMENT '专属视光师归属机构',
    last_pickup_time DATETIME COMMENT '末次取镜时间',
    last_pickup_date DATE COMMENT '末次取镜日期',
    last_pickup_goods VARCHAR(200) COMMENT '末次取镜商品名称',
    last_pickup_goods_id BIGINT COMMENT '末次取镜商品ID',

    -- 购买统计
    buy_cnt INT COMMENT '购买片数',
    refund_cnt INT COMMENT '退货片数',
    is_both_bs_xq TINYINT COMMENT '是否同时购买铂视控和星趣控：0=否，1=是',

    -- 基线时间
    baseline_date DATE COMMENT '基线时间',
    baseline_org_id INT COMMENT '基线检查机构ID',
    baseline_org_name VARCHAR(200) COMMENT '基线检查机构名称',

    -- 基线检查数据
    baseline_right_se_val DECIMAL(10,2) COMMENT '基线右眼SE等效球镜',
    baseline_left_se_val DECIMAL(10,2) COMMENT '基线左眼SE等效球镜',
    baseline_right_sph_val DECIMAL(10,2) COMMENT '基线右眼球镜',
    baseline_left_sph_val DECIMAL(10,2) COMMENT '基线左眼球镜',
    baseline_al_od_val DECIMAL(10,2) COMMENT '基线右眼眼轴',
    baseline_al_os_val DECIMAL(10,2) COMMENT '基线左眼眼轴',

    -- 1个月前检查数据
    m1_right_se_val DECIMAL(10,2) COMMENT '1个月前右眼SE等效球镜',
    m1_left_se_val DECIMAL(10,2) COMMENT '1个月前左眼SE等效球镜',
    m1_right_sph_val DECIMAL(10,2) COMMENT '1个月前右眼球镜',
    m1_left_sph_val DECIMAL(10,2) COMMENT '1个月前左眼球镜',
    m1_al_od_val DECIMAL(10,2) COMMENT '1个月前右眼眼轴',
    m1_al_os_val DECIMAL(10,2) COMMENT '1个月前左眼眼轴',

    -- 3个月前检查数据
    m3_right_se_val DECIMAL(10,2) COMMENT '3个月前右眼SE等效球镜',
    m3_left_se_val DECIMAL(10,2) COMMENT '3个月前左眼SE等效球镜',
    m3_right_sph_val DECIMAL(10,2) COMMENT '3个月前右眼球镜',
    m3_left_sph_val DECIMAL(10,2) COMMENT '3个月前左眼球镜',
    m3_al_od_val DECIMAL(10,2) COMMENT '3个月前右眼眼轴',
    m3_al_os_val DECIMAL(10,2) COMMENT '3个月前左眼眼轴',

    -- 6个月前检查数据
    m6_right_se_val DECIMAL(10,2) COMMENT '6个月前右眼SE等效球镜',
    m6_left_se_val DECIMAL(10,2) COMMENT '6个月前左眼SE等效球镜',
    m6_right_sph_val DECIMAL(10,2) COMMENT '6个月前右眼球镜',
    m6_left_sph_val DECIMAL(10,2) COMMENT '6个月前左眼球镜',
    m6_al_od_val DECIMAL(10,2) COMMENT '6个月前右眼眼轴',
    m6_al_os_val DECIMAL(10,2) COMMENT '6个月前左眼眼轴',

    -- 1个月后检查数据
    p1_right_se_val DECIMAL(10,2) COMMENT '1个月后右眼SE等效球镜',
    p1_left_se_val DECIMAL(10,2) COMMENT '1个月后左眼SE等效球镜',
    p1_right_sph_val DECIMAL(10,2) COMMENT '1个月后右眼球镜',
    p1_left_sph_val DECIMAL(10,2) COMMENT '1个月后左眼球镜',
    p1_al_od_val DECIMAL(10,2) COMMENT '1个月后右眼眼轴',
    p1_al_os_val DECIMAL(10,2) COMMENT '1个月后左眼眼轴',

    -- 3个月后检查数据
    p3_right_se_val DECIMAL(10,2) COMMENT '3个月后右眼SE等效球镜',
    p3_left_se_val DECIMAL(10,2) COMMENT '3个月后左眼SE等效球镜',
    p3_right_sph_val DECIMAL(10,2) COMMENT '3个月后右眼球镜',
    p3_left_sph_val DECIMAL(10,2) COMMENT '3个月后左眼球镜',
    p3_al_od_val DECIMAL(10,2) COMMENT '3个月后右眼眼轴',
    p3_al_os_val DECIMAL(10,2) COMMENT '3个月后左眼眼轴',

    -- 6个月后检查数据
    p6_right_se_val DECIMAL(10,2) COMMENT '6个月后右眼SE等效球镜',
    p6_left_se_val DECIMAL(10,2) COMMENT '6个月后左眼SE等效球镜',
    p6_right_sph_val DECIMAL(10,2) COMMENT '6个月后右眼球镜',
    p6_left_sph_val DECIMAL(10,2) COMMENT '6个月后左眼球镜',
    p6_al_od_val DECIMAL(10,2) COMMENT '6个月后右眼眼轴',
    p6_al_os_val DECIMAL(10,2) COMMENT '6个月后左眼眼轴',

    -- 9个月后检查数据
    p9_right_se_val DECIMAL(10,2) COMMENT '9个月后右眼SE等效球镜',
    p9_left_se_val DECIMAL(10,2) COMMENT '9个月后左眼SE等效球镜',
    p9_right_sph_val DECIMAL(10,2) COMMENT '9个月后右眼球镜',
    p9_left_sph_val DECIMAL(10,2) COMMENT '9个月后左眼球镜',
    p9_al_od_val DECIMAL(10,2) COMMENT '9个月后右眼眼轴',
    p9_al_os_val DECIMAL(10,2) COMMENT '9个月后左眼眼轴',

    -- 1年后检查数据
    y1_right_se_val DECIMAL(10,2) COMMENT '1年后右眼SE等效球镜',
    y1_left_se_val DECIMAL(10,2) COMMENT '1年后左眼SE等效球镜',
    y1_right_sph_val DECIMAL(10,2) COMMENT '1年后右眼球镜',
    y1_left_sph_val DECIMAL(10,2) COMMENT '1年后左眼球镜',
    y1_al_od_val DECIMAL(10,2) COMMENT '1年后右眼眼轴',
    y1_al_os_val DECIMAL(10,2) COMMENT '1年后左眼眼轴',

    -- 15个月后检查数据
    p15_right_se_val DECIMAL(10,2) COMMENT '15个月后右眼SE等效球镜',
    p15_left_se_val DECIMAL(10,2) COMMENT '15个月后左眼SE等效球镜',
    p15_right_sph_val DECIMAL(10,2) COMMENT '15个月后右眼球镜',
    p15_left_sph_val DECIMAL(10,2) COMMENT '15个月后左眼球镜',
    p15_al_od_val DECIMAL(10,2) COMMENT '15个月后右眼眼轴',
    p15_al_os_val DECIMAL(10,2) COMMENT '15个月后左眼眼轴',

    -- 18个月后检查数据
    p18_right_se_val DECIMAL(10,2) COMMENT '18个月后右眼SE等效球镜',
    p18_left_se_val DECIMAL(10,2) COMMENT '18个月后左眼SE等效球镜',
    p18_right_sph_val DECIMAL(10,2) COMMENT '18个月后右眼球镜',
    p18_left_sph_val DECIMAL(10,2) COMMENT '18个月后左眼球镜',
    p18_al_od_val DECIMAL(10,2) COMMENT '18个月后右眼眼轴',
    p18_al_os_val DECIMAL(10,2) COMMENT '18个月后左眼眼轴',

    -- 2年后检查数据
    y2_right_se_val DECIMAL(10,2) COMMENT '2年后右眼SE等效球镜',
    y2_left_se_val DECIMAL(10,2) COMMENT '2年后左眼SE等效球镜',
    y2_right_sph_val DECIMAL(10,2) COMMENT '2年后右眼球镜',
    y2_left_sph_val DECIMAL(10,2) COMMENT '2年后左眼球镜',
    y2_al_od_val DECIMAL(10,2) COMMENT '2年后右眼眼轴',
    y2_al_os_val DECIMAL(10,2) COMMENT '2年后左眼眼轴',

    -- 数据日期（用于分区和管理）
    data_date DATE COMMENT '数据日期（末次取镜日期）',

    -- 元数据
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)
UNIQUE KEY (patient_id, data_date)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 按数据日期分区（可选，根据数据量决定）
-- ALTER TABLE fact_patient_exam_wide
-- ADD PARTITION BY RANGE(`data_date`) (
--     PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
--     PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
--     ...
-- );

-- 创建常用查询的 Rollup（提升查询性能）
-- ALTER TABLE fact_patient_exam_wide
-- ADD ROLLUP recent_data (
--     patient_id, patient_name, mobile,
--     baseline_right_se_val, baseline_left_se_val,
--     m3_right_se_val, m3_left_se_val,
--     p3_right_se_val, p3_left_se_val,
--     p6_right_se_val, p6_left_se_val
-- );