doris-sync/sql/grossprofit/02_create_dwd_table.sql

-- 毛利分析DWD层宽表
CREATE TABLE IF NOT EXISTS fact_fee_grossprofit (
    `id` INT NOT NULL COMMENT "收费明细id",
    `patientId` VARCHAR(50) NULL COMMENT "病历号",
    `created` DATETIME NULL COMMENT "创建时间",
    `modified` DATETIME NULL COMMENT "修改时间",
    `month` VARCHAR(10) NULL COMMENT "月份",
    `org_name` VARCHAR(50) NULL COMMENT "诊所",
    `group_id` INT NULL COMMENT "集团id",
    `group_name` VARCHAR(100) 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(50) NULL COMMENT "姓名",
    `patient_mobile` VARCHAR(50) NULL COMMENT "患者手机",
    `patient_identity` VARCHAR(50) NULL COMMENT "身份",
    `birthday` DATE NULL COMMENT "出生日期",
    `age` INT NULL COMMENT "年龄",
    `source1` VARCHAR(50) NULL COMMENT "一级来源",
    `source2` VARCHAR(50) NULL COMMENT "二级来源",
    `inviter_name` VARCHAR(20) NULL COMMENT "介绍人姓名",
    `inviter_phone` VARCHAR(30) NULL COMMENT "介绍人电话",
    `first_accept_date` DATE NULL COMMENT "首诊日期",
    `first_distance_day` INT NULL COMMENT "首诊距今天数",
    `by_user_name` VARCHAR(100) NULL COMMENT "收银员",
    `doctor_name` VARCHAR(100) NULL COMMENT "接诊医生",
    `opt_name` VARCHAR(50) NULL COMMENT "接诊视光师",
    `assistant_name` VARCHAR(50) NULL COMMENT "接诊医助",
    `pay_status` VARCHAR(10) NULL COMMENT "交易类型",
    `order_no` VARCHAR(100) NULL COMMENT "收费单号",
    `pk_code` VARCHAR(100) NULL COMMENT "诊所唯一ID",
    `uuid_no` VARCHAR(100) NULL COMMENT "集采唯一ID",
    `class_name` VARCHAR(100) NULL COMMENT "项目分类",
    `line_name` VARCHAR(50) NULL COMMENT "产品线",
    `fee_class` VARCHAR(100) NULL COMMENT "收费分类",
    `name` VARCHAR(50) NULL COMMENT "收费项目",
    `combo_name` VARCHAR(100) NULL COMMENT "套餐名称",
    `flag_memo` VARCHAR(100) NULL COMMENT "收费项目类型",
    `price` DECIMAL(11,2) NULL COMMENT "单价",
    `num` INT NULL COMMENT "数量",
    `unit` VARCHAR(50) 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(20) NULL COMMENT "毛利率",
    `discount` VARCHAR(20) NULL COMMENT "折扣比例%",
    `discount_memo` TEXT NULL COMMENT "折扣原因",
    `coupon_name` VARCHAR(500) 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(50) NULL COMMENT "制造商",
    `brands_name` VARCHAR(50) NULL COMMENT "品牌",
    `variety_name` VARCHAR(50) NULL COMMENT "品种",
    `exec_time` DATETIME NULL COMMENT "交付时间",
    `exec_status_name` VARCHAR(10) NULL COMMENT "交付状态",
    `order_status_name` VARCHAR(10) 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 "销售时间",
    `sync_date` DATE NULL COMMENT "同步日期"
) ENGINE=OLAP
DUPLICATE KEY(`id`, `patientId`, `created`)
COMMENT "销售订单毛利统计宽表"
PARTITION BY RANGE (`sync_date`) (
    PARTITION p20190401 VALUES [("2019-04-01"), ("2019-05-01"))
)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
    "replication_num" = "1",
    "storage_medium" = "HDD",
    "enable_dynamic_partition" = "true",
    "dynamic_partition_end" = 1,
    "dynamic_partition_time_unit" = "MONTH",
    "dynamic_partition_history" = "-1"
);