doris-sync/sql/recreate_fact_patient_tables.sql

-- 重建患者消费宽表、诊所日志宽表、销售订单毛利统计宽表。
-- 执行顺序：先执行 backup_current_fact_tables.sql 备份旧表，再执行本文件创建新表。

CREATE TABLE `fact_patientconsumption` (
  `id` bigint NOT NULL AUTO_INCREMENT 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 "专属医助",
  `optometrist_id` int NULL COMMENT "专属视光师ID",
  `optometrist_name` varchar(500) NULL COMMENT "专属视光师",
  `first_opt_name` varchar(500) NULL COMMENT "首诊视光师",
  `first_accept_date` date 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"
);

CREATE TABLE `fact_cliniclog` (
  `id` bigint NOT NULL AUTO_INCREMENT 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 "医生助理",
  `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"
);

CREATE TABLE `fact_fee_grossprofit` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT "自增ID",
  `sync_date` date NOT NULL COMMENT "同步日期",
  `fee_incomedetail_id` int NOT NULL COMMENT "收费明细源表ID",
  `patientId` varchar(500) NULL COMMENT "病历号",
  `created` datetime NULL COMMENT "创建时间",
  `modified` datetime NULL COMMENT "修改时间",
  `month` varchar(10) NULL COMMENT "月份",
  `org_name` varchar(500) NULL COMMENT "诊所",
  `group_id` int NULL COMMENT "集团ID",
  `group_name` varchar(500) NULL COMMENT "集团名称",
  `pay_time` datetime NULL COMMENT "收/退费时间",
  `fee_time` datetime NULL COMMENT "原始购买时间",
  `clinic_id` int NULL COMMENT "挂号ID",
  `come_time` datetime NULL COMMENT "到诊时间",
  `patient_name` varchar(5000) NULL COMMENT "姓名",
  `patient_mobile` varchar(500) NULL COMMENT "患者手机",
  `patient_identity` varchar(500) NULL COMMENT "身份",
  `birthday` date NULL COMMENT "出生日期",
  `age` int NULL COMMENT "年龄",
  `source1` varchar(500) NULL COMMENT "一级来源",
  `source2` varchar(500) NULL COMMENT "二级来源",
  `inviter_name` varchar(500) NULL COMMENT "介绍人姓名",
  `inviter_phone` varchar(500) NULL COMMENT "介绍人电话",
  `first_accept_date` date NULL COMMENT "首诊日期",
  `first_distance_day` int NULL COMMENT "首诊距今天数",
  `by_user_name` varchar(500) NULL COMMENT "收银员",
  `doctor_name` varchar(500) NULL COMMENT "接诊医生",
  `opt_name` varchar(500) NULL COMMENT "接诊视光师",
  `assistant_name` varchar(500) NULL COMMENT "接诊医助",
  `pay_status` varchar(50) NULL COMMENT "交易类型",
  `order_no` varchar(500) NULL COMMENT "收费单号",
  `pk_code` varchar(500) NULL COMMENT "诊所唯一ID",
  `uuid_no` varchar(500) NULL COMMENT "集采唯一ID",
  `class_name` varchar(500) NULL COMMENT "项目分类",
  `line_name` varchar(500) NULL COMMENT "产品线",
  `fee_class` varchar(500) NULL COMMENT "收费分类",
  `name` varchar(500) NULL COMMENT "收费项目",
  `combo_name` varchar(500) NULL COMMENT "套餐名称",
  `flag_memo` varchar(500) NULL COMMENT "收费项目类型",
  `price` decimal(11,2) NULL COMMENT "单价",
  `num` int NULL COMMENT "数量",
  `unit` varchar(500) NULL COMMENT "单位",
  `sum_price` decimal(11,2) NULL COMMENT "原价",
  `should_amt` decimal(11,2) NULL COMMENT "应收金额",
  `cost_amt` decimal(11,2) NULL COMMENT "成本价",
  `profit_amt` decimal(11,2) NULL COMMENT "毛利",
  `profit_rate` varchar(50) NULL COMMENT "毛利率",
  `discount` varchar(50) NULL COMMENT "折扣比例%",
  `discount_memo` text NULL COMMENT "折扣原因",
  `coupon_name` varchar(10000) NULL COMMENT "优惠卷",
  `blq_amt` decimal(11,2) NULL COMMENT "铂林券金额",
  `hx_sum_price` decimal(11,2) NULL COMMENT "业绩原价",
  `hx_num` int NULL COMMENT "业绩数量",
  `hx_amt` decimal(11,2) NULL COMMENT "业绩应收",
  `hx_cost_price` decimal(11,2) NULL COMMENT "业绩成本",
  `pay_sum_price` decimal(11,2) NULL COMMENT "实际小计",
  `pay_num` int NULL COMMENT "实际数量",
  `pay_amt` decimal(11,2) NULL COMMENT "实际金额",
  `pay_cost_price` decimal(11,2) NULL COMMENT "实际成本",
  `pay_profit_amt` decimal(11,2) NULL COMMENT "业绩毛利",
  `pay_profit_rate` decimal(11,2) NULL COMMENT "业绩毛利率",
  `factory_name` varchar(500) NULL COMMENT "制造商",
  `brands_name` varchar(500) NULL COMMENT "品牌",
  `variety_name` varchar(500) NULL COMMENT "品种",
  `exec_time` datetime NULL COMMENT "交付时间",
  `exec_status_name` varchar(50) NULL COMMENT "交付状态",
  `order_status_name` varchar(50) NULL COMMENT "订单状态",
  `cost_flag` int NULL COMMENT "成本标识",
  `update_time` datetime NULL COMMENT "成本更新时间",
  `cost_flag2` int NULL COMMENT "成本标识2",
  `update_time2` datetime NULL COMMENT "成本更新时间2",
  `exec_status` int NULL COMMENT "交付状态",
  `flag` int NULL COMMENT "标识",
  `per_id` int NULL COMMENT "人员id",
  `order_id` int NULL COMMENT "订单id",
  `type_flag` int NULL COMMENT "类型标识",
  `order_status` int NULL COMMENT "订单状态",
  `sell_time` datetime NULL COMMENT "销售时间"
) ENGINE=OLAP
UNIQUE KEY(`id`, `sync_date`)
COMMENT '销售订单毛利统计宽表'
AUTO PARTITION BY RANGE (date_trunc(`sync_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"
);