doris-sync/test.sql

CREATE TABLE `fact_fee_grossprofit` (
  `id` bigint NOT NULL AUTO_INCREMENT(1) 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'))
(PARTITION p20250101000000 VALUES [('2025-01-01'), ('2025-02-01')),
PARTITION p20250201000000 VALUES [('2025-02-01'), ('2025-03-01')),
PARTITION p20250301000000 VALUES [('2025-03-01'), ('2025-04-01')),
PARTITION p20250401000000 VALUES [('2025-04-01'), ('2025-05-01')),
PARTITION p20250501000000 VALUES [('2025-05-01'), ('2025-06-01')),
PARTITION p20250601000000 VALUES [('2025-06-01'), ('2025-07-01')),
PARTITION p20250701000000 VALUES [('2025-07-01'), ('2025-08-01')),
PARTITION p20250801000000 VALUES [('2025-08-01'), ('2025-09-01')),
PARTITION p20250901000000 VALUES [('2025-09-01'), ('2025-10-01')),
PARTITION p20251001000000 VALUES [('2025-10-01'), ('2025-11-01')),
PARTITION p20251101000000 VALUES [('2025-11-01'), ('2025-12-01')),
PARTITION p20251201000000 VALUES [('2025-12-01'), ('2026-01-01')),
PARTITION p20260101000000 VALUES [('2026-01-01'), ('2026-02-01')),
PARTITION p20260201000000 VALUES [('2026-02-01'), ('2026-03-01')),
PARTITION p20260301000000 VALUES [('2026-03-01'), ('2026-04-01')),
PARTITION p20260401000000 VALUES [('2026-04-01'), ('2026-05-01')),
PARTITION p20260501000000 VALUES [('2026-05-01'), ('2026-06-01')))
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"
);;