doris-sync/需求.md

数据同步需求

• 这个项目主要是将mysql中的数据同步到doris中，以便进行数据分析
• F:\公司\BI1.0\doris-sync\jobs\grossprofit.py 销售订单毛利统计同步脚本
• 同步脚本参考src项目接口实现，这是接口 https://his.bolineyecare.com/api/census/order_grossprofit_analysis/?current_page=1&search=&org_id=&exec_flag=&name=&order_no=AC1F20260403170257450&pro_line_id=&exclude_flag=&cost_price_flag=&blq_flag=&start_day=&end_day=&start_date=2026-04-01&end_date=2026-04-30&page_size=50&fee_class=&type_flag=&discount_memo=
• src项目 url 路由 F:\公司\BI1.0\src\apps\census\urls.py url(r'^order_grossprofit_analysis/', OrderGrossprofitAnalysis_V2.as_view()), # 销售订单毛利分析

现在的问题

• 现在财务说这个doris中的数据与src项目销售订单毛利分析接口返回的数据不一样
• 你看下是不是同步的脚本F:\公司\BI1.0\doris-sync\jobs\grossprofit.py有问题
• 注意: doris中的表我统计加了， group_id与group_name参数，注意这是需求，正确的

另外两个脚本的问题

• F:\公司\BI1.0\doris-sync\jobs\clinic_log.py 诊所日志同步脚本
• F:\公司\BI1.0\doris-sync\jobs\consumption.py 患者消费统计宽表同步任务
• 诊所日志同步脚本对应的的接口同样也在src项目，https://his.bolineyecare.com/api/census/org_day_note/?org_id=&doctor_id=&optometrist_id=&content=&current_page=1&customer_manager_staff_id=&customer_optometrist_staff_id=&start_date=2026-05-23&end_date=2026-05-23&page_size=50
• 患者消费统计宽表同步任务对应的接口同样也在src项目，https://his.bolineyecare.com/api/census/clinic_pay_use/?org_id=&type=&appoint_doctor_id=&accept_doctor_id=&appoint_optometrist_id=&accept_optometrist_id=&by_user=&inviter=&content=&current_page=1&customer_manager_staff_id=&customer_optometrist_staff_id=&date_start=2026-05-24&date_end=2026-05-24&pay_time_start=&pay_time_end=&first_accept_start=&first_accept_end=&page_size=50

doris表结构

• 患者消费统计宽表 sql 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 "专属医助", `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')) (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" );;
• 诊所日志宽表 sql 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 "医生助理", `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')) (PARTITION p20250101000000 VALUES [('2025-01-01 00:00:00'), ('2025-02-01 00:00:00')), PARTITION p20250201000000 VALUES [('2025-02-01 00:00:00'), ('2025-03-01 00:00:00')), PARTITION p20250301000000 VALUES [('2025-03-01 00:00:00'), ('2025-04-01 00:00:00')), PARTITION p20250401000000 VALUES [('2025-04-01 00:00:00'), ('2025-05-01 00:00:00')), PARTITION p20250501000000 VALUES [('2025-05-01 00:00:00'), ('2025-06-01 00:00:00')), PARTITION p20250601000000 VALUES [('2025-06-01 00:00:00'), ('2025-07-01 00:00:00')), PARTITION p20250701000000 VALUES [('2025-07-01 00:00:00'), ('2025-08-01 00:00:00')), PARTITION p20250801000000 VALUES [('2025-08-01 00:00:00'), ('2025-09-01 00:00:00')), PARTITION p20250901000000 VALUES [('2025-09-01 00:00:00'), ('2025-10-01 00:00:00')), PARTITION p20251001000000 VALUES [('2025-10-01 00:00:00'), ('2025-11-01 00:00:00')), PARTITION p20251101000000 VALUES [('2025-11-01 00:00:00'), ('2025-12-01 00:00:00')), PARTITION p20251201000000 VALUES [('2025-12-01 00:00:00'), ('2026-01-01 00:00:00')), PARTITION p20260101000000 VALUES [('2026-01-01 00:00:00'), ('2026-02-01 00:00:00')), PARTITION p20260201000000 VALUES [('2026-02-01 00:00:00'), ('2026-03-01 00:00:00')), PARTITION p20260301000000 VALUES [('2026-03-01 00:00:00'), ('2026-04-01 00:00:00')), PARTITION p20260401000000 VALUES [('2026-04-01 00:00:00'), ('2026-05-01 00:00:00')), PARTITION p20260501000000 VALUES [('2026-05-01 00:00:00'), ('2026-06-01 00:00:00'))) 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" );;
• 销售订单毛利统计表结构 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" );;