356 lines
17 KiB
SQL
356 lines
17 KiB
SQL
-- ========================================
|
||
-- 本地汇总表（存储在 shujuku2_system 数据库）
|
||
-- 用于存储每日计算的汇总数据，提升查询性能
|
||
-- 执行方式: mysql --default-character-set=utf8mb4 -u root -p shujuku2_system < summary_tables.sql
|
||
-- ========================================
|
||
|
||
SET NAMES utf8mb4;
|
||
|
||
USE shujuku2_system;
|
||
|
||
-- 1. 每日核心KPI汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_kpi (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL COMMENT '统计日期',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
order_count BIGINT DEFAULT 0 COMMENT '订单数',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退款率(%)',
|
||
attribution_rate DECIMAL(6,2) DEFAULT 0 COMMENT '归因覆盖率(%)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_stat_date (stat_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='每日核心KPI汇总';
|
||
|
||
-- 2. 部门归因汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_attribution (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
dept VARCHAR(20) NOT NULL COMMENT '部门: 客服/仓库/开发/快递/供应商/采购/无归因',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
percent DECIMAL(6,2) DEFAULT 0 COMMENT '占比(%)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_dept (stat_date, dept)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门归因汇总';
|
||
|
||
-- 3. 仓库维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_warehouse (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
warehouse_name VARCHAR(100) NOT NULL COMMENT '仓库名称',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退款率(%)',
|
||
timely_rate DECIMAL(6,2) DEFAULT 0 COMMENT '发货及时率(%)',
|
||
accuracy_rate DECIMAL(6,2) DEFAULT 0 COMMENT '发货准确率(%)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_warehouse (stat_date, warehouse_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='仓库维度汇总';
|
||
|
||
-- 4. 快递公司维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_courier (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
courier_name VARCHAR(100) NOT NULL COMMENT '快递公司',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退款率(%)',
|
||
avg_delivery_hours DECIMAL(6,1) DEFAULT 0 COMMENT '平均时效(h)',
|
||
pickup_timely_rate DECIMAL(6,2) DEFAULT 0 COMMENT '24h揽收及时率(%)',
|
||
damage_count BIGINT DEFAULT 0 COMMENT '破损单量',
|
||
damage_base_count BIGINT DEFAULT 0 COMMENT '破损率分母单量',
|
||
damage_rate DECIMAL(6,2) DEFAULT 0 COMMENT '破损率(%)',
|
||
bad_review_rate DECIMAL(6,2) DEFAULT 0 COMMENT '差评率(%)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_courier (stat_date, courier_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='快递公司维度汇总';
|
||
|
||
-- 5. 省份维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_province (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
province VARCHAR(50) NOT NULL COMMENT '省份',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退款率(%)',
|
||
avg_delivery_hours DECIMAL(6,1) DEFAULT 0 COMMENT '平均时效(h)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_province (stat_date, province)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='省份维度汇总';
|
||
|
||
-- 6. 供应商维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_supplier (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
supplier_name VARCHAR(100) NOT NULL COMMENT '供应商名称',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退款率(%)',
|
||
quality_rate DECIMAL(6,2) DEFAULT 0 COMMENT '品质问题率(%)',
|
||
return_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退货率(%)',
|
||
part_rate DECIMAL(6,2) DEFAULT 0 COMMENT '配件率(%)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_supplier (stat_date, supplier_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商维度汇总';
|
||
|
||
-- 7. 客服维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_cs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
cs_name VARCHAR(100) NOT NULL COMMENT '客服名称',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '关联发货件数',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退款率(%)',
|
||
compensation_count BIGINT DEFAULT 0 COMMENT '赔付次数',
|
||
chat_issue_count BIGINT DEFAULT 0 COMMENT '聊天问题数',
|
||
refund_process_hours_total DECIMAL(12,2) DEFAULT 0 COMMENT '退款处理总时长(h)',
|
||
refund_process_count BIGINT DEFAULT 0 COMMENT '退款处理样本数',
|
||
avg_refund_process_hours DECIMAL(6,2) DEFAULT 0 COMMENT '退款处理时长均值(h)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_cs (stat_date, cs_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客服维度汇总';
|
||
|
||
-- 8. 采购员维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_purchaser (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
purchaser_name VARCHAR(100) NOT NULL COMMENT '采购员名称',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退款率(%)',
|
||
timely_rate DECIMAL(6,2) DEFAULT 0 COMMENT '供货及时率(%)',
|
||
under10_count BIGINT DEFAULT 0 COMMENT '不足10日产品数',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_purchaser (stat_date, purchaser_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='采购员维度汇总';
|
||
|
||
-- 9. 设计师维度汇总表（一、设计师总体数据）
|
||
CREATE TABLE IF NOT EXISTS summary_daily_designer (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
designer_name VARCHAR(100) NOT NULL COMMENT '产品设计师',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
ship_amount DECIMAL(14,2) DEFAULT 0 COMMENT '销售额',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退货件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退货率(%)',
|
||
real_amount DECIMAL(14,2) DEFAULT 0 COMMENT '真实业绩(销售额-售后成本)',
|
||
aftersale_cost DECIMAL(14,2) DEFAULT 0 COMMENT '售后成本(赔付+补偿+运费)',
|
||
aftersale_cost_rate DECIMAL(6,2) DEFAULT 0 COMMENT '售后成本率(%)',
|
||
part_order_count BIGINT DEFAULT 0 COMMENT '配件订单数',
|
||
part_rate DECIMAL(6,2) DEFAULT 0 COMMENT '配件率(%)',
|
||
multi_part_order_count BIGINT DEFAULT 0 COMMENT '多次配件订单数',
|
||
multi_part_rate DECIMAL(6,2) DEFAULT 0 COMMENT '多次配件率(%)',
|
||
problem_spu_count BIGINT DEFAULT 0 COMMENT '问题SPU数',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_designer (stat_date, designer_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设计师维度汇总';
|
||
|
||
-- 9.1 设计师SKU明细表（二、数据明细）
|
||
CREATE TABLE IF NOT EXISTS summary_daily_designer_sku (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
designer_name VARCHAR(100) NOT NULL COMMENT '产品设计师',
|
||
sku_id VARCHAR(100) NOT NULL COMMENT 'SKU编码',
|
||
spu VARCHAR(100) DEFAULT '' COMMENT '商品编码/SPU',
|
||
supplier_name VARCHAR(100) DEFAULT '' COMMENT '供应商',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
ship_amount DECIMAL(14,2) DEFAULT 0 COMMENT '真实销售额',
|
||
aftersale_amount DECIMAL(14,2) DEFAULT 0 COMMENT '售后金额',
|
||
aftersale_cost_rate DECIMAL(6,2) DEFAULT 0 COMMENT '售后成本率(%)',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退货件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退货率(%)',
|
||
part_count BIGINT DEFAULT 0 COMMENT '配件补发次数',
|
||
part_rate DECIMAL(6,2) DEFAULT 0 COMMENT '配件率(%)',
|
||
multi_part_rate DECIMAL(6,2) DEFAULT 0 COMMENT '多次配件率(%)',
|
||
real_amount DECIMAL(14,2) DEFAULT 0 COMMENT '真实业绩',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_designer_sku (stat_date, designer_name, sku_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设计师SKU明细表';
|
||
|
||
-- 9.2 设计师配件明细表（三、配件明细数据）
|
||
CREATE TABLE IF NOT EXISTS summary_daily_designer_part (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
designer_name VARCHAR(100) NOT NULL COMMENT '产品设计师',
|
||
supplier_name VARCHAR(100) DEFAULT '' COMMENT '供应商',
|
||
sku_id VARCHAR(100) NOT NULL COMMENT 'SKU编码',
|
||
spu VARCHAR(100) DEFAULT '' COMMENT '商品编码/SPU',
|
||
part_sku_id VARCHAR(100) DEFAULT '' COMMENT '配件编码',
|
||
part_status VARCHAR(50) DEFAULT '' COMMENT '状态',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
part_count BIGINT DEFAULT 0 COMMENT '补发次数',
|
||
part_rate DECIMAL(6,2) DEFAULT 0 COMMENT '配件率(%)',
|
||
multi_part_rate DECIMAL(6,2) DEFAULT 0 COMMENT '多次配件率(%)',
|
||
category VARCHAR(100) DEFAULT '' COMMENT '类目',
|
||
material VARCHAR(100) DEFAULT '' COMMENT '材质',
|
||
weight DECIMAL(10,2) DEFAULT 0 COMMENT '重量(kg)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_designer_part (stat_date, designer_name, sku_id, part_sku_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设计师配件明细表';
|
||
|
||
-- 10. SKU问题汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_sku (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
sku_id VARCHAR(100) NOT NULL COMMENT 'SKU编码',
|
||
sku_name VARCHAR(255) COMMENT '商品名称',
|
||
spu VARCHAR(100) COMMENT 'SPU编码',
|
||
ship_count BIGINT DEFAULT 0 COMMENT '发货件数',
|
||
refund_count BIGINT DEFAULT 0 COMMENT '退款件数',
|
||
refund_rate DECIMAL(6,2) DEFAULT 0 COMMENT '退款率(%)',
|
||
problem_type VARCHAR(100) COMMENT '主要问题类型',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_sku (stat_date, sku_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SKU问题汇总';
|
||
|
||
-- 11. 聊天问题汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_chat_issue (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
main_reason VARCHAR(100) NOT NULL COMMENT '一级问题',
|
||
second_reason VARCHAR(100) DEFAULT '' COMMENT '二级问题',
|
||
issue_count BIGINT DEFAULT 0 COMMENT '问题数量',
|
||
percent DECIMAL(6,2) DEFAULT 0 COMMENT '占比(%)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_reason (stat_date, main_reason, second_reason)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='聊天问题汇总';
|
||
|
||
-- 12. 月度汇总表（趋势图用）
|
||
CREATE TABLE IF NOT EXISTS summary_monthly_kpi (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_month VARCHAR(7) NOT NULL COMMENT '统计月份(YYYY-MM)',
|
||
ship_count BIGINT DEFAULT 0,
|
||
order_count BIGINT DEFAULT 0,
|
||
refund_count BIGINT DEFAULT 0,
|
||
refund_rate DECIMAL(6,2) DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_stat_month (stat_month)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='月度KPI汇总';
|
||
|
||
-- 13. 数据刷新日志表
|
||
CREATE TABLE IF NOT EXISTS summary_refresh_log (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL COMMENT '统计日期',
|
||
start_time DATETIME NOT NULL COMMENT '开始时间',
|
||
end_time DATETIME COMMENT '结束时间',
|
||
status ENUM('running', 'success', 'failed') DEFAULT 'running' COMMENT '状态',
|
||
duration_ms INT COMMENT '耗时(毫秒)',
|
||
error_message TEXT COMMENT '错误信息',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_stat_date (stat_date),
|
||
INDEX idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据刷新日志';
|
||
|
||
-- 13.5 物流到货时长日度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_delivery (
|
||
stat_date DATE NOT NULL COMMENT '统计日期',
|
||
total_order_count BIGINT DEFAULT 0 COMMENT '签收订单数',
|
||
timely_count BIGINT DEFAULT 0 COMMENT '48h及时签收订单数',
|
||
timely_rate DECIMAL(6,2) DEFAULT 0 COMMENT '48h及时送达率(%)',
|
||
total_delivery_hours DECIMAL(12,2) DEFAULT 0 COMMENT '总签收时长(h)',
|
||
avg_delivery_hours DECIMAL(8,2) DEFAULT 0 COMMENT '平均物流时长(h)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (stat_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物流到货时长日度汇总';
|
||
|
||
-- 13.6 省份物流时效日度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_region_delivery (
|
||
stat_date DATE NOT NULL COMMENT '统计日期',
|
||
province VARCHAR(32) NOT NULL COMMENT '省份',
|
||
total_order_count BIGINT DEFAULT 0 COMMENT '签收订单数',
|
||
total_delivery_hours DECIMAL(12,2) DEFAULT 0 COMMENT '总签收时长(h)',
|
||
avg_delivery_hours DECIMAL(6,1) DEFAULT 0 COMMENT '平均物流时长(h)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (stat_date, province)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='省份物流时效日度汇总';
|
||
|
||
-- 14. 揽收日度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_pickup (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL COMMENT '统计日期',
|
||
total_order_count BIGINT DEFAULT 0 COMMENT '应揽收订单数',
|
||
timeout_count BIGINT DEFAULT 0 COMMENT '超时揽收订单数',
|
||
pickup_rate DECIMAL(6,2) DEFAULT 0 COMMENT '24h揽收及时率(%)',
|
||
avg_pickup_hours DECIMAL(6,1) DEFAULT 0 COMMENT '平均揽收时长(h)',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_stat_date (stat_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='揽收日度汇总';
|
||
|
||
-- 15. 揽收-仓库维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_pickup_warehouse (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
warehouse_name VARCHAR(100) NOT NULL COMMENT '仓库名称',
|
||
total_order_count BIGINT DEFAULT 0,
|
||
timeout_count BIGINT DEFAULT 0,
|
||
pickup_rate DECIMAL(6,2) DEFAULT 0,
|
||
avg_pickup_hours DECIMAL(6,1) DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_warehouse (stat_date, warehouse_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='揽收-仓库维度汇总';
|
||
|
||
-- 16. 揽收-供应商维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_pickup_supplier (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
supplier_name VARCHAR(100) NOT NULL COMMENT '供应商名称',
|
||
total_order_count BIGINT DEFAULT 0,
|
||
timeout_count BIGINT DEFAULT 0,
|
||
pickup_rate DECIMAL(6,2) DEFAULT 0,
|
||
avg_pickup_hours DECIMAL(6,1) DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_supplier (stat_date, supplier_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='揽收-供应商维度汇总';
|
||
|
||
-- 17. 揽收-区域维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_pickup_region (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
province VARCHAR(50) NOT NULL COMMENT '收货省份',
|
||
total_order_count BIGINT DEFAULT 0,
|
||
timeout_count BIGINT DEFAULT 0,
|
||
pickup_rate DECIMAL(6,2) DEFAULT 0,
|
||
avg_pickup_hours DECIMAL(6,1) DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_province (stat_date, province)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='揽收-区域维度汇总';
|
||
|
||
-- 18. 揽收-快递公司维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_pickup_courier (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
courier_name VARCHAR(100) NOT NULL COMMENT '快递公司',
|
||
total_order_count BIGINT DEFAULT 0,
|
||
timeout_count BIGINT DEFAULT 0,
|
||
pickup_rate DECIMAL(6,2) DEFAULT 0,
|
||
avg_pickup_hours DECIMAL(6,1) DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_courier (stat_date, courier_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='揽收-快递公司维度汇总';
|
||
|
||
-- 19. 揽收-区域&快递组合维度汇总表
|
||
CREATE TABLE IF NOT EXISTS summary_daily_pickup_region_courier (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
stat_date DATE NOT NULL,
|
||
province VARCHAR(50) NOT NULL COMMENT '收货省份',
|
||
city VARCHAR(50) DEFAULT '' COMMENT '收货城市',
|
||
district VARCHAR(50) DEFAULT '' COMMENT '收货区/县/街道',
|
||
courier_name VARCHAR(100) NOT NULL COMMENT '快递公司',
|
||
total_order_count BIGINT DEFAULT 0,
|
||
timeout_count BIGINT DEFAULT 0,
|
||
pickup_rate DECIMAL(6,2) DEFAULT 0,
|
||
avg_pickup_hours DECIMAL(6,1) DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_date_region_courier (stat_date, province, city, district, courier_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='揽收-区域&快递组合维度汇总';
|
||
|
||
-- 显示创建结果
|
||
SELECT TABLE_NAME, TABLE_COMMENT
|
||
FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = 'shujuku2_system'
|
||
AND TABLE_NAME LIKE 'summary_%'
|
||
ORDER BY TABLE_NAME;
|