doris-sync/recreate_tables.sql

-- 1. 重建诊所日志宽表 (fact_cliniclog)
DROP TABLE IF EXISTS `fact_cliniclog`;
CREATE TABLE `fact_cliniclog` (
  `id` bigint NOT NULL AUTO_INCREMENT(1) COMMENT "自增ID",
  `come_time` datetime NOT NULL COMMENT "接诊时间",
  `clinic_id` bigint NOT NULL COMMENT "挂号ID",
  `patientId` varchar(500) NULL COMMENT "病例号",
  `patient_name` varchar(5000) NULL COMMENT "姓名",
  `sex` tinyint NULL COMMENT "性别 1男2女",
  `birthday` date NULL COMMENT "出生日期",
  `identity` varchar(500) NULL COMMENT "患者身份",
  `mobile` varchar(500) NULL COMMENT "患者手机号",
  `source1` varchar(10000) NULL COMMENT "患者来源1",
  `source2` varchar(10000) NULL COMMENT "患者来源2",
  `tag_list` varchar(10000) NULL COMMENT "标签列表",
  `patient_type` varchar(500) NULL COMMENT "客户类型",
  `visit_type` tinyint NULL COMMENT "接诊类型 1初诊2复诊3预约4复查",
  `r_sph` varchar(500) NULL COMMENT "右眼球镜",
  `l_sph` varchar(500) NULL COMMENT "左眼球镜",
  `r_nakesight` varchar(500) NULL COMMENT "右眼远视力",
  `l_nakesight` varchar(500) NULL COMMENT "左眼远视力",
  `org_id` int NULL COMMENT "诊所ID",
  `org_name` varchar(500) NULL COMMENT "诊所名称",
  `appoint_time` datetime NULL COMMENT "预约时间",
  `add_user` varchar(500) NULL COMMENT "预约创建人",
  `group_id` int NULL COMMENT "集团ID",
  `group_name` varchar(500) NULL COMMENT "集团名称",
  `matter` varchar(10000) NULL COMMENT "预约就诊事项",
  `accept_doctor_id` int NULL COMMENT "接诊医生ID",
  `accept_doctor_name` varchar(500) NULL COMMENT "接诊医生",
  `accept_optometrist_id` int NULL COMMENT "接诊视光师ID",
  `accept_optometrist_name` varchar(500) NULL COMMENT "接诊视光师",
  `accept_assistant_id` int NULL COMMENT "医生助理ID",
  `accept_assistant_name` varchar(500) NULL COMMENT "医生助理",
  `manager_id` int NULL COMMENT "专属助理ID",
  `manager_name` varchar(500) NULL COMMENT "专属助理",
  `manager_qywx_id` varchar(500) NULL COMMENT "专属助理企微ID",
  `manager_mobile` varchar(500) NULL COMMENT "专属助理手机号",
  `optometrist_id` int NULL COMMENT "专属视光师ID",
  `optometrist_name` varchar(500) NULL COMMENT "专属视光师",
  `optometrist_qywx_id` varchar(500) NULL COMMENT "专属视光师企微ID",
  `optometrist_mobile` varchar(500) NULL COMMENT "专属视光师手机号",
  `this_come_number` int NULL COMMENT "当前就诊次数",
  `inviter` varchar(500) NULL COMMENT "推荐人",
  `inviter_phone` varchar(500) NULL COMMENT "推荐人手机号",
  `channel` varchar(500) NULL COMMENT "渠道",
  `collect_date` date NULL COMMENT "收集日期",
  `activity_location` varchar(10000) NULL COMMENT "收集点位",
  `extension_user` varchar(500) NULL COMMENT "推广人员",
  `extension_user_mobile` varchar(500) NULL COMMENT "推广人员手机号",
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间",
  INDEX idx_patientId (`patientId`) USING INVERTED,
  INDEX idx_mobile (`mobile`) USING INVERTED,
  INDEX idx_come_time (`come_time`) USING INVERTED,
  INDEX idx_org_id (`org_id`) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(`id`, `come_time`)
COMMENT '诊所日志宽表'
AUTO PARTITION BY RANGE (date_trunc(`come_time`, 'month'))
()
DISTRIBUTED BY HASH(`id`) BUCKETS 32
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V3",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

-- 2. 重建患者消费统计宽表 (fact_patientconsumption)
DROP TABLE IF EXISTS `fact_patientconsumption`;
CREATE TABLE `fact_patientconsumption` (
  `id` bigint NOT NULL AUTO_INCREMENT(1) COMMENT "自增ID",
  `pay_date` date NOT NULL COMMENT "收费日期",
  `clinic_id` bigint NOT NULL COMMENT "挂号ID",
  `patientId` varchar(500) NULL COMMENT "病例号",
  `patient_name` varchar(5000) NULL COMMENT "姓名",
  `mobile` varchar(500) NULL COMMENT "手机号",
  `sex` tinyint NULL COMMENT "性别 1男2女",
  `birthday` date NULL COMMENT "出生日期",
  `identity` varchar(500) NULL COMMENT "身份",
  `come_number` int NULL COMMENT "到诊次数 1新客户>1老客户",
  `inviter_name` varchar(500) NULL COMMENT "推荐人",
  `come_time` datetime NULL COMMENT "就诊时间",
  `come_date` date NULL COMMENT "就诊日期",
  `type` tinyint NULL COMMENT "就诊类型 1初诊2复诊3预约4复查",
  `matter` varchar(10000) NULL COMMENT "就诊事项",
  `org_id` int NULL COMMENT "诊所ID",
  `org_name` varchar(500) NULL COMMENT "诊所名称",
  `group_id` int NULL COMMENT "集团ID",
  `group_name` varchar(500) NULL COMMENT "集团名称",
  `apt_doctor_id` int NULL COMMENT "预约医生ID",
  `apt_doctor_name` varchar(500) NULL COMMENT "预约医生",
  `apt_optometrist_id` int NULL COMMENT "预约视光师ID",
  `apt_optometrist_name` varchar(500) NULL COMMENT "预约视光师",
  `act_doctor_id` int NULL COMMENT "接诊医生ID",
  `act_doctor_name` varchar(500) NULL COMMENT "接诊医生",
  `act_optometrist_id` int NULL COMMENT "接诊视光师ID",
  `act_optometrist_name` varchar(500) NULL COMMENT "接诊视光师",
  `act_assistant_name` varchar(500) NULL COMMENT "接诊医助",
  `source1` varchar(10000) NULL COMMENT "来源1",
  `source2` varchar(10000) NULL COMMENT "来源2",
  `manager_id` int NULL COMMENT "专属医助ID",
  `manager_name` varchar(500) NULL COMMENT "专属医助",
  `manager_qywx_id` varchar(500) NULL COMMENT "专属医助企微ID",
  `manager_mobile` varchar(500) NULL COMMENT "专属医助手机号",
  `optometrist_id` int NULL COMMENT "专属视光师ID",
  `optometrist_name` varchar(500) NULL COMMENT "专属视光师",
  `optometrist_qywx_id` varchar(500) NULL COMMENT "专属视光师企微ID",
  `optometrist_mobile` varchar(500) NULL COMMENT "专属视光师手机号",
  `first_opt_name` varchar(500) NULL COMMENT "首诊视光师",
  `first_accept_date` date NULL COMMENT "首次接诊日期",
  `r_sph` varchar(500) NULL COMMENT "右眼球镜",
  `l_sph` varchar(500) NULL COMMENT "左眼球镜",
  `r_nakeye` varchar(500) NULL COMMENT "右眼裸眼视力",
  `l_nakeye` varchar(500) NULL COMMENT "左眼裸眼视力",
  `al_od` varchar(500) NULL COMMENT "右眼眼轴",
  `al_os` varchar(500) NULL COMMENT "左眼眼轴",
  `not_transaction_reason` varchar(10000) NULL COMMENT "未成交原因",
  `total_amt` decimal(15,2) NULL DEFAULT "0" COMMENT "金额合计",
  `pay_time` datetime NULL COMMENT "收费时间",
  `by_user_id` int NULL COMMENT "收银员ID",
  `by_user_name` varchar(500) NULL COMMENT "收银员",
  `coupon_name` varchar(10000) NULL COMMENT "优惠券",
  `add_manager` varchar(10) NULL COMMENT "加客户经理好友",
  `sign_wxapp` varchar(10) NULL COMMENT "注册小程序",
  `collect_date` date NULL COMMENT "收集日期",
  `activity_location` varchar(10000) NULL COMMENT "活动地点",
  `extension_user_id` int NULL COMMENT "推广专员ID",
  `extension_user_name` varchar(500) NULL COMMENT "推广专员",
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间",
  INDEX idx_mobile (`mobile`) USING INVERTED,
  INDEX idx_patient_id (`patientId`) USING INVERTED,
  INDEX idx_org_id (`org_id`) USING INVERTED,
  INDEX idx_come_time (`come_time`) USING INVERTED,
  INDEX idx_pay_date (`pay_date`) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(`id`, `pay_date`)
COMMENT '患者消费统计宽表'
AUTO PARTITION BY RANGE (date_trunc(`pay_date`, 'month'))
()
DISTRIBUTED BY HASH(`id`) BUCKETS 32
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V3",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);