-- ============================================================ -- 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 -- );