dili-ereport.sql
7.69 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
USE dili_etrade;
DROP TABLE IF EXISTS `statistics_order_buyer`;
CREATE TABLE `statistics_order_buyer`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
`market_id` bigint NOT NULL COMMENT '市场id',
`buyer_code` varchar(20) DEFAULT NULL COMMENT '买方编号',
`buyer_id` bigint NOT NULL COMMENT '买方id',
`buyer_name` varchar(40) NOT NULL COMMENT '买方名字',
`total_count_orders` bigint NOT NULL COMMENT '总计订单总数',
`total_count_goods` bigint NOT NULL COMMENT '总计交易重量',
`total_count_amount` bigint NOT NULL COMMENT '总计采购金额',
`total_refund_amount` bigint NOT NULL COMMENT '总计交易退款',
`pay_channel` int NOT NULL COMMENT '交易渠道',
`settle_date` date NOT NULL COMMENT '结算日',
`version` int NOT NULL COMMENT '乐观锁，版本号',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modified_time` datetime NOT NULL DEFAULT (NOW()) ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `buyer_id_idx` (`buyer_id`),
KEY `market_id_idx` (`market_id`),
KEY `pay_channel_idx` (`pay_channel`),
KEY `settle_date_idx` (`settle_date`)
) ENGINE = InnoDB
AUTO_INCREMENT = 1
ROW_FORMAT = DYNAMIC COMMENT ='交易订单买方汇总统计';
DROP TABLE IF EXISTS `statistics_order_buyer_product`;
CREATE TABLE `statistics_order_buyer_product`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
`market_id` bigint DEFAULT NULL COMMENT '市场id',
`buyer_code` varchar(20) DEFAULT NULL COMMENT '买方编号',
`buyer_id` bigint NOT NULL COMMENT '买方id',
`buyer_name` varchar(40) NOT NULL COMMENT '买方名字',
`product_id` bigint NOT NULL COMMENT '商品自增id',
`product_code` varchar(60) DEFAULT NULL COMMENT '商品编码',
`product_name` varchar(40) DEFAULT NULL COMMENT '商品名称',
`count_goods` bigint NOT NULL COMMENT '交易重量',
`order_amount` bigint NOT NULL COMMENT '交易金额',
`orders` bigint NOT NULL COMMENT '商品子订单数',
`pay_channel` int DEFAULT NULL COMMENT '支付方式',
`settle_date` date DEFAULT NULL COMMENT '结算日',
`version` int NOT NULL COMMENT '乐观锁，版本号',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modified_time` datetime NOT NULL DEFAULT (NOW()) ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
KEY `buyer_id_idx` (`buyer_id`),
KEY `market_id_idx` (`market_id`),
KEY `pay_channel_idx` (`pay_channel`),
KEY `product_id_idx` (`product_id`),
KEY `settle_date_idx` (`settle_date`)
) ENGINE = InnoDB
AUTO_INCREMENT = 1
ROW_FORMAT = DYNAMIC COMMENT ='交易订单买家商品统计';
DROP TABLE IF EXISTS `statistics_order_seller`;
CREATE TABLE `statistics_order_seller`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
`market_id` bigint NOT NULL COMMENT '市场id',
`shop_id` bigint NOT NULL COMMENT '商户id',
`shop_name` varchar(70) NOT NULL COMMENT '商户名称',
`shop_custom_id` bigint NOT NULL COMMENT '商户客户id',
`total_count_orders` bigint NOT NULL COMMENT '总计订单数',
`total_count_goods` bigint NOT NULL COMMENT '总计交易重量',
`total_order_amount` bigint NOT NULL COMMENT '总计交易金额',
`total_refund_amount` bigint DEFAULT NULL COMMENT '总计交易退款',
`pay_channel` int NOT NULL COMMENT '支付方式1现金，2支付宝，3微信，4园区卡，5赊销',
`settle_date` date NOT NULL COMMENT '结算日',
`employee_id` bigint DEFAULT NULL COMMENT '店员id',
`version` int NOT NULL COMMENT '乐观锁，版本号',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modified_time` datetime NOT NULL DEFAULT (NOW()) ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
KEY `market_id_idx` (`market_id`),
KEY `pay_channel_idx` (`pay_channel`),
KEY `settle_date_idx` (`settle_date`),
KEY `shop_custom_id_idx` (`shop_custom_id`),
KEY `shop_id_idx` (`shop_id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 1
ROW_FORMAT = DYNAMIC COMMENT ='交易订单统计';
DROP TABLE IF EXISTS `statistics_order_seller_product`;
CREATE TABLE `statistics_order_seller_product`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
`market_id` bigint DEFAULT NULL COMMENT '市场id',
`shop_id` bigint DEFAULT NULL COMMENT '商户id',
`shop_name` varchar(70) DEFAULT NULL COMMENT '商户名称',
`shop_custom_id` bigint DEFAULT NULL COMMENT '商户客户id',
`product_id` bigint NOT NULL COMMENT '商品自增id',
`product_code` varchar(60) DEFAULT NULL COMMENT '商品编码',
`product_name` varchar(40) DEFAULT NULL COMMENT '商品名称',
`count_goods` bigint NOT NULL COMMENT '交易重量',
`order_amount` bigint NOT NULL COMMENT '交易金额',
`settle_date` date DEFAULT NULL COMMENT '结算日',
`employee_id` bigint DEFAULT NULL COMMENT '店员id',
`version` int NOT NULL COMMENT '乐观锁，版本号',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modified_time` datetime NOT NULL DEFAULT (NOW()) ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
KEY `market_id_idx` (`market_id`),
KEY `product_id_idx` (`product_id`),
KEY `settle_date_idx` (`settle_date`),
KEY `shop_custom_id_idx` (`shop_custom_id`),
KEY `shop_id_idx` (`shop_id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 1
ROW_FORMAT = DYNAMIC COMMENT ='交易订单统计';
DROP TABLE IF EXISTS `statistics_product`;
CREATE TABLE `statistics_product`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
`market_id` bigint NOT NULL COMMENT '市场id',
`product_id` bigint NOT NULL COMMENT '商品自增id',
`product_code` varchar(60) DEFAULT NULL COMMENT '商品编码',
`product_name` varchar(40) DEFAULT NULL COMMENT '商品名称',
`product_sales` bigint NOT NULL COMMENT '商品销量',
`product_sales_amount` bigint DEFAULT NULL COMMENT '商品销售金额',
`product_high_price` bigint DEFAULT NULL COMMENT '商品最高单价',
`product_avg_price` bigint DEFAULT NULL COMMENT '商品平均价',
`product_low_price` bigint DEFAULT NULL COMMENT '商品最低单价',
`settle_date` date NOT NULL COMMENT '结算日',
`version` int NOT NULL COMMENT '乐观锁，版本号',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modified_time` datetime NOT NULL DEFAULT (NOW()) ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
KEY `market_id_idx` (`market_id`),
KEY `product_id_idx` (`product_id`),
KEY `settle_date_idx` (`settle_date`)
) ENGINE = InnoDB
AUTO_INCREMENT = 1
ROW_FORMAT = DYNAMIC COMMENT ='商品成交价格统计';